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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> >> bossartn(at)amazon(dot)com <javascript:;> writes:
> >>> 2. As foo, execute "alter role foo set role = '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 startup?
>
> 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
> ​.
>

​So, I just tested and we indeed do (9.6beta-2​) make the effective 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
​psql -U loginrole postgres
SELECT current_role; --> grouprole​

​​I'd say that the expression "SET ROLE" as defined does not 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 | = } 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
​WARNING:\s\srole "group​role" does not exist
​SELECT current_role; --> loginrole​

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.