Grant permissions to user for any new tables created in postgresql

postgresql grant database-permissions
ishan picture ishan · Oct 11, 2013 · Viewed 13.7k times · Source

Currently I am using this to grant permissions:

grant select on all tables in schema public to <user_name>;

alter default privileges in schema public grant select on tables to <user_name>;

According to the documentation, the second statement should have resolved the problem. It does not however auto grant permissions to user_name when a new table is added to the public schema.

I am using this user (user_name) to copy data over to another database.

Answer

ishan picture ishan · Oct 11, 2013

Found the answer. It is in this line in the ALTER DEFAULT PRIVILEGES documentation.

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

I was using alter default privileges from a different user than the one creating the tables.

Make sure to set the role to the user creating the table before the alter default privilege statement:

SET ROLE <user_that_creates_new_tables>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <user_name>;

Related questions

What GRANT USAGE ON SCHEMA exactly do?

I'm trying to create for the first time a Postgres database, so this is probably a stupid question. I assigned basic read-only permissions to the db role that must access the database from my php scripts, and I have a …

Read More
Permission denied for relation

I tried to run simple sql command: select * from site_adzone; and I got this error ERROR: permission denied for relation site_adzone What could be the problem here? I tried also to do select for other tables and got …

Read More
Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More