Обсуждение: GRANT on group does not give access to group members
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.
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.
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