From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "bossartn(at)amazon(dot)com" <bossartn(at)amazon(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #14242: Role with a setconfig "role" setting to a nonexistent role causes pg_upgrade to fail |
Date: | 2016-07-12 00:01:30 |
Message-ID: | CAKFQuwaFyETaE8gF2DCBYyjCT9Cb2AKiHjuSkbv3_UJrVP02Jw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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 "grouprole" 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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-07-12 00:05:40 | Re: BUG #14242: Role with a setconfig "role" setting to a nonexistent role causes pg_upgrade to fail |
Previous Message | Andrew Gierth | 2016-07-11 23:36:05 | Re: BUG #14242: Role with a setconfig "role" setting to a nonexistent role causes pg_upgrade to fail |