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'tWhat? 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 '[EMAIL PROTECTED]'. 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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
