Wednesday, August 04, 2004

Postgres - GRANT on all tables in a DB/Schema

So I was trying to figure out a way to grant all permissions on a postgres DB to a certain user, but the (seemingly) obvious grant all on database foo to bar didn't result in the desired select/update/insert permissions being set.

A lengthy google later and some poking around in groups.google.com found me this thread that gave me a nice easy query to get the statements I wanted, and in the interests of documenting findings (and so that I can find this tip again later when I forget it) I'm posting it here:

select 'grant all on '||schemaname||'.'||tablename||' to bar;' from pg_tables where schemaname in ('baz', 'quux') order by schemaname, tablename;
The link to that thread has other queries for views, sequences & functions. Nice work, John Sidney-Woollett, whoever you are.

12 Comments:

Blogger Saemon said...

What that in like english?

8:49 am  
Blogger Ben said...

I make no apology for geeky posts; this definitely falls under the categories of "databases, geeky stuff" :)

6:02 pm  
Anonymous Anonymous said...

Holy crap, thanks so much!

I wonder why there isn't a better way to do this.

5:53 am  
Anonymous Anonymous said...

Thanks so much for this query... its soo much useful

3:34 am  
Anonymous Robert Treat said...

Actually there *IS* a better way to do this, but it requires Postgres 9. Check out the GRANT ON ALL TABLES IN SCHEMA and similar options in the grant docs:
http://www.postgresql.org/docs/current/interactive/sql-grant.html

6:03 am  
Anonymous Anonymous said...

Can't believe it took them nine versions to figure out that might be useful. Doh!

7:41 am  
Anonymous Suzee said...

Great nugget of info there Ben! Thanks a million!!!

3:32 am  
Anonymous Anonymous said...

great.. it helped me..

12:48 am  
Blogger KTamas said...

Thanks, it helped me too.

10:53 pm  
Blogger KTamas said...

Thanks!

10:53 pm  
Anonymous Anonymous said...

Thanks ! I like your bran dump; it looks same than mine ...

10:43 pm  
Anonymous Anonymous said...

Thanks! Your command was a life-savior! (Grant permissions manually on 500+ tables on a schema wasn't a nice perspective...)

8:27 pm  

Post a Comment

<< Home