From: Jaime Casanova <jcasanov@systemguards.com.ec>
To: Abhijit Menon-Sen <ams@oryx.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Jaime Casanova <systemguards@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Extending grant insert on tables to sequences
Date: Fri, 11 Jul 2008 11:57:37 -0500
Message-ID: <3073cc9b0807110957w71869487p17fb58cb98abc520@mail.gmail.com> (raw)
In-Reply-To: <20080710031140.GA427@toroid.org>
References: <c2d9e70e0805221118x1411e00crd7d4aae7e6e4ea3d@mail.gmail.com>
<c2d9e70e0805232156p20dbe2b9j2a88d35f951d625e@mail.gmail.com>
<20080708133244.GB4095@alvh.no-ip.org>
<20080709185837.GA27406@toroid.org>
<20080709191125.GK3946@alvh.no-ip.org>
<20080710031140.GA427@toroid.org>
On Wed, Jul 9, 2008 at 10:11 PM, Abhijit Menon-Sen <ams@oryx.com> wrote:
> At 2008-07-09 15:11:25 -0400, alvherre@commandprompt.com wrote:
>>
>> No, actually I meant having a lone "list = lappend(list, newseq);" in
>> the loop, so that ExecGrantStmt_oids is called only once.
>
> Yes, I understand what you meant. I just phrased my agreement poorly.
> Here's a more precise phrasing. ;-)
>
> (I agree with Robert Treat that there seems to be no point granting
> SELECT on the sequence. I don't *particularly* care about it, but I
> tend towards wanting to drop that bit. This patch reflects that.)
>
Hi,
sorry for the delay i was busy...
attached is a new version of the patch, it implements Alvaro's
suggestion and fix a bug i found (it wasn't managing GRANT ALL) :(
About the SELECT issue, AFAIU Robert doesn't complaint he just asked
what is the use case... if people think it should be removed ok, but
OTOH: why? i don't think that affects anything...
--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157
Attachments:grant_seq-v2.patch (text/x-diff, 4.1K) download
Index: doc/src/sgml/ref/grant.sgml
===================================================================
RCS file: /home/postgres/cvshome/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.70
diff -c -r1.70 grant.sgml
*** doc/src/sgml/ref/grant.sgml 3 Jul 2008 15:59:55 -0000 1.70
--- doc/src/sgml/ref/grant.sgml 11 Jul 2008 16:29:52 -0000
***************
*** 401,410 ****
</para>
<para>
! Granting permission on a table does not automatically extend
! permissions to any sequences used by the table, including
! sequences tied to <type>SERIAL</> columns. Permissions on
! sequence must be set separately.
</para>
<para>
--- 401,409 ----
</para>
<para>
! Granting permission on a table automatically extend
! permissions to any sequences owned by the table, including
! sequences tied to <type>SERIAL</> columns.
</para>
<para>
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /home/postgres/cvshome/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.147
diff -c -r1.147 aclchk.c
*** src/backend/catalog/aclchk.c 19 Jun 2008 00:46:03 -0000 1.147
--- src/backend/catalog/aclchk.c 11 Jul 2008 16:37:24 -0000
***************
*** 361,366 ****
--- 361,406 ----
}
ExecGrantStmt_oids(&istmt);
+ + /*+ * If the objtype is a relation and the privileges includes INSERT, UPDATE + * or SELECT then extends the GRANT/REVOKE to the sequences owned by the + * relation+ */+ if ((istmt.objtype == ACL_OBJECT_RELATION) && (istmt.all_privs || + (istmt.privileges & (ACL_INSERT | ACL_UPDATE | ACL_SELECT)))) + {+ InternalGrant istmt_seq;+ + istmt_seq.is_grant = istmt.is_grant;+ istmt_seq.objtype = ACL_OBJECT_SEQUENCE;+ istmt_seq.grantees = istmt.grantees;+ istmt_seq.grant_option = istmt.grant_option;+ istmt_seq.behavior = istmt.behavior;+ + istmt_seq.all_privs = false;+ istmt_seq.privileges = ACL_NO_RIGHTS;+ + if (istmt.all_privs)+ istmt_seq.all_privs = true;+ else+ {+ if (istmt.privileges & (ACL_INSERT)) + istmt_seq.privileges |= ACL_USAGE;+ if (istmt.privileges & (ACL_UPDATE)) + istmt_seq.privileges |= ACL_UPDATE;+ if (istmt.privileges & (ACL_SELECT)) + istmt_seq.privileges |= ACL_SELECT;+ }+ + istmt_seq.objects = NIL;+ foreach(cell, istmt.objects)+ istmt_seq.objects = list_concat(istmt_seq.objects,+ getOwnedSequences(lfirst_oid(cell)));+ + if (istmt_seq.objects != NIL)+ ExecGrantStmt_oids(&istmt_seq);+ }
}
/*
Index: src/test/regress/expected/dependency.out
===================================================================
RCS file: /home/postgres/cvshome/pgsql/src/test/regress/expected/dependency.out,v
retrieving revision 1.7
diff -c -r1.7 dependency.out
*** src/test/regress/expected/dependency.out 3 Jul 2008 15:59:55 -0000 1.7
--- src/test/regress/expected/dependency.out 11 Jul 2008 16:53:14 -0000
***************
*** 13,22 ****
-- can't drop neither because they have privileges somewhere
DROP USER regression_user;
ERROR: role "regression_user" cannot be dropped because some objects depend on it
! DETAIL: access to table deptest
DROP GROUP regression_group;
ERROR: role "regression_group" cannot be dropped because some objects depend on it
! DETAIL: access to table deptest
-- if we revoke the privileges we can drop the group
REVOKE SELECT ON deptest FROM GROUP regression_group;
DROP GROUP regression_group;
--- 13,24 ----
-- can't drop neither because they have privileges somewhere
DROP USER regression_user;
ERROR: role "regression_user" cannot be dropped because some objects depend on it
! DETAIL: access to sequence deptest_f1_seq
! access to table deptest
DROP GROUP regression_group;
ERROR: role "regression_group" cannot be dropped because some objects depend on it
! DETAIL: access to sequence deptest_f1_seq
! access to table deptest
-- if we revoke the privileges we can drop the group
REVOKE SELECT ON deptest FROM GROUP regression_group;
DROP GROUP regression_group;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: jcasanov@systemguards.com.ec, ams@oryx.com, alvherre@commandprompt.com, systemguards@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: Extending grant insert on tables to sequences
In-Reply-To: <3073cc9b0807110957w71869487p17fb58cb98abc520@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox