Re: Roles - SET ROLE Updated

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.

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.

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

> Do you want me to rework the
> patch along these lines or are you already working on it?

I'm working on it ...

regards, tom lane