Postgres databases can handle complex website functions easily.

But, what if you get a permission denied error for database Postgres, frustrating right?

This website error occurs due to the lack of database privileges like CONNECT, CREATE, etc.

At Bobcares, we often receive requests to fix the permission denied error as part of our Server Management Services.

Today, let’s discuss this error in detail and see how our Support Engineers fix it for our customers.

How to fix Permission denied for database Postgres?

Postgres is a powerful database that comes up with vast features to help developers.

But, it often shows up permission denied error. Finding the exact reason for this error can be quite tricky.

The core reason for the permission denied error in Postgres is the lack of several privileges.

Now, let’s see the main causes of this error and its respective fixes.

1. Missing CONNECT privilege

Recently, one of our customers approached us with a permission denied error in the Postgres. He tried to log in to his database using psql command,

psql userdb user --password

Here, userdb and user are the database name and username respectively.

But, after entering the password, he got the following error,

psql: FATAL: permission denied for database "userdb"
DETAIL: User does not have CONNECT privilege.

Our Support Engineers checked and found an error with CONNECT privilege.

Usually, the CONNECT privilege allows the user to connect to a database. And we check this privilege at the connection startup.

So, to grant CONNECT privilege, we followed the command,

GRANT CONNECT ON DATABASE userdb TO user ;

This resolved the error effectively.

2. Grant privileges to a new user

In some cases, users try to grant all privileges of a database to a new Postgres user other than the owner. For that, we use the command,

GRANT ALL PRIVILEGES ON DATABASE userdb TO new_user;

But, when we log in as the new user and try to read data from the table, it ends up showing the error,

ERROR: permission denied for relation table_name

This is because GRANT ALL PRIVILEGES ON DATABASE grants CREATE, CONNECT and TEMPORARY privileges on a database to a user.

But, none of these privileges permit the user to read data from the table.

Therefore, it requires the SELECT privilege. We resolve this permission denied error using the command.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

The new_user was then able to read data from the table.

Similarly, we can also resolve the permission denied error by setting DEFAULT privileges to the user.

Here, we use the ALTER DEFAULT PRIVILEGES command to define the default access privileges.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO new_user;

This will be specific to the schema specified in the command. Also, to apply it to the entire database, we use the command,

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO new_user;

3. Missing Postgres user

In the control panel based servers, permission denied error can happen due to missing users as well. For instance, in control panels like Plesk, if the password for PostgreSQL user postgres does not correspond password in the Plesk database, it shows up errors.

Therefore, to fix it, we update the PostgreSQL user with the proper password and sync it with the Plesk DB. And confirm it from Plesk panel at:

[Stuck with permission denied error in Postgres?- We’ll help you.]

Conclusion

In short, the permission denied for database Postgres occurs due to the lack of certain privileges like CONNECT, CREATE, DEFAULT and so on. In today’s writeup, we discussed how our Support Engineers fix Postgres privileges for our customers.