Re: Granting Privileges in Postgres

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.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com