5/19/2010

How to grant select privileges to all tables in database in PostgreSQL

After a couple of research, I figured out the command that grants select privileges to all tables of the database in PostgreSQL.

 select 'grant select on '||schemaname||'.'||tablename||' to username;' from pg_tables where schemana
me in ('public');

Also in the case of views it would be the following.

 select 'grant select on '||schemaname||'.'||viewname||' to username;' from pg_views where schemaname
 in ('public');

You'll get the list of SQL statements to grant all tables the privileges you want like following.

                          ?column?
------------------------------------------------------------
 grant select on public.table_a to username;
 grant select on public.table_s to username;
 grant select on public.table_d to username;
 grant select on public.table_f to username;
 grant select on public.table_g to username;
........

(xx rows)

Then, excute those SQL statements.
That's it.:)

I referred the following post.
http://bensbrain.blogspot.com/2004/08/postgres-grant-on-all-tables-in.html 

No comments: