2

What is the purpose of UPDATE permission if it does not allow to update without SELECT permission?

Generally, I understand that update internally causes select to find target rows, but this "internal select" does not leak to a user, so it is unclear if it is a bug or there is a "deeper meaning" of that.

Assume initially my_user has only USAGE on my_schema and no grants on my_table

Case 1:

GRANT UPDATE ON TABLE my_schema.my_table TO my_user;

UPDATE my_table
   SET my_col = 'X';

>> SQL Error [42501]: ERROR: permission denied for table my_table

Case 2:

GRANT SELECT ON TABLE my_schema.my_table TO my_user;
GRANT UPDATE ON TABLE my_schema.my_table TO my_user;

UPDATE my_table
   SET my_col = 'X';

>> SUCCESS
diziaq
  • 6,881
  • 16
  • 54
  • 96
  • I cannot reproduce that.Your actual `UPDATE` must be different. Please show the actual `UPDATE`. – Laurenz Albe Jun 17 '21 at 20:32
  • 1
    See here [Privileges](https://www.postgresql.org/docs/current/ddl-priv.html) under UPDATE: '(In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.)' – Adrian Klaver Jun 17 '21 at 23:48
  • 1
    But `UPDATE tab SET a=a` is an entirely different statement, and that will require the `SELECT` privilege, as it reads `a`. Either update the question or close it. – Laurenz Albe Jun 18 '21 at 02:27

1 Answers1

3

Your assumption about "internal select" does not leak to a user is erroneous.

The returning keyword is very powerful and can be used to proxy a select statement:

UPDATE my_table
SET my_col = my_col
RETURNING *;

--> will show the same as select * from my_table

However, the select privilege is required only because the value of my_col is read. If you were to use a constant instead, it would work with just the update privilege and it wouldn't let you return the row.

REVOKE select ON my_table FROM my_user;

update my_table set my_col =1;
UPDATE 7
update my_table set my_col =1 returning *;
ERROR:  permission denied for table test
JGH
  • 15,928
  • 4
  • 31
  • 48
  • you are right. What I mean is `GRANT UPDATE` should allow `UPDATE my_table SET my_col = my_col` and prohibit `UPDATE ... RETURNING ...`; and `GRANT SELECT` should allow both. – diziaq Jun 18 '21 at 06:45