How to grant access to multiple tables in PostgreSQL

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"?
chaakAsked:
Who is Participating?
 
adrpoConnect With a Mentor Commented:

Hi,

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:
'start%'
 - all tables that start with 'start'
'start%middle%end'  
 - all tables that start with 'start' have a specific 'middle' and end with 'end'

Cheers,
za-k/
SELECT 
  'GRANT ALL ON'||tablename||' TO GROUP developers;' 
FROM 
  pg_tables 
WHERE
 tablename NOT LIKE 'pg_%' AND tablename NOT LIKE 'sql_%' AND
 tablename LIKE 'filter_on_table_name';

Open in new window

0
 
chaakAuthor Commented:
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)
0
All Courses

From novice to tech pro — start learning today.