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

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> 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 :-(

I'd be in favor of this if we could find an answer to that one.

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

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.

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

regards, tom lane