Re: Role incompatibilities

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> Trying to work in the new role features into the information schema, I
> noticed that there might be a few incompatibilities between the
> implementation and what the SQL standard would like to see.

This is true, and was discussed quite a bit about a year ago now (iirc).
There was some discussion later when Tom introduced the inherit flag
(August or so of '05?) as well.

> The way I understand this is that, according to the SQL standard, there
> should be a current user and optionally a current role. A current role
> can be set by running SET ROLE, and that is only permissible if that
> role has been granted to the current user. (It seems that this must
> have been a direct grant, but that is less important.) The set of
> applicable privileges (used for permission checking) is now the
> privileges held by the current user, the current role, and all roles
> that have been granted to the current role.

It's been a while since I read the spec on this, but I thought the
official SQL spec said that you didn't get the permissions of the role
unless you set role to it.. Perhaps I'm misremembering though. In
general I have to agree with Tom that the spec's seperation of 'current
user' and 'current role' really isn't necessary.. (I may be
misremembering Tom's comments on it)

> It seems that the "inherit" functionality was invented to simulate
> something like this but it doesn't quite do it. What we'd really need
> is a system where roles granted to the current user are not
> automatically activated but roles granted to the current role are. The
> inherit functionality is then only to simulate traditional groups that
> activate all their privileges automatically depending on who is the
> current user.

Inherit allows you to come quite close.. It seems the only thing it
doesn't do is keep the current user's permissions after the set role,
which I'm not entirely upset by, personally...

> The other problem is that using SET ROLE activates the privileges of a
> role but loses the privileges of the current user. In practice this
> may mean that it reduces your privileges while you might want to use it
> to augment your privileges.

Or you may want to use it to reduce your privileges...

> What both of these observations come down to is that in my estimation
> current user and current role should be separated.

The SQL spec wants them seperated. Roles and users aren't as well
thought out in the spec as one might want to think they are... It'd be
alot of additional complication for not much gain to seperate them in
Postgres. The commercial folks don't follow the spec religiously wrt
roles either.

> It's quite possible that I'm reading this wrong in a hurry or can't
> quite simulate it right, so please enlighten me.

Is there a particular issue/problem you're running into? It might make
more sense to focus on what you actually need than what the spec says
you need...

Thanks!

Stephen