Database,

How to Fix ERROR Permission Denied for Schema Public in PostgreSQL

by 12 months agono comment
0
Shares

I have just installed a new PostgreSQL Server on Debian 12 virtual machine. I also created a new database and a new user. All the settings seem all right and I can connect to the server remotely using Navicat Premium from a Windows 11 machine without issue. The only problem is that I cannot create a new table in my Public schema. I got the following error:

ERROR Permission Denied for Schema Public

If you have a similar issue, you can follow the guide below to resolve the problem. To resolve the “Permission Denied for Schema Public” error in PostgreSQL, you need to adjust the access privileges for the ‘public’ schema. This error typically occurs when a user does not have the necessary permissions to perform a specific operation within the ‘public’ schema. To fix this, you can follow these steps:

Step 1. Connect to PostgreSQL

Use a superuser or a user with sufficient privileges to access the database. 

####Login as postgres user
root@Debian12:/home/dhani# sudo -i -u postgres

#### Run psql command
postgres@Debian12:~$ psql
psql (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.

postgres=#

Identify the User and Operation: Determine which user is encountering the permission issue and what operation they are trying to perform. This information is crucial for granting the correct permissions.

Step 2. Grant Schema Privileges

In this example, user ‘dhani‘ has a permission issue on the gis_database. So, we will try to resolve for this user only. First, we need to connect to database gis_database as postgres user. 

#### connect to gis_database as postgres user
postgres=# \c gis_database postgres;
You are now connected to database "gis_database" as user "postgres".
gis_database=# 

#### Grant Schema Privileges
gis_database=# GRANT ALL ON SCHEMA public to dhani;

#### You should see GRANT output if it success
GRANT

Use the GRANT command to provide the necessary privileges to the user for the ‘public’ schema. For example, to grant all privileges, you can use:

GRANT ALL ON SCHEMA public TO username;

Replace ‘username’ with the actual username experiencing the issue.

Once the permissions have been adjusted, the user should be able to perform the desired operation within the ‘public’ schema without encountering the “Permission Denied” error.

Leave a Reply