CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
bilelovitch
bilelovitch

Reputation: 2175

Grant an automatic select to a all tables / PostgreSQL

I have a production database, where the user can create a set of tables that inherits a master table X. The matter is when i want to apply an automatic grant for all the tables;

Naturally with this command : GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only ; this will only be applied to tables that have already been created.

The aim, is to applied the command for all the newest tables.

NB : I already tried to make a trigger on tables pg_class, pg_namespace but it's impossible and it's the same for the pg_tables view;

Upvotes: 2

Views: 2589

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21316

You can set the default grants for newly created objects with the ALTER DEFAULT PRIVILEGES command, e.g.:

ALTER DEFAULT PRIVILEGES
FOR USER creator_role IN SCHEMA public
GRANT SELECT ON TABLES TO read_only;

As an aside, while you can't install triggers on the system catalogs, you can achieve the same thing (in Postgres 9.3+) with event triggers, which fire on any CREATE/DROP/ALTER statement.

Upvotes: 3

Related Questions