Re: BUG #14242: Role with a setconfig "role" setting to a nonexistent role causes pg_upgrade to fail

On Mon, Jul 11, 2016 at 8:05 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Jul 11, 2016 at 7:36 PM, Andrew Gierth <
> andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>
>> Tom> If the named role is the same as the actual role, then it's
>> Tom> useless. If they're different, it seems at best confusing. In
>> Tom> the context of ALTER DATABASE SET, it seems both confusing and
>> Tom> possibly a security hazard.
>>
>> It _appears_ to silently fail if the user logging in is not actually a
>> member of the specified role. I have not looked at the code.
>>
>
> ​WARNING:\s\spermission denied to set role "grouprole"
>
>
​Fun times...
[up-thread commands still in effect]
ALTER DATABASE postgres SET ROLE loginrole2;
psql -U loginrole postgres
WARNING: permission denied to set role "grouprole"
WARNING: permission denied to set role "loginrole2"
postgres=>

Note the code comment at about: ​src/backend/commands/user(dot)c(at)478-479

"
Although it will work to say
​ ​
ALTER ROLE role ROLE rolenames", we don't document it.
​"

While that's good to know the specific syntax in the comment is invalid on
its face. It also doesn't say "why" we don't document it nor why it needs
to be accepted. I'd say at this point the why is immaterial though.

I'm still in favor of documenting both commands and reducing our parental
involvement here.

In light of the above double-warning I'm concerned that "precedence" isn't
happening correctly here - but that could be an implementation artifact
(the more specific combination is executed second so that it ends up
overriding any settings attempted to be set by the less specific
​configuration). In this case, though, the failed attempt to set the
db+role setting would have resulted in the role setting taking effect if it
was valid. I don't recall us making this distinction clear in the
documentation.

Tangentially, I'm not sure what, if anything, to do with 18.1 given this
knowledge. 18.1 was written assuming that the GUC variation of these
commands cannot fail and thus it is safe to execute the DATABASE version
followed by a ROLE specific version followed by a DATABASE+ROLE version.
This seems correct on its face and as I said up-thread this whole ROLE
business isn't really a configuration variable even though it is
shoe-horned into that infrastructure.​ I'm inclined to leave well enough
alone.

David J.