PostgreSQL Permissions¶

PostgreSQL permissions work in layers. Understanding the layers makes debugging much easier.

Permission layers¶

1. Authentication
2. Role attributes
3. Database privileges
4. Schema privileges
5. Object privileges
6. Ownership
7. Role membership
8. Default privileges

Role attributes¶

Common role attributes:

• LOGIN
• SUPERUSER
• CREATEDB
• CREATEROLE

Check role attributes:

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
WHERE rolname = 'tenant_user';

Database privileges¶

Important database privileges:

• CONNECT
• CREATE
• TEMPORARY

Example:

GRANT CONNECT ON DATABASE mydb TO tenant_user;

Schema privileges¶

Important schema privileges:

• USAGE
• CREATE

Example:

GRANT USAGE ON SCHEMA public TO tenant_user;

Table privileges¶

Common table privileges:

• SELECT
• INSERT
• UPDATE
• DELETE

Example:

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO tenant_user;

Sequence privileges¶

Sequence permissions are separate from table permissions.

GRANT USAGE, SELECT, UPDATE
ON ALL SEQUENCES IN SCHEMA public
TO tenant_user;

Useful inspection commands¶

\du
\l
\dn+
\dp

Default privileges¶

Grants on existing tables do not automatically apply to future tables.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tenant_user;