Database root credentials rotation and Vault users for PostGres management

Vault
1

Hello,

I’m managing several PostGres instances, each of them containing several databases.
We are using LDAP for authentication, and we plan to use it through Vault to allow for temporary access to people on our LDAP server to the PostGres instances.

So a user connect to Vault with his LDAP creds, and then read on the proper database endpoints for Vault to generate credentials for him on the concerned database.
Everything works fine so far.

The problems comes when I want to use the root rotation feature that comes with the Database Secret Engine.
From what I understand of the Secret Engine, I have to create one Database Configuration per database, and not per PostGres instance (vault write database/config/my-postgresql-database).
If I have database A and B on the same instance, I’ll have 2 databases configuration in my Secret Engine.
The thing is, as they’re on the same instance, they use the same set of credentials (it’s a user dedicated for Vault as recommended in the doc’).

And, when I rotate the root creds for database A, everything is working fine.
But then, when I want to generate creds for database B, it’s not working anymore, because the creds for the vault user have been rotated with database A.

The only workaround I can think of, if I want to keep the rotation feature, is to have one vault user per database on each PostGres instance, for the creds to be isolated from each other.

Is this the only way? Or am I missing something here?

Thanks.

2

Don’t believe this is the case. You configure the Postgres server with a user that can manage the databases you want to get creds for (with the pseudo “root” user), then you configure each database/finer-grain-cred by adding a role

3

The thing is, the CREATE STATEMENT of the role you’re using is related to your configuration.
And thus, executed in the database you specified in the configuration.

For PostGres, if you want to GRANT permissions on a database and it’s objects, you need to be connected to the concerned database and use the GRANT query.

I don’t know any way to connect to a default database and GRANT permissions on other databases from there, unfortunately :frowning:

4

Not sure that is correct. You connect to the server, then access/run cmds on the database.
A single user can have permissions to multiple databases.
Have you tried this and can’t get it working?

5

Yep, if I don’t specify a database, it’ll try to connect to a database named after my user.
So if my user is vault and I try to configure a “database-less” connection on vault, it’ll try to connect to the database vault with user vault.

If the database exists, well, you’ll be able to grant permissions on objects of this database.
AFAIK, that’s it.

Tools like psql seems to disconnect and reconnect you whenever you use \c MyDB to switch the current database, meaning your connection can only be on one DB at a time.
I looked into it a bit before posting here.

But I’m no postgres expert, I might be missing something.