A flaw was found in the way Postgresql allowed a user to modify the behavior of a query for other users. An attacker with a user account could use this flaw to execute code with the permissions of superuser in the database. Versions 9.3 through 10 are affected.
The problem described in CVE-2018-1058 centers around the default “public” schema and how PostgreSQL uses the search_path setting. The ability to create objects with the same names in different schemas, combined with how PostgreSQL searches for objects within schemas, presents an opportunity for a user to modify the behavior of a query for other users. For example, a malicious user could insert a trojan-horse function that, when executed by a superuser, grants escalated privileges to the malicious user.
The easiest way to explain this is through an example.
There are two database users, alice and bob who both have access to the same database that contains the default public schema and a table in that schema with the following definition:
CREATE TABLE a (full_name varchar(255));
In the application that both alice and bob work on, there is a line of code that both users execute to return the names from table a as lowercase strings, i.e.
SELECT lower(full_name) FROM a;
The lower function is defined in the pg_catalog schema and accepts a single argument of type text. The PostgreSQL query parser knows that it can cast full_name from type varchar to text and thus use the lower function.
Knowing that the system has only the default public schema set up, user alice decides to create the following function in the public schema:
CREATE FUNCTION lower(varchar) RETURNS text AS $$
SELECT ‘ALICE WAS HERE: ‘ || $1;
$$ LANGUAGE SQL IMMUTABLE;
Though there is a function named lower in the pg_catalog schema, the above function is created successfully in the public schema as it is namespaced in a different location.
Additionally, the lower function in the public schema is a better fit for data in the full_name column, and thus if bob tries to run the following query:
SELECT lower(full_name) FROM a;
He will end up seeing a surprise message from alice indicating that she “was here” in addition to the expected return data. Thus, alice has successfully inserted a trojan function.
How Can I Protect My Databases?
There are several ways to protect your PostgreSQL installation from CVE-2018-1058.
Do not allow users to create new objects in the public schema
As a superuser, run the following command in all of your databases:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Running REVOKE CREATE ON SCHEMA public FROM PUBLIC; prevents all non-superusers from creating objects in the public schema. This setting will protect a PostgreSQL database from the problem described in CVE-2018-1058.
Once this command is run, certain operations could fail within your database. For example, a non-superuser will not be able to create tables or functions anymore with the public schema, which may affect how a user manages application schema migrations.
Note that the REVOKE command is more powerful than running DROP SCHEMA public; as pg_dump does not preserve the public schema removal.
After running this command, you should strongly consider auditing your public schema to see if any users have created functions that have names similar to ones in the pg_catalog. From the command-line tool (e.g. psql), you can see a list of functions available in the public schema by running:
To see a full list of functions defined In the pg_catalog schema, please run:
Set the default search_path for database users
A superuser can issue the following command to each user on your system to remove the public schema from the default search_path for a user:
ALTER ROLE username SET search_path = “$user”;
The above command preserves the default search_path that PostgreSQL provides, i.e. if there is a schema with the same name as SESSION_USER, then PostgreSQL will look for objects in the SESSION_USER schema first.
Note that any user with the CREATEROLE permission have the ability to alter the default search_path for other users. If that is the case, then please use the “Do not allow users to create new objects in the public schema” strategy described above to protect your system from CVE-2018-1058.
Set the default search_path in the PostgreSQL configuration file (postgresql.conf)
Similar to the previous step, an administrator can remove the public schema from the search_path setting in the postgresql.conf configuration file. A user that has the CREATEROLE or CREATEDB permissions or is the owner of the database can either alter the search_path for other users or create objects in the public schema for a database. If that is the case, then please use the “Do not allow users to create new objects in the public schema” strategy described above to protect your system from CVE-2018-1058.
Where to Find More Info
Please review the updated documentation to understand how to protect your PostgreSQL installation from CVE-2018-1058:
- Data Definition: Schemas
- dblink: Connection Options
- ECPG: Examples for Connections
- Packaging Related Objects into an Extension
- libpq: Database Connection Control Functions
- Large Object: Example
- Function Security
If you have further questions about CVE-2018-1058, please subscribe to and send an email to the firstname.lastname@example.org mailing list.
- PostgreSQL Wiki on CVE-2018-1058
- Red Hat Issue Tracking – https://bugzilla.redhat.com/show_bug.cgi?id=1547044
- Ubuntu Advisory and Patches – https://usn.ubuntu.com/3589-1/
- PostgreSQL Advisory – https://www.postgresql.org/about/news/1834/