Fix PostgreSQL ERROR: permission denied for table

TL;DR

Your role doesn't have privileges on the table. Connect as a superuser or the table owner and run:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_table TO your_role;

For full access:

GRANT ALL PRIVILEGES ON TABLE your_table TO your_role;

Also make sure the role has schema-level access — this is the part people always forget:

GRANT USAGE ON SCHEMA public TO your_role;

The Error

ERROR: permission denied for table users

This fires when a PostgreSQL role tries to query or modify a table it has no privileges on. You'll hit it most often after creating a new app user, switching databases, restoring from a dump, or running a migration as a different role than your app actually connects as.

Why This Happens

PostgreSQL's privilege model is strict. Every role starts with zero table access — connecting to a database says nothing about what you can do inside it. Owning the database does not mean you own the objects inside it.

The usual suspects:

• New app user was created but never granted table privileges
• Tables were created by postgres or a migration user — your app role can't touch them
• Restored from pg_dump and the dump didn't include GRANT statements
• Schema USAGE privilege is missing (the permission check fails before PostgreSQL even looks at the table)

Step 1: Check What Privileges Are Missing

Run this as a superuser to see the current grants on a table:

-- In psql
\dp your_table

-- Or via SQL
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'your_table';

Check schema-level access too:

SELECT nspname, nspacl
FROM pg_namespace
WHERE nspname = 'public';

If your role isn't listed, that's your answer.

Fix 1: Grant Table Privileges

Connect as the table owner or a superuser, then grant what the role needs:

-- Read-only
GRANT SELECT ON TABLE your_table TO your_role;

-- Read + write
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_table TO your_role;

-- All tables in a schema at once
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_role;

Fix 2: Grant Schema USAGE

Table-level grants alone aren't enough. The role also needs USAGE on the schema containing the table:

GRANT USAGE ON SCHEMA public TO your_role;

Skip this and PostgreSQL rejects the query before it even checks table privileges — but the error still says permission denied for table. That mismatch makes the missing schema grant easy to overlook.

Fix 3: Fix Sequence Access for INSERT

Tables using serial, bigserial, or GENERATED columns rely on an underlying sequence. Without access to that sequence, INSERTs will fail even after you've granted table privileges:

-- Specific sequence
GRANT USAGE, SELECT ON SEQUENCE your_table_id_seq TO your_role;

-- All sequences in schema
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO your_role;

Fix 4: Default Privileges (Prevent Recurrence)

Tired of re-running GRANT after every migration? Default privileges fix that. New tables and sequences get access automatically from this point forward:

-- Run this as the role that creates tables (e.g., your migration user)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO your_role;

Heads up: these only apply to objects created after this command runs. They're also scoped to whoever runs the command. Say your migrations connect as postgres but you ran ALTER DEFAULT PRIVILEGES as app_admin — PostgreSQL won't apply them to those new tables.

Full Setup Script

Here's a complete script to run as superuser when onboarding a new app role:

-- Connect to the right database first
\c your_database

-- Schema access
GRANT USAGE ON SCHEMA public TO app_user;

-- Existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Existing sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Future tables and sequences (run as the migration user)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

Verify the Fix

Switch to the role and test directly:

-- From a superuser session
SET ROLE your_role;
SELECT * FROM your_table LIMIT 1;
RESET ROLE;

Or connect as the role from the command line:

psql -U your_role -d your_database -c "SELECT * FROM your_table LIMIT 1;"

No error means it worked.

Tips

On Linux, PostgreSQL has two separate permission layers. SQL privileges cover what roles can do inside the database — everything above falls into this category. File-level permissions on data directories, socket files, and pg_hba.conf are handled at the OS level. For that side of things, the Unix Permissions Calculator on ToolCraft is handy for translating chmod values without doing octal math in your head.

Using pg_dump/pg_restore? Only pass --no-acl when you deliberately want to strip grants from the dump. Leave it out and pg_dump includes all GRANT statements automatically. That said — a --no-acl flag accidentally left in a restore pipeline is behind a surprising number of "why does nothing have permissions after restore?" incidents.