Thread: ROLE INHERIT

Hello,

I'm a bit new to Postgre, and I'm experimenting with the roles stuff.

I want to know why If I create a role called "administrator" (a group basically, no login) :

CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE;

And then create a user

CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator;

admin doesn't have the CREATEROLE privilege himself, but because he is part of a group that has it, why doesn't this fall back on him having it?

When I try to use that admin user to create another role, it says insufficient privileges.

Am I missing something in this role stuff ?

Thanks

David

David Legault wrote:
> Hello,
>
> I'm a bit new to Postgre, and I'm experimenting with the roles stuff.
>
> I want to know why If I create a role called "administrator" (a group
> basically, no login) :
>
> CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE;
>
> And then create a user
>
> CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator;
>
> admin doesn't have the CREATEROLE privilege himself, but because he is
> part of a group that has it, why doesn't this fall back on him having it?
>
> When I try to use that admin user to create another role, it says
> insufficient privileges.
>
> Am I missing something in this role stuff ?
>
> Thanks
>
> David
>
Doesn't the inherit property need to be on the role that will do the
inheriting? I.e. if admin is to inherit the privileges of administrator,
then admin needs the inherit property.

"A role with the INHERIT attribute can automatically use whatever
database privileges have been granted to all roles it is directly or
indirectly a member of."

--
Paul Lambert
Database Administrator
AutoLedgers

David Legault escribió:
> Hello,
>
> I'm a bit new to Postgre, and I'm experimenting with the roles stuff.
>
> I want to know why If I create a role called "administrator" (a group
> basically, no login) :
>
> CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE;
>
> And then create a user
>
> CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator;
>
> admin doesn't have the CREATEROLE privilege himself, but because he is part
> of a group that has it, why doesn't this fall back on him having it?
>
> When I try to use that admin user to create another role, it says
> insufficient privileges.
>
> Am I missing something in this role stuff ?

A single point, which is that while the privileges that are inherited
are those that you can GRANT and REVOKE with the respective commands.
CREATEROLE and the others are not inherited.

Also, keep in mind that while role "admin" does not have CREATEDB
privilege, if you grant it the CREATEROLE privilege it will easily be
able to create a database by creating another role with CREATEDB
privilege.  So don't grant CREATEROLE to just anyone.

FYI, the short name of PostgreSQL is "Postgres", not Postgre.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

The docs should probably be more clear about this because you can do:

GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]


GRANT on Roles

This variant of the GRANT command grants membership in a role to one or more other roles. Membership in a role is significant because it conveys the privileges granted to a role to each of its members. GRANT administrator TO admin;

I thought it would transfer that CREATEROLE privilege too.

Thanks

David

"David Legault" <legault.david@gmail.com> writes:
> I thought it would transfer that CREATEROLE privilege too.

This is documented someplace ... ah, under CREATE ROLE:

: 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.

The main reason we did that is that SUPERUSER seemed a bit too dangerous
to be an inheritable privilege.  You could argue the other role
attribute bits either way, but for simplicity they all act the same.

            regards, tom lane

Tom Lane wrote:

"David Legault" <legault.david@gmail.com> writes: 

I thought it would transfer that CREATEROLE privilege too.   

I've been dying to get 2 cents in on this.  Tell me if this suggestion makes any sense.

We use real database users in our systems, we don't connect in with an over-endowed user and then arbitrate security in client code.   Therefore, we depend entirely upon the server's ability to enforce security.

The practical advantage of this, which is huge, is that nowhere in my Postgres settings do I have to make allowance for the web user (apache) to "go root" with respect to Postgres.  As a general rule we consider this good of course, because a remote exploit on the web server could not do anything the user could not do anyway.

Except for the hole.  On a public site that lets users register, we have to have  way to let the web server assume the role of somebody who has createuser privelege, and that's pretty much the end of the no-root policy.  If an exploit could be placed, it could simply go into that mode and create a superuser. 

What would be really nice is if you could limit the ability of CREATEUSER to grant roles.  A nice general solution would be to allow a user with CREATEUSER privelege to only put other users into the same groups that person is in, or perhaps into a list specified by a higher-privelege user.

What's chances of anything like that showing up?

And, dumb question, am I mistaking the purpose of INHERIT and it already does what I'm saying?  I don't think so because INHERIT does not let somebody create users out of the void.

This is documented someplace ... ah, under CREATE ROLE:

: 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.

The main reason we did that is that SUPERUSER seemed a bit too dangerous
to be an inheritable privilege.  You could argue the other role
attribute bits either way, but for simplicity they all act the same.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend 

Well basically, I was planning on building my user accounts directly using the postgres user/group policy and get away from the model superuser always connects to the DB.

I kind of think it defeats the purpose of inheritance of attributes if you can't inherit from all attributes of a certain group.

basically, you have a web app with a guest account into a guests group for any anonymous user that comes in. Once they log in, they have access to their privileges. If an "administrator" group user logs in I want him to be able to add "sub-admin" people and administer the users/groups via an admin web interface. Since my application is built in modules, all web application privileges (usually page/action combo) are correlated with the group privileges (which determine which stored proc/functions) that person has access via his membership to the groups.

The way I see it , is just an extra loop for me. When I create a new user and assign him a group, I'll simply check if any of the group membership he has contains the CREATEROLE and just give it to him and vice-versa if he loses a group and doesn't have it anymore.

Could there be an option (off by default) that could tell Postgres to INHERIT those 4 conditions like the command:

GRANT role [, ...] TO username [, ...] [ WITH [ADMIN OPTION] [FULL INHERIT] ] 

Or something similar.

Thanks

David

* Kenneth Downs (ken@secdat.com) wrote:
> We use real database users in our systems, we don't connect in with an
> over-endowed user and then arbitrate security in client code.
> Therefore, we depend entirely upon the server's ability to enforce security.

We do the same thing. :)

> The practical advantage of this, which is huge, is that nowhere in my
> Postgres settings do I have to make allowance for the web user (apache)
> to "go root" with respect to Postgres.  As a general rule we consider
> this good of course, because a remote exploit on the web server could
> not do anything the user could not do anyway.

Right, exactly.

> Except for the hole.  On a public site that lets users register, we have
> to have  way to let the web server assume the role of somebody who has
> createuser privelege, and that's pretty much the end of the no-root
> policy.  If an exploit could be placed, it could simply go into that
> mode and create a superuser.

Thankfully we don't have any systems with this requirement. :)  But, a
possible solution might be to create an OOB mechanism by which you can
only pass a username/pw/whatever is needed to create a user.  ie: dump
it into a file that's checked using cron or something like that.  Then
another process (the cronjob, or what have you) is privileged and can
create the user on your behalf.  You have to be very careful in
programming the privleged job but ideally the worst an attacker could do
would be to create additional users within whatever the normal
constraints are (which, theoretically, is something they can do from the
website *anyway*).

> What would be really nice is if you could limit the ability of
> CREATEUSER to grant roles.  A nice general solution would be to allow a
> user with CREATEUSER privelege to only put other users into the same
> groups that person is in, or perhaps into a list specified by a
> higher-privelege user.
>
> What's chances of anything like that showing up?

Honestly, I think the above approach would be better than having
something in the database to try and do that...

    Thanks,

        Stephen

Kenneth Downs <ken@secdat.com> writes:
> Except for the hole.  On a public site that lets users register, we have
> to have  way to let the web server assume the role of somebody who has
> createuser privelege, and that's pretty much the end of the no-root
> policy.  If an exploit could be placed, it could simply go into that
> mode and create a superuser.

> What would be really nice is if you could limit the ability of
> CREATEUSER to grant roles.

I believe that a role that has CREATEROLE but not SUPERUSER can only
create non-SUPERUSER roles.  Does that help?

            regards, tom lane

Tom Lane wrote:

Kenneth Downs <ken@secdat.com> writes: 

Except for the hole.  On a public site that lets users register, we have 
to have  way to let the web server assume the role of somebody who has 
createuser privelege, and that's pretty much the end of the no-root 
policy.  If an exploit could be placed, it could simply go into that 
mode and create a superuser.    

What would be really nice is if you could limit the ability of 
CREATEUSER to grant roles.   

I believe that a role that has CREATEROLE but not SUPERUSER can only
create non-SUPERUSER roles.  Does that help?
		regards, tom lane 

Probably not.  The problem is that a person with createrole can create any role, so by mistake or exploit a user can be given admin access (admin here defined by roles given, not by SUPERUSER flag) to another database by a role that itself is supposed to be a public-only mostly read-only role.