Re: [PATCHES] Roles - SET ROLE Updated

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> >> After rereading SQL99 4.31, I don't think there is any need to
> >> distinguish CURRENT_USER from CURRENT_ROLE, mainly because our
> >> implementation does not distinguish users from roles at all.
>
> > CURRENT_USER and CURRENT_ROLE can have different values, as I understand
> > SQL2003, and there are places where one is used instead of the other
>
> 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. 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.

> Given that we don't really distinguish users and roles, I would be
> inclined to make the same argument for CURRENT_ROLE too, leaving
> SHOW ROLE (and its function equivalent) as the only way to see what
> you SET ROLE to. But it's less likely to break existing apps if we
> don't.

I don't quite follow this- the point of SET ROLE is to change your
authorization identifier to be a specific role instead of the current
role. What I had thought you were suggesting was to make it so that
after a SET ROLE the CURRENT_USER shows what you SET ROLE to. This
sounds like SET ROLE is just there for looks and completely ignored for
authorization purposes, making it next to useless.

> > (such as with the 'grantor' in grants, according to SQL2003 the
> > 'grantor' should be the CURRENT_USER, regardless of if CURRENT_ROLE is
> > set or not).
>
> Exactly. CURRENT_USER has to be the active authorization identifier.

No, that's an exception, and only for what ends up in the table recorded
as the 'grantor'. Re-reading 4.34 it's apparently actually supposed to
be a "last-in, first-out" mechanism, though I don't see any way for a
user (beyond a connect statement) to actually change CURRENT_USER,
unlike SET ROLE which can be used to change CURRENT_ROLE (and in so
doing put it at the top of the 'stack'). 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. My patch didn't handle
such multi-level SET ROLE's, but it's certainly something which could be
done.

Thanks,

Stephen