Thread: GRANT on group does not give access to group members

GRANT on group does not give access to group members

From
wild_oscar
Date:
Dear all,

I'm a bit confused about privilege management in PostgreSQL.

I have a database "db1" , schema "schema1" and table "table1", created with
a superuser.

Now, following the documentation (and what I've learnt about user
management), I created a group called admin and a user login, and gave the
login user the admin privileges.

CREATE ROLE admin NOINHERIT;
CREATE ROLE login LOGIN ENCRYPTED PASSWORD
'md5c2740ac0c81b17602438f3ac849fea08' NOINHERIT;
GRANT admin TO login;

Now, if I grant:

GRANT ALL ON TABLE schema1.table1 TO GROUP admin;

Selecting * from the tabel with user "login" won't work:

ERROR: permission denied for relation table1

If I grant directly to the user:

GRANT ALL ON TABLE schema1.table1 TO login;

It WORKS. Now, that makes the idea of creating few role groups and setting
privileges to them, and later adding maybe a lot of users and just adding
them to the role groups pointless. So why does PostgreSQL work like this and
how can I achieve the common "grant to group" approach?

On another question, if I want to grant privileges to all tables I have to
do them ONE BY ONE. Granting the privileges on the database or the schema
won't recursively grant them on the tables, am I correct?

Thanks a lot for your help!
--
View this message in context:
http://www.nabble.com/GRANT-on-group-does-not-give-access-to-group-members-tf4435748.html#a12654908
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: GRANT on group does not give access to group members

From
wild_oscar
Date:
Well, after further searching and reviewing the code I believe the problem
was the NOINHERIT in the login role creation.

So the remaining question is:

On another question, if I want to grant privileges to all tables I have to
do them ONE BY ONE. Granting the privileges on the database or the schema
won't recursively grant them on the tables, am I correct?

Is the only solution the usage of scripts such as this one:
http://pgedit.com/public/sql/acl_admin/index.html
http://pgedit.com/public/sql/acl_admin/index.html
?



wild_oscar wrote:
>
> Dear all,
>
> I'm a bit confused about privilege management in PostgreSQL.
>
> I have a database "db1" , schema "schema1" and table "table1", created
> with a superuser.
>
> Now, following the documentation (and what I've learnt about user
> management), I created a group called admin and a user login, and gave the
> login user the admin privileges.
>
> CREATE ROLE admin NOINHERIT;
> CREATE ROLE login LOGIN ENCRYPTED PASSWORD
> 'md5c2740ac0c81b17602438f3ac849fea08' NOINHERIT;
> GRANT admin TO login;
>
> Now, if I grant:
>
> GRANT ALL ON TABLE schema1.table1 TO GROUP admin;
>
> Selecting * from the tabel with user "login" won't work:
>
> ERROR: permission denied for relation table1
>
> If I grant directly to the user:
>
> GRANT ALL ON TABLE schema1.table1 TO login;
>
> It WORKS. Now, that makes the idea of creating few role groups and setting
> privileges to them, and later adding maybe a lot of users and just adding
> them to the role groups pointless. So why does PostgreSQL work like this
> and how can I achieve the common "grant to group" approach?
>
> On another question, if I want to grant privileges to all tables I have to
> do them ONE BY ONE. Granting the privileges on the database or the schema
> won't recursively grant them on the tables, am I correct?
>
> Thanks a lot for your help!
>

--
View this message in context:
http://www.nabble.com/GRANT-on-group-does-not-give-access-to-group-members-tf4435748.html#a12655884
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: GRANT on group does not give access to group members

From
Sean Davis
Date:
wild_oscar wrote:
> Well, after further searching and reviewing the code I believe the problem
> was the NOINHERIT in the login role creation.
>
> So the remaining question is:
>
> On another question, if I want to grant privileges to all tables I have to
> do them ONE BY ONE. Granting the privileges on the database or the schema
> won't recursively grant them on the tables, am I correct?
>
> Is the only solution the usage of scripts such as this one:
> http://pgedit.com/public/sql/acl_admin/index.html
> http://pgedit.com/public/sql/acl_admin/index.html
>

PgAdminIII will do these tasks quite nicely using the grant wizard.

Sean