PostgreSQL is one of Vault's database secrets engine's supported plugins. This plugin generates database credentials for the PostgreSQL database dynamically/statically depending on the defined roles. This article aims to list the minimal permissions for creating a custom role to be associated with the user/role specified while configuring the PostgreSQL DB secrets engine in Vault.
Please note that this article is tested on Vault Enterprise v1.15.4+ent with Vault and PostgreSQL running on two different VMs.
Solution
In this section, we'll look at the way to configure the minimum permission required for the user/role at the database level, and to begin with, we'll create a user called admin, and set a password for it to be used in Vault. Please note that the activity is being performed using the default user i.e., postgres that was created during the first installation and has the SUPERUSER privileges.
:~$ sudo -u postgres psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.
postgres=# CREATE USER admin;
postgres=# ALTER ROLE admin WITH PASSWORD 'admin';# Check the user created, and see it has not any attributes assigned yet.
postgres=# \du+
List of roles
Role name | Attributes | Description
-----------------------------------------------------+-----------------------------------+
admin | |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
Now, we will add the necessary minimum permissions to the role admin to achieve the use case:
postgres=# ALTER ROLE "admin" WITH LOGIN;
postgres=# ALTER ROLE "admin" WITH NOSUPERUSER;
postgres=# ALTER ROLE "admin" WITH CREATEROLE;# Check the updated user attributes.
postgres=# \du+
List of roles
Role name | Attributes | Description
-----------------------------------------------------+-----------------------------+
admin | Create role |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
Time to read the config set for the Vault with only the required details and generate a dynamic/static cred with the DB user we created.
:~$ vault read database/config/postgres
Key Value
--- -----
allowed_roles [*]
connection_details map[backend:database connection_url:postgresql://{{username}}:{{password}}@192.168.0.175:5432/postgres max_connection_lifetime:0s max_idle_connections:0 max_open_connections:4 username:admin]
password_policy n/a
plugin_name postgresql-database-plugin
plugin_version n/a
root_credentials_rotate_statements []
:~$ vault read database/roles/postgres-dynamic
Key Value
--- -----
creation_statements [CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";]
credential_type password
db_name postgres
default_ttl 1h
max_ttl 24h
renew_statements []
revocation_statements []
rollback_statements []
:~$ vault read database/creds/postgres-dynamic
Key Value
--- -----
lease_id database/creds/postgres-dynamic/G6qkDn4dFPKKpUFpyFjQLyIN
lease_duration 1h
lease_renewable true
password VotSlrggLZi-YDa77Gfo
username v-root-postgres-cJ9U2xHBDzuR92M3TlyW-1708184959
# Please note that in the absence of 'username_template', default template is used.
postgres=# \du+
List of roles
Role name | Attributes | Description
-----------------------------------------------------+------------------------------------------------------------+-----------
admin | Create role |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
v-root-postgres-cJ9U2xHBDzuR92M3TlyW-1708184959 | Password valid until 2024-02-17 16:49:24+00 |
:~$ psql -h localhost -d postgres -U v-root-postgres-cJ9U2xHBDzuR92M3TlyW-1708184959
Password for user v-root-postgres-cJ9U2xHBDzuR92M3TlyW-1708184959: <passed the password generated via previous commmand>
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=> \conninfo
You are connected to database "postgres" as user "v-root-postgres-cJ9U2xHBDzuR92M3TlyW-1708184959" on host "localhost" (address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
:~$ vault read database/static-roles/postgres-static
Key Value
--- -----
credential_type password
db_name postgres
last_vault_rotation 2024-02-17T15:58:09.685505097Z
rotation_period 24h
rotation_statements []
username admin
:~$ vault read database/static-creds/postgres-static
Key Value
--- -----
last_vault_rotation 2024-02-17T15:58:09.685505097Z
password lOg2UsI3p-VEOK5Pejam
rotation_period 24h
ttl 23h57m10s
username admin
:~$ psql -h localhost -d postgres -U admin
Password for user admin: <passed the password generated via previous commmand>
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=> \conninfo
You are connected to database "postgres" as user "admin" on host "localhost" (address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Important
By default, PostgreSQL provides "GRANT USAGE ON SCHEMA public" to all the new users that are getting created, very similar to what my article is doing while generating a dynamic cred using "GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";".
But in the actual live systems, and obviously due to security concerns organizations generally "REVOKE" all the default privileges on public schema and add only the required DB permissions/grants one by one as and when required.
This could be a probable reason for the Vault user (admin) having issues while connecting to the DB.
Granting the "USAGE" on schema public to the Vault user for the DB is all it needs as part of the extra permissions so it can at least verify the connection. You can run "REVOKE ALL PRIVILEGES ON SCHEMA public FROM public" and try executing the command to see if things start working or not.
References