Managing Users and Roles

CREATE ROLE app_reader;             -- NOLOGIN by default
CREATE ROLE app_writer INHERIT;

CREATE USER alice PASSWORD 'secret';       -- shorthand for CREATE ROLE LOGIN

ALTER ROLE alice PASSWORD 'newSecret';
\password alice                            -- psql: encrypts client-side

ALTER ROLE app_reader LOGIN;
ALTER ROLE old_user NOLOGIN;               -- disable

ALTER ROLE admin SUPERUSER;

ALTER ROLE alice CREATEDB;

ALTER ROLE alice CREATEROLE;

CREATE ROLE repl REPLICATION LOGIN PASSWORD '...';

ALTER ROLE app_reader CONNECTION LIMIT 50;

ALTER ROLE temp_user VALID UNTIL '2025-12-31 23:59:59 UTC';

ALTER ROLE alice SET search_path = app, public;
ALTER ROLE alice SET statement_timeout = '30s';
ALTER ROLE alice IN DATABASE shop SET work_mem = '64MB';

ALTER ROLE alice RENAME TO alice_smith;

SET password_encryption = 'scram-sha-256';
ALTER ROLE alice PASSWORD 'NewStrongPass';

REASSIGN OWNED BY alice TO app_owner;
DROP OWNED BY alice;
DROP ROLE alice;

\du
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin FROM pg_roles;