PostgreSQL SQL - Permission to Select

Author

Permission to Select

Eugene E.

2006-03-13, 7:25 am

Hi all
the serious problem with permissions is encountered

NOTE: the following example is really useful but there is no room to
describe it's use.

db=# CREATE USER u;
db=# CREATE TABLE t (i int, a text);
db=# REVOKE all ON t FROM u;
db=# GRANT update,insert,delete
ON t TO u;
db=# \c - u

db=> INSERT INTO t VALUES (1,'x');
INSERT
db=> UPDATE t SET a='y' WHERE i=1;
ERROR: Permission denied for relation t;
db=> UPDATE t SET a='y';
UPDATE

1) The user "u" is permitted but unable to perfom the operation !
2) A user is able to update WHOLE table but unable to update ANY part of
it !

Please examine the following patch and make your judgment:

--- src/backend/executor/execMain.c.orig 2005-11-22 1:23:08.000000000 +0300
+++ src/backend/executor/execMain.c 2006-02-17 13:19:29.000000000 +0300
@@ -460,6 +460,16 @@
bool do_select_into;
TupleDesc tupType;

+ if ( operation == CMD_UPDATE || operation == CMD_DELETE )
+ {
+ ListCell *l;
+ foreach(l, parseTree->rtable)
+ {
+ RangeTblEntry *rte = lfirst(l);
+ rte->requiredPerms ^= ACL_SELECT;
+ }
+ }
+
/*
* Do permissions checks. It's sufficient to examine the query's top
* rangetable here --- subplan RTEs will be checked during

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Rod Taylor

2006-03-13, 9:24 am

On Mon, 2006-03-13 at 12:51 +0300, Eugene E. wrote:
> Hi all
> the serious problem with permissions is encountered
>
> NOTE: the following example is really useful but there is no room to
> describe it's use.
>
>
> db=# CREATE USER u;
> db=# CREATE TABLE t (i int, a text);
> db=# REVOKE all ON t FROM u;
> db=# GRANT update,insert,delete
ON t TO u;
> db=# \c - u
>
> db=> INSERT INTO t VALUES (1,'x');
> INSERT
> db=> UPDATE t SET a='y' WHERE i=1;
> ERROR: Permission denied for relation t;
> db=> UPDATE t SET a='y';
> UPDATE
>
> 1) The user "u" is permitted but unable to perfom the operation !
> 2) A user is able to update WHOLE table but unable to update ANY part of
> it !
>
Good chance this was on purpose.

BEGIN;
UPDATE compensation SET salary = salary WHERE name = 'Tom' and
salary BETWEEN 50000 and 60000;
-- No rows updated -- that's not Toms salary
rollback;

BEGIN;
UPDATE compensation SET salary = salary WHERE name = 'Tom' and
salary BETWEEN 60000 and 70000;
-- One row updated so I found the range, I need a raise!
rollback;

By allowing the user a where clause you grant them select privileges.
You will find that delete works the same way.

This is one of those times when per column permissions are useful. You
could grant them select access on the "name" column but not the "salary"
column.

--

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Alvaro Herrera

2006-03-13, 9:24 am

Rod Taylor wrote:

> By allowing the user a where clause you grant them select privileges.
> You will find that delete works the same way.
>
> This is one of those times when per column permissions are useful. You
> could grant them select access on the "name" column but not the "salary"
> column.

If I understand clearly, the patch he posted modified things so that if
the user issued an UPDATE command, the SELECT permission was required as
well. Thus a user with UPDATE privileges but no SELECT was not allowed
to execute the UPDATE command.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Rod Taylor

2006-03-13, 9:24 am

On Mon, 2006-03-13 at 10:46 -0400, Alvaro Herrera wrote:
> Rod Taylor wrote:
>
>
> If I understand clearly, the patch he posted modified things so that if
> the user issued an UPDATE command, the SELECT permission was required as
> well. Thus a user with UPDATE privileges but no SELECT was not allowed
> to execute the UPDATE command.

Okay, I got it backward. The exclamation mark behind the first point
made me think it was an issue.

What information can be retrieved from a structure by being able to
update all rows? You possibly find the range of values (max/min via
integer overflow) but still would not know which tuple is associated
with which value.

I don't have a use-case for update but the delete / insert combination
is quite handy for doing data resets from a host which should not be
able to retrieve the interim states.

---------------------------(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

Tom Lane

2006-03-13, 11:24 am

"Eugene E." <sad@bankir.ru> writes:
> db=# REVOKE all ON t FROM u;
> db=# GRANT update,insert,delete
ON t TO u;
> db=# \c - u

> db=> INSERT INTO t VALUES (1,'x');
> INSERT
> db=> UPDATE t SET a='y' WHERE i=1;
> ERROR: Permission denied for relation t;
> db=> UPDATE t SET a='y';
> UPDATE

This behavior is correct and as documented in the UPDATE reference page:

You must have the UPDATE privilege on the table to update it, as
well as the SELECT privilege to any table whose values are read
in the expressions or condition.

The use of "i" in the WHERE clause is what causes SELECT privilege to be
needed.

If we had per-column privileges then we could be finer-grained about it,
but we don't (yet).

> Please examine the following patch and make your judgment:

This patch is so wrong it's not even worth discussing :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

PFC

2006-03-13, 8:24 pm

> What information can be retrieved from a structure by being able to
> update all rows?

Write a plpgsql function snoop(x) which inserts x into a table 'log'
created by you, and also returns x.
UPDATE users SET password=snoop(passw
ord).
Read log table.
Done.

If you have WHERE rights on a table, you can guess any column content
pretty quick. Just do a binary search. Some time ago I had a friend whose
website had some SQL injection holes, and he said "so what ? who cares ? I
have magicquotes" (lol), so I coded a python script which injected a
"password BETWEEN x AND y" (using CHR() to avoid quotes) and narrowed the
search. It took about 1 minute to get the password (which turned out to be
md5 that resisted a few seconds to dictionary attack using whatever evil
hax0rz tool).

---------------------------(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

Eugene E.

2006-03-14, 3:24 am

Tom Lane wrote:
> "Eugene E." <sad@bankir.ru> writes:
>
>
>
>
>
> This behavior is correct and as documented in the UPDATE reference page:

Good
if you have a strange behavior - just document it. quite good.

> You must have the UPDATE privilege on the table to update it, as
> well as the SELECT privilege to any table whose values are read
> in the expressions or condition.

This means that some privileges are NOT INDEPENDENT.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Eugene E.

2006-03-14, 3:24 am

PFC wrote:
>
>
>
>
> Write a plpgsql function snoop(x) which inserts x into a table
> 'log' created by you, and also returns x.
> UPDATE users SET password=snoop(passw
ord).
> Read log table.
> Done.

This trick is available _NOW_.
(in the current state of permission system)

---------------------------(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

Markus Schaber

2006-03-27, 9:38 am

Hi, Eugene,

Eugene E. wrote:

> This means that some privileges are NOT INDEPENDENT.

No, it means that the UPDATE operation needs both UPDATE and SELECT
privileges.

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---------------------------(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

Eugene E.

2006-03-28, 3:29 am

Markus Schaber wrote:
> Hi, Eugene,
>
> Eugene E. wrote:
>
>
>
>
> No, it means that the UPDATE operation needs both UPDATE and SELECT
> privileges.
>
> Markus
>

thanx.
I already clear this to me.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links