Thread: Users, groups and inheritance questions

Hi chaps,

Apologies in advance if there's something in the docs I've missed here, but I have had a good look around and I can't
finda good explanation anywhere. 

I'm looking at setting up group roles to manage our users, but I can't quite get my head around how the inheritance is
supposedto work, I'm hoping I've just totally overlooked something here. 

I created a group 'admins' as follows:

CREATE ROLE admins NOSUPERUSER NOINHERIT CREATEDB CREATEROLE;

Then I create a user in this group:

CREATE USER test WITH PASSWORD 'passw' IN GROUP admins;

So I can see in pgAdmin for my test user:

CREATE ROLE test LOGIN
  ENCRYPTED PASSWORD 'md5b140e5c3c4fb663063316e011e54ec3d'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT admins TO test;

This test user can't create databases, nor can it create roles. I get "permission denied to create role"

I thought that if user 'test' was in group 'admins' and I specified INHERIT then it'd inherit those permissions?

I'm confused??

Thanks
Glyn







      __________________________________________________________
Sent from Yahoo! Mail.
A Smarter Inbox http://uk.docs.yahoo.com/nowyoucan.html

Glyn Astill <glynastill@yahoo.co.uk> writes:
> I thought that if user 'test' was in group 'admins' and I specified INHERIT then it'd inherit those permissions?

No, inheritance of permissions only works for GRANT-able permissions;
the special role properties like CREATEDB are outside that scope.

I think though that if test does "SET ROLE admins" then she'd be
able to create a database.

            regards, tom lane

Hello Glyn,it's confusing, but You didn't read the manual very carefully! Short excerpt:The INHERIT
attributegoverns inheritance of grantable privileges (that is, access privileges fordatabase objects and role
memberships).It does not apply to the special role attributes set by CREATEROLE and ALTER ROLE. For example, being a
memberof a role with CREATEDB privilege does notimmediately grant the ability to create databases, even if INHERIT is
set;it would be necessary tobecome that role via SET ROLE before creating a database. bye...Ludwig