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 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;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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
