PostgreSQL "permission denied for table": causes and fixes

Your application, a report or a colleague connects to PostgreSQL and gets one of these:

ERROR:  permission denied for table employees
ERROR:  permission denied for schema app
ERROR:  permission denied for sequence tickets_id_seq

All three mean the same thing: the role you are connected as has not been granted the privilege it needs. PostgreSQL checks privileges on several levels — the schema, the table and, for inserts, sometimes the sequence — and each level produces its own message. This guide shows how to find which one is missing and how to grant exactly that, nothing more.

Every SQL example on this page runs as-is in the SQL Playground, on a real PostgreSQL database with the same tables.

TL;DR

SELECT current_user;                                            -- who am I?
SELECT has_table_privilege('reporting', 'employees', 'SELECT'); -- can this role read the table?
GRANT USAGE ON SCHEMA public TO reporting;                      -- schema access
GRANT SELECT ON employees TO reporting;                         -- table access

Reproduce the error

Create a role with no privileges, switch to it, and try to read a table:

-- ERROR: permission denied for table employees
CREATE ROLE reporting LOGIN;
SET ROLE reporting;
SELECT count(*) FROM employees;

SET ROLE is a handy way to test privileges: you keep your connection but act as another role. RESET ROLE takes you back.

1. Check who you really are

The most common surprise is connecting as a different role than you think — a connection pool, an .env file or a PGUSER variable can all change it.

RESET ROLE;
SELECT current_user, session_user;

session_user is the role you logged in as; current_user is the role whose privileges apply right now (they differ after SET ROLE).

2. See what the role is allowed to do

Ask PostgreSQL directly:

SELECT has_table_privilege('reporting', 'employees', 'SELECT') AS can_read,
       has_schema_privilege('reporting', 'public', 'USAGE') AS can_use_schema;

Or list every privilege granted on the table:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'employees'
ORDER BY grantee, privilege_type;

In psql, \dp employees shows the same information in a compact form.

3. Grant the table privilege

Grant only what the role needs — SELECT for reading, plus INSERT, UPDATE or DELETE for writing:

GRANT SELECT ON employees, departments TO reporting;
SET ROLE reporting;
SELECT d.name, count(*) FROM employees e JOIN departments d ON d.id = e.department_id GROUP BY d.name;
RESET ROLE;

It works now. Privileges on one table don’t cover others: a query that joins two tables needs a privilege on both.

4. “permission denied for schema”

To reach a table, a role first needs USAGE on the schema that contains it. For the default public schema, every role has USAGE already; for your own schemas it must be granted:

-- ERROR: permission denied for schema app
RESET ROLE;
CREATE SCHEMA app;
CREATE TABLE app.invoices (id integer PRIMARY KEY, total numeric(10,2) NOT NULL);
GRANT SELECT ON app.invoices TO reporting;
SET ROLE reporting;
SELECT * FROM app.invoices;

The table privilege alone isn’t enough. Add schema access:

RESET ROLE;
GRANT USAGE ON SCHEMA app TO reporting;
SET ROLE reporting;
SELECT * FROM app.invoices;
RESET ROLE;

5. All tables — and the ones created tomorrow

To give read access to every existing table in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

This covers existing tables only. A table created next week will again give “permission denied”. Default privileges fix that:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;
CREATE TABLE audit_log (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, message text NOT NULL);
SET ROLE reporting;
SELECT count(*) FROM audit_log;
RESET ROLE;

PostgreSQL 14 and newer also have the built-in role pg_read_all_data: GRANT pg_read_all_data TO reporting; gives read access to everything in the database, current and future. Convenient for a read-only reporting user — but it really is everything.

6. “permission denied for sequence” on INSERT

Tables with a serial column get their ids from a separate sequence, and inserting a row needs USAGE on that sequence too:

-- ERROR: permission denied for sequence tickets_id_seq
RESET ROLE;
CREATE TABLE tickets (id serial PRIMARY KEY, title text NOT NULL);
GRANT INSERT ON tickets TO reporting;
SET ROLE reporting;
INSERT INTO tickets (title) VALUES ('Disk full on db-01');

Grant the sequence (or all sequences in the schema):

RESET ROLE;
GRANT USAGE ON SEQUENCE tickets_id_seq TO reporting;
SET ROLE reporting;
INSERT INTO tickets (title) VALUES ('Disk full on db-01');
RESET ROLE;

Identity columns (GENERATED ... AS IDENTITY, PostgreSQL 10+) don’t have this problem — the table privilege is enough. The departments table in this database uses one:

GRANT INSERT ON departments TO reporting;
SET ROLE reporting;
INSERT INTO departments (name, location) VALUES ('Legal', 'Lisbon');
RESET ROLE;

7. “permission denied for schema public” when creating a table

Since PostgreSQL 15, ordinary roles can no longer create tables in the public schema by default — only the database owner can. Migrations that worked on PostgreSQL 14 suddenly fail:

-- ERROR: permission denied for schema public
RESET ROLE;
SET ROLE reporting;
CREATE TABLE scratch (id integer);

The clean fix is a schema owned by the application role. Granting CREATE on public also works, but gives every member of that role the right to create objects there:

RESET ROLE;
CREATE SCHEMA reporting AUTHORIZATION reporting;
SET ROLE reporting;
CREATE TABLE reporting.scratch (id integer);
RESET ROLE;

Other errors that look similar

• must be owner of table employees — ALTER TABLE, DROP TABLE and CREATE INDEX need ownership, not just privileges. Run them as the owner, or ALTER TABLE employees OWNER TO app_owner;.
• A query returns no rows instead of an error — the table may use row-level security (ALTER TABLE … ENABLE ROW LEVEL SECURITY), which filters rows silently.
• permission denied for database — the role lacks CONNECT on the database: GRANT CONNECT ON DATABASE mydb TO reporting;.