55 Roles and GRANT

PostgreSQL has a single concept — the role — for both users and groups. A role can log in (with LOGIN), be granted into other roles (for group-like behavior), own objects, and have privileges granted to it. GRANT adds privileges; REVOKE removes them. Every database object has an owner with full rights; everyone else needs explicit grants. The principle is least privilege: give each role only what it needs.

What you’ll learn

• Creating roles with and without login
• Granting and revoking privileges on schemas, tables, and columns
• The USAGE privilege on schemas (often forgotten)
• DEFAULT PRIVILEGES for objects created in the future
• Row-level security (RLS) — a brief introduction

Example

-- Inspect the current session
SELECT current_user, session_user;

-- Create ordinary roles (requires CREATEROLE or superuser privileges)
-- CREATE ROLE app_read NOLOGIN;                                 -- a "group" role
-- CREATE ROLE alice LOGIN PASSWORD 'change-me' IN ROLE app_read;
-- CREATE ROLE app_write NOLOGIN;

-- Grant access to a schema and read on tables
-- GRANT USAGE  ON SCHEMA public TO app_read;
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

-- Future tables in this schema also get SELECT for app_read
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public
--   GRANT SELECT ON TABLES TO app_read;

-- Write-level
-- GRANT app_read TO app_write;                          -- inheritance: app_write also reads
-- GRANT INSERT, UPDATE, DELETE ON fruits TO app_write;

-- Revoke
-- REVOKE INSERT ON fruits FROM app_write;

-- Inspect your privileges on a table
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'fruits' AND grantee = current_user;

-- Row-level security: per-row visibility decided by a policy
-- (demo on a separate table so we don't change `fruits` permissions)
DROP TABLE IF EXISTS tenant_docs;
CREATE TABLE tenant_docs (
  id       integer PRIMARY KEY,
  tenant   text NOT NULL,
  content  text NOT NULL
);
INSERT INTO tenant_docs VALUES (1, 'acme', 'hello'), (2, 'globex', 'world');

ALTER TABLE tenant_docs ENABLE ROW LEVEL SECURITY;
-- Apply the policy to the table owner as well (superusers/BYPASSRLS still bypass it).
ALTER TABLE tenant_docs FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tenant_docs
  USING (tenant = current_setting('app.tenant', true));

-- Set the tenant for the session and try a select
SET app.tenant TO 'acme';
SELECT * FROM tenant_docs;        -- only sees the 'acme' row

SET app.tenant TO 'globex';
SELECT * FROM tenant_docs;        -- only sees the 'globex' row

-- Cleanup
DROP POLICY IF EXISTS tenant_isolation ON tenant_docs;
DROP TABLE IF EXISTS tenant_docs;

Table privileges include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and, in PostgreSQL 17, MAINTAIN. On a schema, the most important are USAGE (lets a role refer to objects inside) and CREATE (lets the role create new objects). On a database, CONNECT, CREATE, and TEMPORARY matter.

Privileges on tables are not inherited from schema-level grants. To use a table in schema public, a role needs USAGE on public and a privilege (e.g. SELECT) on the table. Missing the schema-level USAGE produces the common “permission denied for schema public” error.

DEFAULT PRIVILEGES solves the “new tables get no grants” problem: any object created later by a role inherits the configured grants automatically. They apply to objects created by the configured creating role, not automatically to objects created by every role. Configure each migration/owner role that creates tables.

Row-level security (RLS) is a more advanced feature: you ENABLE ROW LEVEL SECURITY on a table and write POLICYs that filter rows by predicate. With RLS, a query like SELECT * FROM tenant_docs returns only the rows visible to the current role/setting when the role is subject to the policy. A freely changeable app.tenant setting is only a demonstration: a real application must prevent untrusted clients from choosing another tenant.

Run the example

To run, use a non-superuser role without BYPASSRLS that can create tables in your scratch database. FORCE ROW LEVEL SECURITY makes this table owner obey the policy. Superusers still see both tenants. The commented role-management commands need appropriate role-administration privileges.

$ psql -f source/roles-and-grant.sql postgres
 current_user | session_user
--------------+--------------
 your_user    | your_user
(1 row)
 ...

Common pitfalls

• Forgetting GRANT USAGE ON SCHEMA public. A role can have SELECT on every table and still fail to query because it lacks schema-level usage.
• Granting privileges on existing tables without configuring DEFAULT PRIVILEGES — every new table the app creates is unreachable by the read-only role.
• Mixing SECURITY DEFINER functions with a permissive search_path opens a privilege-escalation door. Always set search_path on such functions.
• Storing passwords in CREATE ROLE ... PASSWORD 'plain' is acceptable for personal databases; for production use SCRAM authentication and never hardcode passwords in SQL scripts.

Downloads

SQL file: roles-and-grant.sql · All lesson files · Setup guide