Granting Privileges in Postgres

From: Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Granting Privileges in Postgres
Date: 2011-08-08 04:58:37
Message-ID: 4E3F6CFD.8060003@orkash.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2011-08-08 05:03:45 Re: Granting Privileges in Postgres
Previous Message Pavel Stehule 2011-08-08 04:39:46 Re: Query with rightmost function does not use index