The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Create a schema | CREATE SCHEMA app AUTHORIZATION app_owner; | View examples |
| Set role search path | ALTER ROLE app_runtime
SET search_path = app, pg_catalog; | View examples |
| Create a login role | CREATE ROLE app_runtime LOGIN PASSWORD NULL; | View examples |
| Create a group role | CREATE ROLE app_readonly NOLOGIN; | View examples |
| Grant role membership | GRANT app_readonly TO analyst_user; | View examples |
| Allow schema lookup | GRANT USAGE ON SCHEMA app TO app_readonly; | View examples |
| Grant existing table reads | GRANT
SELECT
ON ALL TABLES IN SCHEMA app TO app_readonly; | View examples |
| Grant sequence use | GRANT USAGE,
SELECT
ON ALL SEQUENCES IN SCHEMA app TO app_runtime; | View examples |
| Revoke public schema creation | REVOKE CREATE ON SCHEMA public FROM PUBLIC; | View examples |
| Grant future table reads | ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT
SELECT
ON TABLES TO app_readonly; | View examples |
| Test a privilege | SELECT has_table_privilege('app_runtime', 'app.orders', 'SELECT'); | View examples |
| Transfer ownership | ALTER TABLE app.orders OWNER TO app_owner; | View examples |
PostgreSQL roles can own objects, inherit memberships, and optionally log in; schemas are namespaces whose CREATE privilege affects object-resolution security. Grant the smallest object privileges needed, separate ownership from runtime login, qualify security-sensitive names, and configure default privileges from the role that will create future objects.
Step by step
Detailed examples
Treat writable schemas in search_path as code execution risk
Unqualified names resolve through search_path, and the first valid match wins. A role with CREATE in a searched schema can shadow functions or objects. Revoke unnecessary CREATE, put pg_catalog deliberately, and schema-qualify migration and security-definer code.
CREATE SCHEMA app AUTHORIZATION app_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER ROLE app_runtime SET search_path = app, pg_catalog;Separate login, membership, and ownership roles
Every database identity is a role; LOGIN permits connection and NOLOGIN roles aggregate privileges or own objects. Membership can inherit privileges or require SET ROLE depending on role options and version. Do not make application logins object owners or superusers.
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_runtime LOGIN PASSWORD NULL;
GRANT app_readonly TO analyst_user;Grant namespace and object privileges separately
USAGE on a schema permits name lookup but not table access. Table grants do not automatically include sequences used by inserts, and ALL TABLES affects only existing objects. PUBLIC is an implicit group containing every role, so review its database and schema grants.
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app FROM app_readonly;Configure defaults for the actual creator role
ALTER DEFAULT PRIVILEGES affects future objects created by a specified role, optionally in a schema; it does not rewrite existing objects. Running it as an administrator instead of the migration owner is a common mistake. Apply existing grants and defaults as separate migration steps.
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;Audit effective rights and ownership before revocation
Owners have inherent capabilities not represented as ordinary grants, superusers bypass privilege checks, and membership can provide indirect rights. Use has_*_privilege functions and catalog views under representative roles. Transfer ownership deliberately before dropping an owner role.
SELECT has_schema_privilege('app_runtime', 'app', 'USAGE'),
has_table_privilege('app_runtime', 'app.orders', 'SELECT');
SELECT schemaname, tablename, tableowner
FROM pg_tables WHERE schemaname = 'app'
ORDER BY tablename;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.