Thread: Permission Problem for DELETE
Hi,
I've below data structure with listed permissions:
(commsrv@192.168.1.160:5432/test_1_5_0_0) [2008-05-27 09:34:32]
> \d commsrv.outgoingmessages
Table "commsrv.outgoingmessages"
Column | Type | Modifiers
------------------------+--------------------------------+-----------
messageid | bigint | not null
...
Indexes:
"pk_outgoingmessages_messageid" PRIMARY KEY, btree (messageid)
Triggers:
commsrv_outgoingmessages_update_for_emove
AFTER UPDATE ON outgoingmessages
FOR EACH ROW
EXECUTE PROCEDURE commsrv_outgoingmessages_update_for_emove()
(commsrv@192.168.1.160:5432/test_1_5_0_0) [2008-05-27 09:34:33]
> \z commsrv.outgoingmessages
Access privileges for database "test_1_5_0_0"
Schema | Name | Type | Access privileges
---------+------------------+-------+---------------------
commsrv | outgoingmessages | table | test=arwdxt/test
: emove=ad/test
: commsrv=arwdxt/test
(1 row)
PostgreSQL doesn't allow me to DELETE a row from
commsrv.outgoingmessages with emove role.
(emove@192.168.1.160:5432/test_1_5_0_0) [2008-05-27 09:37:11]
> EXPLAIN DELETE FROM commsrv.outgoingmessages WHERE messageid = 261120;
ERROR: permission denied for relation outgoingmessages
(emove@192.168.1.160:5432/test_1_5_0_0) [2008-05-27 09:37:17]
> DELETE FROM commsrv.outgoingmessages WHERE messageid = 261120;
ERROR: permission denied for relation outgoingmessages
Any ideas about what I might be missing?
Regards.
On Tue, 27 May 2008, Volkan YAZICI <yazicivo@ttmail.com> writes:
> PostgreSQL doesn't allow me to DELETE a row from
> commsrv.outgoingmessages with emove role.
By the way, I just learnt that I cannot do INSERT too.
(emove@192.168.1.160:5432/test_1_5_0_0) [2008-05-27 14:47:01]
> INSERT INTO commsrv.outgoingmessages
SELECT messageid+1, vendorid, ...
FROM commsrv.outgoingmessages
ORDER BY messageid DESC
LIMIT 1;
ERROR: permission denied for relation outgoingmessages
I suspected, schema permissions first...
(emove@192.168.1.160:5432/test_1_5_0_0) [2008-05-27 14:46:58]
> \dn+ commsrv
List of schemas
Name | Owner | Access privileges | Description
---------+-------+--------------------------------------------+-------------
commsrv | test | {test=UC/test,commsrv=U/test,emove=U/test} |
But this doesn't look like to be the case.
Does anybody have an idea? What might I be missing? I'll be really
really appreciated for any kind of help.
Regards.
Volkan YAZICI <yazicivo@ttmail.com> writes:
> Access privileges for database "test_1_5_0_0"
> Schema | Name | Type | Access privileges
> ---------+------------------+-------+---------------------
> commsrv | outgoingmessages | table | test=arwdxt/test
> : emove=ad/test
> : commsrv=arwdxt/test
> (1 row)
> PostgreSQL doesn't allow me to DELETE a row from
> commsrv.outgoingmessages with emove role.
> (emove@192.168.1.160:5432/test_1_5_0_0) [2008-05-27 09:37:11]
>>>> EXPLAIN DELETE FROM commsrv.outgoingmessages WHERE messageid = 261120;
> ERROR: permission denied for relation outgoingmessages
You don't have SELECT privilege, which is required to read any of the
columns in the WHERE clause.
(Not sure if this is explained anywhere in our manual :-(, but the
behavior is required by SQL spec.)
regards, tom lane
Hi,
I rarely use GRANT -- nearly once every 1-2 year -- and everytime I
forget this small detail: DELETE/INSERT/UPDATE privileges require SELECT
privilege also.
On Tue, 27 May 2008, Tom Lane <tgl@sss.pgh.pa.us> writes:
> You don't have SELECT privilege, which is required to read any of the
> columns in the WHERE clause.
As far as I tested, even
DELETE FROM foo;
UPDATE foo SET bar = NULL;
commands _require_ SELECT permissions.
> (Not sure if this is explained anywhere in our manual :-(, but the
> behavior is required by SQL spec.)
Would attached patch be a first step for that purpose?
Regards.
Index: doc/src/sgml/ref/grant.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.68
diff -u -r1.68 grant.sgml
--- doc/src/sgml/ref/grant.sgml 5 May 2008 01:21:03 -0000 1.68
+++ doc/src/sgml/ref/grant.sgml 27 May 2008 17:21:31 -0000
@@ -461,6 +461,14 @@
access privileges display. A <literal>*</> will appear only when
grant options have been explicitly granted to someone.
</para>
+
+ <para>
+ It must also be noted that <term>INSERT</term>, <term>UPDATE</term>
+ and <term>DELETE</term> priviliges require <term>SELECT</term>
+ privilege to be able to scan related table to locate about to be
+ updated rows on the table. Usage of these permissions without an
+ appropriate <term>SELECT</term> privilege will raise a permission error.
+ </para>
</refsect1>
<refsect1 id="sql-grant-examples">
yazicivo@ttmail.com (Volkan =?utf-8?B?WWF6xLFjxLE=?=) writes:
> On Tue, 27 May 2008, Tom Lane <tgl@sss.pgh.pa.us> writes:
>> You don't have SELECT privilege, which is required to read any of the
>> columns in the WHERE clause.
> As far as I tested, even
> DELETE FROM foo;
> UPDATE foo SET bar = NULL;
> commands _require_ SELECT permissions.
Well, you tested wrong then. It works as expected for me, which is
that you need SELECT if the query involves fetching any existing
column value:
regression=# create user joe;
CREATE ROLE
regression=# create table foo (f1 int, f2 int);
CREATE TABLE
regression=# grant delete , update on table foo to joe;
GRANT
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> update foo set f1 = null;
UPDATE 0
regression=> update foo set f1 = f2;
ERROR: permission denied for relation foo
regression=> delete from foo;
DELETE 0
regression=> delete from foo where f1 = 42;
ERROR: permission denied for relation foo
regression=>
regards, tom lane
On Tue, 27 May 2008, Tom Lane <tgl@sss.pgh.pa.us> writes:
> Well, you tested wrong then. It works as expected for me, which is
> that you need SELECT if the query involves fetching any existing
> column value:
Pff... Sorry for the noise. (I created example table under a differrent
schema than "public" to be able to test effects of schema priviliges to
INSERT/UPDATE/DELETE commands, but I somehow forgot that detail later.)
I updated the doc patch.
Regards.
Index: doc/src/sgml/ref/grant.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.68
diff -u -r1.68 grant.sgml
--- doc/src/sgml/ref/grant.sgml 5 May 2008 01:21:03 -0000 1.68
+++ doc/src/sgml/ref/grant.sgml 27 May 2008 18:01:56 -0000
@@ -461,6 +461,14 @@
access privileges display. A <literal>*</> will appear only when
grant options have been explicitly granted to someone.
</para>
+
+ <para>
+ It must also be noted that <term>UPDATE</term> and <term>DELETE</term>
+ queries involving <term>WHERE</term> clauses require <term>SELECT</term>
+ privilege to be able to scan related table to locate about to be updated
+ rows on the table. Usage of such queries without an appropriate
+ <term>SELECT</term> privilege will raise a permission error.
+ </para>
</refsect1>
<refsect1 id="sql-grant-examples">
yazicivo@ttmail.com (Volkan =?utf-8?B?WWF6xLFjxLE=?=) writes:
> On Tue, 27 May 2008, Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Well, you tested wrong then. It works as expected for me, which is
>> that you need SELECT if the query involves fetching any existing
>> column value:
> Pff... Sorry for the noise. (I created example table under a differrent
> schema than "public" to be able to test effects of schema priviliges to
> INSERT/UPDATE/DELETE commands, but I somehow forgot that detail later.)
> I updated the doc patch.
I applied a docs patch for this, though not exactly what you sent in.
Thanks for pointing out the omission.
regards, tom lane
On Tue, 27 May 2008, Tom Lane <tgl@sss.pgh.pa.us> writes: > I applied a docs patch for this, though not exactly what you sent in. A lot better. Thanks for your interest. Let's see if I'll ask same question next year. Regards.