Postgresql Permission Denied For Relation

You face this problem in PostgreSQL DBMS when we don’t provide all the required privileges created to the table.

You sometimes misunderstand that allowing permission to the table can be done by granting permissions to the database. However, you need to grant permission to tables in a schema when you create a table.

For example:

select * from rock_solutions;

ERROR: permission denied for relation rock_solutions

Now to solve this, we need to pass a command as:

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

Let us consider a detail Example as:

CREATE DATABASE "rock_solutions";

CREATE USER user_name WITH PASSWORD 'securepassword';

CREATE TABLE "New_table"(

"id" int NOT NULL,

"data" text NOT NULL

);

INSERT INTO "New_table" values(0, "required_data");

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

The above syntax can solve the problem and make sure that you log into Psql as the owner of the tables or the superuser.