| 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: | Whole Thread | Raw Message | 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
| 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 |