PostgreSQL

List of sql queries to manage users, roles

A cheatsheet by @nathan-appere|Refreshed 2 months ago.Refresh|View source on Github

List users & their privileges

List users & their privileges

SELECT
  usename AS role_name,
  CASE WHEN usesuper
    AND usecreatedb THEN
    CAST('superuser, create database' AS pg_catalog.text)
  WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
  WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
  ELSE
    CAST('' AS pg_catalog.text)
  END role_attributes
FROM
  pg_catalog.pg_user
ORDER BY
  role_name DESC;

List roles

List roles

SELECT * FROM pg_roles;

List table permissions

List table permissions

SELECT
  *
FROM
  information_schema.role_table_grants
WHERE
  table_schema = 'public';

List databases a user can connect to

List databases a user can connect to

SELECT datname
FROM pg_database
WHERE has_database_privilege('username', datname, 'CONNECT')

Schema permissions

Schema permissions

SELECT
  r.usename AS grantor,
  e.usename AS grantee,
  nspname,
  privilege_type,
  is_grantable
FROM
  pg_namespace
  JOIN LATERAL (
    SELECT
      *
    FROM
      aclexplode(nspacl) AS x) a ON TRUE
  JOIN pg_user e ON a.grantee = e.usesysid
  JOIN pg_user r ON a.grantor = r.usesysid;

Remove role

Remove role

Some context for AWS and the lack of superuser privileges.

/* Note: does not affect objects within other databases, so repeat for all DBs where the role owns anything. */
REASSIGN OWNED BY username TO postgres;

/* Drops all the objects within the current database that are owned by the role. */
DROP     OWNED BY username;

DROP         ROLE username;

The following might be necessary:

REVOKE ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM username;

I tend to use 3 roles: a simple readonly one (often going to a read-replica), write for row creation or update, ops for migrations.

readonly role

readonly role

CREATE ROLE role_appname_readonly;
GRANT CONNECT ON DATABASE database_appname_production TO role_appname_readonly;
GRANT USAGE ON SCHEMA public TO role_appname_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_appname_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO role_appname_readonly;

CREATE USER user_appname_readonly WITH PASSWORD 'xxxxxx';
GRANT role_appname_readonly TO user_appname_readonly;

write role

write role

CREATE ROLE role_appname_write;
GRANT CONNECT ON DATABASE database_appname_production TO role_appname_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_appname_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_appname_write;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO role_appname_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO role_appname_write;

CREATE USER user_appname_write WITH PASSWORD 'xxxxxx';
GRANT role_appname_write TO user_appname_write;

ops role

ops role

CREATE ROLE role_appname_ops;
GRANT CONNECT ON DATABASE database_appname_production TO role_appname_ops;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_appname_ops;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_appname_ops;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO role_appname_ops;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO role_appname_ops;

CREATE USER user_appname_ops WITH PASSWORD 'xxxxxx';
GRANT role_appname_ops TO user_appname_ops;

On your local setup, in order to be able to run all migrations, add:

ALTER USER user_appname_ops CREATEDB;

Note: in PostgreSQL, only the owner of a database can drop a database. This is a non issue in production, as on AWS the db already exists and won’t be owned by user_appname_ops

Password generation

Password generation

  • 8 to 128 characters.
  • Must url encode special characters.
pwgen 128 3
  • https://jasonaowen.net/blog/2017/Feb/09/aws-postgresql-rds-passwords/