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
>
>