Thread: Role incompatibilities
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/
* 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
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/
* 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
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
useridentifier, 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
granteeis 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
applicableprivileges 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/
* 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
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/
* 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
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 approximatedby giving users the NOINHERIT attribute, while
rolesare given theINHERIT 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
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/
* Peter Eisentraut (peter_e@gmx.net) wrote:
> 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".
Sorry, Tom corrected me, 'users' should be 'noinherit' to follow the
spec.
> This currently works in PostgreSQL but it loses the privileges of
> myuser, which violates the standard.
The standard wants them tracked seperately.
> > 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 really don't see a whole lot of value in this.
> 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.
Yes, to conform exactly to the standard you'd have to again seperate
roles from users. I don't think this is actually a useful thing to do
though. We allow users to set things up the way the spec wants them
(except for the complication wrt set role dropping the current 'user'
permissions but I don't see a good way to avoid that without a great deal
of trouble), if they choose to, while retaining backwards compatibility
with prior versions (for the most part anyway, user/groups with the same
name can't be handled correctly, for example).
Dropping the current user permissions when doing a 'set role' is
actually something I like, personally... I also like the 'noinherit'
flag which I think is more flexible than what the SQL spec requires.
If there are use cases where what we're doing is causing problems for
users who are looking for what the SQL spec has exactly, please let us
know.. Given that other databases don't religiously follow the SQL spec
wrt roles either makes me suspect you won't find too cases like that..
Alternatively, come up with a patch to change the current behaviour and
ask folks to review it.
Thanks,
Stephen
Am Samstag, 25. März 2006 01:02 schrieb Stephen Frost: > If there are use cases where what we're doing is causing problems for > users who are looking for what the SQL spec has exactly, please let us > know.. Given that other databases don't religiously follow the SQL spec > wrt roles either makes me suspect you won't find too cases like that.. My concern here is to arrive at a standards conforming role system. Clearly we've established that the current one doesn't do it. Moreover, I'm now convinced that some aspects of the current implementation arose from an attempt to implement the standard but failed because of misunderstandings. I'd be interested to know what incompatibilities you have found in other database systems, so we know what we're dealing with. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> My concern here is to arrive at a standards conforming role system. Clearly
> we've established that the current one doesn't do it. Moreover, I'm now
> convinced that some aspects of the current implementation arose from an
> attempt to implement the standard but failed because of misunderstandings.
No, the current implementation is a compromise between exact standards
compatibility and backwards compatibility with our historical "groups"
behavior. I'm not really prepared to toss the latter overboard.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > My concern here is to arrive at a standards conforming role system. Clearly
> > we've established that the current one doesn't do it. Moreover, I'm now
> > convinced that some aspects of the current implementation arose from an
> > attempt to implement the standard but failed because of misunderstandings.
>
> No, the current implementation is a compromise between exact standards
> compatibility and backwards compatibility with our historical "groups"
> behavior. I'm not really prepared to toss the latter overboard.
I think what Peter would want is for us to track CURRENT_USER (the role
who logged in) and to always add the CURRENT_USER to the list of roles
available after a 'SET ROLE'. That would at least get us a little
closer to the spec though I'm not sure if it matches what other DBs do.
I'll try to check on that sometime this weekend.
I know that Oracle, at least, gives you all roles which have been
granted to you on login on at least the system I've got access to. If
you then 'set role' to a given role you *are* dropping privileges, not
adding them. I don't know if there's a way to configure Oracle for the
standards-compliant method. I'm also not sure what other databases do.
The noinherit for 'user' roles is more difficult, of course. We can
tell people to set noinherit on the roles that can log in but I'm not
sure Peter's happy with that. We could have a server option of
"sql_user_noinherit" or some such which automatically set noinherit for
roles which have the 'login' attribute and inherit for those without.
This would create the artificial distinction between users and roles
which the specification has but makes for some very odd problems- do you
then disallow roles with 'login' from being granted to others? What
about grants done prior to the option being set, ignore them? What if
the admin sets 'nologin' for such a role, do the old grants suddenly
come back?
Anyhow, I'm really not sure it's the best approach in this case to try
to follow the spec to the letter and not just because it breaks
backwards compatibility for us but also because I don't think any
actually implemented database follows it exactly either and as such the
spec hasn't been that well thought out in this area.
Thanks,
Stephen
Am Samstag, 25. März 2006 16:10 schrieb Tom Lane: > No, the current implementation is a compromise between exact standards > compatibility and backwards compatibility with our historical "groups" > behavior. I'm not really prepared to toss the latter overboard. My two major sticking points here are the SET ROLE command and the noinherit feature. The SET ROLE command is not required by our historical group behavior (because we didn't have it before) and does not do what the SQL standard says it should do. The noinherit feature is not required by the historical group behavior (because groups are yes-inherit) and is not in the SQL standard either. So these two features were just mistakes as far as I can tell. I'm not passing judgement on whether a command like the currently implemented SET ROLE command or a feature like the currently implemented noinherit feature is useful. They are just not in line with either the historical group behavior or the SQL standard. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Am Samstag, 25. März 2006 17:27 schrieb Stephen Frost: > I think what Peter would want is for us to track CURRENT_USER (the role > who logged in) and to always add the CURRENT_USER to the list of roles > available after a 'SET ROLE'. That would at least get us a little > closer to the spec though I'm not sure if it matches what other DBs do. This is sort of but not really what I meant. What I think would work rather elegantly is having both a current_user and a current_role and having the current_role set to current_user on login. (The SQL standard would require the current role to be empty at login.) Then you get exactly the traditional group behavior. You can also use SET ROLE to set the current role (but not the current user) to some other permissible role. This will give you the applicable privileges of your current user and that new role. The nice thing about this is that this gives you both traditional PostgreSQL behavior by default and completely SQL conforming behavior if you ask for it. (If we are really ambitious, we can let people put their preferred current_role into their session startup settings to give them exact SQL standard behavior by default without requiring SET ROLE NONE first.) Note that we don't need any inherit feature at all for any of that, nor does this require separate user and role entities. -- Peter Eisentraut http://developer.postgresql.org/~petere/
* Peter Eisentraut (peter_e@gmx.net) wrote:
> Am Samstag, 25. März 2006 17:27 schrieb Stephen Frost:
> > I think what Peter would want is for us to track CURRENT_USER (the role
> > who logged in) and to always add the CURRENT_USER to the list of roles
> > available after a 'SET ROLE'. That would at least get us a little
> > closer to the spec though I'm not sure if it matches what other DBs do.
>
> This is sort of but not really what I meant. What I think would work rather
> elegantly is having both a current_user and a current_role and having the
> current_role set to current_user on login. (The SQL standard would require
> the current role to be empty at login.) Then you get exactly the traditional
> group behavior. You can also use SET ROLE to set the current role (but not
> the current user) to some other permissible role. This will give you the
> applicable privileges of your current user and that new role. The nice thing
> about this is that this gives you both traditional PostgreSQL behavior by
> default and completely SQL conforming behavior if you ask for it. (If we are
> really ambitious, we can let people put their preferred current_role into
> their session startup settings to give them exact SQL standard behavior by
> default without requiring SET ROLE NONE first.) Note that we don't need any
> inherit feature at all for any of that, nor does this require separate user
> and role entities.
Well, at first blush it would seem like you could use GetSessionUserId()
for the 'CURRENT_USER' and modify roles_has_privs_of() to automatically
seed the roles_list with the 'CURRENT_USER'. This has the initial issue
that SECURITY DEFINER functions would end up including the permissions
of the calling user unless some additional checking is done, I believe.
I'm also pretty sure the SQL spec considers 'SESSION_USER' different
from 'CURRENT_USER' in some situations though I'm not sure which in
Postgres our current SESSION/CURRENT_USER arguments are closer to.
You'd also have to make sure you don't recurse down from the
'CURRENT_USER' if it's from the seed because otherwise SET ROLE would
end up being a no-op. I guess instead of seeding it, you could add it
on at the end provided it wasn't already in the list. I'm also
concerned about questions along the lines of 'who has permissions on
what' and that being stable/sensible.
I certainly don't feel we should get rid of 'inherit' though as that's a
very useful feature. I'm also not personally terribly interested in
trying to implement this, I'm mostly postulating some concrete changes
which could be made to give others the opportunity to find issues with
them. I also feel that we really can't do what the SQL spec requires
without seperating users from roles as I don't beleve you're supposed
to be able to grant users to roles or set role to a user. These kinds
of issues make me that much less interested in trying to implement the
spec verbatim.
I'd certainly be much happier to listen to concrete suggestions on
specific code changes or even better, patches to implement those
changes... Just as I don't feel the spec has been entirely thought
through because it hasn't actually been fully implemented in this
regard, I don't feel these changes and behaviors are really being
defined well enough to discuss very well and consider the ramifications
of them.
Thanks,
Stephen
Stephen Frost wrote: > I also feel that we really can't do what > the SQL spec requires without seperating users from roles as I don't > beleve you're supposed to be able to grant users to roles or set role > to a user. Actually, for my proposal to work, we *must* keep users and roles the same. While this is an extension of SQL, it is not an incompatibility. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Is there a TODO here? --------------------------------------------------------------------------- Peter Eisentraut wrote: > Am Samstag, 25. M?rz 2006 16:10 schrieb Tom Lane: > > No, the current implementation is a compromise between exact standards > > compatibility and backwards compatibility with our historical "groups" > > behavior. I'm not really prepared to toss the latter overboard. > > My two major sticking points here are the SET ROLE command and the noinherit > feature. The SET ROLE command is not required by our historical group > behavior (because we didn't have it before) and does not do what the SQL > standard says it should do. The noinherit feature is not required by the > historical group behavior (because groups are yes-inherit) and is not in the > SQL standard either. So these two features were just mistakes as far as I > can tell. > > I'm not passing judgement on whether a command like the currently implemented > SET ROLE command or a feature like the currently implemented noinherit > feature is useful. They are just not in line with either the historical > group behavior or the SQL standard. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > > ---------------------------(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 > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Sorry to ressurect this thread. However, I've been playing with the new role system and I'd prefer to keep CURRENT_USER as the login user, and not making it a synonymn for CURRENT_ROLE. In my application, I love the ability to "shed" privleges by "SET ROLE dataentry;". However, I need CURRENT_USER to remain as 'clark' for audit trail triggers (recording that 'dataentry' changed a particular order is kinda useless). I have a related information_schema question. Tom said that I could probably use "login" or "inherit" to determine which 'roles' are users, and which are really roles. Is this still the advice? That said, shouldn't PostgreSQL just call this mixed-thingy an 'authority' to reduce confusion. Then role-is-authority and user-is-authority. Probably too late, but, just in case it is still changable... My deeper question is... from the information_schema, is it possible (both in theory via definition, and in pratice via implementation) to obtain two things: (a) the roles to which I can do "SET ROLE" with, I guess this is my granted roles? (b) the roles to which I currently am using for my permission(s), or simply, the role inherit graph and my current role Thanks for your time, Clark P.S. There isn't a way to list "all roles" from the information_schema, except via DISTINCT on a table that refers tothem? On Mon, Apr 10, 2006 at 03:41:59PM -0400, Bruce Momjian wrote: | | Is there a TODO here? | | --------------------------------------------------------------------------- | | Peter Eisentraut wrote: | > Am Samstag, 25. M?rz 2006 16:10 schrieb Tom Lane: | > > No, the current implementation is a compromise between exact standards | > > compatibility and backwards compatibility with our historical "groups" | > > behavior. I'm not really prepared to toss the latter overboard. | > | > My two major sticking points here are the SET ROLE command and the noinherit | > feature. The SET ROLE command is not required by our historical group | > behavior (because we didn't have it before) and does not do what the SQL | > standard says it should do. The noinherit feature is not required by the | > historical group behavior (because groups are yes-inherit) and is not in the | > SQL standard either. So these two features were just mistakes as far as I | > can tell. | > | > I'm not passing judgement on whether a command like the currently implemented | > SET ROLE command or a feature like the currently implemented noinherit | > feature is useful. They are just not in line with either the historical | > group behavior or the SQL standard. | > | > -- | > Peter Eisentraut | > http://developer.postgresql.org/~petere/ | > | > ---------------------------(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 | > | | -- | Bruce Momjian http://candle.pha.pa.us | EnterpriseDB http://www.enterprisedb.com | | + If your life is a hard drive, Christ can be your backup. + | | ---------------------------(end of broadcast)--------------------------- | TIP 3: Have you checked our extensive FAQ? | | http://www.postgresql.org/docs/faq |
* Clark C. Evans (cce@clarkevans.com) wrote:
> Sorry to ressurect this thread. However, I've been playing with the new
> role system and I'd prefer to keep CURRENT_USER as the login user, and
> not making it a synonymn for CURRENT_ROLE. In my application, I love the
> ability to "shed" privleges by "SET ROLE dataentry;". However, I need
> CURRENT_USER to remain as 'clark' for audit trail triggers (recording
> that 'dataentry' changed a particular order is kinda useless).
This sounds like a reasonable point. I'm not sure it's something we can
actually do something about but I believe it's something worth thinking
about.
> I have a related information_schema question. Tom said that I could
> probably use "login" or "inherit" to determine which 'roles' are users,
> and which are really roles. Is this still the advice? That said,
Yes, this there isn't really any real difference between the two...
> shouldn't PostgreSQL just call this mixed-thingy an 'authority' to
> reduce confusion. Then role-is-authority and user-is-authority.
> Probably too late, but, just in case it is still changable...
I'm not really sure this would buy us all that much...
> My deeper question is... from the information_schema, is it possible
> (both in theory via definition, and in pratice via implementation) to
> obtain two things:
>
> (a) the roles to which I can do "SET ROLE" with, I guess this is
> my granted roles?
>
> (b) the roles to which I currently am using for my permission(s),
> or simply, the role inherit graph and my current role
These should be 'applicable_roles' and 'enabled_roles', respectively.
One possible issue I just noticed was that they both seem to follow
through 'noinherit' roles (even though actual permissions do not). Only
'applicable_roles' should follow through 'noinherit' roles,
'enabled_roles' shouldn't. They do work correctly otherwise, from what
I can tell:
abc=> select * from applicable_roles;grantee | role_name | is_grantable
---------+---------------------+--------------admin | postgres | NOsfrost | admin |
NOsfrost | app1_admin | NO
(3 rows)
abc=> select * from enabled_roles ; role_name
---------------------sfrostpostgresadminapp1_admin
(4 rows)
abc=> set role app1_admin;
SET
abc=> select * from enabled_roles ; role_name
---------------------app1_admin
(1 row)
abc=> select * from applicable_roles ;grantee | role_name | is_grantable
---------+-----------+--------------
(0 rows)
> P.S. There isn't a way to list "all roles" from the information_schema,
> except via DISTINCT on a table that refers to them?
I'm not sure a way is defined by the SQL spec, which we try to follow in
information_schema. pg_authid will give you the list but you need extra
permissisons to view that. I don't think it'd be out of the question to
add a 'pg_roles' view that provided the full list if there was enough
demand for it...
Thanks,
Stephen
"Clark C. Evans" <cce@clarkevans.com> writes:
> Sorry to ressurect this thread. However, I've been playing with the new
> role system and I'd prefer to keep CURRENT_USER as the login user, and
> not making it a synonymn for CURRENT_ROLE. In my application, I love the
> ability to "shed" privleges by "SET ROLE dataentry;". However, I need
> CURRENT_USER to remain as 'clark' for audit trail triggers (recording
> that 'dataentry' changed a particular order is kinda useless).
Aren't you looking for SESSION_USER?
regards, tom lane