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.

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

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.

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

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

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

* Peter Eisentraut (peter_e@gmx.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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEJAJWrzgMPqB3kigRAicgAJ9z/f7iualumgxRBNXOALiCYhhNGACggsFN
SsK+odFTQLeAB6uB1NnNYBU=
=TF3d
-----END PGP SIGNATURE-----

Stephen Frost wrote:
> 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...

The particular issue I'm running into is that I'm trying to get the
information schema up to speed but the current role implementation
doesn't really match anywhere. I remember the discussion about the
inherit flag vaguely, and I think I might even have contributed to the
confusion, but the fact is that some concepts like the purpose of SET
ROLE and the difference between enabled and applicable roles have
apparently been misunderstood.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

* Peter Eisentraut (peter_e@gmx.net) wrote:
> Stephen Frost wrote:
> > 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...
>
> The particular issue I'm running into is that I'm trying to get the
> information schema up to speed but the current role implementation
> doesn't really match anywhere. I remember the discussion about the
> inherit flag vaguely, and I think I might even have contributed to the
> confusion, but the fact is that some concepts like the purpose of SET
> ROLE and the difference between enabled and applicable roles have
> apparently been misunderstood.

Well.. Applicable roles are roles which you can "SET ROLE" to, but
which you don't automatically get the permissions of (inherit). As I
recall, the spec wants all roles to be like this until an explicit "SET
ROLE" is done. When a "SET ROLE" is done, then that role (and all other
roles granted to it) are "enabled".

In Postgres terms, the "pg_has_role()" function can provide the answer
to both questions, based on what's passed in.

For 'enabled' roles:

pg_has_role('abc','USAGE');

For 'applicable' roles:

pg_has_role('abc','MEMBER');

Where the current user is asking the question "do I have USAGE/MEMBER
(enabled/applicable) rights on role 'abc'?"

At least, I'm pretty sure that's the idea. Hopefully that helps clear
up what should be done in information_schema...

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEJDPgrzgMPqB3kigRAq3sAJ0a/LiDfNP4GQ92PuUn5Ws4Tc3gEACgi0P9
xGVhz0YDGoq7afSfK0KvdoE=
=W0yU
-----END PGP SIGNATURE-----

Stephen Frost wrote:
> Well.. Applicable roles are roles which you can "SET ROLE" to, but
> which you don't automatically get the permissions of (inherit). As I
> recall, the spec wants all roles to be like this until an explicit
> "SET ROLE" is done. When a "SET ROLE" is done, then that role (and
> all other roles granted to it) are "enabled".

I admit that I had thought exactly that until just the other day when I
started researching this, but in my current understanding the standard
means something altogether different.

Let's start in part 2, 4.34.4:

The term enabled authorization identifiers denotes the set of
authorization identifiers whose members are the current user
identifier, the current role name, and every role name that is
contained in the current role name.

The term applicable privileges for an authorization identifier A
denotes the union of the set of privileges whose grantee is PUBLIC
with the set of privileges whose grantees are A and, if A is a role
name, every role name contained in A.

The term current privileges denotes the union of the applicable
privileges for the current user identifier with the applicable
privileges for the current role name.

This means approximately that the applicable privileges are the enabled
privileges plus the privileges granted to PUBLIC.

This is also consistent with the definitions of the views
applicable_roles and enabled_roles in the information schema.

The invocation of these definitions happens in the Access Rules of the
various clauses, which typically contain something like this (here for
the UPDATE command):

The current privileges for TN shall include UPDATE for each <object
column>.

So what in fact happens here is that the applicable privileges of
current user and role determine what you can do. The enabled roles
have no practical meaning (except in defining what you can see in the
information schema, which is weird).

> In Postgres terms, the "pg_has_role()" function can provide the
> answer to both questions, based on what's passed in.
>
> For 'enabled' roles:
>
> pg_has_role('abc','USAGE');

What this actually gives you is both the enabled and the applicable
roles because apparently it doesn't work to grant roles to PUBLIC,
which would be the only difference.

> For 'applicable' roles:
>
> pg_has_role('abc','MEMBER');

What you get from this has no equivalent in the SQL standard.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

* Peter Eisentraut (peter_e@gmx.net) wrote:
> Stephen Frost wrote:
> > Well.. Applicable roles are roles which you can "SET ROLE" to, but
> > which you don't automatically get the permissions of (inherit). As I
> > recall, the spec wants all roles to be like this until an explicit
> > "SET ROLE" is done. When a "SET ROLE" is done, then that role (and
> > all other roles granted to it) are "enabled".
>
> I admit that I had thought exactly that until just the other day when I
> started researching this, but in my current understanding the standard
> means something altogether different.

Alright, now you're trying to confuse me. :P

> Let's start in part 2, 4.34.4:
>
> The term enabled authorization identifiers denotes the set of
> authorization identifiers whose members are the current user
> identifier, the current role name, and every role name that is
> contained in the current role name.
>
> The term applicable privileges for an authorization identifier A
> denotes the union of the set of privileges whose grantee is PUBLIC
> with the set of privileges whose grantees are A and, if A is a role
> name, every role name contained in A.
>
> The term current privileges denotes the union of the applicable
> privileges for the current user identifier with the applicable
> privileges for the current role name.

You were talking about 'enabled' vs. 'applicable' roles. Above they're
talking about 'enabled authorization identifiers' (the list of roles
you currently have the permissions of) and 'applicable privileges' (the
specific privileges you have as that set of roles).

> This means approximately that the applicable privileges are the enabled
> privileges plus the privileges granted to PUBLIC.

Hang on, you're confusing things again. They're not talking about
'enabled privileges', they're talking about 'enabled authorization
identifiers'. You're right that the 'applicable privileges' are the
privileges granted to the 'enabled authorization identifiers', plus
PUBLIC.

> This is also consistent with the definitions of the views
> applicable_roles and enabled_roles in the information schema.

If those are the view names then I think they might add to the
confusion, and thank the spec for that.

> The invocation of these definitions happens in the Access Rules of the
> various clauses, which typically contain something like this (here for
> the UPDATE command):
>
> The current privileges for TN shall include UPDATE for each <object
> column>.
>
> So what in fact happens here is that the applicable privileges of
> current user and role determine what you can do. The enabled roles
> have no practical meaning (except in defining what you can see in the
> information schema, which is weird).

This sounds more-or-less right... I think the reason for this is that
what's in information_schema is sometimes supposed to be filtered down
to only what you 'own'. Ownership isn't an 'applicable privilege' but
is instead an attribute of each object. Ownership is granted when roles
are granted though which is where the 'enabled authorization
identifiers' comes in: You're considered to be the 'owner' of everything
which any of your 'enabled authorization identifiers' own.

Perhaps an example here would help:

current user: user1

'enabled authorization identifiers':
user1
role1 (current role)
role2 (granted to role1)
role3 (granted to role1)

'applicable privileges':
select,insert,update,delete on table xyz

owner of xyz: role1 (thus, 'user1' is also considered an 'owner')

I could see a reason to want to know what 'enabled authorization
identifiers' you've currently got, though I'm not sure right off that we
expose this in an easy way to get to the full list in Postgres
(pg_auth_members has this information but you have to handle the
recursion). I'm not sure you actually need the full list though, you
just need to use pg_has_role() to do the check on each of the objects.

> > In Postgres terms, the "pg_has_role()" function can provide the
> > answer to both questions, based on what's passed in.
> >
> > For 'enabled' roles:
> >
> > pg_has_role('abc','USAGE');
>
> What this actually gives you is both the enabled and the applicable
> roles because apparently it doesn't work to grant roles to PUBLIC,
> which would be the only difference.

It tells you if you have the rights of 'abc' currently or not. If you
want to know if you have a certain privilege on a certain table
currently or not you can just use the regular 'has_table_privilege'
type functions...

> > For 'applicable' roles:
> >
> > pg_has_role('abc','MEMBER');
>
> What you get from this has no equivalent in the SQL standard.

This doesn't apply from what you've quoted above, but I'm pretty sure
there's something about roles which you can 'set role' to but which you
don't currently have the rights of in the SQL spec...

I hope this helps? If not then I'm probably going to have to go reread
the spec again some more myself.

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEJEC1rzgMPqB3kigRAl4EAJ4xzu7bZhUZugDU8MqJ/54mC8dQ/gCghv3X
Pwi4rAWeGcm/mDSVh7UZgs0=
=vNJu
-----END PGP SIGNATURE-----

Stephen Frost wrote:
> You were talking about 'enabled' vs. 'applicable' roles. Above
> they're talking about 'enabled authorization identifiers' (the list
> of roles you currently have the permissions of) and 'applicable
> privileges' (the specific privileges you have as that set of roles).

According to the definition, an authorization identifier is either a
user or a role, so I don't see where the problem is.

enabled authorization identifiers -- as defined

applicable authorization identifiers -- as defined

enabled roles -- all enabled authorization identifiers that are roles

applicable roles -- all applicable authorization identifiers that are
roles

> > > For 'applicable' roles:
> > >
> > > pg_has_role('abc','MEMBER');
> >
> > What you get from this has no equivalent in the SQL standard.
>
> This doesn't apply from what you've quoted above,

The set of roles pg_has_role('abc','MEMBER') minus
pg_has_role('abc','USAGE') can only be nonempty if you define roles
with NOINHERIT, but the SQL standard doesn't provide for that. QED.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

* Peter Eisentraut (peter_e@gmx.net) wrote:
> Stephen Frost wrote:
> > You were talking about 'enabled' vs. 'applicable' roles. Above
> > they're talking about 'enabled authorization identifiers' (the list
> > of roles you currently have the permissions of) and 'applicable
> > privileges' (the specific privileges you have as that set of roles).
>
> According to the definition, an authorization identifier is either a
> user or a role, so I don't see where the problem is.
>
> enabled authorization identifiers -- as defined
> applicable authorization identifiers -- as defined

I didn't find 'applicable authorization identifiers' in my copy of
SQL2003..

> enabled roles -- all enabled authorization identifiers that are roles
>
> applicable roles -- all applicable authorization identifiers that are
> roles

'enabled roles' don't appear to be discussed in 'Foundation'
unfortunately, just 'applicable roles', which only comes up in the
grant/revoke statements. 'applicable roles', according to the
information_schema view in the spec, would appear to be 'MEMBER'
rights from pg_has_role.

'enabled roles' view in the spec information_schema appears to
correspond to 'USAGE' rights from pg_has_role.

And these are different because of the user/role distinction in the Spec
which Postgres doesn't have but can emulate with the 'noinherit' flag.

> > > > For 'applicable' roles:
> > > >
> > > > pg_has_role('abc','MEMBER');
> > >
> > > What you get from this has no equivalent in the SQL standard.
> >
> > This doesn't apply from what you've quoted above,
>
> The set of roles pg_has_role('abc','MEMBER') minus
> pg_has_role('abc','USAGE') can only be nonempty if you define roles
> with NOINHERIT, but the SQL standard doesn't provide for that. QED.

Eh, it does and it doesn't. The SQL standard says that no roles are
automatically inheirited and that you have to 'set role' to them. Thus,
all non-user roles which are granted to users in Postgres would need to
be defined 'noinherit' to have things work as the spec wants.

So while the spec doesn't explicitly define 'NOINHERIT', it's implicit
for roles granted to users. Thus, when the question comes up "what
roles can user X 'set role' to?" (which does happen in the SQL spec, ie:
'applicable_roles'), the "pg_has_role('abc','MEMBER')" needs to be used
to find the answer.

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEJE7WrzgMPqB3kigRAvIKAJ9wxufjwyRADVtMbayuvE 6I3iMTcwCgip26
fzr0dYJ5LjQg4M9CDY8vn58=
=MKUK
-----END PGP SIGNATURE-----

Stephen Frost <sfrost@snowman.net> writes:
> Eh, it does and it doesn't. The SQL standard says that no roles are
> automatically inheirited and that you have to 'set role' to them. Thus,
> all non-user roles which are granted to users in Postgres would need to
> be defined 'noinherit' to have things work as the spec wants.

We note in the CREATE ROLE docs:

The behavior specified by the SQL standard is most closely approximated
by giving users the NOINHERIT attribute, while roles are given the
INHERIT attribute.

For the purposes of the information_schema, it might work best to
consider NOINHERIT (rather than LOGIN) as being what identifies a user
rather than a role.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Stephen Frost wrote:
> Eh, it does and it doesn't. The SQL standard says that no roles are
> automatically inheirited and that you have to 'set role' to them.
> Thus, all non-user roles which are granted to users in Postgres would
> need to be defined 'noinherit' to have things work as the spec wants.

This is not what the SQL standard says. According to the SQL standard,
I can do

CREATE ROLE a;
CREATE ROLE b;
CREATE ROLE c;
GRANT SELECT ON TABLE foo TO c;
GRANT c TO b;
GRANT b TO a;
GRANT a TO myuser;
SET ROLE a;
SELECT * FROM foo;

This contradicts the theory that all roles are mandated to be
"noinherit".

This currently works in PostgreSQL but it loses the privileges of
myuser, which violates the standard.

> So while the spec doesn't explicitly define 'NOINHERIT', it's
> implicit for roles granted to users.

This is the important distinction. The "inherit" flag should not be a
property of a role but should be determined at run time. Roles granted
to the current role are automatically activated, roles granted to the
current user need to be set first. So it's not even a matter of
"granted to users", it's a matter of whether the grantee is the
"current user" or the "current role" of the session.

I think the only reasonable way to achieve standard comformance is to
track current user and current role separately. To achieve traditional
group behavior we can extend the standard by the simple feature that
the current role is initialized to be the current user (rather than
being empty), which would automatically draw in the privileges from all
contained roles. Then we can forget about the inherit flag altogether.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend