Permission Problem for DELETE

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.

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.

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.