Advanced PostgreSQL: Functions, JSON, and SecurityLesson 6.4

PostgreSQL roles and permissions explained: GRANT REVOKE CREATE ROLE

CREATE ROLE CREATE USER, GRANT privileges, REVOKE, role inheritance, superuser, pg_hba.conf auth, DEFAULT PRIVILEGES, schema permissions, principle of least privilege

Roles and Permissions

PostgreSQL uses roles for all authentication and authorization. A user is just a role with LOGIN permission. Roles can inherit permissions from other roles.

Create roles

CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE ROLE readonly NOLOGIN;
CREATE ROLE app_admin LOGIN PASSWORD 'admin_pw' IN ROLE readonly;

Grant permissions

GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Grant on sequences (needed for IDENTITY inserts)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

Default privileges for future tables

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Revoke

REVOKE INSERT ON users FROM app_user;

Your application should connect as a role with only SELECT/INSERT/UPDATE/DELETE on the tables it needs - never as the superuser, and never with CREATE TABLE or DROP TABLE in production.