PostgreSQL Roles, GRANT and Row-Level Security

Roles

PostgreSQL has one concept — the role — that serves as both a user and a group. A role that can log in is, informally, a "user".

CREATE ROLE app_user LOGIN PASSWORD 'StrongPass!42';
CREATE ROLE reporting LOGIN PASSWORD 'AnotherPass!7' CONNECTION LIMIT 10;
CREATE ROLE readonly NOLOGIN;                       -- a group role, never logs in directly

ALTER ROLE app_user VALID UNTIL '2027-01-01';
ALTER ROLE app_user PASSWORD 'NewPass!99';

Granting privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON students TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
GRANT SELECT (id, name_en) ON students TO reporting;         -- column-level
GRANT EXECUTE ON FUNCTION dept_stats(text) TO app_user;
GRANT USAGE ON SCHEMA academic TO app_user;                   -- needed to reach objects in it
GRANT ALL PRIVILEGES ON DATABASE school TO owner_role;

Privilege levels: database, schema, table, column, sequence, function. USAGE on the schema is required before any table-level grant does anything — a common surprise for people new to Postgres.

Group roles and membership

CREATE ROLE app_read;
CREATE ROLE app_write;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_write;

GRANT app_read TO reporting;
GRANT app_read, app_write TO app_user;

Default privileges

GRANT only applies to objects that already exist. Set defaults so future tables inherit privileges automatically:

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

Revoking and inspecting

REVOKE INSERT, UPDATE ON students FROM app_user;
\du                              -- list roles
\dp students                     -- privileges on a table
SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_name = 'students';

Row-level security

A Postgres feature MySQL has no equivalent for: restrict which rows a role can see or change, enforced inside the database itself.

ALTER TABLE students ENABLE ROW LEVEL SECURITY;

CREATE POLICY dept_isolation ON students
  USING (department = current_setting('app.current_dept'));

-- in the application, per connection:
SET app.current_dept = 'CSE';
SELECT * FROM students;   -- only CSE rows are visible, no WHERE needed anywhere

CREATE POLICY own_rows_only ON orders
  FOR ALL
  USING (customer_id = current_setting('app.user_id')::int)
  WITH CHECK (customer_id = current_setting('app.user_id')::int);

USING filters what a role can read; WITH CHECK restricts what it can write. RLS is checked automatically on every query, which makes multi-tenant isolation a database guarantee instead of an application convention that a forgotten WHERE can silently break.

Principle of least privilege

Role	Typical grants
Web application	`SELECT, INSERT, UPDATE, DELETE` on its schema only
Reporting / BI	`SELECT` only, ideally on a replica
Migration job	Adds `CREATE`, used only during deploys
Backup	`pg_read_all_data`, or the `REPLICATION` attribute

Never let an application connect as a superuser. A vulnerability in a SELECT-only role leaks data; the same vulnerability as a superuser can drop the database.

Roles & Privileges