Home / Database / PostgreSQL Permission Denied: Role Privileges Management

Database

PostgreSQL Permission Denied: Role Privileges Management

Resolve PostgreSQL permission denied errors by understanding role privileges, ownership, and proper access control configuration.

Published: Nov 15, 202513 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

# PostgreSQL Permission Denied: Role Privileges Management

You're getting permission errors when your application or users try to access PostgreSQL:

bash

ERROR:  permission denied for table users
ERROR:  permission denied for schema api
ERROR:  must be owner of relation products
ERROR:  permission denied to create database

Understanding PostgreSQL's privilege system is essential for database administration. Let's diagnose and fix permission issues systematically.

Introduction

This article covers troubleshooting steps and solutions for PostgreSQL Permission Denied: Role Privileges Management. The error typically occurs in production environments and can cause service disruptions if not addressed promptly.

Symptoms

Common error messages include:

bash

ERROR:  permission denied for table users
ERROR:  permission denied for schema api
ERROR:  must be owner of relation products
ERROR:  permission denied to create database

```sql -- Current user SELECT current_user, session_user;

-- What roles am I a member of? SELECT r.rolname, r.rolsuper, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin FROM pg_roles r WHERE r.rolname IN ( SELECT role.name FROM pg_auth_members m JOIN pg_roles role ON m.roleid = role.oid JOIN pg_roles member ON m.member = member.oid WHERE member.rolname = current_user ) OR r.rolname = current_user; ```

```sql -- Check table privileges SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE table_name = 'users' ORDER BY grantee, privilege_type;

-- Check ownership SELECT n.nspname AS schema, c.relname AS table_name, pg_get_userbyid(c.relowner) AS owner, c.relkind AS type FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = 'users';

-- Grant missing privileges GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;

-- Grant all on table GRANT ALL PRIVILEGES ON users TO app_admin; ```

Common Causes

Configuration misconfiguration
Missing or incorrect credentials
Network connectivity issues
Version compatibility problems
Resource exhaustion or limits
Permission or access denied

Step-by-Step Fix

1.Check logs for specific error messages
2.Verify configuration settings
3.Test network connectivity
4.Review recent changes
5.Apply corrective action
6.Verify the fix

Understanding PostgreSQL Privileges

PostgreSQL has a layered permission system:

1.Role membership - User belongs to a role
2.Schema privileges - Access to schema
3.Object privileges - Access to tables, views, functions
4.Column privileges - Access to specific columns
5.Row-level security - Access to specific rows

Quick Permission Check

```sql -- Current user SELECT current_user, session_user;

Diagnosing Permission Issues

Error: "permission denied for table"

```sql -- Check table privileges SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE table_name = 'users' ORDER BY grantee, privilege_type;

-- Grant missing privileges GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;

-- Grant all on table GRANT ALL PRIVILEGES ON users TO app_admin; ```

Error: "permission denied for schema"

```sql -- Check schema privileges SELECT nspname AS schema, pg_get_userbyid(nspowner) AS owner, array_agg(privilege_type) AS privileges FROM pg_namespace n JOIN aclexplode(n.nspacl) AS a ON true WHERE nspname = 'api' GROUP BY nspname, nspowner;

-- Grant schema usage GRANT USAGE ON SCHEMA api TO app_user;

-- Grant create in schema GRANT CREATE, USAGE ON SCHEMA api TO app_developer; ```

Error: "must be owner of relation"

This error occurs when trying to modify an object you don't own:

```sql -- Check who owns the table SELECT n.nspname AS schema, c.relname AS table, pg_get_userbyid(c.relowner) AS owner FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = 'products';

-- Option 1: Have the owner make the change SET ROLE table_owner; -- Make changes RESET ROLE;

-- Option 2: Transfer ownership ALTER TABLE products OWNER TO app_admin;

-- Option 3: Grant necessary privileges GRANT ALL PRIVILEGES ON products TO app_admin; ```

Error: "permission denied for column"

Column-level privileges can restrict access:

```sql -- Check column privileges SELECT grantee, table_name, column_name, privilege_type FROM information_schema.column_privileges WHERE table_name = 'users' ORDER BY column_name, privilege_type;

-- Grant column access GRANT SELECT (id, name, email) ON users TO app_readonly;

-- Revoke column access REVOKE SELECT (password_hash, salt) ON users FROM app_readonly; ```

Common Permission Scenarios

Scenario 1: Application User Setup

Create a proper application user with least privileges:

```sql -- Create role CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password' NOINHERIT;

-- Grant schema usage GRANT USAGE ON SCHEMA public TO app_user; GRANT USAGE ON SCHEMA app_schema TO app_user;

-- Grant table permissions GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO app_user;

-- Grant sequence permissions (for SERIAL columns) GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app_schema TO app_user;

-- Set default privileges for future objects ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user; ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT USAGE, SELECT ON SEQUENCES TO app_user; ```

Scenario 2: Read-Only User

```sql -- Create read-only role CREATE ROLE readonly_user WITH LOGIN PASSWORD 'secure_password';

-- Grant schema usage GRANT USAGE ON SCHEMA public TO readonly_user;

-- Grant select on all tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Set default for future tables ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

-- Prevent modifications (ensure no write permissions) REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM readonly_user; ```

Scenario 3: Admin Role with Superuser Access

```sql -- Create admin role CREATE ROLE app_admin WITH LOGIN PASSWORD 'secure_password' CREATEROLE CREATEDB;

-- Or make superuser (use sparingly) ALTER USER app_admin WITH SUPERUSER;

-- Grant ownership of existing objects DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('ALTER TABLE %I.%I OWNER TO app_admin', r.table_schema, r.table_name); END LOOP; END $$; ```

Scenario 4: Multi-Tenant Access

Use row-level security for tenant isolation:

```sql -- Create tenant policy ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create policy for tenant access CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.current_tenant')::integer);

-- Grant table access GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO tenant_user;

-- Application sets tenant context SET app.current_tenant = '123'; -- Now queries automatically filter by tenant SELECT * FROM orders; -- Only sees tenant 123's orders ```

Managing Role Hierarchy

Create Role Groups

```sql -- Create base roles (no login) CREATE ROLE app_read WITH NOLOGIN; CREATE ROLE app_write WITH NOLOGIN; CREATE ROLE app_admin WITH NOLOGIN;

-- Grant permissions to base roles GRANT USAGE ON SCHEMA app_schema TO app_read; GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_read;

GRANT USAGE ON SCHEMA app_schema TO app_write; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO app_write; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app_schema TO app_write;

-- Create login roles CREATE USER analyst WITH PASSWORD 'password' IN ROLE app_read; CREATE USER developer WITH PASSWORD 'password' IN ROLE app_write; CREATE USER admin_user WITH PASSWORD 'password' IN ROLE app_admin, app_write, app_read;

-- Or grant roles after creation GRANT app_read TO analyst; GRANT app_write TO developer; GRANT app_admin TO admin_user; ```

Role Inheritance

```sql -- Check role inheritance SELECT rolname, rolinherit FROM pg_roles WHERE rolname = 'app_user';

-- Enable inheritance (default) ALTER ROLE app_user INHERIT;

-- Disable inheritance (must SET ROLE) ALTER ROLE app_user NOINHERIT;

-- With NOINHERIT, user must explicitly set role SET ROLE app_admin; ```

Function and Procedure Permissions

Function Execution Permissions

```sql -- Check function privileges SELECT routine_name, routine_type, data_type, security_type FROM information_schema.routines WHERE routine_schema = 'public';

-- Grant function execution GRANT EXECUTE ON FUNCTION calculate_total(integer) TO app_user;

-- Grant all functions in schema GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

-- Security definer (runs as function owner) CREATE OR REPLACE FUNCTION sensitive_operation() RETURNS void SECURITY DEFINER -- Runs as function owner, not caller LANGUAGE plpgsql AS $$ BEGIN -- Has elevated privileges INSERT INTO audit_log (action) VALUES ('sensitive_operation'); END; $$;

-- Security invoker (runs as caller - default) CREATE FUNCTION safe_operation() RETURNS void SECURITY INVOKER -- Runs as caller LANGUAGE plpgsql AS $$ BEGIN -- Only has caller's privileges INSERT INTO user_actions (user_id, action) VALUES (current_user_id(), 'safe'); END; $$; ```

Trigger Function Permissions

sql

-- Trigger functions should typically be SECURITY DEFINER
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER
SECURITY DEFINER
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, user_id, timestamp)
    VALUES (TG_TABLE_NAME, TG_OP, current_user_id, now());
    RETURN NEW;
END;
$$;

Troubleshooting Permission Issues

Comprehensive Permission Query

```sql -- View all privileges for a user SELECT 'TABLE' AS object_type, table_schema AS schema, table_name AS name, privilege_type AS privilege FROM information_schema.table_privileges WHERE grantee = 'app_user'

UNION ALL

SELECT 'SEQUENCE' AS object_type, sequence_schema AS schema, sequence_name AS name, privilege_type AS privilege FROM information_schema.usage_privileges WHERE grantee = 'app_user' AND object_type = 'SEQUENCE'

UNION ALL

SELECT 'SCHEMA' AS object_type, nspname AS schema, nspname AS name, privilege_type FROM pg_namespace n JOIN aclexplode(n.nspacl) a ON true WHERE pg_get_userbyid(a.grantee) = 'app_user'

ORDER BY object_type, schema, name; ```

Debug Permission Denied

```sql -- Function to check if a user has a specific privilege CREATE OR REPLACE FUNCTION has_privilege( user_name text, table_name text, privilege text ) RETURNS boolean AS $$ BEGIN RETURN ( SELECT has_table_privilege(user_name, table_name, privilege) ); END; $$ LANGUAGE plpgsql;

-- Use it SELECT has_privilege('app_user', 'users', 'SELECT'); SELECT has_privilege('app_user', 'users', 'INSERT');

-- Check all privileges for a table SELECT privilege_type, has_table_privilege('app_user', 'users', privilege_type) AS has_privilege FROM unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']) AS privilege_type; ```

Check Effective Privileges

```sql -- Simulate another user to test permissions SET ROLE app_user;

-- Try the operation SELECT * FROM users;

-- Check current privileges SELECT current_user, session_user; SELECT * FROM pg_user WHERE usename = current_user;

-- Reset role RESET ROLE; ```

Fixing Common Permission Issues

Fix Ownership Chain

```sql -- Find objects owned by wrong user SELECT n.nspname AS schema, c.relname AS object, c.relkind AS type, pg_get_userbyid(c.relowner) AS owner FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE pg_get_userbyid(c.relowner) = 'postgres' AND n.nspname NOT LIKE 'pg_%' ORDER BY n.nspname, c.relname;

-- Fix ownership in bulk DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'app_schema' LOOP EXECUTE format('ALTER TABLE %I.%I OWNER TO app_admin', r.table_schema, r.table_name); END LOOP; END $$; ```

Grant Permissions for All Objects

```sql -- Grant all privileges on all existing objects DO $$ DECLARE r RECORD; BEGIN -- Tables FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %I.%I TO app_admin', r.table_schema, r.table_name); END LOOP;

-- Sequences FOR r IN SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('GRANT ALL PRIVILEGES ON SEQUENCE %I.%I TO app_admin', r.sequence_schema, r.sequence_name); END LOOP;

-- Functions FOR r IN SELECT proname, oidvectortypes(proargtypes) AS args FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('GRANT ALL PRIVILEGES ON FUNCTION %I(%s) TO app_admin', r.proname, r.args); END LOOP; END $$; ```

Verification

After making permission changes:

```sql -- Verify user can connect psql -U app_user -d mydb -c "SELECT current_user;"

-- Verify table access SELECT has_table_privilege('app_user', 'users', 'SELECT') AS can_select, has_table_privilege('app_user', 'users', 'INSERT') AS can_insert, has_table_privilege('app_user', 'users', 'UPDATE') AS can_update, has_table_privilege('app_user', 'users', 'DELETE') AS can_delete;

-- Verify schema access SELECT has_schema_privilege('app_user', 'app_schema', 'USAGE') AS can_use, has_schema_privilege('app_user', 'app_schema', 'CREATE') AS can_create;

-- Test actual operation SET ROLE app_user; SELECT * FROM users LIMIT 1; INSERT INTO users (name) VALUES ('test'); RESET ROLE; ```

Verification

Action	Command
Grant table access	`GRANT SELECT, INSERT ON table TO user;`
Grant all tables	`GRANT ALL ON ALL TABLES IN SCHEMA s TO user;`
Grant sequence	`GRANT USAGE, SELECT ON SEQUENCE seq TO user;`
Grant schema	`GRANT USAGE, CREATE ON SCHEMA s TO user;`
Change owner	`ALTER TABLE t OWNER TO new_owner;`
Create user	`CREATE USER name WITH PASSWORD 'pass';`
Create role	`CREATE ROLE name WITH NOLOGIN;`
Add to role	`GRANT role TO user;`
Check privilege	`SELECT has_table_privilege('user', 'table', 'SELECT');`
View grants	`SELECT * FROM information_schema.table_privileges;`
Set role	`SET ROLE role_name;`
Reset role	`RESET ROLE;`

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

PostgreSQL Permission Denied: Role Privileges Management PostgreSQL Permission Denied: Role Privileges Management Database PostgreSQL Permission Denied: Role Privileges Management troubleshooting PostgreSQL Permission Denied: Role Privileges Management fix Resolve PostgreSQL permission denied errors by understanding role privileges, ownership, and proper access control configuration Database Resolve PostgreSQL permission denied errors by understanding role privileges, ownership, and proper access control configuration

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Nov 15, 2025

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

PostgreSQL Permission Denied: Role Privileges Management

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Understanding PostgreSQL Privileges

Quick Permission Check

Diagnosing Permission Issues

Error: "permission denied for table"

Error: "permission denied for schema"

Error: "must be owner of relation"

Error: "permission denied for column"

Common Permission Scenarios

Scenario 1: Application User Setup

Scenario 2: Read-Only User

Scenario 3: Admin Role with Superuser Access

Scenario 4: Multi-Tenant Access

Managing Role Hierarchy

Create Role Groups

Role Inheritance

Function and Procedure Permissions

Function Execution Permissions

Trigger Function Permissions

Troubleshooting Permission Issues

Comprehensive Permission Query

Debug Permission Denied

Check Effective Privileges

Fixing Common Permission Issues

Fix Ownership Chain

Grant Permissions for All Objects

Verification

Verification

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading