PostgreSQL User and Role Management on Ubuntu 24.04

Create roles

-- Basic login role
CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';

-- Superuser (use sparingly)
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';

-- Read-only role (no LOGIN)
CREATE ROLE readonly;

Grant privileges

-- Grant connect to database
GRANT CONNECT ON DATABASE appdb TO alice;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO alice;

-- Grant read-only on all current tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Grant read-only on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- Grant full access
GRANT ALL PRIVILEGES ON DATABASE appdb TO alice;

Assign role membership

GRANT readonly TO alice;

List roles

\du

Revoke privileges

REVOKE ALL PRIVILEGES ON DATABASE appdb FROM alice;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM alice;

Change password

ALTER ROLE alice WITH PASSWORD 'NewAlicePass!';

Drop role

-- Remove object ownership first
REASSIGN OWNED BY alice TO postgres;
DROP OWNED BY alice;
DROP ROLE alice;

pg_hba.conf authentication methods