Postgres Roles and Permissions: CREATE USER, GRANT, and Least Privilege

The default for many projects is a single database user — often postgres, a superuser — used by the app, migrations, the admin dashboard and every developer. It works until something goes wrong: a SQL injection, a bad migration, or an AI agent running DROP TABLE against the wrong connection string. A superuser can do anything, so anything can happen.

Postgres has a fine-grained permission system. Using even a little of it limits the blast radius.

Roles are users (and groups)

In Postgres, users and groups are both "roles." A role with LOGIN can connect; a role without it is effectively a group you grant to other roles.

CREATE ROLE app_user LOGIN PASSWORD 'long-random-password';   -- a user
CREATE ROLE readonly;                                          -- a group
GRANT readonly TO analyst;                                     -- analyst joins the group

CREATE USER is just CREATE ROLE … LOGIN.

Role attributes to be careful with: SUPERUSER (bypasses all checks), CREATEDB, CREATEROLE, BYPASSRLS. Your app needs none of them.

What can be granted

Permissions work in layers. To read a table, a role needs:

1. CONNECT on the database,
2. USAGE on the schema,
3. SELECT on the table.

GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;   -- for serial/identity IDs

Missing any layer gives permission denied for schema public or permission denied for table orders — the message tells you which layer.

Ownership matters

The role that creates a table owns it, and owners can do anything to their tables — including DROP and ALTER — regardless of grants. So if your app creates tables (e.g. migrations run with the app's credentials), the app owns them and can drop them.

Separate the two:

• An owner/migrator role that owns the schema and runs migrations. (Database migrations)
• An app role that can read and write data but not change structure.

The "ALL TABLES" trap: default privileges

GRANT … ON ALL TABLES applies to tables that exist right now. The table created by next week's migration won't be included, and your app will start failing with permission errors.

Fix with default privileges, set by the role that creates tables:

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, SELECT ON SEQUENCES TO app_user;

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

A practical three-role setup

-- 1. Owner: runs migrations, owns everything
CREATE ROLE migrator LOGIN PASSWORD '…';
CREATE SCHEMA app AUTHORIZATION migrator;

-- 2. App: data access only
CREATE ROLE app_user LOGIN PASSWORD '…';
GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA app TO app_user;

-- 3. Read-only: dashboards, analysts, AI agents exploring data
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA app TO readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app
  GRANT SELECT ON TABLES TO readonly;

CREATE ROLE analyst LOGIN PASSWORD '…' IN ROLE readonly;

Then:

• The app's DATABASE_URL uses app_user.
• Deploys run migrations with migrator.
• Admin tools, BI dashboards and AI agents investigating data use a readonly login. An agent with a read-only connection can't delete production data however confused it gets. (Stop an AI agent deleting your production database)

Tighten further where useful: revoke DELETE if your app only soft-deletes (soft deletes), or grant on specific columns.

Lock down the public schema

On older Postgres versions, every role could create tables in public. Since Postgres 15 this is revoked by default for new databases, but upgraded databases may still allow it:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(PUBLIC here means "every role.")

Row-level security for multi-tenant data

Grants control which tables a role can touch. Row-level security controls which rows — e.g. only the current tenant's. It's what Supabase relies on. (Supabase RLS explained, multi-tenant Postgres patterns)

Checking what a role can do

\du                                   -- in psql: list roles
\dp app.*                             -- table privileges
SELECT has_table_privilege('app_user', 'app.orders', 'DELETE');

The summary

• Postgres roles are users and groups; your app should never be a superuser.
• Access needs database CONNECT, schema USAGE, and table privileges.
• Owners can drop their tables — separate the migrator from the app role.
• Use ALTER DEFAULT PRIVILEGES so future tables get the right grants.
• Give dashboards and AI agents a read-only role.

---

EasySpawn servers include PostgreSQL with daily backups, so you can set up separate app, migration and read-only roles — and restore if something still goes wrong. See how it works or join the waitlist.

Related: Postgres Connection Strings Explained · SQL Injection Explained · Multi-Tenant SaaS on Postgres · How to Back Up a Postgres Database