There were some security patches released this month for Postgres, to put further restrictions on pathways that malicious users could use to leverage the search_path to insert malicious code.
What is the search path?
What is search_path? Postgres has a database/schema/table hierarchy. If you specify a table without the schema, then Postgres will look for the table within the schemas listed in the search path. See https://www.postgresql.org/docs/12/runtime-config-client.html
Here’s the default search_path:
postgres=# show search_path; search_path ----------------- "$user", public
Example of not using schema name in query:
postgres=# select * from firstname limit 5; first_name ------------ Aach Aachie Aachje Aacht Aachte (5 rows)
Exploit Pathways and Remediations
Functions are one way malicious users can exploit default search paths. After the first security hole was found, here were some remediations. (And these continue to inform best practices.) https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path
The patch to that CVE https://github.com/postgres/postgres/commit/582edc369cdbd348d68441fc50fa26a84afd0c1a forced an empty search path for some users/backend processes.
Here’s the code to empty the search path: https://github.com/postgres/postgres/blob/cec57b1a0fbcd3833086ba686897c5883e0a2afc/src/include/common/connect.h#L25
If you do that on your command line, the schema is required in queries:
postgres=# SELECT pg_catalog.set_config('search_path', '', false); postgres=# select * from firstname limit 5; ERROR: relation "firstname" does not exist LINE 1: select * from firstname limit 5; ^ postgres=# select * from public.firstname limit 5; first_name ------------ Aach Aachie Aachje Aacht Aachte (5 rows)
This “empty search path” has been extended this week to cover an additional security hole, in logical replication and extensions: https://github.com/postgres/postgres/commit/11da97024abbe76b8c81e3f2375b2a62e9717c67
Security patches available should be applied, especially for users of logical replication. The patch unfortunately doesn’t cover all pathways to malicious use of extensions, but should be done anyway. As a stopgap (especially, for example on managed cloud installations) until this can be updated, follow the tips in https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path
Interested in working with Valerie? Schedule a tech call.