Thread: Per database users/admins, handy for database virtual hosting...
I've had to work through this and have with a series of messy tables and functions, but this screams a need for a more elegant solution. I've dug through the archives and didn't come up with a satisfying long term answer for virtual hosting beyond what I've already implemented. Per cluster users is handy for the admins because I can create one account for me and not think about needing to create an account for every database in the cluster. Per database users, on the other hand, is ideal for database virtual hosting, but is a PITA for DBA's who need to create accounts in every database in the cluster. I haven't read much in the last few months, but archives from 2002 suggested there wasn't much on the table in terms of making this happen beyond adding a function that runs as a DBA to create users (which I've done). What's the feasibility of augmenting the system catalogs so that something similar to the following is possible: CREATE VIEW pg_catalog.pg_shadow ASSELECT usename, usesysid, usecreatedb, usesuper, usecatupd, passwd, valuntil, useconfigFROMpg_catalog.pg_shadow_cluster UNION ALLSELECT usename, usesysid, usecreatedb, usesuper, usecatupd, passwd,valuntil, useconfigFROM pg_catalog.pg_shadow_db; And then set up RULEs to direct INSERTs, UPDATEs, DELETEs from pg_shadow to pg_shadow_db. CREATE USER/ALTER USER operates on pg_catalog_db, then CREATE CLUSTER USER/ALTER CLUSTER USER operates on pg_catalog_cluster. Tom, what do you think? What other ideas do you have kicking around in your head? *shrug* Something for the TODO list and/or an inspired hacker. -sc -- Sean Chittenden
On 25-Mar-04, at 8:18 PM, Sean Chittenden wrote: > I haven't read much in the last few months, but archives from 2002 > suggested there wasn't much on the table in terms of making this > happen beyond adding a function that runs as a DBA to create users > (which I've done). Well, the db_user_namespace GUC var has been implemented, but it is a hack. -Neil
Sean Chittenden <sean@chittenden.org> writes:
> What's the feasibility of augmenting the system catalogs so that
> something similar to the following is possible:
> CREATE VIEW pg_catalog.pg_shadow AS
> SELECT usename, usesysid, usecreatedb, usesuper,
> usecatupd, passwd, valuntil, useconfig
> FROM pg_catalog.pg_shadow_cluster
> UNION ALL
> SELECT usename, usesysid, usecreatedb, usesuper,
> usecatupd, passwd, valuntil, useconfig
> FROM pg_catalog.pg_shadow_db;
The main problem I can see is usesysid conflicts. For example suppose
userid 42 is created in database A, and then someone in database B
decides to create a global user with id 42. The latter someone can't
even see that he's causing a problem in database A :-(
I'd be in favor of this if we could find an answer to that one.
Maybe something dirty like reserving separate ranges of sysid for local
and global users would get the job done, but I haven't spent any time
trying to poke holes in that idea...
Come to think of it, the same risk of conflict applies for user *names*,
and we can't easily make an end-run around that.
> And then set up RULEs to direct INSERTs, UPDATEs, DELETEs from
> pg_shadow to pg_shadow_db. CREATE USER/ALTER USER operates on
> pg_catalog_db, then CREATE CLUSTER USER/ALTER CLUSTER USER operates on
> pg_catalog_cluster.
Nope, other way round, default behavior for backwards compatibility must
be to create cluster-wide users. CREATE LOCAL USER is what to add.
regards, tom lane
>> What's the feasibility of augmenting the system catalogs so that >> something similar to the following is possible: > >> CREATE VIEW pg_catalog.pg_shadow AS >> SELECT usename, usesysid, usecreatedb, usesuper, >> usecatupd, passwd, valuntil, useconfig >> FROM pg_catalog.pg_shadow_cluster >> UNION ALL >> SELECT usename, usesysid, usecreatedb, usesuper, >> usecatupd, passwd, valuntil, useconfig >> FROM pg_catalog.pg_shadow_db; > > The main problem I can see is usesysid conflicts. For example suppose > userid 42 is created in database A, and then someone in database B > decides to create a global user with id 42. The latter someone can't > even see that he's causing a problem in database A :-( Hrm... that's true. The UID sequence would be shared, but that doesn't prevent someone from forcing a DBA from having a non-sequential UID. Here's a list of the scenarios that I can think of: LOCAL USERs, as you suggest later, are bound to a given database, who cares if the local DBA mucks with the UID of the user? They're still confined to their local database and there's no risk to the integrity of the system. A local DBA shouldn't be able to muck with pg_shadow_cluster anyway, so no harm should be possible. Let's say a local DBA creates a user with UID that conflicts with a cluster wide user. What's the worst that could happen? The UID of the cluster wide user inherits perms of the local user with the same UID. In most deployment scenarios where system admins would deploy PostgreSQL and use LOCAL USERS, the CLUSTER USERS are probably a DBAs with his/her pg_catalog.pg_shadow_cluster.usesuper = TRUE, so inheriting privs is of little consequence. If a CLUSTER USER is just a normal user, then the permissions could get wonky. It may be worth while logging a UID conflict and closing the connection for security reasons if usesuper = FALSE. It seems like it'd be possible to have TRIGGERs on pg_shadow_db that'd check to make sure the UID wasn't already in use and make a stink if it were already in use in the cluster's catalog. You can only protect people from wandering off the range so far... Beyond a database picking up problems, I can't think of any other consequences... at least not that'd affect the entire cluster... but I'm still a bit new to the problem and may have missed something. > Maybe something dirty like reserving separate ranges of sysid for local > and global users would get the job done, but I haven't spent any time > trying to poke holes in that idea... Well, it's reasonably tried and true in the *NIX world with reserved UIDs being the only ones allowed to bind to ports less than 1024. Beyond being arbitrary limits, it seems to have worked well to date. Reserving the lower 10K UIDs for cluster users isn't a bad idea... going further, given that I haven't heard of a database with more than 1B users... use 2^30 through 2^31 as the UID range for local users and 0 through (2^30 - 1) as the range for cluster wide UIDs. If someone gripes about having only 1B UIDs for cluster wide/local admin purposes... > Come to think of it, the same risk of conflict applies for user > *names*, > and we can't easily make an end-run around that. That's why I used UNION ALL in my example. Reserved usernames that are in the cluster should be just as valid as usernames that are in the local database table. I'm not sure how the authentication bit works internally, but that seems like a matter of changing the routine to do: SELECT TRUE FROM pg_catalog.pg_shadow WHERE usename = :username AND password = :pw; and checking to see if the query returns at least one row. >> And then set up RULEs to direct INSERTs, UPDATEs, DELETEs from >> pg_shadow to pg_shadow_db. CREATE USER/ALTER USER operates on >> pg_catalog_db, then CREATE CLUSTER USER/ALTER CLUSTER USER operates on >> pg_catalog_cluster. > > Nope, other way round, default behavior for backwards compatibility > must > be to create cluster-wide users. CREATE LOCAL USER is what to add. Ah, good point. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
>> Come to think of it, the same risk of conflict applies for user
>> *names*, and we can't easily make an end-run around that.
> That's why I used UNION ALL in my example. Reserved usernames that are
> in the cluster should be just as valid as usernames that are in the
> local database table.
I don't follow. You can't think that allowing the same name to appear
globally and locally is a good idea. If I say "GRANT TO foo", who am
I granting privileges to? And I don't want to say that there is no
difference because they are the same user. That will open up some nasty
security holes, eg, being able to pretend that you are the global
postgres superuser if you can set the password for a local user by the
same name.
regards, tom lane
> You can't think that allowing the same name to appear > globally and locally is a good idea. Actually, I do think it is a good idea. > If I say "GRANT TO foo", who am > I granting privileges to? SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than CLUSTER GRANT TO foo; SET username_precedence TO GLOBAL,LOCAL; GRANT TO foo; > And I don't want to say that there is no > difference because they are the same user. Agreed, they should be the same user. > That will open up some nasty > security holes, eg, being able to pretend that you are the global > postgres superuser if you can set the password for a local user by the > same name. Agreed, but if a cluster is using LOCAL USERs, I doubt highly that CLUSTER/GLOBAL users would be in use much beyond super users. -sc -- Sean Chittenden
>> I haven't read much in the last few months, but archives from 2002 >> suggested there wasn't much on the table in terms of making this >> happen beyond adding a function that runs as a DBA to create users >> (which I've done). > > Well, the db_user_namespace GUC var has been implemented, but it is a > hack. And it doesn't handle the case of letting the local database admin create users (without giving them access to the rest of the database), which is what I'm after. -sc -- Sean Chittenden
On Thu, Mar 25, 2004 at 08:24:59PM -0800, Sean Chittenden wrote: > >You can't think that allowing the same name to appear > >globally and locally is a good idea. > > Actually, I do think it is a good idea. > > >If I say "GRANT TO foo", who am > >I granting privileges to? > > SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than > CLUSTER > GRANT TO foo; > SET username_precedence TO GLOBAL,LOCAL; > GRANT TO foo; Yes, it possible, but I not sure if this commands dependence issomething wanted and nice. You can use "GRANT TO LOCALfoo" rather thanconnect more commands together. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
Sean Chittenden <sean@chittenden.org> writes:
>> You can't think that allowing the same name to appear
>> globally and locally is a good idea.
> Actually, I do think it is a good idea.
>> If I say "GRANT TO foo", who am
>> I granting privileges to?
> SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than
> CLUSTER
> GRANT TO foo;
> SET username_precedence TO GLOBAL,LOCAL;
> GRANT TO foo;
>> And I don't want to say that there is no
>> difference because they are the same user.
> Agreed, they should be the same user.
What? You are contradicting yourself. That "precedence" hack makes
sense only if there is a difference.
>> That will open up some nasty
>> security holes, eg, being able to pretend that you are the global
>> postgres superuser if you can set the password for a local user by the
>> same name.
> Agreed, but if a cluster is using LOCAL USERs, I doubt highly that
> CLUSTER/GLOBAL users would be in use much beyond super users. -sc
Exactly my point. I think that it might be possible for a
locally-privileged DBA to give himself superuser privileges by skating
on this confusion between who is whom. Once he creates a local user
with the same name as the global superuser, the door is open to problems
--- not only possible bugs in our own code, but plain old human error on
the part of the real superuser.
In short, I say it's a bad idea with no redeeming social value. I can't
see any positive use-case for having local usernames that conflict with
global ones.
regards, tom lane
>>> You can't think that allowing the same name to appear >>> globally and locally is a good idea. > >> Actually, I do think it is a good idea. > >>> If I say "GRANT TO foo", who am >>> I granting privileges to? > >> SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than >> CLUSTER >> GRANT TO foo; >> SET username_precedence TO GLOBAL,LOCAL; >> GRANT TO foo; > >>> And I don't want to say that there is no >>> difference because they are the same user. > >> Agreed, they should be the same user. ^ n't > What? You are contradicting yourself. That "precedence" hack makes > sense only if there is a difference. Ack, brain-o, you're right: what a difference an "n't" makes. >>> That will open up some nasty >>> security holes, eg, being able to pretend that you are the global >>> postgres superuser if you can set the password for a local user by >>> the >>> same name. > >> Agreed, but if a cluster is using LOCAL USERs, I doubt highly that >> CLUSTER/GLOBAL users would be in use much beyond super users. -sc > > Exactly my point. I think that it might be possible for a > locally-privileged DBA to give himself superuser privileges by skating > on this confusion between who is whom. I don't think that's possible though... let's say there are two databases, hostingco and customer1. That gives us two different pg_shadow_db tables. On top of that, there is a central pg_shadow_cluster table that is shared among all databases. Let's suppose there is: 1) a superuser 'dba' in pg_shadow_cluster (password 'foo'); 2) a superuser 'dba' in pg_shadow_db in the hostingco database (password 'bar'); and, 3) a normal user 'dba' in pg_shadow_db in the customer1 database (password 'baz'). Here are the scenarios with the UNION example I gave: pg_shadow_cluster 'dba' case: *) the 'dba' account in pg_shadow_cluster (dba/cluster) could log in to all of the databases. *) the dba/cluster account could only be logged into if someone had the right password. *) the UID for the dba/cluster account is irrelevant because any perms the UID has won't prevent him/her from walking through the entire database. *) if a database creates a user with the same UID as the dba/cluster, the local database admin doesn't gain anything if the dba/cluster account modifies the database/does work. If the dba/cluster account does do work in the local database with a shared UID, the UID will resolve to the local database first preventing the local account with the shared UID from gaining cluster wide privs (only accounts with cluster superuser privs should be able to change the resolution from LOCAL,GLOBAL to GLOBAL,LOCAL). hostingco 'dba' case: *) The 'dba' account in pg_shadow_db (dba/hostingco) could log in to only the local database housing the pg_shadow_db table. *) The dba/hostingco account behaves identically to the dba/cluster... I don't think there's a need to even prevent this account from changing the username resolution because changing databases requires a new connection where GUC settings are reset. *) The dba/hostingco account can't log into any other database because the dba/hostingco account only lives in the database specific pg_shadow_db table. *) If the dba/cluster admin logs into the hostingco database, the UID resolution would be GLOBAL,LOCAL instead of LOCAL,GLOBAL. If there is a shared UID, the local admin who created the shared UID account only stands to loose, but can't gain elevated privs. customer1 'dba' case: *) Identical to the dba/hostingco case except the account isn't a superuser. Have I missed a case? As for the reason for the usefulness of having shared usernames, the 'www', 'dba', 'admin', 'web', 'php', or [commoon_application_name] accounts are very popular names for logging in and I'd like to not deprive customers of ease of use because they're in a hosted environment instead of a dedicated environment. Another solution would be to have CREATE USER done by a local admin create users in the form of 'username@database'. This prevents duplicate usernames and allows us to use the current hack of local database users. > Once he creates a local user > with the same name as the global superuser, the door is open to > problems > --- not only possible bugs in our own code, but plain old human error > on > the part of the real superuser. How so? Can you give a scenario where this'd make a difference? I think putting a trigger on pg_shadow_db to prevent users from mucking with the UID would be a sufficient anti-foot shooting measure. -sc -- Sean Chittenden
Richard Huxton <dev@archonet.com> writes:
> Maybe it's me being slow, but are we not being over-complicated here? What's
> wrong with saying "database D1 looks up users in local table, D2 in the
> global table". If you are connected to D1, then no-one can see the global
> userlist.
Hmm. That would amount to saying that there are no global superusers
for D1, which might be a bit of a problem --- if local DBA paints
himself into a corner, you can't get him out. Backing up a cluster that
has not got global superusers would be a PITA too.
Still, I think you are right that we gotta think outside the box if
we're going to find a way to do this.
regards, tom lane
On Friday 26 March 2004 15:09, Tom Lane wrote: > Sean Chittenden <sean@chittenden.org> writes: > > > > Agreed, but if a cluster is using LOCAL USERs, I doubt highly that > > CLUSTER/GLOBAL users would be in use much beyond super users. -sc > > Exactly my point. I think that it might be possible for a > locally-privileged DBA to give himself superuser privileges by skating > on this confusion between who is whom. Once he creates a local user > with the same name as the global superuser, the door is open to problems > --- not only possible bugs in our own code, but plain old human error on > the part of the real superuser. Maybe it's me being slow, but are we not being over-complicated here? What's wrong with saying "database D1 looks up users in local table, D2 in the global table". If you are connected to D1, then no-one can see the global userlist. The global user "richard" cannot log into D1, and the local user "richard" can log only into D1. > In short, I say it's a bad idea with no redeeming social value. I can't > see any positive use-case for having local usernames that conflict with > global ones. In a shared-hosting situation, I can see "local super-users" both wanting to create users called (e.g.) "plone". -- Richard Huxton Archonet Ltd
Sean Chittenden <sean@chittenden.org> writes:
> Another solution would be to have CREATE USER done by a local admin
> create users in the form of 'username@database'. This prevents
> duplicate usernames and allows us to use the current hack of local
> database users.
Yeah, I think it would be reasonable to leave that facility as-is and
invent a category of user privileges that only allows creation/deletion
of local usernames. We'd have to think about how that should interact
with other superuser attributes such as the ability to bypass
privileges. But breaking down "superuserness" into finer-grained
privileges has been on the wish list for awhile.
>> Once he creates a local user with the same name as the global
>> superuser, the door is open to problems --- not only possible bugs in
>> our own code, but plain old human error on the part of the real
>> superuser.
> How so? Can you give a scenario where this'd make a difference?
Well, it's the standard sort of attack where you try to trick a user
with more privs than you into doing something he'd not do if he was
aware of who/what he was really doing it to. Something like granting
privileges to a local user when he thought he was granting to a global
user, or vice versa. Or making the wrong user a member of a group.
I'm not sure that this is actually very probable, if the usage scenario
is that global users are always superusers --- there'd seldom be any
reason to go granting them any additional privileges. But if we are
also thinking of having multiple categories of user privileges then it's
less far-fetched.
Other possible problems include resetting the password of the wrong
user. This would be particularly bad if a database's local superuser
can choose the setting of the "encrypt passwords by default" GUC switch
--- he might have the opportunity to see the cleartext password of some
global user. Obviously that hole can be plugged now that I've pointed
it out, but what other ones are there?
regards, tom lane
> Richard Huxton <dev@archonet.com> writes: >> Maybe it's me being slow, but are we not being over-complicated here? >> What's >> wrong with saying "database D1 looks up users in local table, D2 in the >> global table". If you are connected to D1, then no-one can see the >> global >> userlist. > > Hmm. That would amount to saying that there are no global superusers > for D1, which might be a bit of a problem --- if local DBA paints > himself into a corner, you can't get him out. Backing up a cluster that > has not got global superusers would be a PITA too. So you write a script to add a local superuser when you create the database. Or, we could do it in the createdb/CREATE DATABASE code - just clone the "postgres" user. Last resort, I'm sure the files themselves could be hacked if you had to. If people are running a shared environment, it's fair to assume they know a little of what they're doing. > Still, I think you are right that we gotta think outside the box if > we're going to find a way to do this. More a case of thinking under the box here.
>> Another solution would be to have CREATE USER done by a local admin >> create users in the form of 'username@database'. This prevents >> duplicate usernames and allows us to use the current hack of local >> database users. > > Yeah, I think it would be reasonable to leave that facility as-is and > invent a category of user privileges that only allows creation/deletion > of local usernames. We'd have to think about how that should interact > with other superuser attributes such as the ability to bypass > privileges. But breaking down "superuserness" into finer-grained > privileges has been on the wish list for awhile. How about splitting usesuper into just the following two privileges: *) createuser *) usesuper A normal user can be the owner of a database so there's no need for an "admin" like priv. createuser allows the user to create other users with the same or lesser privs, and usesuper is basically exactly what we've got now. How's that? It doesn't give us database local usernames, but it's a big first step toward virtual hosting. >>> Once he creates a local user with the same name as the global >>> superuser, the door is open to problems --- not only possible bugs in >>> our own code, but plain old human error on the part of the real >>> superuser. > >> How so? Can you give a scenario where this'd make a difference? > > Well, it's the standard sort of attack where you try to trick a user > with more privs than you into doing something he'd not do if he was > aware of who/what he was really doing it to. Something like granting > privileges to a local user when he thought he was granting to a global > user, or vice versa. Or making the wrong user a member of a group. > > I'm not sure that this is actually very probable, if the usage scenario > is that global users are always superusers --- there'd seldom be any > reason to go granting them any additional privileges. But if we are > also thinking of having multiple categories of user privileges then > it's > less far-fetched. Eh, yes and no. The cluster database admin is the last line of defense... the local dba is who users would talk to and even then, the cluster database admin should only restore local admin privs to the local dba. Beyond that, the cluster dba shouldn't be involved with much... separating the UID ranges for global vs local users wouldn't be a bad idea the more I think about it, even though I don't think there's much of a risk except for possibly to the local database integrity. > Other possible problems include resetting the password of the wrong > user. This would be particularly bad if a database's local superuser > can choose the setting of the "encrypt passwords by default" GUC switch > --- he might have the opportunity to see the cleartext password of some > global user. Obviously that hole can be plugged now that I've pointed > it out, but what other ones are there? Other holes... none that I can think of beyond: *) changing privs - accidentally elevating a global user's privs when thinking it was a local user that was getting its privs elevated. *) changing the wrong password - same prob as changing the privs *) granting privs to structures to the wrong UID because of incorrect username resolution Beyond leaving admins with possible land mines that needs to be well documented... but that's the trade off for the flexibility, IMHO. Proxy authentication has the same pit falls in kerberos or SASL but that hasn't stopped the security community from adopting kerberos in high security installations.... the pit falls just need to be documented. A lot of this could be subverted if pg_hba.conf was also stored in the database and could be used to limit what users could connect to what databases and from what IPs. :) BTW, has it been discussed to add a way in pg_hba.conf to limit the number of connections per IP address or per subnet mask? 2 connections per /32 or 4 connections per /30? -sc -- Sean Chittenden