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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
