Re: SET ROLE and reserved roles

Tom,

On Wednesday, April 13, 2016, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Stephen Frost <sfrost(at)snowman(dot)net <javascript:;>> writes:
> > On Wednesday, April 13, 2016, Robert Haas <robertmhaas(at)gmail(dot)com
> <javascript:;>> wrote:
> >> Well ... yeah. But that doesn't mean it should be impossible to SET
> >> to that role itself. I'm a little worried that could create strange
> >> corner cases.
>
> > Being able to create objects owned by a default role was one of those
> > strange corner cases I was trying to avoid.
>
> If you want to prevent that, I think it needs to be done somewhere else
> than here. What about "ALTER OWNER TO pg_signal_backend", for instance?

Checks are included in that code path to disallow it.

> But perhaps more to the point, why is it a strange corner case for one
> of these roles to own objects? Isn't it *more* of a strange corner case
> to try to prohibit it? Certainly the bootstrap superuser owns lots of
> objects, and I don't see why these roles can't.
>

Specifically because these are purpose built roles for managing access to
privileges which previously were only available to a superuser.

As an example, what if we decide that rights for a given capability, such
as "signal backend" really make sense as grant-able on a per-database
basis, and we work out the issues with the limited number of bits we
currently have, and some future version of PG has support for "GRANT SIGNAL
BACKEND TO user1 ON DATABASE db1;". We would want to transition the
existing users of pg_signal_backend to that system and then drop the
pg_signal_backend default role. I'm not looking to go in such a direction,
but it could certainly happen. Another case would be if we add a capability
and a default role to manage access to it and then later remove that entire
capability, what then?

If the default roles can own objects and otherwise be used for other
purposes then we can never, ever, be rid of any which we create. The
argument you use for the bootstrap user isn't the same as it will
absolutely always be required to exist, so allowing a user to do what they
wish with it is perfectly fine.

The default roles are for the system to provide and manage, with admins
using them to grant access to other users, not for general usage, as
designed.

Thanks!

Stephen