Users, Roles & Permissions

roles: users and groups are the same concept

CREATE ROLE app_readonly;                              -- can't log in by default, useful as a group
CREATE ROLE alice WITH LOGIN PASSWORD 'secret';         -- can log in — this is what other systems call a "user"
CREATE USER bob WITH PASSWORD 'secret';                 -- CREATE USER is just shorthand for CREATE ROLE ... WITH LOGIN

GRANT app_readonly TO alice;                            -- alice inherits everything granted to app_readonly

There's no separate "group" object — a role that other roles are granted membership in serves that purpose. This is simpler than it sounds once you stop looking for a distinct group concept.

GRANT and REVOKE

GRANT SELECT ON orders TO app_readonly;
GRANT SELECT, INSERT, UPDATE ON orders TO app_readwrite;
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin_role;
GRANT USAGE ON SCHEMA public TO app_readonly;           -- needed before table-level grants matter at all

REVOKE INSERT ON orders FROM app_readwrite;

A grant on a table doesn't implicitly grant access to the schema containing it — USAGE on the schema is a separate, required prerequisite. This trips people up constantly when a grant "doesn't seem to work."

default privileges for future tables

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_readonly;
-- applies to tables created AFTER this statement runs — not retroactive

Without this, every new table needs its own explicit GRANT, which is exactly the kind of manual step that gets forgotten during a migration and causes a mysterious permission-denied error in production weeks later.

least-privilege patterns for application accounts

Pattern	Why
Never connect the application as a superuser or the role that owns the schema	a SQL-injection or application bug then can't `DROP TABLE` or read other databases on the same server
Separate read-only and read-write roles, even for the same app	a reporting/analytics service gets the read-only role, limiting blast radius if that service is compromised
A distinct, more-privileged role for running migrations, not used for normal app traffic	day-to-day traffic can't `ALTER TABLE` even if something goes wrong in application code

inspecting permissions

\du         -- list roles, in psql
\dp orders  -- show grants on the orders table, in psql

where to go from here

PostgreSQL Fundamentals — where the postgres superuser role first shows up.
SQL from Python — which role an application actually connects as.