Grant permissions to user for any new tables created in postgresql

Question

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.

Solution 1

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>;

Solution 2

I was looking for same thing, I found other way to solve this. Based on postgresql documentation we can create event trigger, so when new table is created, grant query will execute automatically. So no matter who created new table, other user allowed to use it.

CREATE OR REPLACE FUNCTION auto_grant_func()
RETURNS event_trigger AS $$
BEGIN
    grant all on all tables in schema public to <username>;
    grant all on all sequences in schema public to <username>;
    grant select on all tables in schema public to <username>;
    grant select on all sequences in schema public to <username>;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER auto_grant_trigger
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE PROCEDURE auto_grant_func();

Solution 3

To grant default privileges, you need to grant to the user you are creating the table with.

You are creating the tables as SA_user, but reading the tables as READ_user. Your code needs to look like:

ALTER DEFAULT PRIVILEGES 
FOR USER SA_user
IN SCHEMA schema_name
GRANT SELECT ON TABLES TO READ_user;

So whenever the SA_user creates a table, it will grant select rights for the READ_user.

Comments

[WhatsThePoint]

never done postgres but why isn't it execute function?

[Ratnakri]

@WhatsThePoint, sorry i dont get it, can you explain your question?

[WhatsThePoint]

at the bottom you have done execute procedure, why isn't it function?

[Erwin Brandstetter]

@WhatsThePoint: EXECUTE PROCEDURE in this context is a misleading syntax oddity, inspired by a vague SQL standard, and eventually fixed in Postgres 11. See: dba.stackexchange.com/a/255319/3684

[cowbert]

Since this is currently the most upvoted answer, leon de vries's answer is most applicable for postgresql versions >= 9.0