On Tue, May 22, 2012 at 9:37 AM, Thom Brown <t...@linux.com> wrote: > On 22 May 2012 14:04, Stephen Frost <sfr...@snowman.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
