PostgreSQL User and Role Management on RHEL 9

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public TO alice;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
GRANT ALL PRIVILEGES ON DATABASE appdb TO alice;

Role membership

GRANT readonly TO alice;

List / change / drop

\du
ALTER ROLE alice WITH PASSWORD 'NewPass!';
REASSIGN OWNED BY alice TO postgres;
DROP OWNED BY alice;
DROP ROLE alice;

pg_hba.conf methods