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 find a 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 supposed to 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
<p>Hello Glyn,</p><p>it's confusing, but You didn't read the manual very carefully!</p><p> </p><p>Short excerpt:</p><p>The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges for<br />database objects and role memberships). It does not apply to the special role attributes set by CREATE<br />ROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does not<br />immediately grant the ability to create databases, even if INHERIT is set; it would be necessary to<br />become that role via SET ROLE before creating a database.</p><p> </p><p>bye...</p><p>Ludwig</p><p> </p><p> </p>
Import Notes
Resolved by subject fallback
Thanks Tom & ludwig, I understand now.
<b><a rel="nofollow">Glyn Astill</a>
</b>
----- Original Message ----
From: "ludwig@kni-online..de" <ludwig@kni-online.de>
To: pgsql-general@postgresql.org
Sent: Friday, 28 March, 2008 3:24:34 PM
Subject: Re: [GENERAL] Users, groups and inheritance questions
Hello Glyn,
it's confusing, but You didn't read the manual very carefully!
Short excerpt:
The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges for
database objects and role memberships). It does not apply to the special role attributes set by CREATE
ROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does not
immediately grant the ability to create databases, even if INHERIT is set; it would be necessary to
become that role via SET ROLE before creating a database.
bye...
Ludwig
__________________________________________________________
Sent from Yahoo! Mail.
A Smarter Inbox http://uk.docs.yahoo.com/nowyoucan.html
Import Notes
Resolved by subject fallback