Score:0

Why can't I grant select privileges to all table in schema with PostgreSQL?

in flag

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.

Score:0
in flag

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;
Score:0
cn flag

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;

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.