How do I fix a "permission denied for schema public" error in PostgreSQL?
An ERROR: permission denied for schema public error happens when the connected user does not have the appropriate privileges to access the public schema. Users need CREATE privileges to create new objects in the schema and USAGE privileges to view objects in the schema.
To resolve the permission denied error, you need to update the user’s privileges on the public schema to CREATE, USAGE, or ALL (both CREATE and USAGE), depending on the level of access you want the user to have.
Follow the instructions in How to Modify User Privileges in PostgreSQL Databases. When you’ve connected to the database, you can grant privileges on the public schema with the following example command, substituting the permission level you want (ALL in the example) and the user (example_user in this example):
GRANT ALL PRIVILEGES ON SCHEMA public TO example_user;
To verify the privilege change, you can query the information_schema.role_schema_grants system catalog, which stores details about user privileges on schemas. For example, the following query lists the permissions that example_user has on the public schema:
SELECT grantee, privilege_type
FROM information_schema.role_schema_grants
WHERE schema_name = 'public'
AND grantee = 'example_user';
The query outputs a table with grantee and privilege_type columns:
...
grantee | privilege_type
--------------+----------------
example_user | USAGE
example_user | CREATE
...
This example output shows that example_user has both USAGE and CREATE privileges on the public schema.
Related Topics
