Re: Per database users/admins, handy for database virtual hosting...

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