How to Fix a "permission denied for schema public" Error in PostgreSQL?

A permission denied for schema public error occurs when the connected user lacks the required privileges to access or create objects in the public schema. Vultr Managed PostgreSQL clusters enforce schema-level permissions to ensure secure multi-tenant operation.

By default, the public schema exists in every database, but non-admin users may not automatically have CREATE or USAGE privileges. To resolve this error and grant the necessary access safely, follow these steps:

1. Connect to your database using the admin account (vultradmin) automatically created when your cluster was provisioned. This user has full privileges required to manage roles and schema access.

   console Copy

   $ psql postgres://vultradmin:<password>@<hostname>:<port>/defaultdb
   

   Explain Code

2. Check the privileges of the target user on the public schema. For PostgreSQL 14 and later, run the following query:

   sql Copy

   defaultdb=> SELECT
       grantee.rolname AS grantee,
       nspname AS schema_name,
       has_schema_privilege(grantee.rolname, nspname, 'USAGE') AS usage,
       has_schema_privilege(grantee.rolname, nspname, 'CREATE') AS create
   FROM pg_roles grantee
   CROSS JOIN pg_namespace n
   WHERE grantee.rolname = '<target_user>'
       AND n.nspname = 'public';
   

   Explain Code

   The output shows which privileges the user currently has. For example:

   sql Copy

   grantee        | schema_name | usage | create 
   ---------------+-------------+-------+--------
   {target_user}  | public      | f     | f
   

   Explain Code

3. To allow the user to create and access objects in the public schema, grant USAGE and CREATE privileges:

   sql Copy

   defaultdb=> GRANT USAGE, CREATE ON SCHEMA public TO <target_user>;
   

   Explain Code

4. Re-run the query from Step 2 to confirm that the privileges are now set correctly. Example output after granting:

   sql Copy

   grantee        | schema_name | usage | create
   ---------------+-------------+-------+--------
   {target_user}  | public      | t     | t
   

   Explain Code

   The t values indicate that the test_user now has both USAGE and CREATE privileges on the public schema.

In this way, schema-level security is maintained while allowing the user to operate in the public schema without permission errors.