Grant Privileges on Future Tables in Postgresql

Grant privileges on future tables in PostgreSQL?

It looks like the solution is to alter default privileges for backup user:

alter default privileges in schema public grant all on tables to backup;
alter default privileges in schema public grant all on sequences to backup;

From the comment by Matt Schaffer:

As caveat, the default only applies to the user that executed the
alter statement. This confused me since I was driving most of my
permissions statements from the postgres user but creating tables from
an app user. In short, you might need something like this depending on
your setup:

ALTER DEFAULT PRIVILEGES FOR USER webapp IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup;
ALTER DEFAULT PRIVILEGES FOR USER webapp IN SCHEMA public GRANT SELECT ON TABLES TO backup;

Where webapp is the user that will be creating new tables in the futrue and backup is the user that will be able to read from new tables created by webapp.

Postgres: Grant access to future tables to various users

If you ALTER DEFAULT PRIVILEGES FOR ROLE group_x, that means that the privileges are only granted on future objects created by user group_x.

So you need to specify the user that creates tables in the FOR ROLE clause.

Postgres: granting access to a role/user for future tables created by a different role/user

You should write

ALTER DEFAULT PRIVILEGES FOR USER role__migration ...

If you omit the FOR USER clause, the privileges are only granted on objects created by the user who ran ALTER DEFAULT PRIVILEGES.

With the above statement, the privileges are granted when role__migration creates an object. That does not extend to members of the role role__migration.

PostgreSQL - Grant select on all tables (and future tables), in *all schemas*

You can't make that, as said here : grant usage & privileges on future created schema in PostgreSQL

The best is to think the other way: every time you create a schema, you GRANT the role at the same time: (Take a look at the link for more information)

CREATE FUNCTION new_user_schema (user text, pwd text) RETURNS void AS $$
DECLARE
  usr name;
  sch name;
BEGIN
  -- Create the user
  usr := quote_identifier(user);
  EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', usr, quote_literal(pwd));

  -- Create the schema named after the user and set default privileges
  sch := quote_identifier('sch_' || user);
  EXECUTE format('CREATE SCHEMA %I', sch);
  EXECUTE format('ALTER SCHEMA %I OWNER TO %L', sch, usr);
  EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I
                    GRANT SELECT ON TABLES TO %L', sch, usr);
END; $$ LANGUAGE plpgsql STRICT;

PostgreSQL grant access to table

The way you wrote the ALTER DEFAULT PRIVILEGES statement, it applies to tables created by the user that ran the statement.

To change the default privileges for tables created by user1, run

ALTER DEFAULT PRIVILEGES FOR ROLE user1 IN SCHEMA data GRANT ALL ON TABLES TO user2;

---

Related Topics