Re: Per-Database Roles

On May22, 2012, at 18:03 , Thom Brown wrote:
> On 22 May 2012 16:57, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>> On May22, 2012, at 16:09 , Tom Lane wrote:
>>> Thom Brown <thom(at)linux(dot)com> writes:
>>>> Conflicts would occur where localrolename matches an existing local
>>>> role name within the same database, or a global role name, but not a
>>>> local role name within another database. The problem with this,
>>>> however, is that creating global roles would need conflict checks
>>>> against local roles in every database, unless a manifest of all local
>>>> roles were registered globally.
>>>
>>> Yeah. The same type of issue arises for the roles' OIDs. You'd really
>>> want local and global roles to have nonconflicting OIDs, else it's
>>> necessary to carry around an indication of which type each role is;
>>> which would be more or less a show-stopper in terms of the number of
>>> catalogs and internal APIs affected. But I don't currently see any
>>> nice way to guarantee that if each database has a private table of
>>> local roles.
>>
>> Maybe we could simply make all global role's OIDs even, and all local ones
>> odd, or something like that.
>
> Wouldn't that instantly make all previous versions of database
> clusters un-upgradable?

Only if pg_upgrade needs to preserve the OIDs of roles. I kinda hoped it
wouldn't, because role OIDs aren't usually stored in non-system tables.

Hm… thinking about this further, it'd actually be sufficient for all newly
allocated role OIDs to follow the odd/even rule, if we additionally check
for conflicts with existing global role OIDs when allocating the OID of a new
local role. Which is much, much easier than checking for conflicts when
allocating a global OIDs, because for that you'd have to check against the
local role OIDs within *all* databases, not just against one shared table.

best regards,
Florian Pflug