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 <[EMAIL PROTECTED]> wrote:
Tom Lane wrote:
"David Legault" <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> 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
