Overcoming SELECT ... FOR UPDATE permission restrictions

Alexander Law <exclusion@gmail.com>

From: Alexander Lakhin <exclusion@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2018-04-13T04:10:01Z
Lists: pgsql-hackers
Hello hackers,

Can you please explain, is this a bug or intended behaviour?

Running as non-privileged user:

postgres=> SELECT datid, datname FROM pg_stat_database FOR UPDATE;
ERROR: permission denied for view pg_stat_database (SQLState: 42501)

But:

postgres=> CREATE VIEW pgsd AS SELECT * FROM pg_stat_database; SELECT 
datid, datname FROM pgsd FOR UPDATE;
CREATE VIEW
  datid |  datname
-------+-----------
  13021 | postgres
      1 | template1
  13020 | template0
(3 rows)


(And lock is really held by the second SELECT.)

Best regards,

------
Alexander Lakhin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Commits

  1. Simplify view-expansion code in rewriteHandler.c.

  2. Fix enforcement of SELECT FOR UPDATE permissions with nested views.