Charles Lavery

Postgres Login/Role Example

Basic setup of a sudo-style user/role in postgres. username can login with no super privileges (NOINHERIT) but can SET ROLE to superuser for administration purposes (akin to sudo). Allows for separation of concerns and protection. A default database for the user can be useful for a playground and allow running psql with no db specified. No password will be set requiring the user to be logged into his system account (ie. password auth will always fail).

> \du
> CREATE ROLE username WITH LOGIN NOINHERIT
> CREATE ROLE superuser WITH SUPERUSER CREATEDB CREATEROLE NOLOGIN
> CREATE ROLE admin WITH CREATEDB CREATEROLE NOLOGIN
> GRANT superuser TO username
> GRANT admin TO username
> CREATE DATABASE username WITH OWNER username
> \du

...later as system account username


> SET ROLE superuser
> ...as admin...
> RESET ROLE
> SET ROLE admin
> CREATE DATABASE...
> RESET ROLE