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

On Mon, Jul 11, 2016 at 7:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> >> bossartn@amazon.com <javascript:;> writes:
> >>> 2. As foo, execute "alter role foo set role =3D 'foo'"
>
> > I'm at a loss to understand what this does when it isn't broken.
> Assuming
> > valid grants does the user become the assigned role upon session startu=
p?
>
> As written it does nothing much.  But "SET ROLE" is defined by the SQL
> standard, and what I'd expect this to do is execute an implicit SET ROLE
> at login.  Whether that's a good idea is pretty debatable, though, thus
> my question whether we'd not be better off to forbid this
> =E2=80=8B.
>

=E2=80=8BSo, I just tested and we indeed do (9.6beta-2=E2=80=8B) make the e=
ffective role
the value associated with the "ROLE" configuration variable associated to
the user.

CREATE ROLE loginrole WITH LOGIN PASSWORD 'password';
CREATE ROLE grouprole;
GRANT grouprole TO loginrole
ALTER ROLE loginrole SET ROLE TO grouprole
=E2=80=8Bpsql -U loginrole postgres
SELECT current_role; --> grouprole=E2=80=8B

=E2=80=8B=E2=80=8BI'd say that the expression "SET ROLE" as defined does no=
t match any of
the valid variations of ALTER ROLE that we've documented (i.e., ROLE is not
a "configuration_parameter").  We should document it explicitly.

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET
ROLE { TO | =3D } role_identifier
Does the standard provide guidance on the syntax for the equivalent of
"RESET ROLE"?

"The role_identifier in the <third> variant is used in conjunction with the
SET ROLE SQL command and causes a newly connected session to switch to the
named role.  If for some reason the named role no longer exists - which can
happen if it is renamed or removed - future attempts to login will provoke
a WARNING and the original user will remain active.  Note that this role
change happens after all configuration_parameters for the original role
have been setup - no configurations attached to the target role are applied=
.

role_identifier:
The name of an existing role

I would also take a page from the search_path GUC and ignore any attempt to
associate an undefined role - or at worse make it a warning.  In fact, we
already do this during the login attempt.  We should extend the forgiveness
to here as well.

DROP ROLE grouprole; -- OK
psql -U loginrole postgres
=E2=80=8BWARNING:\s\srole "group=E2=80=8Brole" does not exist
=E2=80=8BSELECT current_role; --> loginrole=E2=80=8B


In hindsight we probably could do better if we didn't treat "ROLE" like any
other configuration parameter.  I'm not sure what incremental improvements
could be made. Would a record in pg_depend that is set/cleared upon
invoking of ROLE-related commands?  Would that be sufficient?

David J.