Re: Per-Database Roles

On May22, 2012, at 22:35 , Stephen Frost wrote:
> * 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..

From a security POV, yup, you'd want global roles to take precedence.
But OTOH, you wouldn't want your application to suddenly break because
someone created a global role which shadows the local role you've created
a year ago.

Hm… thinking about this… No matter which takes precedence, we'd need some
way to explicitly specify global or local scope anyway. And we'd have to
prevent roles from being named in a way that conflicts with whatever
explicit specification we come up with, even if that causes pain for some
unlucky existing users. Avoiding these conflicts entirely is going to be
impossible, I fear, since we don't currently restrict role names in any way
AFAIK, and we store them in GUCs without any quoting.

So maybe we should just pick some qualification like prefixing local roles
with 'local.', forbid global roles from starting with 'local.', and be done
with it? Not the most elegant solution maybe, but it avoids surprises...

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

"Not very easy" is quite an understatement, I fear. "Very nearly impossible"
is more like it IMHO.

best regards,
Florian Pflug