28

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.

ishan
  • 1,029
  • 1
  • 12
  • 19
  • You might want to have a look at [How to GRANT SELECT by default to B, on tables created by A?](https://dba.stackexchange.com/q/238898/181382): [this solution](https://dba.stackexchange.com/a/238899/181382) solves it using default privileges too. – ebosi May 23 '19 at 14:19

3 Answers3

26

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>;
arturomp
  • 28,790
  • 10
  • 43
  • 72
ishan
  • 1,029
  • 1
  • 12
  • 19
  • 1
    Since this is currently the most upvoted answer, [leon de vries's answer](https://stackoverflow.com/a/58415200/2718295) is most applicable for postgresql versions >= 9.0 – cowbert Feb 19 '21 at 19:32
18

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.

Matt Hampel
  • 5,088
  • 12
  • 52
  • 78
leon de vries
  • 179
  • 1
  • 4
  • 1
    Is there any way to query for these existing relationships in the database? In other words, after I run this command, how can I verify that it was saved somewhere correctly? Thanks. – vinhboy Jul 22 '22 at 22:53
  • @vinhboy Something around these lines SELECT defaclnamespace::regnamespace AS schema_name, defaclrole::regrole AS role_name, defaclacl FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclrole = (SELECT oid FROM pg_roles WHERE rolname = 'SA_user') AND defaclnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_name'); – Luis Da Silva Jun 13 '23 at 10:16
15

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();
ebosi
  • 1,285
  • 5
  • 17
  • 37
Ratnakri
  • 309
  • 3
  • 4