Re: ROLE INHERIT

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

On 2/15/07, Kenneth Downs <ken(at)secdat(dot)com> wrote:
>
> Tom Lane wrote:
>
> "David Legault" <legault(dot)david(at)gmail(dot)com> <legault(dot)david(at)gmail(dot)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
>
>
>
>