Thread: Granting Privileges in Postgres

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

On 08/07/11 9:58 PM, 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.

GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO rolename;

this ON ALL TABLE IN SCHEMA option is new in 9.0



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

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

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

On Mon, 2011-08-08 at 11:42 +0530, Adarsh Sharma wrote:
> 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;
>

For sequences, you need to look at information_schema.sequences. For
others, well, it depends on what objects you'll have in your database.


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