Permission denied for relation <table_name>

Question

So I'm making this app and I'm using Postgres and I've already created a database, a user and a password and granted all privileges on the database to the user I've created.

The thing is, when I switch the database in psql using \c <database_name> I get in just fine and can use queries on it.

But when I run psql using postgres://user_name:password@localhost:5432/databasename on terminal and try to select * from the <table_name> it gives me this message

permission denied for relation <table_name>

Can you please tell me what to do, I've had this problem before and I had to create another database or change the user but I want a better solution please.

PS: I've tried to use this :

GRANT ALL PRIVILEGES ON TABLE <table_name> to <user_name>

This is how I created and accessed my database:

rawan95=# create database food ;
CREATE DATABASE

rawan95=# create user meal with password '123';
CREATE ROLE

rawan95=# grant all privileges on database food to meal;
GRANT

rawan95=# \c food
You are now connected to database "food" as user "rawan95".

After that, I've built it using

food=# \i src/database/db_build.sql
BEGIN  
DROP TABLE  
CREATE TABLE  
INSERT 0 1  
COMMIT

Then I selected data from the table just fine, but when I try to access it using this, I get an error: psql postgres://meal:123@localhost:5432/food

food=> select * from foods;

ERROR:  permission denied for relation foods

\c <database_name> uses the same user as the previous connection. Whereas psql postgres://user_name:password@localhost:5432/databasename uses a different user - so apparently you did not grant all needed privilege to the second user — a_horse_with_no_name
So next time I create a database I should not access it using \c, instead I should directly use this: psql postgres://user_name:password@localhost:5432/databasename , then run my \i db_build.sql to create my tables, right? — Rawan Al-Rajabi
You can use \c but you also need to specify the user you want to change to \c databasename user_name — a_horse_with_no_name
I've tried it and this is what I got: rawan95=# \c food meal &&&&&&& FATAL: Peer authentication failed for user "meal" &&&&&&& Previous connection kept — Rawan Al-Rajabi
I worked it out using this: askubuntu.com/questions/820792/… — Rawan Al-Rajabi

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2019-08-13T19:26:15

You are granting the privileges before you create the tables. As there are no tables at that moment nothing is granted. The tables you created are not owned by the user meal but the user rawan95 (which the \c command told you).

Plus: granting "all privileges" on a database, does not grant any select privilege. As documented in the manual "all privileges" are: CREATE, CONNECT, TEMPORARY, TEMP. The CREATE privilege would allow the user meal to create tables in that database.

If you want all those tables to be owned by the user meal you need to run your setup script after you connected as the user meal (the \c command did not change the current user)

If you do want rawan95 to be the owner of the tables, you need to either grant the select privilege after creating all tables:

grant select on all tables in schema public to meal;

Or, you can change the default privilege before creating the tables (before running db_build.sql), so that they are applied to all tables in the future:

alter default privileges in schema public
  grant select on all tables to meal;

The alter default privileges only has an effect for tables that are created after that. So to fix your current setup, you need to first grant select on the existing tables, and the change the default privileges for all tables that are created in the future.

Permission denied for relation <table_name>

2 Answers