I've had to work through this and have with a series of messy tables
and functions, but this screams a need for a more elegant solution.
I've dug through the archives and didn't come up with a satisfying long
term answer for virtual hosting beyond what I've already implemented.
Per cluster users is handy for the admins because I can create one
account for me and not think about needing to create an account for
every database in the cluster. Per database users, on the other hand,
is ideal for database virtual hosting, but is a PITA for DBA's who need
to create accounts in every database in the cluster. I haven't read
much in the last few months, but archives from 2002 suggested there
wasn't much on the table in terms of making this happen beyond adding a
function that runs as a DBA to create users (which I've done).
What's the feasibility of augmenting the system catalogs so that
something similar to the following is possible:
CREATE VIEW pg_catalog.pg_shadow ASSELECT usename, usesysid, usecreatedb, usesuper, usecatupd, passwd, valuntil,
useconfigFROMpg_catalog.pg_shadow_cluster UNION ALLSELECT usename, usesysid, usecreatedb, usesuper, usecatupd,
passwd,valuntil, useconfigFROM pg_catalog.pg_shadow_db;
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.
Tom, what do you think? What other ideas do you have kicking around in
your head?
*shrug* Something for the TODO list and/or an inspired hacker. -sc
--
Sean Chittenden