Re: Per-Database Roles

Stephen,

> Which is "preferred"
> when you do a 'grant select' or 'grant role'?

The local role is preferred, the same way we allow objects in the local
schema to overshadow objects in the global schema.

> 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?

The feature wouldn't be useful if we didn't allow conflicts between two
local role names. However, we could prohibit conflicts between a local
role name and a global role name if it made the feature considerably
easier. Users would find workarounds which weren't too arduous.

> How
> would you handle migrating an existing global role to a per-database
> role?

Again, I think it would be OK not handling it. i.e., the user needs to
do the following:

1. create a new local role
2. reassign all the objects belonging to the global role to the local role
3. drop the global role
4. rename the local role

It'd be somewhat of a PITA, but I suspect that most people using the
"local roles" feature would recreate their databases from scratch
anyway. And we could offer some sample scripts for the above on the
wiki and elsewhere. Obviously, a more elegant migration command would
be ideal, but that could wait for the following PG release; we usually
follow the "make things possible first, and easy later" plan anyway.

Given that I'd love to have this feature, I'm trying to pare down its
requirements to a managable size. Trying to do everything at once will
only result in the feature stalling until 10.5.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com