pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPermission to Select
10+ messages / 6 participants
[nested] [flat]
* Permission to Select
@ 2006-03-13 09:51 Eugene E. <sad@bankir.ru>
0 siblings, 2 replies; 10+ messages in thread
From: Eugene E. @ 2006-03-13 09:51 UTC (permalink / raw)
To: pgsql-sql
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:
inline patch 1: src/backend/executor/execMain.c (+0/-0 lines) download inline-1.patch
--- 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 @@
* Re: Permission to Select
@ 2006-03-13 14:33 Rod Taylor <pg@rbt.ca>
parent: Eugene E. <sad@bankir.ru>
1 sibling, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2006-03-13 14:33 UTC (permalink / raw)
To: Eugene E. <sad@bankir.ru>; +Cc: pgsql-sql
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.
--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-13 14:46 Alvaro Herrera <alvherre@commandprompt.com>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2006-03-13 14:46 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Eugene E. <sad@bankir.ru>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-13 15:10 Rod Taylor <pg@rbt.ca>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2006-03-13 15:10 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Eugene E. <sad@bankir.ru>; pgsql-sql
On Mon, 2006-03-13 at 10:46 -0400, Alvaro Herrera wrote:
> 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.
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-13 15:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Eugene E. <sad@bankir.ru>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2006-03-13 15:27 UTC (permalink / raw)
To: Eugene E. <sad@bankir.ru>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-13 20:51 PFC <lists@peufeu.com>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 10+ messages in thread
From: PFC @ 2006-03-13 20:51 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: pgsql-sql
> 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(password).
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).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-14 05:59 Eugene E. <sad@bankir.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Eugene E. @ 2006-03-14 05:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
> "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:
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-14 06:06 Eugene E. <sad@bankir.ru>
parent: PFC <lists@peufeu.com>
0 siblings, 0 replies; 10+ messages in thread
From: Eugene E. @ 2006-03-14 06:06 UTC (permalink / raw)
To: PFC <lists@peufeu.com>; +Cc: Rod Taylor <pg@rbt.ca>; pgsql-sql
PFC wrote:
>
>
>> 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(password).
> Read log table.
> Done.
This trick is available _NOW_.
(in the current state of permission system)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-27 14:25 Markus Schaber <schabi@logix-tt.com>
parent: Eugene E. <sad@bankir.ru>
0 siblings, 1 reply; 10+ messages in thread
From: Markus Schaber @ 2006-03-27 14:25 UTC (permalink / raw)
To: Eugene E. <sad@bankir.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Permission to Select
@ 2006-03-28 05:54 Eugene E. <sad@bankir.ru>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 0 replies; 10+ messages in thread
From: Eugene E. @ 2006-03-28 05:54 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Markus Schaber wrote:
> 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
>
thanx.
I already clear this to me.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-03-28 05:54 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-13 09:51 Permission to Select Eugene E. <sad@bankir.ru>
2006-03-13 14:33 ` Rod Taylor <pg@rbt.ca>
2006-03-13 14:46 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-03-13 15:10 ` Rod Taylor <pg@rbt.ca>
2006-03-13 20:51 ` PFC <lists@peufeu.com>
2006-03-14 06:06 ` Eugene E. <sad@bankir.ru>
2006-03-13 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-14 05:59 ` Eugene E. <sad@bankir.ru>
2006-03-27 14:25 ` Markus Schaber <schabi@logix-tt.com>
2006-03-28 05:54 ` Eugene E. <sad@bankir.ru>
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