Control who can do what — create roles and login users, grant and revoke privileges, set defaults for future tables, allow connections in pg_hba.conf, and restrict rows per user with row-level security.
Why: in PostgreSQL a "user" is just a role that is allowed to log in. You create roles to represent people and applications, each with its own password, so access can be granted and audited per role.
-- a login role for your application
CREATE ROLE app_user WITH LOGIN PASSWORD 'a-strong-password';-- a group role with no login, used to bundle privileges
CREATE ROLE readonly;GRANT readonly TO app_user;Why: new roles can do almost nothing until you grant them privileges. GRANT hands out specific permissions on specific objects; REVOKE takes them back. Note: a role also needs USAGE on the schema before it can touch tables inside it.
GRANT USAGE ON SCHEMA public TO readonly;GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;GRANT SELECT, INSERT, UPDATE, DELETE
ON orders TO app_user;REVOKE DELETE ON orders FROM app_user;Why: GRANT only affects tables that exist right now — a table you create tomorrow would not be covered. ALTER DEFAULT PRIVILEGES sets the rule once so every future table automatically grants the right access.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;Why: before a role can even reach the database, the pg_hba.conf file must allow the connection. Each line says: connection type, which database, which user, from which address, and how to authenticate. Note: "scram-sha-256" is the modern, secure password method. This is file content (not a command you run) — paste it into pg_hba.conf and reload PostgreSQL.
# TYPE DATABASE USER ADDRESS METHOD
host shop app_user 10.0.0.0/24 scram-sha-256
host all all 0.0.0.0/0 rejectWhy: sometimes you need users to see only their own rows of a shared table. Row-level security (RLS) enforces that in the database itself with a policy, so even a raw SQL query cannot leak another user’s data. Note: table owners and superusers bypass RLS unless you add FORCE — and to actually watch it filter you must query as a non-superuser role, so this demo switches to the app_user role you created earlier in this lesson.
-- setup: a table whose rows each belong to a user
CREATE TABLE IF NOT EXISTS documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
owner text NOT NULL,
title text NOT NULL
);
INSERT INTO documents (owner, title) VALUES
('app_user', 'Owned by app_user'),
('postgres', 'Owned by postgres');
GRANT SELECT ON documents TO app_user;ALTER TABLE documents ENABLE ROW LEVEL SECURITY;ALTER TABLE documents FORCE ROW LEVEL SECURITY; -- apply the policy to the owner tooDROP POLICY IF EXISTS owner_only ON documents;
CREATE POLICY owner_only ON documents
USING (owner = current_user);-- become a non-superuser: you now see only your own row
SET ROLE app_user;
SELECT * FROM documents; -- returns just the app_user row
RESET ROLE;