Re: Granting Privileges in Postgres

Guillaume Lelarge wrote:

On Mon, 2011-08-08 at 10:28 +0530, Adarsh Sharma wrote: 

Dear all,

Today I researched on giving privileges in Postgres databases. I have 4 
databases and near about 150 tables, 50-60 sequences and also some views 
in it.

I want to give privileges to a new user in all these objects. I created 
a function for that but don't know how to give privileges on all objects 
all at once.

**************Function for granting all privileges on all tables in 
postgres database**************************
Step 1 : Create a new user with password

create user abc with password 'as123';

Step 2 :

create function grant_all(a text) returns void as $$

declare

name text;
user_name alias for $1;

begin

for name in select table_name from information_schema.tables where 
table_schema = 'public' loop

execute 'grant all on table ' || name || ' to ' ||  user_name ;

end loop;

end;

$$ language plpgsql;

Step 3 :

select grant_all('abc');


Step 4 :

Finish

This will grant on tables only but Do I need to manually issue grant 
commands on all objects.
I want to issue it all at once.   

You just need to add the other "GRANT ALL ON <object type> <object name>
to <user name>" in your function.

But how it picks all view & sequence names one by one, I iterate in my loop each table name .
Manually the command is :

grant all on sequence_name to user_name;

Thanks