Notes

Notes on Postgres role management

This page is managed by StJohn Piano.

If you have any questions, comments, or suggestions - please contact StJohn Piano on Tela:
tela.app/id/stjohn_piano/7c51a6

The commands in these notes can be run in the Postgres client psql.

Connect to Postgres database db using psql:

PGPASSWORD='password'
PGPASSWORD=$PGPASSWORD psql -h localhost -d db -U user

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

The concept of roles subsumes the concepts of “users” and “groups”.

See current database:

SELECT CURRENT_DATABASE();

List databases:

\l , \list

Change database:

\c database_name;

Alternative: \connect database_name

See current schema:

SELECT CURRENT_SCHEMA;

List schemas within the current database:

SELECT schema_name FROM information_schema.schemata;

Alternative: SELECT nspname FROM pg_namespace;

Change schema:

SET SCHEMA 'public';

List tables in the current database:

\dt

List tables ordered by schema:

SELECT table_name, table_schema FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name;

See current role:

SELECT CURRENT_ROLE;

List all roles:

\du , \du+

See which roles can connect to the current database:

SELECT rolname, HAS_DATABASE_PRIVILEGE(rolname, 'postgres', 'connect')
FROM pg_roles
WHERE rolcanlogin;

See which databases a role can connect to:

SELECT db.datname, r.rolname, HAS_DATABASE_PRIVILEGE(r.rolname, db.datname, 'connect')
FROM pg_roles r
CROSS JOIN pg_database db
WHERE r.rolcanlogin
AND db.datallowconn
AND r.rolname = 'user'
ORDER BY db.datname, r.rolname;

See which roles can connect to each database:

SELECT db.datname, r.rolname, HAS_DATABASE_PRIVILEGE(r.rolname, db.datname, 'connect')
FROM pg_roles r
CROSS JOIN pg_database db
WHERE r.rolcanlogin
AND db.datallowconn
ORDER BY db.datname, r.rolname;

See table privileges for a role:

SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'user';

See table ownership for a role:

SELECT * FROM pg_tables 
WHERE tableowner = 'user';

See schema privileges for all roles:

WITH users AS (
  SELECT rolname, oid FROM pg_roles
  UNION SELECT 'PUBLIC', 0
)
SELECT
  r.rolname AS grantor,
  e.rolname AS grantee,
  nspname AS schema,
  privilege_type,
  is_grantable
FROM pg_namespace,
  aclexplode(nspacl) AS a
  JOIN users AS e
    ON a.grantee = e.oid
  JOIN users AS r
    ON a.grantor = r.oid
;

The privileges to create tables, views, functions, operators, data types, etc are not properties of the role. They are properties of the relevant object (e.g. a schema, a table, etc).

Change password for a role:

ALTER ROLE user WITH PASSWORD 'changeme';

Note: A role is permitted to change its own password. A database superuser can change the password for any role.

Create a readonly user

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'changeme';

GRANT CONNECT ON DATABASE bitcoin_data TO readonly_user;

GRANT USAGE ON SCHEMA public TO readonly_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Delete a user

A role's privileges must be explicitly removed before the role can be dropped.

Note: If you drop the only superuser role, you can restart Postgres in single-user mode where you'll have superuser privileges again.

Note: If a user owns objects, you need to use REASSIGN OWNED to change the ownership of all the objects before deleting the user.

-- This will also remove all privileges on the tables within the schema.
REVOKE ALL PRIVILEGES ON SCHEMA public FROM readonly_user;

DROP ROLE readonly_user;

Confirm the drop by either \du+ or the command below (which should return 0).

SELECT COUNT(*) FROM pg_roles WHERE rolname = 'readonly_user';