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:
> > 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.

Perhaps the specification isn't but I'm pretty sure other
implementations follow the SET ROLE -> current authorization
identifier (and thus dropping other rights granted to the CURRENT_USER).

Having thought about this a bit more I'd like to know what security
holes you're thinking would be introduced by this change. CURRENT_USER
was always required to be set in my original patch, and SET ROLE didn't
exist before and only ever dropped privileges anyway. A current app is
rather unlikely I'd think to use SET ROLE and *then* base authorization
decisions off the value of CURRENT_USER...

I suppose I'm being dense but I'd like to get a better explanation of
the specific problem before trying to come up with an acceptable
solution.

> > 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.

Looking back on it I'd have to agree that there does seem something a
bit odd here. There are some places where it's limited to the current
role (the ROLE_*_GRANTS that I had originally been looking at) but other
places indicate cases where the 'user' is the 'owner', or is in the role
of the 'owner'. The grantee cases tend to have 'public', CURRENT_USER
or an enabled_role. Interestingly, there *is* a distinction that's made
here, when you think about it:

This lists things which the CURRENT_USER or the ENABLED_ROLES (via a SET
ROLE) has access to. This does *not* list objects in the
APPLICABLE_ROLES set. This indicates that SET ROLE *does* drop
privileges, but you may still see objects which the underlying user can
directly, but not things which the underlying user can see indirectly
through other roles (unless those other roles are available under
ENABLED_ROLES).

The odd bit is that this doesn't seem to handle the case where
CURRENT_ROLE is NULL very cleanly- if you've not SET ROLE then it's
expected you have access to anything which a role you've been granted
has access to, instead you only see those things which you directly own
or which are available to 'public'.

I recall you telling me to go back and look at the spec at one point
regarding what a given user could see via information_schema and to
submit a patch if something in information_schema was wrong. Well,
seems like perhaps information_schema might have been following the
spec (since this isn't what I would have expected).

> > 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.

It's in 4.34.1.1, at least in the SQL2003 specification, and it reads:
"This stack is maintained using a "last-in, first-out" discipline, and
effectively only the top cell is visible. When an SQL-session is
started, by explicit or implicit execution of a <connect statement>, the
authorization stack is initialized with one cell, which contains only
the user identifier known as the SQL-session user identifier, a role
name, known as the SQL-session role name may be added subsequently."

It also says:
"The <set session user identifier statement> changes the value of the
current user identifier and of the SQL session user identifier. The
<set role statement> changes the value of the current role name."

Which does seem to conflict. Were it meaning that SET ROLE pushes onto
the stack I'd expect the wording to reflect that instead of saying
"chagnes". This stack-like behaviour of multiple set-role statements
isn't something I can currently think I'd have any need for, but it does
more closely follow how 'su's in Unix work.

Thanks,

Stephen