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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store