Hi, While evaluating the PostgreSQL 18 beta, I had a thought experiment where I thought it might be possible to use the new virtual generated columns to gain superuser privileges for a regular user.
Attached is a sample exploit, that achieves this, key components: - the GENERATED column uses a user defined immutable function - this immutable function cannot ALTER ROLE (needs volatile) - therefore this immutable function calls a volatile function - the volatile function can contain any security exploit The problem I think for PostgreSQL 18 is quite high, as I think it is more likely that a superuser issues a `SELECT` against any table (graphical DB clients for example, showing the first N rows in a window) However, the problem *also* exists for the GENERATED [...] STORED columns, so probably all pg versions >= 12? although it is less likely that a superuser would `INSERT` into those tables? Here's a transcript of the output of the file that shows it: You are now connected to database "postgres" as user "regular". CREATE FUNCTION CREATE FUNCTION CREATE TABLE INSERT 0 1 i | j ---+--- 1 | 1 (1 row) You are now connected to database "postgres" as user "postgres". stage | case -------------------------------+-------------- Before superuser did a SELECT | regular user (1 row) i | j ---+--- 1 | 1 (1 row) stage | case ------------------------------+----------- After superuser did a SELECT | superuser (1 row) Forwarding this discussion from secur...@postgresql.org: On Fri, 16 May 2025 at 23:12, Noah Misch <n...@leadboat.com> wrote: > > On Fri, May 16, 2025 at 07:16:13PM +0200, Feike Steenbergen wrote: > > On Fri, 16 May 2025 at 19:00, Noah Misch <n...@leadboat.com> wrote: > > > Thanks for the report. Does this attack work if the reader uses COPY > > instead of SELECT? COPY has been safe, so we should think twice before > > > making it unsafe. > > > > Plain COPY seems safe, that's a very good thing: > > > > -- This does not cause the regular user to become superuser COPY > > exploit_generated.generated_sample TO STDOUT; > > > > -- This is safe, with a useful error: COPY > > exploit_generated.generated_sample(i, j) TO STDOUT; > > > > ERROR: column "j" is a generated column DETAIL: Generated columns cannot be > > used in COPY. > > > > Copy wrapped around a select however is not safe, (not a suprise I think): > > > > -- This is unsafe COPY (SELECT * FROM exploit_generated.generated_sample) TO > > STDOUT; > > That suggests virtual generated table columns have the same risk as views, not > more risk. That is good news. > > > > In other words, virtual generated columns make a table into a hybrid of > > > view and table, so anything odd that we've needed to do to views and > > foreign tables may apply to tables containing virtual generated columns. > > > > Yeah, that to me is the gist of the issue, that a plain `SELECT` against any > > such table can be used to run arbitrary function calls. On Fri, 16 May 2025 at 23:12, Noah Misch <n...@leadboat.com> wrote: > > If nothing else, I think the project will need to extend > restrict_nonsystem_relation_kind so virtual generated columns become one of > the things it can block.
exploit_generated.sql
Description: Binary data
