Role-based access control for Amazon Aurora PostgreSQL with Vault

Amazon Web Services (AWS) provides two managed PostgreSQL options: Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora PostgreSQL.

With PostgreSQL, you can create users and roles with granular access permissions. Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default.

Vault Database Secrets Engine

PostgreSQL is one of the supported plugins for the database secrets engine. This plugin generates database credentials dynamically based on configured roles for the PostgreSQL database.

Enable the database secrets engine on the Vault server to start using postgresql-database-plugin.

$ vault secrets enable database Success! Enabled the database secrets engine at: database/

Enforcing least privilege

Permissions must be granted at the database, schema, and schema object level. Use the master user to create roles per application or use case, like readonly and readwrite.

The first step is to create a new read-only role named ro.

CREATE ROLE ro;

Grant this role permission to connect to your database named db.

GRANT CONNECT ON DATABASE db TO ro;

Grant this role usage access to your schema named schema.

GRANT USAGE ON SCHEMA schema TO ro;

Grant the ro role access to run select on all the tables and views in the schema.

GRANT SELECT ON ALL TABLES IN SCHEMA schema TO ro;

Grant permission below to ensure that new tables and views are also accessible automatically.

ALTER DEFAULT PRIVILEGES IN SCHEMA schema GRANT SELECT ON TABLES TO ro;

Configure Vault with the proper plugin and Amazon Aurora PostgreSQL connection information.

$ vault write database/config/db \     
plugin_name=postgresql-database-plugin \     
allowed_roles="ro" \     
connection_url="postgresql://{{username}}:{{password}}@<clusterName>.<region>.rds.amazonaws.com:5432/db" \
username="vaultuser" \
password="vaultpass!"

Configure a role that maps a name in Vault to an SQL statement to execute to create the database credential.

$ vault write database/roles/ro \
db_name=db \
creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT ro TO \"{{name}}\";" \     
default_ttl="1h" \     
max_ttl="24h"
Success! Data written to: database/roles/ro

This role generates a credential for a user that only needs to read data from the db database.

Usage

Generate a new credential by reading from the /creds endpoint with the name of the role.

$ vault read database/creds/ro
Key                Value 
---                ----- 
lease_id           database/creds/ro/2f6a614c-4aa2-7b19-24b9-ad944a8d4de6 
lease_duration     1h 
lease_renewable    true 
password           SsnoaA-8Tv4t34f41baD 
username           v-vaultuse-ro-x

You can connect and authenticate to your cluster using the username and password with the following CLI command.

psql -h <clusterName>.<region>.rds.amazonaws.com -p 5432 -d db -U v-vaultuse-ro-x

Because you are authenticated with the read-only role, you can read data using SELECT statement.

select * from <schema>.<table> LIMIT 3;

However, you are not authorized to use other statements such as INSERT, UPDATE and DELETE.

ERROR:  permission denied for schema <schema>

The full list of configurable options can be seen on the PostgreSQL Database Plugin HTTP API page.