Skip to main content

permission denied for schema public

PostgreSQL error code 42501 signifies an insufficient_privilege error. This means the database user attempting an operation does not have the necessary permissions to perform that action.
The "permission denied for schema public" error in PostgreSQL indicates that the connected user lacks the necessary privileges to perform an action within the public schema. This issue is particularly common in PostgreSQL versions 15 and later, where the default permissions on the public schema were tightened, revoking CREATE permission from PUBLIC (all users) and granting it only to the database owner.
To resolve this error, you need to grant the required permissions to the user attempting the operation. The most common solution involves granting CREATE and USAGE privileges on the public schema to the specific user.
Here's how to address the issue:
  • Connect to the database as a superuser or the database owner: This is crucial for granting permissions. You can typically use the postgres user or another user with administrative privileges.
Code

    psql -h <your_db_host> -U postgres -d <your_database_name> -W
  • Grant the necessary permissions to your target user:
    • To allow creating objects (e.g., tables, functions) in the public schema:
Code

        GRANT CREATE ON SCHEMA public TO <your_user>;
  • To allow viewing and using objects already present in the public schema:
Code

        GRANT USAGE ON SCHEMA public TO <your_user>;
  • To grant both CREATE and USAGE (and other privileges):
Code

        GRANT ALL ON SCHEMA public TO <your_user>;
Replace <your_user> with the actual username that is encountering the "permission denied" error.
Important Considerations:
  • Database Specificity:
    Permissions are granted per database. Ensure you are connected to the correct database when executing the GRANT commands.
  • User and Schema Ownership:
    In some cases, the issue might stem from the database or schema owner not matching the user attempting the operation. You might need to adjust the ownership using ALTER DATABASE <database_name> OWNER TO <your_user>; or ALTER SCHEMA public OWNER TO <your_user>; if appropriate for your setup.
  • Application User:
    If you are using an application (e.g., a web application with a database connection), ensure the user specified in your application's database configuration has these grants
Back to top