Password management

[davissp14]

We need to provide some basic tooling surrounding password management. We have a decent process in place for seeding the initial credentials, but the process for managing these credentials long-term is still a little hairy.

I think one simple thing we could do is provide an fly postgres changepassword <user> <password> command that streamlines the change password process for users and ensures it's performed in a safe manor.

One big question is whether or not it makes sense to provide everyone superuser privileges, by default?

When a new PG cluster is created, users are required to specify their credentials via secrets. Once the provision has completed, users are supplied with a postgres user with superuser privileges. The problem is that once users connect, they have the ability to delete users and change passwords via the ALTER USER command. If this is used to change the flypgadmin, repluser or the postgres user credentials, it will create a disconnect between the supplied secrets and the actual credentials used by PG. This could inadvertently bring down their cluster.

We have quite a few different routes we could potentially take here:

1. We provide superuser privileges and accept the possibility that people inadvertently break their cluster.
2. We provide a normal user by default, which has privileges to create databases, roles, login, etc. and document how to access the superuser if required for their use-case.
3. We restrict PG clusters to normal users and superusers are reserved for internal administration.
4. ?

I would love peoples thoughts on this.

[mrkurt]

For just the SU password, I'm ok with ignoring this until it becomes a problem. People can poke around and f'up their clusters, that's totally fine and I bet it's pretty rare. We should definitely give everyone admin permissions! Reduced permissions don't make a lot of sense (they can SSH to their Postgres and do what they want already)

More generally, though, giving people easy ways to manage DB users, create read only users, is useful.

This gets complicated because of linked apps. It would be nice to be able to rotate DB permissions for linked apps, but we'd need to add a new user, update the linked app secret, let it deploy, then remove the old user to make it work.

[davissp14]

This gets complicated because of linked apps. It would be nice to be able to rotate DB permissions for linked apps, but we'd need to add a new user, update the linked app secret, let it deploy, then remove the old user to make it work.

Yeah... I think the general procedure seems straight forward, however, monitoring the entire process and knowing when it's actually safe to yank the original credentials seems a bit harder.

[tv42]

We provide a normal user by default, which has privileges to create databases, roles, login, etc. and document how to access the superuser if required for their use-case.

If that user account can create roles etc, what's the difference to being superuser?

We restrict PG clusters to normal users and superusers are reserved for internal administration.

I for one create roles and update their passwords as part of my schema migration mechanism. You can't take away that power, or your PG app becomes less useful.

Perhaps a thing to consider instead is, what's the route to recovery if and when someone does change one of the passwords used by fly infrastructure. Can the user obtain the "supposed value" and set it back to that (are the secrets visible anywhere)? If you don't want to make them visible, you could detect a "bad password" error from Postgres and guide the user to an ALTER ROLE statement that has the SCRAM form of the password...

Now, should flyctl postgres connect default to superuser? Maybe not. But is there a solid answer what account it should use? Should each person running flyctl get their own PG role by default? And also, beyond the existence of flyctl postgres connect --user, is this really Fly's problem?

[davissp14]

This issue is quite old and hasn't really been thought about since. :D. Some password rotation tooling could still be useful, but it's certainly not high on our priority list.

If that user account can create roles etc, what's the difference to being superuser?

In short, superuser bypasses all permission checks.

I for one create roles and update their passwords as part of my schema migration mechanism. You can't take away that power, or your PG app becomes less useful.

We have no intention of forcing a particular process for app specific credentials. There are three different credentials outlined in the README that need to be handled in a specific manor, but you're free to manage other roles/passwords however you see fit. We provide PG utility commands in an effort to make life easier, but users are by no means bound to the tooling we provide.

Perhaps a thing to consider instead is, what's the route to recovery if and when someone does change one of the passwords used by fly infrastructure. Can the user obtain the "supposed value" and set it back to that (are the secrets visible anywhere)? If you don't want to make them visible, you could detect a "bad password" error from Postgres and guide the user to an ALTER ROLE statement that has the SCRAM form of the password...

The credentials outlined within the README can be viewed within the PG environment and are pretty easy to correct. The secret and the associated password within PG just need to match.

Now, should flyctl postgres connect default to superuser? Maybe not. But is there a solid answer what account it should use? Should each person running flyctl get their own PG role by default? And also, beyond the existence of flyctl postgres connect --user, is this really Fly's problem?

We started out having a non-superuser as the default, but it ended up causing too much friction for some users based on their use-case. Users just want things to work and typically don't want to spend too much time fiddling with permissions.