2

I have a database my_database, and it have some tables named my_table_1, my_table_2, ... , my_table_128 under schema public. I need to grant select privileges for all tables in schema public to user my_db_user, so I execute SQL command below:

GRANT SELECT ON ALL TABLES IN SCHEMA public to my_db_user;

And it executed without error. But after I connect with user my_db_user and tried to select something, it displaied permission denied. And I tried execute SQL command below with admin user:

GRANT SELECT ON TABLE public.my_table_1 TO my_db_user;

Then connect with user my_db_user and tried to select something, it works.

fajin yu
  • 195
  • 3
  • 10

2 Answers2

0

Did you grant permissions to all tables before creating the tables? If so, you may need to grant permissions again.

I like making all items in the public schema public to all users, so you may want to try:

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO PUBLIC;
dcbeckman
  • 1
  • 1
0

ERROR: permission denied for table asm_info SQL state: 42501

  1. Connect with user who is superuser or user with grantable privileges.
  2. Connect to the database in which table exists. [Most Important]
  3. Then, run the following command : GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO developer;

-bash-4.1$ psql erp; erp->database where table exists

erp=# \c

You are now connected to database "erp" as user "postgres".

erp=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO developer;

Foyez Ahmed
  • 1