Re: Per-Database Roles

* Josh Berkus (josh(at)agliodbs(dot)com) wrote:
> The local role is preferred, the same way we allow objects in the local
> schema to overshadow objects in the global schema.

I would think we'd want the exact opposite. I don't want my global
'postgres' user to be overwritten by some local one that the admin of
this particular DB created..

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

Sorry, I was meaning between global space and local space. Clearly we
must allow and handle cleanly overlaps between local spaces.

The issue with not allowing global spaces to overlap local ones is that
we'd have to check every local list when creating a global account;
that doesn't seem very easy to do. On the flip side, allowing
duplicates between global and local would remove the need to check local
lists when creating global accounts, but would add complexity and could
lead to odd semantics when there is a duplicate.

> 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

Right, that seems like it would work fine.

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

Sure.

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

If you could help me work out the semantics and the high-level issues,
I'd love to spend time on this for 9.3...

Thanks,

Stephen