Re: [PATCHES] Roles - SET ROLE Updated

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> It's possible for CURRENT_ROLE to be null according to the spec; if you
>> like we could implement that as returning what the current outer-level
>> SET ROLE value is (which would then make it semantically more like
>> SESSION_USER than CURRENT_USER). I don't think CURRENT_USER should ever
>> be allowed to be null, or to be different from the active authorization
>> identifier, first because it's silly and second because it will break
>> existing applications that depend on CURRENT_USER for authorization
>> checking.

> Sorry about the existing applications, but this does go directly against
> the SQL2003 specification.

The spec isn't sufficiently well-designed in this area to make me
willing to insert security holes into existing apps in order to follow
it slavishly. They clearly failed to think through the
grant-role-to-PUBLIC business, and the whole distinction between users
and roles is pretty artificial anyway.

> At least from my reading of SQL2003 5.37
> ROLE_COLUMN_GRANTS view, which 'Identifies the privileges on columns
> defined in this catalog that are available to or granted by the
> currently enabled roles':

> WHERE ( GRANTEE IN ( SELECT ROLE_NAME FROM ENABLED_ROLES )

> Where the ENABLED_ROLES view operates specifically off of the
> 'CURRENT_ROLE' value.

OK, so we make CURRENT_ROLE return the SET ROLE value (possibly NULL).

I notice that the privilege-related info schema views consistently check
privileges via locutions like

WHERE ( SCHEMA_OWNER = CURRENT_USER
OR
SCHEMA_OWNER IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) )

which is a tad odd if it's intended to model the privileges you
currently have; the implication of that is that you cannot drop any of
your "login ID"'s privileges by doing SET ROLE, which surely is not
the intended behavior (else you might as well not have SET ROLE at all;
the only possible use of SET ROLE is to *restrict* your privileges,
since any role you can become represents privileges you'd have anyway
without SET ROLE). So I'm pretty unconvinced that the spec is being
self-consistent here.

> Technically I believe this
> actually allows multiple levels of 'SET ROLE's to be done and for 'SET
> ROLE NONE's to only pull off the top-level.

I don't see anything in the spec that suggests that reading to me.

regards, tom lane