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

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