This is part of a multi-part series on using postgres databases, vault, and a variety of other tools to effect short-lived database credentials for real use.

As postgres uses user and role interchangably, so will I, although I'll generally try to use user to refer to a role with login permissions.

Postgres roles and privileges

There are some really interesting and powerful capabilities for managing roles in postgres, and as I've become more familiar with them, I understand why so many of us are in the habit of granting overly-broad privileges in the database realm.

A few important things to know about roles and privileges:

• The owner of a new object is the user/role that created that object. As such, any default privileges for objects owned by that user must either be assigned by that user, or assigned on behalf of the user. Be careful/consistent about the role that creates tables and sequences so that you prevent problems. Only the owner is allowed to alter tables and sequences.

• By default new objects are not provided privileges to existing roles. In order to ensure that new tables, sequences, etc. are usable by the roles that we are creating, they must be either granted explicitly each time a new object is created, or you must have an appropriate default permission for the public role or an explicit role. NOTE: defaults for new objects are owner-dictated, and thus the default role must be granted by the owner of the table, or on behalf of the owner of the table or it won't work as desired.

• There are rules about dropping roles that may not be obvious. An owning role cannot be deleted until that owned object is removed or ownership is given to another role. Additionally any privileges owned by the role need to be dropped before dropping the role.

• By default, all users have create privileges in the public schema for each database. This is not necessary for the creation of temporary tables, but generally makes it easier for all new users to work with a database. When designing your access controls, it probably makes sense to revoke all unnecessary privileges from public (revoke create on schema public from public).

• Roles that are not marked noinherit in their definition will be inherited by any user granted that role unless you explicitly mark the grant noinherit. This may make sense when you are looking at privileges that don't grant ownership, but if you are trying to ensure that you don't have unexpectedly-owned tables, you will want your ownership roles to have noinherit set or grant them noinherit.

• Prior to version 16 It's worth noting that non-owning users with the createrole permission do not have the ability to directly set their roles to be any arbitrary role on the system. However, I've found nothing to prevent a user with createrole permission from granting itself any role on the system except superuser. Keep that in mind that these granting roles are potent.

• As of PostgreSQL 16, there is indeed a specific mechanism for making sure that roles are not granted by just any user who has the createrole permission. As of version 16, the grantor must have role admin capability on any roles that it will assign. As such, our vault role needs to have GRANT owner,readwrite,readonly to vault-admin with admin option set in order to create the roles with the appropriate role. As such, the vault-admin role is now more limited in what it can do.

Role design

Designing an appropriate role structure is complicated and this is not intended to be one-size-fits-all in the least. However, I hope it serves as a useful starting-off point. This design expects tob e used with dynamic roles through vault, and I'll detail that at the end.

The recommended "group" roles are:

role	purpose	privileges
postgres	superuser role (maybe break glass)	SUPERUSER, LOGIN
vault-admin	used by vault to manage dyanmic users	CREATEROLE, LOGIN, granted each role with ADMIN OPTION
owner	create/modify tables	ALL on SCHEMA public and ALL on tables and sequences
readwrite	read/write to tables	SELECT on SCHEMA public and ALL on tables and sequences
readonly	read-only on tables	SELECT on SCHEMA public and SELECT on tables and SELECT on sequences

NOTE: The readwrite user is granted ALL on tables and sequences. This may be a bit more than necessary, consider if you want to grant REFERENCES and TRIGGER tables, since these are frequently unnecssary. Although the USAGE privilege is separate on sequences, it's not particularly useful to allow UPDATE without allowing USAGE, as that won't allow you to use the sequence in a nextval call.

When creating the dynamic roles (assuming use with vault), there will be three role templates used:

role	creation statement
owner	CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE \"owner\" NOINHERIT;
readwrite	CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE \"readwrite\" INHERIT;
readonly	CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE \"readonly\" INHERIT;

The creation statements are all very similar, except for the specifics of which role is assigned and if they are inherited.

In my case, I'm choosing to use inherit for the non-ownership roles because it removes the need to set role. However, it's not necessary and unless you need the (temporary) user name, setting role is probably preferred.

This structure can be used for any application framework, and with or without a secrets maanger like vault, but since my example here started as a vault example, the creation of these items would be sequenced thusly:

1. Create the new database
2. Create the owner role and grant it appropriate privileges
3. Create the readwrite role and grant it the appropriate privileges and defaults in the schema on behalf of owner
4. Create the readwrite role and grant it the appropriate privileges and defaults in the schema on behalf of owner

At this point, you have what you need to create the database admin user, the reader, and the writer. If you're using vault, then use the creation statements above. If not, you can use similar statements manually so that if you have multiple users, you can control access centrally.