Queryfailederror Permission Denied For Schema Public

by

The error “QueryFailedError: Permission Denied for Schema Public” is a common issue in PostgreSQL when a user or application does not have the required permissions to access or modify objects in the public schema.

This topic explains the causes of this error and provides step-by-step solutions to fix it.

1. Understanding the “Permission Denied” Error

What Does the Error Mean?

This error occurs when a user tries to perform an operation (such as SELECT, INSERT, UPDATE, or DELETE) on the public schema without having the necessary permissions.

Common Scenarios Where This Error Occurs

  • A new user does not have the required privileges.
  • The public schema’s default permissions have been changed.
  • A migration or database restore modified user roles.
  • The application is trying to access a table without the correct privileges.

2. Checking Current Permissions

To diagnose the issue, check the current permissions for the public schema.

Check Schema Ownership

Run the following SQL query in PostgreSQL to see the owner of the public schema:

SELECT nspname, pg_catalog.pg_get_userbyid(nspowner) 
FROM pg_catalog.pg_namespace 
WHERE nspname = 'public';

This will show the user who owns the public schema. If the current user is not listed, permissions may need to be granted.

Check User Privileges

To check what privileges a user has on the public schema, use:

SELECT grantee, privilege_type 
FROM information_schema.role_schema_grants 
WHERE schema_name = 'public';

This will list the users and their assigned privileges.

3. Granting the Necessary Permissions

If the error occurs due to missing permissions, grant them manually using the following steps.

Grant Usage and Create Privileges on Public Schema

If the user needs access to the public schema, run:

GRANT USAGE ON SCHEMA public TO your_user;
GRANT CREATE ON SCHEMA public TO your_user;

Replace your_user with the actual database user.

Grant SELECT, INSERT, UPDATE, and DELETE on Tables

If the user needs access to tables inside the public schema, grant the necessary permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_user;

To ensure future tables also have the correct permissions, apply this command:

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_user;

Grant Permissions on Sequences

If the user needs to insert data into tables with auto-incrementing IDs, grant sequence privileges:

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO your_user;

To apply this for future sequences:

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

4. Changing Schema Ownership

If the error is due to an ownership issue, you can change the owner of the public schema:

ALTER SCHEMA public OWNER TO your_user;

Be cautious when changing ownership, as it may affect existing users or applications.

5. Checking Role and Database Connection Issues

If the problem persists, ensure the user has database connection access:

GRANT CONNECT ON DATABASE your_database TO your_user;

Also, verify that the user has the correct role assigned:

SELECT rolname FROM pg_roles WHERE rolname = 'your_user';

If the user does not exist, create it and assign privileges:

CREATE USER your_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

6. Fixing Permission Issues in Docker or Cloud Environments

If you are using PostgreSQL in Docker or a cloud-hosted service (like AWS RDS, Google Cloud SQL), permission settings might be different.

Fixing in Docker

If PostgreSQL is running in a Docker container, log into the container:

docker exec -it your_container_name psql -U your_user -d your_database

Then, run the necessary GRANT commands inside the PostgreSQL shell.

Fixing in AWS RDS

For managed PostgreSQL services like AWS RDS, you may not have full access to the postgres superuser. Instead, use an RDS admin account and grant privileges accordingly.

7. Preventing Future Permission Errors

To avoid this issue in the future:

  • Always assign the correct privileges when creating new users.
  • Regularly audit database roles and permissions.
  • Use default privileges to automatically grant permissions on new tables.
  • If using ORMs like TypeORM, Sequelize, or Prisma, ensure migrations are applied with the correct user roles.

The error “QueryFailedError: Permission Denied for Schema Public” happens when a database user lacks the necessary privileges to access or modify objects in the public schema.

To fix this error:

  1. Check current permissions using SQL queries.
  2. Grant the required privileges to the user.
  3. Ensure schema ownership is correct if needed.
  4. Verify database connections and roles.
  5. Adjust permissions in Docker or cloud environments.

By following these steps, you can successfully resolve this PostgreSQL error and prevent future permission issues.