How to fix Insufficient privilege in PostgreSQL

First, determine which user is encountering the error and what operation they were attempting. Connect to the database and check the current user:

SELECT current_user;

This shows the connected user role. Note the exact operation that failed (SELECT, INSERT, UPDATE, DELETE, etc.) and the object name (table, schema, function, etc.) from the error message.

Users need USAGE privilege on the schema containing the object before they can access any objects within it. Connect as the schema owner or superuser and grant schema access:

GRANT USAGE ON SCHEMA public TO username;

Replace public with the actual schema name if using a custom schema, and username with the affected user. Without this, users cannot access any objects in the schema regardless of table-level privileges.

Once schema access is granted, grant the specific privileges needed on the table. Connect as the table owner or superuser:

For SELECT (read) access:

GRANT SELECT ON table_name TO username;

For INSERT (write) access:

GRANT INSERT ON table_name TO username;

For UPDATE access:

GRANT UPDATE ON table_name TO username;

For DELETE access:

GRANT DELETE ON table_name TO username;

For all privileges:

GRANT ALL PRIVILEGES ON table_name TO username;

If the error mentions "permission denied for sequence", the user needs USAGE privilege on the sequence used by auto-increment columns:

GRANT USAGE ON SEQUENCE sequence_name TO username;

For auto-increment columns, also grant the privilege to the sequence typically named with _seq suffix:

GRANT USAGE ON SEQUENCE table_name_id_seq TO username;

Or grant on all sequences in the schema:

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO username;

If the error occurs when calling stored procedures or functions:

GRANT EXECUTE ON FUNCTION function_name() TO username;

Or grant execution on all functions in a schema:

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO username;

Verify that the privileges are now in place using the \dp (display privileges) command in psql:

-- Check table privileges
\dp table_name

-- Check schema privileges
SELECT grantee, privilege_type
FROM information_schema.role_schema_grants
WHERE schema_name = 'public' AND grantee = 'username';

-- Check function privileges
\df+ function_name

The output should show the username with the appropriate privileges (SELECT, INSERT, UPDATE, DELETE, etc.).

Connect to the database as the user that was receiving the insufficient privilege error and retry the operation:

psql -U username -d database_name

Then run the same query that previously failed. It should now succeed. If it still fails, check that you granted the correct privilege type for the operation being attempted.