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.