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(at)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