Skip site navigation (1) Skip section navigation (2)

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

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Per database users/admins, handy for database virtual hosting...
Date: 2004-03-26 01:18:49
Message-ID: 8958A3F6-7EC3-11D8-ABDF-000A95C705DC@chittenden.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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 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


Responses

pgsql-hackers by date

Next:From: Neil ConwayDate: 2004-03-26 01:47:48
Subject: Re: Per database users/admins, handy for database virtual hosting...
Previous:From: David FetterDate: 2004-03-25 22:02:40
Subject: Re: HEAD compile troubles

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group