Re: db_user_namespace a "temporary measure"

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On 03/11/2014 06:57 AM, Tom Lane wrote:
>> Mind you, I wouldn't be unhappy to see it go away; it's a kluge and always
>> has been. I'm just expecting lots of push-back if we try. And it's kind
>> of hard to resist push-back when you don't have a substitute to offer.

> Yeah, what we really need is encapsulated per-DB users and local
> superusers. I think every agrees that this is the goal, but nobody
> wants to put in the work to implement a generalized solution.

Well ... you know, how hard would it really be? Extend the primary
key of pg_authid to be (username, database_oid) with the convention
of database_oid = 0 for a globally known user. Add some syntax to
CREATE USER to indicate whether you're creating a global user
(the default) or one known only within the current database. I'm
not quite sure what to do with local users for pg_dump/pg_dumpall;
perhaps pg_dump should get the responsibility of creating local users?
But otherwise it seems like there are no issues that we'd not have to
solve anyway if we wanted to make db_user_name less of a crock.

In particular, I'd like to see an exclusion that prevents local users
from having the same name as any global user, so that we don't have
ambiguity in GRANT and similar commands. This doesn't seem simple to
enforce (if we supported partial indexes on system catalogs, it would
be ...) but surely this representation is more amenable to enforcing it
than the existing one.

regards, tom lane