Skip to content
Tutorials Center
My learning

Topics

Lesson 74 of 90 ยท Security and Administration

Roles, Privileges and GRANT

Control access in PostgreSQL with roles, LOGIN, GRANT and REVOKE, default privileges and schema rights. Build least-privilege roles for apps and reporting.

Every connection to PostgreSQL is made as a role, and every permission check asks the same question: may this role do this to that object? Getting roles and privileges right is one of the cheapest security wins you have. A web application that connects as a superuser turns every SQL injection bug into total data loss. An application that connects as a role which can only read and write its own tables turns the same bug into a much smaller problem.

This lesson explains roles, the GRANT and REVOKE commands, schema and default privileges, predefined roles, and a least-privilege layout for real applications. Role management needs a server with separate users, so the PostgreSQL commands are read-only. The runnable examples build a small privilege model out of tables and a recursive query, so you can see how membership and grants combine.

Roles are the only identity

User A role that can log in.
Group A role that other roles belong to.
Same thing CREATE USER is CREATE ROLE with LOGIN.
1 / 2

Roles, users and groups

PostgreSQL has one concept: the role. A role can own objects, hold privileges, log in, and contain other roles. The words "user" and "group" are conventions:

SQL
-- A role that can log in (a "user")
CREATE ROLE app_user LOGIN PASSWORD 'change-me-please';

-- Exactly the same thing, older spelling
CREATE USER app_user2 PASSWORD 'change-me-please';

-- A role that cannot log in, used to collect privileges (a "group")
CREATE ROLE readonly NOLOGIN;

Roles belong to the whole cluster, not to one database (see Clusters, Databases and Schemas). Useful role attributes include:

Attribute Meaning
LOGIN May start a session. Without it the role is a group.
SUPERUSER Bypasses almost every permission check. Keep these to a minimum.
CREATEDB / CREATEROLE May create databases or manage roles.
REPLICATION May connect for replication (see Streaming and Logical Replication).
BYPASSRLS Ignores row-level security policies.
CONNECTION LIMIT n Caps concurrent sessions for the role.
VALID UNTIL 'timestamp' The password expires at that time.

Never store a real password in a script. Passwords are hashed with SCRAM on the server (see Authentication, TLS and Encryption), and in psql the \password command sets one without it appearing in logs or history.

Membership and inheritance

GRANT role TO role makes one role a member of another. Members normally inherit the privileges of the roles they belong to:

SQL
CREATE ROLE alice LOGIN;
CREATE ROLE bob   LOGIN;

GRANT readonly TO alice;
GRANT readonly TO bob;

Now anything granted to readonly is available to both users. Change the group once and everyone follows. Since PostgreSQL 16 each membership has its own INHERIT and SET options, so a grant can allow SET ROLE without automatic inheritance, and a role with CREATEROLE can only manage roles it has been given admin rights over, not every role in the cluster.

The inheritance rule is a graph walk. The next example resolves which roles a user effectively holds, by following memberships transitively:

SQL Resolving role membership

Press Run (or Ctrl + Enter) to see the result here.

alice reaches readonly through analysts, so a privilege granted to readonly applies to her too.

GRANT and REVOKE

Privileges are granted on objects. The common ones are:

Object Privileges
Database CONNECT, CREATE, TEMPORARY
Schema USAGE (look up objects inside), CREATE
Table or view SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
Sequence USAGE, SELECT, UPDATE
Function EXECUTE
SQL
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Writers also need to INSERT/UPDATE and to use identity/serial sequences
GRANT INSERT, UPDATE, DELETE ON orders, order_items TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Column-level privileges
GRANT SELECT (id, name) ON customers TO support;

REVOKE DELETE ON orders FROM app_user;

A common surprise is that GRANT SELECT ON ALL TABLES IN SCHEMA public only affects tables that exist now. Tables created later are not covered, which is what default privileges are for. Another surprise: new objects start with only their owner (and superusers) having access, except a few defaults granted to the special role PUBLIC (every role): CONNECT and TEMPORARY on databases, and EXECUTE on functions. In PostgreSQL 15 and later, PUBLIC no longer has CREATE on the public schema, so ordinary users cannot create tables there any more unless you grant it. Many guides still tell you to run REVOKE ALL ON DATABASE shop FROM PUBLIC to close the door properly; that remains a sound practice.

To inspect privileges in psql, use \du (roles), \dp (tables) and \dn+ (schemas), or query the catalogs (see Monitoring and System Catalogs).

Checking a privilege

The following simulation models a grants table, with a role's own grants and those inherited through membership, and answers "can this role do this to that table?":

SQL Can this role run this statement?

Press Run (or Ctrl + Enter) to see the result here.

alice may read orders through readonly and reports through analysts, but she cannot insert, because no role she holds has INSERT. bob can update orders but never gained SELECT on customers. This is exactly the check PostgreSQL makes, with an added requirement of USAGE on the schema.

Default privileges

ALTER DEFAULT PRIVILEGES says what to grant automatically on future objects created by a given role in a given schema:

SQL
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO app_user;

The FOR ROLE part matters: the setting applies only to objects created by that role. If migrations run as another role, the defaults do nothing. Use \ddp in psql to list them.

A least-privilege layout

A layout that works for most applications uses three kinds of roles:

SQL
-- 1. The owner runs migrations; nobody logs in as it day to day
CREATE ROLE migrator LOGIN PASSWORD '...';
CREATE DATABASE shop OWNER migrator;

-- 2. The application can read and write data but not change the schema
CREATE ROLE app_user LOGIN PASSWORD '...' CONNECTION LIMIT 50;

-- 3. A read-only role for reports and BI tools
CREATE ROLE report_user LOGIN PASSWORD '...';
GRANT readonly TO report_user;

REVOKE ALL ON DATABASE shop FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO app_user, report_user;

The owner is the only role allowed to run ALTER TABLE, so even a compromised application cannot drop tables. Sensitive columns can be hidden with column privileges or a view, and per-row restrictions come from Row-Level Security. If a role must be removed, first run REASSIGN OWNED BY old_role TO new_role; and DROP OWNED BY old_role;, then DROP ROLE old_role;.

Predefined roles

PostgreSQL ships roles for common jobs, so you do not hand out superuser for them:

Role Allows
pg_read_all_data Read every table, view and sequence (14 and later)
pg_write_all_data Write to every table (14 and later)
pg_monitor Read monitoring views and settings
pg_read_all_stats / pg_read_all_settings Read statistics or all settings
pg_signal_backend Cancel or terminate other users' sessions
pg_maintain Run VACUUM, ANALYZE, REINDEX and similar on all tables (17 and later)
pg_read_server_files, pg_write_server_files, pg_execute_server_program Dangerous: access the server's file system or run programs
SQL
GRANT pg_monitor TO metrics_exporter;

Treat the last row of the table like superuser access. Whoever can run programs on the database host controls the host.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between CREATE USER and CREATE ROLE?

    PostgreSQL has a single role concept. CREATE USER is an alias for CREATE ROLE that gives the role the LOGIN attribute by default.
  2. 2. A role has SELECT on a table but still gets "permission denied for schema". What is missing?

    To reach objects inside a schema a role needs USAGE on that schema in addition to privileges on the objects themselves.
  3. 3. You ran GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly. Next week migrations create a new table. Can readonly read it?

    ON ALL TABLES covers tables that exist at the time of the command. ALTER DEFAULT PRIVILEGES FOR ROLE migrator covers tables that the named role creates later.
  4. 4. Why should an application not connect as a superuser?

    Superusers skip nearly all permission checks. A least-privilege application role limits the damage of bugs and injection attacks.

Summary

  • Everything is a role: LOGIN makes a user, NOLOGIN roles act as groups, and membership passes privileges on.
  • Access needs CONNECT on the database, USAGE on the schema and the right privilege on the object.
  • GRANT ... ON ALL TABLES only covers existing tables; use ALTER DEFAULT PRIVILEGES FOR ROLE for future ones.
  • Since PostgreSQL 15, PUBLIC cannot create objects in the public schema by default.
  • Use an owner role for migrations, a limited application role, and a read-only role for reporting.
  • Predefined roles such as pg_monitor avoid handing out superuser.

Next: Row-Level Security.