Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Is there a TODO here? --- Russell Smith wrote: Alvaro Herrera wrote: Russell Smith wrote: Alvaro Herrera wrote: Alvaro Herrera wrote: 2. decide that the standard is braindead and just omit dumping the grantor when it's no longer available, but don't remove pg_auth_members.grantor Which do people feel should be implemented? I can do whatever we decide; if no one has a strong opinion on the matter, my opinion is we do (2) which is the easiest. Here is a patch implementing this idea, vaguely based on Russell's. I haven't had time to finalize my research about this, but the admin option with revoke doesn't appear to work as expected. Here is my sample SQL for 8.2.4 create table test (x integer); \z create role test1 noinherit; create role test2 noinherit; grant select on test to test1 with grant option; grant select on test to test2; \z test set role test1; revoke select on test from test2; \z test set role test2; select * from test; reset role; revoke all on test from test2; revoke all on test from test1; drop role test2; drop role test1; drop table test; \q The privilege doesn't appear to be revoked by test1 from test2. I'm not sure if this is related, but I wanted to bring it up in light of the options we have for grantor. Humm, but the privilege was not granted by test1, but by the user you were using initially. The docs state in a note that A user can only revoke privileges that were granted directly by that user. I understand that this would apply to the grantor stuff being discussed in this thread as well, but I haven't seen anyone arguing that we should implement that for GRANT ROLE (and I asked three times if people felt it was important and nobody answered). Well, I would vote for implementing this in GRANT ROLE. I wish to use it in my security model. I don't think the spec is brain dead when you understand what it's trying to achieve. Example: 2 Groups of administrators who are allowed to grant a role to users of the system App_Admin_G1 App_Admin_G2 App_User SET ROLE App_Admin_G1 GRANT App_User TO Fred; SET ROLE App_Admin_G2 GRANT App_User TO John; SET ROLE App_Admin_G1 REVOKE App_User FROM John; As App_Admin_G1 did not grant App_User rights to John, he should not be able to take them away. I currently have a situation where I would like to be able to do the above. I have two separate departments who might grant privileges for the same application to the same user. One department administrator should not be able to revoke the privileges set by the other one. I would expect superusers to be able to revoke from anybody, or the owner. I'm not sure what the owner is when we talk about granting roles. Regards Russell Smith ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Bruce Momjian wrote: Is there a TODO here? Yes, I think so: * Implement the SQL standard mechanism whereby REVOKE ROLE only revokes the privilege as granted by the invoking role, and not those granted by other roles -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Added to TODO: --- Alvaro Herrera wrote: Bruce Momjian wrote: Is there a TODO here? Yes, I think so: * Implement the SQL standard mechanism whereby REVOKE ROLE only revokes the privilege as granted by the invoking role, and not those granted by other roles -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Russell Smith wrote: Alvaro Herrera wrote: Alvaro Herrera wrote: 2. decide that the standard is braindead and just omit dumping the grantor when it's no longer available, but don't remove pg_auth_members.grantor Which do people feel should be implemented? I can do whatever we decide; if no one has a strong opinion on the matter, my opinion is we do (2) which is the easiest. Here is a patch implementing this idea, vaguely based on Russell's. I haven't had time to finalize my research about this, but the admin option with revoke doesn't appear to work as expected. Here is my sample SQL for 8.2.4 create table test (x integer); \z create role test1 noinherit; create role test2 noinherit; grant select on test to test1 with grant option; grant select on test to test2; \z test set role test1; revoke select on test from test2; \z test set role test2; select * from test; reset role; revoke all on test from test2; revoke all on test from test1; drop role test2; drop role test1; drop table test; \q The privilege doesn't appear to be revoked by test1 from test2. I'm not sure if this is related, but I wanted to bring it up in light of the options we have for grantor. Humm, but the privilege was not granted by test1, but by the user you were using initially. The docs state in a note that A user can only revoke privileges that were granted directly by that user. I understand that this would apply to the grantor stuff being discussed in this thread as well, but I haven't seen anyone arguing that we should implement that for GRANT ROLE (and I asked three times if people felt it was important and nobody answered). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Alvaro Herrera wrote: Alvaro Herrera wrote: 2. decide that the standard is braindead and just omit dumping the grantor when it's no longer available, but don't remove pg_auth_members.grantor Which do people feel should be implemented? I can do whatever we decide; if no one has a strong opinion on the matter, my opinion is we do (2) which is the easiest. Here is a patch implementing this idea, vaguely based on Russell's. Applied to CVS HEAD, 8.2 and 8.1. If we want to start tracking the grantor as a shared dependency, and have REVOKE work per spec (i.e. only revoke the privileges actually granted by the role executing REVOKE), those are separate patches (and they should be applied only to HEAD). This patch merely fixes the fact that pg_dumpall failed to work for busted databases. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Alvaro Herrera wrote: Alvaro Herrera wrote: Alvaro Herrera wrote: 2. decide that the standard is braindead and just omit dumping the grantor when it's no longer available, but don't remove pg_auth_members.grantor Which do people feel should be implemented? I can do whatever we decide; if no one has a strong opinion on the matter, my opinion is we do (2) which is the easiest. Here is a patch implementing this idea, vaguely based on Russell's. Applied to CVS HEAD, 8.2 and 8.1. If we want to start tracking the grantor as a shared dependency, and have REVOKE work per spec (i.e. only revoke the privileges actually granted by the role executing REVOKE), those are separate patches (and they should be applied only to HEAD). This patch merely fixes the fact that pg_dumpall failed to work for busted databases. Should there also be a doc patch for this, the document descriptions seemed different to what is actually implemented. I'll check that before I make any further comments. Russell ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Alvaro Herrera wrote: Russell Smith wrote: Alvaro Herrera wrote: Alvaro Herrera wrote: 2. decide that the standard is braindead and just omit dumping the grantor when it's no longer available, but don't remove pg_auth_members.grantor Which do people feel should be implemented? I can do whatever we decide; if no one has a strong opinion on the matter, my opinion is we do (2) which is the easiest. Here is a patch implementing this idea, vaguely based on Russell's. I haven't had time to finalize my research about this, but the admin option with revoke doesn't appear to work as expected. Here is my sample SQL for 8.2.4 create table test (x integer); \z create role test1 noinherit; create role test2 noinherit; grant select on test to test1 with grant option; grant select on test to test2; \z test set role test1; revoke select on test from test2; \z test set role test2; select * from test; reset role; revoke all on test from test2; revoke all on test from test1; drop role test2; drop role test1; drop table test; \q The privilege doesn't appear to be revoked by test1 from test2. I'm not sure if this is related, but I wanted to bring it up in light of the options we have for grantor. Humm, but the privilege was not granted by test1, but by the user you were using initially. The docs state in a note that A user can only revoke privileges that were granted directly by that user. I understand that this would apply to the grantor stuff being discussed in this thread as well, but I haven't seen anyone arguing that we should implement that for GRANT ROLE (and I asked three times if people felt it was important and nobody answered). Well, I would vote for implementing this in GRANT ROLE. I wish to use it in my security model. I don't think the spec is brain dead when you understand what it's trying to achieve. Example: 2 Groups of administrators who are allowed to grant a role to users of the system App_Admin_G1 App_Admin_G2 App_User SET ROLE App_Admin_G1 GRANT App_User TO Fred; SET ROLE App_Admin_G2 GRANT App_User TO John; SET ROLE App_Admin_G1 REVOKE App_User FROM John; As App_Admin_G1 did not grant App_User rights to John, he should not be able to take them away. I currently have a situation where I would like to be able to do the above. I have two separate departments who might grant privileges for the same application to the same user. One department administrator should not be able to revoke the privileges set by the other one. I would expect superusers to be able to revoke from anybody, or the owner. I'm not sure what the owner is when we talk about granting roles. Regards Russell Smith ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Alvaro Herrera wrote:
2. decide that the standard is braindead and just omit dumping the
grantor when it's no longer available, but don't remove
pg_auth_members.grantor
Which do people feel should be implemented? I can do whatever we
decide; if no one has a strong opinion on the matter, my opinion is we
do (2) which is the easiest.
Here is a patch implementing this idea, vaguely based on Russell's.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/bin/pg_dump/pg_dumpall.c
===
RCS file: /home/alvherre/cvs/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.90
diff -c -p -r1.90 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 10 Feb 2007 14:58:55 - 1.90
--- src/bin/pg_dump/pg_dumpall.c 14 May 2007 23:13:43 -
*** dumpRoleMembership(PGconn *conn)
*** 702,709
res = executeQuery(conn, SELECT ur.rolname AS roleid,
um.rolname AS member,
! ug.rolname AS grantor,
! a.admin_option
FROM pg_auth_members a
LEFT JOIN pg_authid ur on ur.oid = a.roleid
LEFT JOIN pg_authid um on um.oid = a.member
--- 702,709
res = executeQuery(conn, SELECT ur.rolname AS roleid,
um.rolname AS member,
! a.admin_option,
! ug.rolname AS grantor
FROM pg_auth_members a
LEFT JOIN pg_authid ur on ur.oid = a.roleid
LEFT JOIN pg_authid um on um.oid = a.member
*** dumpRoleMembership(PGconn *conn)
*** 717,730
{
char *roleid = PQgetvalue(res, i, 0);
char *member = PQgetvalue(res, i, 1);
! char *grantor = PQgetvalue(res, i, 2);
! char *option = PQgetvalue(res, i, 3);
fprintf(OPF, GRANT %s, fmtId(roleid));
fprintf(OPF, TO %s, fmtId(member));
if (*option == 't')
fprintf(OPF, WITH ADMIN OPTION);
! fprintf(OPF, GRANTED BY %s;\n, fmtId(grantor));
}
PQclear(res);
--- 717,740
{
char *roleid = PQgetvalue(res, i, 0);
char *member = PQgetvalue(res, i, 1);
! char *option = PQgetvalue(res, i, 2);
fprintf(OPF, GRANT %s, fmtId(roleid));
fprintf(OPF, TO %s, fmtId(member));
if (*option == 't')
fprintf(OPF, WITH ADMIN OPTION);
!
! /*
! * We don't track the grantor very carefully in the backend, so cope
! * with the possibility that it has been dropped.
! */
! if (!PQgetisnull(res, i, 3))
! {
! char *grantor = PQgetvalue(res, i, 3);
!
! fprintf(OPF, GRANTED BY %s, fmtId(grantor));
! }
! fprintf(OPF, ;\n);
}
PQclear(res);
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
So the discussion died again with nothing being decided. I see we have several choices: 1. implement the standard, per Russell suggestion below 2. decide that the standard is braindead and just omit dumping the grantor when it's no longer available, but don't remove pg_auth_members.grantor 3. decide that the standard is braindead and remove pg_auth_members.grantor Which do people feel should be implemented? I can do whatever we decide; if no one has a strong opinion on the matter, my opinion is we do (2) which is the easiest. Russell Smith wrote: My possible suggestion is; 1. Implement the standard for revoking only your privileges by default. 2. Allow the object owner to revoke privileges assigned by any role, as if you drop and recreate the object you can achieve this anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Alvaro Herrera wrote: Stephen Frost wrote: I don't have time right at the moment (leaving shortly and will be gone all weekend) but what I would do is check the SQL standard, especially the information schema, for any requirement to track the grantor. Much of what I did was based on the standard so that may have been the instigation for tracking grantor. Hmm. I had forgotten the information schema. I just checked: the only view using pg_auth_members is APPLICABLE_ROLES, and that one doesn't display the grantor column. This section of the standard is relevant: 4.34.3 Roles Each grant is represented and identified by a role authorization descriptor. A role authorization descriptor includes: — The role name of the role. — The authorization identifier of the grantor. — The authorization identifier of the grantee. — An indication of whether or not the role was granted with the WITH ADMIN OPTION and hence is grantable. ... continues reading the spec ... Ah, here it is, 12.7 revoke statement. It says that if role revokes another role from a third role, it will only remove the privileges that were granted by him, not someone else. That is, if roles A and B grant a role Z to C, and then role A revokes Z from C, then role C continues to have the role Z because of the grant B gave. So we have a problem here, because this alvherre=# create role a; CREATE ROLE alvherre=# create role b; CREATE ROLE alvherre=# create role z admin a, b; CREATE ROLE alvherre=# create role c; CREATE ROLE alvherre=# set session authorization a; SET alvherre= grant z to c; GRANT ROLE alvherre= set session authorization b; SET alvherre= grant z to c; NOTICE: role c is already a member of role z should not emit any noise, but instead add another grant of Z to C with grantor B. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
* Alvaro Herrera ([EMAIL PROTECTED]) wrote: Ah, here it is, 12.7 revoke statement. It says that if role revokes another role from a third role, it will only remove the privileges that were granted by him, not someone else. Hmm. I'm not sure, but that may have been a case where it was generally decided that the spec was somewhat braindead in this fashion (it seems so in my personal view of this, honestly...). To issue a revoke and have it not work would be kind of concerning. If we do end up following this path we should emit a warning (at least...) if the user still has the rights which are being revoked, even if through someone else. Perhaps that also implies that tracking the grantor is unnecessary. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Stephen Frost [EMAIL PROTECTED] writes: Hmm. I'm not sure, but that may have been a case where it was generally decided that the spec was somewhat braindead in this fashion (it seems so in my personal view of this, honestly...). To issue a revoke and have it not work would be kind of concerning. If we do end up following this path we should emit a warning (at least...) if the user still has the rights which are being revoked, even if through someone else. That's not how it works for rights on ordinary objects. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
* Tom Lane ([EMAIL PROTECTED]) wrote:
Stephen Frost [EMAIL PROTECTED] writes:
Hmm. I'm not sure, but that may have been a case where it was generally
decided that the spec was somewhat braindead in this fashion (it seems
so in my personal view of this, honestly...). To issue a revoke and
have it not work would be kind of concerning. If we do end up following
this path we should emit a warning (at least...) if the user still has
the rights which are being revoked, even if through someone else.
That's not how it works for rights on ordinary objects.
Not quite sure which bit you're referring to here.. On 8.1, at least,
we ignore a grant which has a matching right and target:
sfrost= set role u1;
sfrost= \dp
Access privileges for database sfrost
Schema | Name | Type |Access privileges
+--+---+-
sfrost | test | table | {u1=arwdRxt/u1,u3=r/u1}
(1 row)
sfrost= reset role;
RESET
sfrost= set role u2;
SET
sfrost= grant select on test to u3;
GRANT
sfrost= \dp
Access privileges for database sfrost
Schema | Name | Type |Access privileges
+--+---+-
sfrost | test | table | {u1=arwdRxt/u1,u3=r/u1}
(1 row)
Additionally, any user with ownership rights on the table in question
can revoke the rights of a user. Still as u2:
sfrost= revoke select on test from u3;
REVOKE
sfrost= \dp
Access privileges for database sfrost
Schema | Name | Type | Access privileges
+--+---+---
sfrost | test | table | {u1=arwdRxt/u1}
(1 row)
If you're saying we don't currently warn if a revoke leaves the
priviledges in-tact for the right and target, I'm not sure you can
currently get in a state where it'd be possible to run into that.
Either you have the rights to remove the grant on the object
(you're an 'owner' of it), in which case the grant will be removed
if it exists (based on the right and target, regardless of who
granted it), or you don't, in which case you get a permission denied
ERROR outright. If regular object permissions were ever changed to
require the grantor to be the revoker, I would want a warning in the
case described for regular objects as well.
If you're saying we don't currently require that the grantor be the
revoker on regular objects, I would agree. :)
Thanks,
Stephen
signature.asc
Description: Digital signature
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Stephen Frost [EMAIL PROTECTED] writes: If you're saying we don't currently warn if a revoke leaves the priviledges in-tact for the right and target, I'm not sure you can currently get in a state where it'd be possible to run into that. I'm thinking of the case that comes up periodically where newbies think that revoking a right from a particular user overrides a grant to PUBLIC of the same right. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Based on the discussion so far, it seems to me that the sane course of action is to continue to register the grantor, because the standard mandates that it should be there; but ignore the parts where we revoke selectively, because that's a stupid thing to do. So we do deviate, if slightly. So we will have pg_dumpall do nothing special if the grantor has gone away since granting the privilege. That is, exactly the patch that was submitted, no new code needs to be written. (Maybe a mention in the compatibility section of REVOKE is warranted, though I'm not sure). Does anyone object to this course of action? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: If you're saying we don't currently warn if a revoke leaves the priviledges in-tact for the right and target, I'm not sure you can currently get in a state where it'd be possible to run into that. I'm thinking of the case that comes up periodically where newbies think that revoking a right from a particular user overrides a grant to PUBLIC of the same right. Technically, the grant to public is a different target from the target of the revoke in such a case. Following the spec would mean that even when the grant and the revoke target is the same (unless you're the original grantor) the right won't be removed. I'm not against adding a warning in the case you describe though, but I don't see it being as necessary for that case. What the spec describes is, at least in my view, much more counter-intuitive than how PG currently works. Thanks, Stephen signature.asc Description: Digital signature
