PostgreSQL Privileges: Setting Up Read-Only and Least-Privilege Roles with GRANT, REVOKE, and DEFAULT PRIVILEGES

PostgreSQL Privilege Model — It's All About Roles

PostgreSQL has no separate "users" and "groups." Both are unified as a single role. If it can log in, we commonly call it a "user"; if it holds other roles as members, we use it like a "group." Internally they are the same object.

• A role with the LOGIN attribute = a connection account (User)
• A role without LOGIN = a privilege bundle (Group)
• Privileges are granted to roles with GRANT and inherited through membership.

Creating Roles

-- Login account (for the application)
CREATE ROLE app_user LOGIN PASSWORD 'S3cr3t!23';

-- Privilege bundle (group) — cannot log in
CREATE ROLE readonly;
CREATE ROLE readwrite;

-- Admin-oriented attributes
CREATE ROLE analyst LOGIN PASSWORD '...' 
  CONNECTION LIMIT 5       -- concurrent connection limit
  VALID UNTIL '2027-01-01'; -- expiration date

Key attributes: LOGIN, SUPERUSER (full power—do not overuse), CREATEDB, CREATEROLE, REPLICATION, CONNECTION LIMIT, VALID UNTIL.

ALTER ROLE app_user CONNECTION LIMIT 20;   -- change attributes
ALTER ROLE app_user PASSWORD 'new_pw';     -- change password

Privilege Hierarchy — Think in Three Layers

PostgreSQL privileges are layered from top to bottom. You need privileges on the upper layer before you can reach the layer below.

The classic trap: even if you grant SELECT on a table, queries fail if the role lacks USAGE on the schema that contains it. You must grant both layers.

GRANT / REVOKE in Practice

Here is the full flow for setting up a proper read-only role.

-- 1. Allow database connections
GRANT CONNECT ON DATABASE appdb TO readonly;

-- 2. Allow schema usage (the most commonly forgotten grant)
GRANT USAGE ON SCHEMA public TO readonly;

-- 3. SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 4. Sequence access (if needed)
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

For a write role, add DML on top of that.

GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;  -- for SERIAL / auto-increment

Revoke privileges symmetrically with REVOKE.

REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user;

Simplify Management with Group Membership

Don't GRANT to every account individually. Collect privileges on group roles and add accounts to those groups—that is far easier to maintain.

-- Privileges go on the group
GRANT readonly TO analyst;     -- analyst inherits all privileges of readonly
GRANT readwrite TO app_user;

-- Check / revoke membership
REVOKE readwrite FROM app_user;

From then on, adding or removing privileges on readonly applies immediately to every account in that group.

DEFAULT PRIVILEGES — Auto-Grant on Future Tables

GRANT SELECT ON ALL TABLES applies only to tables that exist at the moment you run it. Newly created tables get no privileges, so queries break after every deploy—a common incident. Use ALTER DEFAULT PRIVILEGES so future objects receive grants automatically.

-- So that every table app_owner creates in public gets SELECT for readonly
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON SEQUENCES TO readonly;

The PUBLIC Schema Trap — Know This

On PostgreSQL 14 and earlier, every role has CREATE and USAGE on the public schema by default. That means any account can create tables in public out of the box. Harden by revoking explicitly.

-- Prevent anyone from creating objects in public
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- (optional) also block automatic CONNECT on the new database
REVOKE ALL ON DATABASE appdb FROM PUBLIC;

From PostgreSQL 15 onward, that default CREATE privilege is gone, which is safer. Check your running version with SELECT version();.

Inspecting Privileges — psql Meta-Commands

\du                    -- list roles and attributes
\du+ app_user          -- details for a specific role (including membership)
\dp public.*           -- per-table privileges (access privileges)
\dn+                   -- schema privileges

You can also check what a given role can do on a given table with a function.

SELECT has_table_privilege('readonly', 'public.orders', 'SELECT');  -- t / f

Least-Privilege Checklist

• Do not grant SUPERUSER, CREATEDB, or CREATEROLE to application accounts.
• The app usually only needs the readwrite group. Isolate schema changes (DDL) on a separate migration account.
• Analytics and reporting accounts get readonly only.
• Design future-table privileges up front with ALTER DEFAULT PRIVILEGES.
• Revoke default CREATE on the public schema to block unauthorized object creation.

PostgreSQL privileges come down to three axes: "a role is both a user and a group," "privileges are three layers—database → schema → table," and "future objects use DEFAULT PRIVILEGES." Nail those and you can design most production requirements cleanly.