From 5770172cb0c9df9e6ce27c507b449557e5b45124 Mon Sep 17 00:00:00 2001 From: Noah Misch Date: Mon, 26 Feb 2018 07:39:44 -0800 Subject: [PATCH] Document security implications of search_path and the public schema. The ability to create like-named objects in different schemas opens up the potential for users to change the behavior of other users' queries, maliciously or accidentally. When you connect to a PostgreSQL server, you should remove from your search_path any schema for which a user other than yourself or superusers holds the CREATE privilege. If you do not, other users holding CREATE privilege can redefine the behavior of your commands, causing them to perform arbitrary SQL statements under your identity. "SET search_path = ..." and "SELECT pg_catalog.set_config(...)" are not vulnerable to such hijacking, so one can use either as the first command of a session. As special exceptions, the following client applications behave as documented regardless of search_path settings and schema privileges: clusterdb createdb createlang createuser dropdb droplang dropuser ecpg (not programs it generates) initdb oid2name pg_archivecleanup pg_basebackup pg_config pg_controldata pg_ctl pg_dump pg_dumpall pg_isready pg_receivewal pg_recvlogical pg_resetwal pg_restore pg_rewind pg_standby pg_test_fsync pg_test_timing pg_upgrade pg_waldump reindexdb vacuumdb vacuumlo. Not included are core client programs that run user-specified SQL commands, namely psql and pgbench. PostgreSQL encourages non-core client applications to do likewise. Document this in the context of libpq connections, psql connections, dblink connections, ECPG connections, extension packaging, and schema usage patterns. The principal defense for applications is "SELECT pg_catalog.set_config('search_path', '', false)", and the principal defense for databases is "REVOKE CREATE ON SCHEMA public FROM PUBLIC". Either one is sufficient to prevent attack. After a REVOKE, consider auditing the public schema for objects named like pg_catalog objects. Authors of SECURITY DEFINER functions use some of the same defenses, and the CREATE FUNCTION reference page already covered them thoroughly. This is a good opportunity to audit SECURITY DEFINER functions for robust security practice. Back-patch to 9.3 (all supported versions). Reviewed by Michael Paquier and Jonathan S. Katz. Reported by Arseniy Sharoglazov. Security: CVE-2018-1058 --- doc/src/sgml/config.sgml | 10 +++- doc/src/sgml/contrib.sgml | 2 +- doc/src/sgml/dblink.sgml | 36 ++++++++---- doc/src/sgml/ddl.sgml | 94 +++++++++++++++++++++++--------- doc/src/sgml/ecpg.sgml | 29 ++++++++++ doc/src/sgml/extend.sgml | 56 ++++++++++++++----- doc/src/sgml/libpq.sgml | 82 ++++++++++++++++++++++------ doc/src/sgml/lobj.sgml | 11 ++++ doc/src/sgml/ref/pgbench.sgml | 11 ++++ doc/src/sgml/ref/psql-ref.sgml | 12 ++++ doc/src/sgml/user-manag.sgml | 15 +++-- src/test/examples/testlibpq.c | 21 +++++-- src/test/examples/testlibpq2.c | 29 +++++++--- src/test/examples/testlibpq2.sql | 4 +- src/test/examples/testlibpq3.c | 13 ++++- src/test/examples/testlibpq3.sql | 3 +- src/test/examples/testlibpq4.c | 19 ++++++- src/test/examples/testlo.c | 11 ++++ src/test/examples/testlo64.c | 11 ++++ 19 files changed, 369 insertions(+), 100 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 4c998fe51f..00fc364c0a 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6329,6 +6329,13 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; setting, either globally or per-user. + + For more information on schema handling, see + . In particular, the default + configuration is suitable only when the database has a single user or + a few mutually-trusting users. + + The current effective value of the search path can be examined via the SQL function @@ -6340,9 +6347,6 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; appearing in search_path were resolved. - - For more information on schema handling, see . - diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index 0622227bee..b626a345f3 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -75,7 +75,7 @@ CREATE EXTENSION module_name; choice. To do that, add SCHEMA schema_name to the CREATE EXTENSION command. By default, the objects will be placed in your current creation - target schema, typically public. + target schema, which in turn defaults to public. diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml index 4c07f886aa..87e14ea093 100644 --- a/doc/src/sgml/dblink.sgml +++ b/doc/src/sgml/dblink.sgml @@ -83,7 +83,7 @@ dblink_connect(text connname, text connstr) returns text libpq-style connection info string, for example hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres - password=mypasswd. + password=mypasswd options=-csearch_path=. For details see . Alternatively, the name of a foreign server. @@ -104,6 +104,17 @@ dblink_connect(text connname, text connstr) returns text Notes + + If untrusted users have access to a database that has not adopted a + secure schema usage pattern, + begin each session by removing publicly-writable schemas from + search_path. One could, for example, + add options=-csearch_path= to + connstr. This consideration is not specific + to dblink; it applies to every interface for + executing arbitrary SQL commands. + + Only superusers may use dblink_connect to create non-password-authenticated connections. If non-superusers need this @@ -121,13 +132,13 @@ dblink_connect(text connname, text connstr) returns text Examples -SELECT dblink_connect('dbname=postgres'); +SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) -SELECT dblink_connect('myconn', 'dbname=postgres'); +SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK @@ -416,7 +427,8 @@ dblink(text sql [, bool fail_on_error]) returns setof record SELECT * - FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc') + FROM dblink('dbname=mydb options=-csearch_path=', + 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; @@ -450,7 +462,8 @@ SELECT * CREATE VIEW myremote_pg_proc AS SELECT * - FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc') + FROM dblink('dbname=postgres options=-csearch_path=', + 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text); SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%'; @@ -461,7 +474,8 @@ SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%'; Examples -SELECT * FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc') +SELECT * FROM dblink('dbname=postgres options=-csearch_path=', + 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ @@ -479,7 +493,7 @@ SELECT * FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc') byteaout | byteaout (12 rows) -SELECT dblink_connect('dbname=postgres'); +SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK @@ -503,7 +517,7 @@ SELECT * FROM dblink('select proname, prosrc from pg_proc') byteaout | byteaout (12 rows) -SELECT dblink_connect('myconn', 'dbname=regression'); +SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path='); dblink_connect ---------------- OK @@ -778,7 +792,7 @@ dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) ret Examples -SELECT dblink_connect('dbname=postgres'); +SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK @@ -899,7 +913,7 @@ dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) Examples -SELECT dblink_connect('dbname=postgres'); +SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK @@ -1036,7 +1050,7 @@ dblink_close(text connname, text cursorname [, bool fail_on_error]) returns text Examples -SELECT dblink_connect('dbname=postgres'); +SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 15a9285136..2b879ead4b 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2172,6 +2172,20 @@ CREATE TABLE public.products ( ... ); in other schemas in the database. + + The ability to create like-named objects in different schemas complicates + writing a query that references precisely the same objects every time. It + also opens up the potential for users to change the behavior of other + users' queries, maliciously or accidentally. Due to the prevalence of + unqualified names in queries and their use + in PostgreSQL internals, adding a schema + to search_path effectively trusts all users having + CREATE privilege on that schema. When you run an + ordinary query, a malicious user able to create objects in a schema of + your search path can take control and execute arbitrary SQL functions as + though you executed them. + + schema current @@ -2288,8 +2302,9 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; the schema public. This allows all users that are able to connect to a given database to create objects in its - public schema. If you do - not want to allow that, you can revoke that privilege: + public schema. + Some usage patterns call for + revoking that privilege: REVOKE CREATE ON SCHEMA public FROM PUBLIC; @@ -2339,50 +2354,75 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; Usage Patterns - Schemas can be used to organize your data in many ways. There are - a few usage patterns that are recommended and are easily supported by - the default configuration: + Schemas can be used to organize your data in many ways. There are a few + usage patterns easily supported by the default configuration, only one of + which suffices when database users mistrust other database users: + - If you do not create any schemas then all users access the - public schema implicitly. This simulates the situation where - schemas are not available at all. This setup is mainly - recommended when there is only a single user or a few cooperating - users in a database. This setup also allows smooth transition - from the non-schema-aware world. + Constrain ordinary users to user-private schemas. To implement this, + issue REVOKE CREATE ON SCHEMA public FROM PUBLIC, + and create a schema for each user with the same name as that user. If + affected users had logged in before this, consider auditing the public + schema for objects named like objects in + schema pg_catalog. Recall that the default search + path starts with $user, which resolves to the user + name. Therefore, if each user has a separate schema, they access their + own schemas by default. - You can create a schema for each user with the same name as - that user. Recall that the default search path starts with - $user, which resolves to the user name. - Therefore, if each user has a separate schema, they access their - own schemas by default. + Remove the public schema from each user's default search path + using ALTER ROLE user SET + search_path = "$user". Everyone retains the ability to + create objects in the public schema, but only qualified names will + choose those objects. A user holding the CREATEROLE + privilege can undo this setting and issue arbitrary queries under the + identity of users relying on the setting. If you + grant CREATEROLE to users not warranting this + almost-superuser ability, use the first pattern instead. + + - If you use this setup then you might also want to revoke access - to the public schema (or drop it altogether), so users are - truly constrained to their own schemas. + Remove the public schema from search_path in + postgresql.conf. + The ensuing user experience matches the previous pattern. In addition + to that pattern's implications for CREATEROLE, this + trusts database owners the same way. If you assign + the CREATEROLE + privilege, CREATEDB privilege or individual database + ownership to users not warranting almost-superuser access, use the + first pattern instead. - To install shared applications (tables to be used by everyone, - additional functions provided by third parties, etc.), put them - into separate schemas. Remember to grant appropriate - privileges to allow the other users to access them. Users can - then refer to these additional objects by qualifying the names - with a schema name, or they can put the additional schemas into - their search path, as they choose. + Keep the default. All users access the public schema implicitly. This + simulates the situation where schemas are not available at all, giving + a smooth transition from the non-schema-aware world. However, any user + can issue arbitrary queries under the identity of any user not electing + to protect itself individually. This pattern is acceptable only when + the database has a single user or a few mutually-trusting users. + + + For any pattern, to install shared applications (tables to be used by + everyone, additional functions provided by third parties, etc.), put them + into separate schemas. Remember to grant appropriate privileges to allow + the other users to access them. Users can then refer to these additional + objects by qualifying the names with a schema name, or they can put the + additional schemas into their search path, as they choose. + @@ -2405,7 +2445,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should - not use (perhaps even remove) the public schema. + not use the public schema. diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml index 5a8d1f1b95..98b6840520 100644 --- a/doc/src/sgml/ecpg.sgml +++ b/doc/src/sgml/ecpg.sgml @@ -186,6 +186,18 @@ EXEC SQL CONNECT TO target AS chapter). + + If untrusted users have access to a database that has not adopted a + secure schema usage pattern, + begin each session by removing publicly-writable schemas + from search_path. For example, + add options=-csearch_path= + to options, or + issue EXEC SQL SELECT pg_catalog.set_config('search_path', '', + false); after connecting. This consideration is not specific to + ECPG; it applies to every interface for executing arbitrary SQL commands. + + Here are some examples of CONNECT statements: @@ -266,8 +278,11 @@ int main() { EXEC SQL CONNECT TO testdb1 AS con1 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL CONNECT TO testdb2 AS con2 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL CONNECT TO testdb3 AS con3 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; /* This query would be executed in the last opened database "testdb3". */ EXEC SQL SELECT current_database() INTO :dbname; @@ -1093,6 +1108,7 @@ EXEC SQL BEGIN DECLARE SECTION; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; in = PGTYPESinterval_new(); EXEC SQL SELECT '1 min'::interval INTO :in; @@ -1147,6 +1163,7 @@ EXEC SQL BEGIN DECLARE SECTION; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; num = PGTYPESnumeric_new(); dec = PGTYPESdecimal_new(); @@ -1221,6 +1238,7 @@ EXEC SQL END DECLARE SECTION; memset(dbid, 0, sizeof(int) * 8); EXEC SQL CONNECT TO testdb; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; /* Retrieve multiple rows into arrays at once. */ EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database; @@ -1887,6 +1905,7 @@ char *stmt = "SELECT u.usename as dbaname, d.datname " EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb AS con1 USER testuser; +EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL PREPARE stmt1 FROM :stmt; @@ -4317,6 +4336,7 @@ main(void) EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb AS con1 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL PREPARE stmt1 FROM :query; EXEC SQL DECLARE cur1 CURSOR FOR stmt1; @@ -4478,6 +4498,7 @@ main(void) EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO uptimedb AS con1 USER uptime; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL PREPARE stmt1 FROM :query; EXEC SQL DECLARE cur1 CURSOR FOR stmt1; @@ -5909,6 +5930,7 @@ main(void) memset(buf, 1, buflen); EXEC SQL CONNECT TO testdb AS con1; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; conn = ECPGget_PGconn("con1"); printf("conn = %p\n", conn); @@ -6038,6 +6060,7 @@ class TestCpp TestCpp::TestCpp() { EXEC SQL CONNECT TO testdb1; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; } void Test::test() @@ -6117,6 +6140,7 @@ void db_connect() { EXEC SQL CONNECT TO testdb1; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; } void @@ -6510,12 +6534,14 @@ EXEC SQL END DECLARE SECTION; ECPGdebug(1, stderr); EXEC SQL CONNECT TO :dbname USER :user; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL SELECT version() INTO :ver; EXEC SQL DISCONNECT; printf("version: %s\n", ver); EXEC SQL CONNECT TO :connection USER :user; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL SELECT version() INTO :ver; EXEC SQL DISCONNECT; @@ -7116,6 +7142,7 @@ EXEC SQL BEGIN DECLARE SECTION; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb AS con1 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL ALLOCATE DESCRIPTOR d; /* Declare, open a cursor, and assign a descriptor to the cursor */ @@ -7673,6 +7700,7 @@ EXEC SQL BEGIN DECLARE SECTION; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb AS con1; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL SELECT current_database(), 256 INTO :t:t_ind LIMIT 1; @@ -7829,6 +7857,7 @@ int main(void) { EXEC SQL CONNECT TO testdb AS con1; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL ALLOCATE DESCRIPTOR d; EXEC SQL DECLARE cur CURSOR FOR SELECT current_database(), 'hoge', 256; EXEC SQL OPEN cur; diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index 5f1bb70e97..6c043cdd02 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -430,6 +430,32 @@ dropping the whole extension. + + Defining Extension Objects + + + + Widely-distributed extensions should assume little about the database + they occupy. In particular, unless you issued SET search_path = + pg_temp, assume each unqualified name could resolve to an + object that a malicious user has defined. Beware of constructs that + depend on search_path implicitly: IN + and CASE expression WHEN + always select an operator using the search path. In their place, use + OPERATOR(schema.=) ANY + and CASE WHEN expression. + + + + Extension Files @@ -984,24 +1010,24 @@ SELECT * FROM pg_extension_update_paths('extension_name (LEFTARG = pg_catalog.text, + RIGHTARG = pg_catalog.text, PROCEDURE = pair); -CREATE OR REPLACE FUNCTION pair(text, text) -RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair;'; +-- "SET search_path" is easy to get right, but qualified names perform better. +CREATE OR REPLACE FUNCTION lower(pair) +RETURNS pair LANGUAGE SQL +AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;' +SET search_path = pg_temp; -CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = anyelement, PROCEDURE = pair); -CREATE OPERATOR ~> (LEFTARG = anyelement, RIGHTARG = text, PROCEDURE = pair); -CREATE OPERATOR ~> (LEFTARG = anyelement, RIGHTARG = anyelement, PROCEDURE = pair); -CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, PROCEDURE = pair); +CREATE OR REPLACE FUNCTION pair_concat(pair, pair) +RETURNS pair LANGUAGE SQL +AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k, + $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;'; ]]> @@ -1013,7 +1039,7 @@ CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, PROCEDURE = pair); # pair extension comment = 'A key/value pair data type' default_version = '1.0' -relocatable = true +relocatable = false diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index f327d4b5b5..2a8e1f2e07 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -65,6 +65,22 @@ the return value for a successful connection before queries are sent via the connection object. + + + If untrusted users have access to a database that has not adopted a + secure schema usage pattern, + begin each session by removing publicly-writable schemas from + search_path. One can set parameter key + word options to + value -csearch_path=. Alternately, one can + issue PQexec(conn, "SELECT + pg_catalog.set_config('search_path', '', false)") after + connecting. This consideration is not specific + to libpq; it applies to every interface for + executing arbitrary SQL commands. + + + On Unix, forking a process with open libpq connections can lead to @@ -6878,7 +6894,8 @@ main(void) { mydata *data; PGresult *res; - PGconn *conn = PQconnectdb("dbname = postgres"); + PGconn *conn = + PQconnectdb("dbname=postgres options=-csearch_path="); if (PQstatus(conn) != CONNECTION_OK) { @@ -8305,6 +8322,22 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + + /* + * Should PQclear PGresult whenever it is no longer needed to avoid memory + * leaks + */ + PQclear(res); + /* * Our test case here involves using a cursor, for which we must be inside * a transaction block. We could do the whole thing with a single @@ -8320,11 +8353,6 @@ main(int argc, char **argv) PQclear(res); exit_nicely(conn); } - - /* - * Should PQclear PGresult whenever it is no longer needed to avoid memory - * leaks - */ PQclear(res); /* @@ -8400,16 +8428,16 @@ main(int argc, char **argv) * populate a database with the following commands * (provided in src/test/examples/testlibpq2.sql): * + * CREATE SCHEMA TESTLIBPQ2; + * SET search_path = TESTLIBPQ2; * CREATE TABLE TBL1 (i int4); - * * CREATE TABLE TBL2 (i int4); - * * CREATE RULE r1 AS ON INSERT TO TBL1 DO * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2); * - * and do this four times: + * Start this program, then from psql do this four times: * - * INSERT INTO TBL1 VALUES (10); + * INSERT INTO TESTLIBPQ2.TBL1 VALUES (10); */ #ifdef WIN32 @@ -8464,6 +8492,22 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + + /* + * Should PQclear PGresult whenever it is no longer needed to avoid memory + * leaks + */ + PQclear(res); + /* * Issue LISTEN command to enable notifications from the rule's NOTIFY. */ @@ -8474,11 +8518,6 @@ main(int argc, char **argv) PQclear(res); exit_nicely(conn); } - - /* - * should PQclear PGresult whenever it is no longer needed to avoid memory - * leaks - */ PQclear(res); /* Quit after four notifies are received. */ @@ -8545,8 +8584,9 @@ main(int argc, char **argv) * Before running this, populate a database with the following commands * (provided in src/test/examples/testlibpq3.sql): * + * CREATE SCHEMA testlibpq3; + * SET search_path = testlibpq3; * CREATE TABLE test1 (i int4, t text, b bytea); - * * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004'); * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000'); * @@ -8678,6 +8718,16 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, "SET search_path = testlibpq3"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); + /* * The point of this program is to illustrate use of PQexecParams() with * out-of-line parameters, as well as binary transmission of data. diff --git a/doc/src/sgml/lobj.sgml b/doc/src/sgml/lobj.sgml index 6b5aaebbbc..771795ae66 100644 --- a/doc/src/sgml/lobj.sgml +++ b/doc/src/sgml/lobj.sgml @@ -933,6 +933,17 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); + res = PQexec(conn, "begin"); PQclear(res); printf("importing file \"%s\" ...\n", in_filename); diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index 3dd492cec1..59b573b501 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -1688,5 +1688,16 @@ statement latencies in milliseconds: database server. + + Security + + + If untrusted users have access to a database that has not adopted a + secure schema usage pattern, + do not run pgbench in that + database. pgbench uses unqualified names and + does not manipulate the search path. + + diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 8bd9b9387e..bfdf859731 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -735,6 +735,18 @@ testdb=> of the command are displayed on the screen. + + If untrusted users have access to a database that has not adopted a + secure schema usage pattern, + begin your session by removing publicly-writable schemas + from search_path. One can + add options=-csearch_path= to the connection string or + issue SELECT pg_catalog.set_config('search_path', '', + false) before other SQL commands. This consideration is not + specific to psql; it applies to every interface + for executing arbitrary SQL commands. + + Whenever a command is executed, psql also polls for asynchronous notification events generated by diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index ae15efed95..94fd4ebf58 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -571,14 +571,17 @@ GRANT pg_signal_backend TO admin_user; - Function and Trigger Security + Function Security - Functions and triggers allow users to insert code into the backend - server that other users might execute unintentionally. Hence, both - mechanisms permit users to Trojan horse - others with relative ease. The only real protection is tight - control over who can define functions. + Functions, triggers and row-level security policies allow users to insert + code into the backend server that other users might execute + unintentionally. Hence, these mechanisms permit users to Trojan + horse others with relative ease. The strongest protection is tight + control over who can define objects. Where that is infeasible, write + queries referring only to objects having trusted owners. Remove + from search_path the public schema and any other schemas + that permit untrusted users to create objects. diff --git a/src/test/examples/testlibpq.c b/src/test/examples/testlibpq.c index 4d9af82dd1..92a05e5309 100644 --- a/src/test/examples/testlibpq.c +++ b/src/test/examples/testlibpq.c @@ -48,6 +48,22 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + + /* + * Should PQclear PGresult whenever it is no longer needed to avoid memory + * leaks + */ + PQclear(res); + /* * Our test case here involves using a cursor, for which we must be inside * a transaction block. We could do the whole thing with a single @@ -63,11 +79,6 @@ main(int argc, char **argv) PQclear(res); exit_nicely(conn); } - - /* - * Should PQclear PGresult whenever it is no longer needed to avoid memory - * leaks - */ PQclear(res); /* diff --git a/src/test/examples/testlibpq2.c b/src/test/examples/testlibpq2.c index 07c6317a21..76787fe010 100644 --- a/src/test/examples/testlibpq2.c +++ b/src/test/examples/testlibpq2.c @@ -13,16 +13,16 @@ * populate a database with the following commands * (provided in src/test/examples/testlibpq2.sql): * + * CREATE SCHEMA TESTLIBPQ2; + * SET search_path = TESTLIBPQ2; * CREATE TABLE TBL1 (i int4); - * * CREATE TABLE TBL2 (i int4); - * * CREATE RULE r1 AS ON INSERT TO TBL1 DO * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2); * - * and do this four times: + * Start this program, then from psql do this four times: * - * INSERT INTO TBL1 VALUES (10); + * INSERT INTO TESTLIBPQ2.TBL1 VALUES (10); */ #ifdef WIN32 @@ -77,6 +77,22 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + + /* + * Should PQclear PGresult whenever it is no longer needed to avoid memory + * leaks + */ + PQclear(res); + /* * Issue LISTEN command to enable notifications from the rule's NOTIFY. */ @@ -87,11 +103,6 @@ main(int argc, char **argv) PQclear(res); exit_nicely(conn); } - - /* - * should PQclear PGresult whenever it is no longer needed to avoid memory - * leaks - */ PQclear(res); /* Quit after four notifies are received. */ diff --git a/src/test/examples/testlibpq2.sql b/src/test/examples/testlibpq2.sql index 1686c3ed0d..e8173e4293 100644 --- a/src/test/examples/testlibpq2.sql +++ b/src/test/examples/testlibpq2.sql @@ -1,6 +1,6 @@ +CREATE SCHEMA TESTLIBPQ2; +SET search_path = TESTLIBPQ2; CREATE TABLE TBL1 (i int4); - CREATE TABLE TBL2 (i int4); - CREATE RULE r1 AS ON INSERT TO TBL1 DO (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2); diff --git a/src/test/examples/testlibpq3.c b/src/test/examples/testlibpq3.c index e11e0567ca..00e62b43d2 100644 --- a/src/test/examples/testlibpq3.c +++ b/src/test/examples/testlibpq3.c @@ -8,8 +8,9 @@ * Before running this, populate a database with the following commands * (provided in src/test/examples/testlibpq3.sql): * + * CREATE SCHEMA testlibpq3; + * SET search_path = testlibpq3; * CREATE TABLE test1 (i int4, t text, b bytea); - * * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004'); * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000'); * @@ -141,6 +142,16 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, "SET search_path = testlibpq3"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); + /* * The point of this program is to illustrate use of PQexecParams() with * out-of-line parameters, as well as binary transmission of data. diff --git a/src/test/examples/testlibpq3.sql b/src/test/examples/testlibpq3.sql index 9d9e217e5d..2213306509 100644 --- a/src/test/examples/testlibpq3.sql +++ b/src/test/examples/testlibpq3.sql @@ -1,4 +1,5 @@ +CREATE SCHEMA testlibpq3; +SET search_path = testlibpq3; CREATE TABLE test1 (i int4, t text, b bytea); - INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004'); INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000'); diff --git a/src/test/examples/testlibpq4.c b/src/test/examples/testlibpq4.c index 0ec04313c0..a20f6249b4 100644 --- a/src/test/examples/testlibpq4.c +++ b/src/test/examples/testlibpq4.c @@ -22,8 +22,10 @@ exit_nicely(PGconn *conn1, PGconn *conn2) } static void -check_conn(PGconn *conn, const char *dbName) +check_prepare_conn(PGconn *conn, const char *dbName) { + PGresult *res; + /* check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { @@ -31,6 +33,17 @@ check_conn(PGconn *conn, const char *dbName) dbName, PQerrorMessage(conn)); exit(1); } + + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit(1); + } + PQclear(res); } int @@ -80,10 +93,10 @@ main(int argc, char **argv) /* make a connection to the database */ conn1 = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName1); - check_conn(conn1, dbName1); + check_prepare_conn(conn1, dbName1); conn2 = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName2); - check_conn(conn2, dbName2); + check_prepare_conn(conn2, dbName2); /* start a transaction block */ res1 = PQexec(conn1, "BEGIN"); diff --git a/src/test/examples/testlo.c b/src/test/examples/testlo.c index 7afe24714a..be5c72b9a6 100644 --- a/src/test/examples/testlo.c +++ b/src/test/examples/testlo.c @@ -232,6 +232,17 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); + res = PQexec(conn, "begin"); PQclear(res); printf("importing file \"%s\" ...\n", in_filename); diff --git a/src/test/examples/testlo64.c b/src/test/examples/testlo64.c index bb188cc3a1..39ba009c52 100644 --- a/src/test/examples/testlo64.c +++ b/src/test/examples/testlo64.c @@ -256,6 +256,17 @@ main(int argc, char **argv) exit_nicely(conn); } + /* Set always-secure search path, so malicous users can't take control. */ + res = PQexec(conn, + "SELECT pg_catalog.set_config('search_path', '', false)"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); + res = PQexec(conn, "begin"); PQclear(res); printf("importing file \"%s\" ...\n", in_filename); -- 2.40.0