A Guide to CVE-2018-1058: Protect Your Search Path

==Overview==
On March 1, 2018, The PostgreSQL Global Development Group released an update to all supported versions of the PostgreSQL database system, including 10.3, 9.6.8, 9.5.12, 9.4.17, 9.3.22.
The purpose of the release was to address CVE-2018-1058, which describes how a user can create like-named objects in different schemas that can change the behavior of other users' queries and cause unexpected or malicious behavior, also known as a "trojan-horse" attack. Most of this release centered around added documentation that described the issue and how to take steps to mitigate the impact on PostgreSQL databases.
The below guide is a detailed explanation of CVE-2018-1058 and how to protect against users exploiting PostgreSQL installations.
==Background: What is CVE-2018-1058?==
The PostgreSQL 7.3 release introduced "schemas," which allowed users to create objects (e.g. tables, functions, etc.) in separate namespaces. When a user creates a database, by default, PostgreSQL creates a schema called public where, by default, all new objects (e.g. tables, functions) are created. With this configuration, a query such as:
SELECT * FROM table_a;
is equivalent to:
SELECT * FROM public.table_a;
Without adjusting the configuration or access control settings, any user that can connect to a database can also create objects in the public schema for that database. A PostgreSQL administrator can grant and revoke permissions for a user to both use and/or create objects within a particular schema.
Schemas allow users to namespace objects, so objects of the same name can exist in different schemas in the same database. If there are objects with the same name in different schemas and the specific schema/object pair is not specified (i.e. schema.object), PostgreSQL decides which object to use based on the search_path setting. The search_path setting specifies the order the schemas are searched when looking for an object. The default value for search_path is $user,public where $user refers to the name of the user connected (which can be determined by executing SELECT SESSION_USER;).
For example, if the SESSION_USER is alice and there exists the following tables:
CREATE TABLE alice.a AS SELECT 1::int AS id;
CREATE TABLE public.a AS SELECT 'a'::text AS id;
Then if alice executes "SELECT * FROM a;" she will receive the following results:
 id 
----
  1
Regardless of contents of search_path, PostgreSQL searches the system catalog schema, pg_catalog, to see if there are any matching objects in it. The contents of pg_catalog include objects such as built-in functions and operators. If pg_catalog is not specified in the search_path then PostgreSQL searches for objects in the pg_catalog before looking in any other schemas.
==The Problem: CVE-2018-1058==
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.
==Impact: Are My PostgreSQL Installations Affected?==
The short answer: based on your setup, your installation is probably affected, but it may not be in imminent danger.
This issue exists in all supported versions of PostgreSQL as of March 1, 2018. However, there are already methods available to protect your databases in all supported PostgreSQL versions. These methods are described in further detail in the next section.
Your installation may not be at risk if you already do one of the following:
* If you write your queries with specific schema.object form, including objects that exist in the pg_catalog (e.g. calling SELECT pg_catalog.lower('ALICE');), then you are not immediately vulnerable to this issue.
* If you have a single-user accessing a database and you trust everyone who has access to that user, you are probably not immediately vulnerable to this issue.
* If you have already taken the risk mitigation steps in the next section, you are not vulnerable to this issue.
If you run your PostgreSQL installation with multi-user access to a database and rely on the search_path setting to look up objects, please read the next section to understand what are the best ways to protect your databases.
==Next Steps: 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:
\df public.*
To see a full list of functions defined In the pg_catalog schema, please run:
\df pg_catalog.*
===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.
==Getting Help: 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
* psql
* Function Security
If you have further questions about CVE-2018-1058, please subscribe to and send an email to the pgsql-general@postgresql.org mailing list.
If you would like to make additional technical contributions to the PostgreSQL project, please subscribe to and send an email to pgsql-hackers@postgresql.org mailing list.
If you believe you have found a new security vulnerability in PostgreSQL, please send an email to security@postgresql.org.
==Acknowledgements==
The PostgreSQL Global Development Group would like to thank Arseniy Sharoglazov for reporting this problem to the security team.
==Links==
* Download PostgreSQL
* Release Notes for PostgreSQL 10.3
* PostgreSQL Security
* PostgreSQL Versioning Policy