Re: Per-Database Roles

On Tue, May 22, 2012 at 9:37 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 22 May 2012 14:04, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> What would the semantics of that look like though?  Which is "preferred"
>> when you do a 'grant select' or 'grant role'?  Or do we just disallow
>> overlaps between per-DB roles and global roles?  If we don't allow
>> duplicates, I suspect a lot of the other questions suddenly become a lot
>> easier to deal with, but would that be too much of a restriction?  How
>> would you handle migrating an existing global role to a per-database
>> role?
>
> Perhaps:
>
> CREATE [ GLOBAL | LOCAL ] ROLE name [ LIKE role_name ] [ [ WITH ]
> option [ ... ] ]
>
> Then:
>
> CREATE LOCAL ROLE localrolename LIKE globalrolename;
>
> REASSIGN OWNED BY globalrolename TO localrolename;
>
> 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.

There are race conditions to worry about, too. In most cases, we rely
on the btree index machinery as a final backstop against duplicate
catalog entries. But that doesn't work unless everything's in one
catalog, nor for anything more complicated than "this set of columns
taken together should be unique over every role". Even if we were OK
with incurring the ugliness of storing per-database roles in a shared
catalog, the uniqueness constraint you'd want is something like "no
two roles can share the same name unless they have unequal database
OIDs neither of which is zero", which I don't believe we can enforce
via the btree machinery, at least not without an expression index that
won't work in a system catalog anyway.

In retrospect, I think the idea of shared catalogs was probably a bad
idea. I think we should have made roles and tablespaces database
objects rather than shared objects, and come up with some ad-hoc
method of representing the set of available databases. But that
decision seems to have been made sometime pre-1996, so the thought of
changing it now is pretty painful, but I can dream...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company