We help IT Professionals succeed at work.

How to grant access to multiple tables in PostgreSQL

chaak asked
Is there a way to grant access to multiple tables at once?  I know how to create groups, but I have dozens of tables that need access by the developers (set up as a group) and read only access needed by the apache user.  Is there anything such as a cascading "Grant" option like there is with "Revoke"?
Watch Question



1. Use a Schema
One idea would be to put the tables you need to
grant access to into a different schema.
Then you just:
GRANT ALL ON SCHEMA some_schema TO developers;

2. Another more specifc way, if you know more about the table names...
If the tables have a name you could filter, then there is another way to do this:
Generate a script via the code below that you will then run via psql.
The code filters out PostgreSQL system tables (that start with pg_ and sql_)
to be sure that you don't grant access to these tables.
The filter_on_table_name could be anything, for example:
 - all tables that start with 'start'
 - all tables that start with 'start' have a specific 'middle' and end with 'end'

  'GRANT ALL ON'||tablename||' TO GROUP developers;' 
 tablename NOT LIKE 'pg_%' AND tablename NOT LIKE 'sql_%' AND
 tablename LIKE 'filter_on_table_name';

Open in new window


Thank you very much.  Option 2 is exactly what I needed.  Definitely something I should have done myself, but I guess I was having one of those days.  Thank you for the perfect answer.  (Still would be nice if Postgres had a cascading grant feature)