Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit70396db

Browse files
committed
Document security implications of search_path and the public schema.
The ability to create like-named objects in different schemas opens upthe 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 userother than yourself or superusers holds the CREATE privilege. If you donot, other users holding CREATE privilege can redefine the behavior ofyour commands, causing them to perform arbitrary SQL statements underyour identity. "SET search_path = ..." and "SELECTpg_catalog.set_config(...)" are not vulnerable to such hijacking, so onecan use either as the first command of a session. As specialexceptions, the following client applications behave as documentedregardless of search_path settings and schema privileges: clusterdbcreatedb createlang createuser dropdb droplang dropuser ecpg (notprograms it generates) initdb oid2name pg_archivecleanup pg_basebackuppg_config pg_controldata pg_ctl pg_dump pg_dumpall pg_isreadypg_receivewal pg_recvlogical pg_resetwal pg_restore pg_rewind pg_standbypg_test_fsync pg_test_timing pg_upgrade pg_waldump reindexdb vacuumdbvacuumlo. Not included are core client programs that run user-specifiedSQL commands, namely psql and pgbench. PostgreSQL encourages non-coreclient applications to do likewise.Document this in the context of libpq connections, psql connections,dblink connections, ECPG connections, extension packaging, and schemausage patterns. The principal defense for applications is "SELECTpg_catalog.set_config('search_path', '', false)", and the principaldefense for databases is "REVOKE CREATE ON SCHEMA public FROM PUBLIC".Either one is sufficient to prevent attack. After a REVOKE, considerauditing the public schema for objects named like pg_catalog objects.Authors of SECURITY DEFINER functions use some of the same defenses, andthe CREATE FUNCTION reference page already covered them thoroughly.This is a good opportunity to audit SECURITY DEFINER functions forrobust security practice.Back-patch to 9.3 (all supported versions).Reviewed by Michael Paquier and Jonathan S. Katz. Reported by ArseniySharoglazov.Security:CVE-2018-1058
1 parente170b8c commit70396db

19 files changed

+369
-101
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -5987,6 +5987,13 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
59875987
setting, either globally or per-user.
59885988
</para>
59895989

5990+
<para>
5991+
For more information on schema handling, see
5992+
<xref linkend="ddl-schemas">. In particular, the default
5993+
configuration is suitable only when the database has a single user or
5994+
a few mutually-trusting users.
5995+
</para>
5996+
59905997
<para>
59915998
The current effective value of the search path can be examined
59925999
via the <acronym>SQL</acronym> function
@@ -5997,10 +6004,6 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
59976004
<function>current_schemas</> shows how the items
59986005
appearing in <varname>search_path</varname> were resolved.
59996006
</para>
6000-
6001-
<para>
6002-
For more information on schema handling, see <xref linkend="ddl-schemas">.
6003-
</para>
60046007
</listitem>
60056008
</varlistentry>
60066009

‎doc/src/sgml/contrib.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -75,7 +75,7 @@ CREATE EXTENSION <replaceable>module_name</>;
7575
choice. To do that, add <literal>SCHEMA
7676
<replaceable>schema_name</></literal> to the <command>CREATE EXTENSION</>
7777
command. By default, the objects will be placed in your current creation
78-
target schema,typically <literal>public</>.
78+
target schema,which in turn defaults to <literal>public</>.
7979
</para>
8080

8181
<para>

‎doc/src/sgml/dblink.sgml

Lines changed: 25 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,7 @@ dblink_connect(text connname, text connstr) returns text
8383
<listitem>
8484
<para><application>libpq</>-style connection info string, for example
8585
<literal>hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres
86-
password=mypasswd</>.
86+
password=mypasswd options=-csearch_path=</>.
8787
For details see <xref linkend="libpq-connstring">.
8888
Alternatively, the name of a foreign server.
8989
</para>
@@ -104,6 +104,17 @@ dblink_connect(text connname, text connstr) returns text
104104
<refsect1>
105105
<title>Notes</title>
106106

107+
<para>
108+
If untrusted users have access to a database that has not adopted a
109+
<link linkend="ddl-schemas-patterns">secure schema usage pattern</link>,
110+
begin each session by removing publicly-writable schemas from
111+
<varname>search_path</varname>. One could, for example,
112+
add <literal>options=-csearch_path=</literal> to
113+
<parameter>connstr</parameter>. This consideration is not specific
114+
to <filename>dblink</filename>; it applies to every interface for
115+
executing arbitrary SQL commands.
116+
</para>
117+
107118
<para>
108119
Only superusers may use <function>dblink_connect</> to create
109120
non-password-authenticated connections. If non-superusers need this
@@ -121,13 +132,13 @@ dblink_connect(text connname, text connstr) returns text
121132
<title>Examples</title>
122133

123134
<screen>
124-
SELECT dblink_connect('dbname=postgres');
135+
SELECT dblink_connect('dbname=postgres options=-csearch_path=');
125136
dblink_connect
126137
----------------
127138
OK
128139
(1 row)
129140

130-
SELECT dblink_connect('myconn', 'dbname=postgres');
141+
SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
131142
dblink_connect
132143
----------------
133144
OK
@@ -416,7 +427,8 @@ dblink(text sql [, bool fail_on_error]) returns setof record
416427

417428
<programlisting>
418429
SELECT *
419-
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
430+
FROM dblink('dbname=mydb options=-csearch_path=',
431+
'select proname, prosrc from pg_proc')
420432
AS t1(proname name, prosrc text)
421433
WHERE proname LIKE 'bytea%';
422434
</programlisting>
@@ -450,7 +462,8 @@ SELECT *
450462
<programlisting>
451463
CREATE VIEW myremote_pg_proc AS
452464
SELECT *
453-
FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
465+
FROM dblink('dbname=postgres options=-csearch_path=',
466+
'select proname, prosrc from pg_proc')
454467
AS t1(proname name, prosrc text);
455468

456469
SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';
@@ -461,7 +474,8 @@ SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';
461474
<title>Examples</title>
462475

463476
<screen>
464-
SELECT * FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
477+
SELECT * FROM dblink('dbname=postgres options=-csearch_path=',
478+
'select proname, prosrc from pg_proc')
465479
AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
466480
proname | prosrc
467481
------------+------------
@@ -479,7 +493,7 @@ SELECT * FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
479493
byteaout | byteaout
480494
(12 rows)
481495

482-
SELECT dblink_connect('dbname=postgres');
496+
SELECT dblink_connect('dbname=postgres options=-csearch_path=');
483497
dblink_connect
484498
----------------
485499
OK
@@ -503,7 +517,7 @@ SELECT * FROM dblink('select proname, prosrc from pg_proc')
503517
byteaout | byteaout
504518
(12 rows)
505519

506-
SELECT dblink_connect('myconn', 'dbname=regression');
520+
SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path=');
507521
dblink_connect
508522
----------------
509523
OK
@@ -778,7 +792,7 @@ dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) ret
778792
<title>Examples</title>
779793

780794
<screen>
781-
SELECT dblink_connect('dbname=postgres');
795+
SELECT dblink_connect('dbname=postgres options=-csearch_path=');
782796
dblink_connect
783797
----------------
784798
OK
@@ -899,7 +913,7 @@ dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error])
899913
<title>Examples</title>
900914

901915
<screen>
902-
SELECT dblink_connect('dbname=postgres');
916+
SELECT dblink_connect('dbname=postgres options=-csearch_path=');
903917
dblink_connect
904918
----------------
905919
OK
@@ -1036,7 +1050,7 @@ dblink_close(text connname, text cursorname [, bool fail_on_error]) returns text
10361050
<title>Examples</title>
10371051

10381052
<screen>
1039-
SELECT dblink_connect('dbname=postgres');
1053+
SELECT dblink_connect('dbname=postgres options=-csearch_path=');
10401054
dblink_connect
10411055
----------------
10421056
OK

‎doc/src/sgml/ddl.sgml

Lines changed: 67 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -2119,6 +2119,20 @@ CREATE TABLE public.products ( ... );
21192119
in other schemas in the database.
21202120
</para>
21212121

2122+
<para>
2123+
The ability to create like-named objects in different schemas complicates
2124+
writing a query that references precisely the same objects every time. It
2125+
also opens up the potential for users to change the behavior of other
2126+
users' queries, maliciously or accidentally. Due to the prevalence of
2127+
unqualified names in queries and their use
2128+
in <productname>PostgreSQL</productname> internals, adding a schema
2129+
to <varname>search_path</varname> effectively trusts all users having
2130+
<literal>CREATE</literal> privilege on that schema. When you run an
2131+
ordinary query, a malicious user able to create objects in a schema of
2132+
your search path can take control and execute arbitrary SQL functions as
2133+
though you executed them.
2134+
</para>
2135+
21222136
<indexterm>
21232137
<primary>schema</primary>
21242138
<secondary>current</secondary>
@@ -2235,8 +2249,9 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
22352249
the schema
22362250
<literal>public</literal>. This allows all users that are able to
22372251
connect to a given database to create objects in its
2238-
<literal>public</literal> schema. If you do
2239-
not want to allow that, you can revoke that privilege:
2252+
<literal>public</literal> schema.
2253+
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for
2254+
revoking that privilege:
22402255
<programlisting>
22412256
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
22422257
</programlisting>
@@ -2286,50 +2301,75 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
22862301
<title>Usage Patterns</title>
22872302

22882303
<para>
2289-
Schemas can be used to organize your data in many ways. There are
2290-
a fewusage patternsthat are recommended and are easily supported by
2291-
the default configuration:
2304+
Schemas can be used to organize your data in many ways. There are a few
2305+
usage patternseasily supported by the default configuration, only one of
2306+
which suffices when database users mistrust other database users:
22922307
<itemizedlist>
22932308
<listitem>
2309+
<!-- "DROP SCHEMA public" is inferior to this REVOKE, because pg_dump
2310+
doesn't preserve that DROP. -->
22942311
<para>
2295-
If you do not create any schemas then all users access the
2296-
public schema implicitly. This simulates the situation where
2297-
schemas are not available at all. This setup is mainly
2298-
recommended when there is only a single user or a few cooperating
2299-
users in a database. This setup also allows smooth transition
2300-
from the non-schema-aware world.
2312+
Constrain ordinary users to user-private schemas. To implement this,
2313+
issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>,
2314+
and create a schema for each user with the same name as that user. If
2315+
affected users had logged in before this, consider auditing the public
2316+
schema for objects named like objects in
2317+
schema <literal>pg_catalog</literal>. Recall that the default search
2318+
path starts with <literal>$user</literal>, which resolves to the user
2319+
name. Therefore, if each user has a separate schema, they access their
2320+
own schemas by default.
23012321
</para>
23022322
</listitem>
23032323

23042324
<listitem>
23052325
<para>
2306-
You can create a schema for each user with the same name as
2307-
that user. Recall that the default search path starts with
2308-
<literal>$user</literal>, which resolves to the user name.
2309-
Therefore, if each user has a separate schema, they access their
2310-
own schemas by default.
2326+
Remove the public schema from each user's default search path
2327+
using <literal>ALTER ROLE <replaceable>user</replaceable> SET
2328+
search_path = "$user"</literal>. Everyone retains the ability to
2329+
create objects in the public schema, but only qualified names will
2330+
choose those objects. A user holding the <literal>CREATEROLE</literal>
2331+
privilege can undo this setting and issue arbitrary queries under the
2332+
identity of users relying on the setting. If you
2333+
grant <literal>CREATEROLE</literal> to users not warranting this
2334+
almost-superuser ability, use the first pattern instead.
23112335
</para>
2336+
</listitem>
23122337

2338+
<listitem>
23132339
<para>
2314-
If you use this setup then you might also want to revoke access
2315-
to the public schema (or drop it altogether), so users are
2316-
truly constrained to their own schemas.
2340+
Remove the public schema from <varname>search_path</varname> in
2341+
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>.
2342+
The ensuing user experience matches the previous pattern. In addition
2343+
to that pattern's implications for <literal>CREATEROLE</literal>, this
2344+
trusts database owners the same way. If you assign
2345+
the <literal>CREATEROLE</literal>
2346+
privilege, <literal>CREATEDB</literal> privilege or individual database
2347+
ownership to users not warranting almost-superuser access, use the
2348+
first pattern instead.
23172349
</para>
23182350
</listitem>
23192351

23202352
<listitem>
23212353
<para>
2322-
To install shared applications (tables to be used by everyone,
2323-
additional functions provided by third parties, etc.), put them
2324-
into separate schemas. Remember to grant appropriate
2325-
privileges to allow the other users to access them. Users can
2326-
then refer to these additional objects by qualifying the names
2327-
with a schema name, or they can put the additional schemas into
2328-
their search path, as they choose.
2354+
Keep the default. All users access the public schema implicitly. This
2355+
simulates the situation where schemas are not available at all, giving
2356+
a smooth transition from the non-schema-aware world. However, any user
2357+
can issue arbitrary queries under the identity of any user not electing
2358+
to protect itself individually. This pattern is acceptable only when
2359+
the database has a single user or a few mutually-trusting users.
23292360
</para>
23302361
</listitem>
23312362
</itemizedlist>
23322363
</para>
2364+
2365+
<para>
2366+
For any pattern, to install shared applications (tables to be used by
2367+
everyone, additional functions provided by third parties, etc.), put them
2368+
into separate schemas. Remember to grant appropriate privileges to allow
2369+
the other users to access them. Users can then refer to these additional
2370+
objects by qualifying the names with a schema name, or they can put the
2371+
additional schemas into their search path, as they choose.
2372+
</para>
23332373
</sect2>
23342374

23352375
<sect2 id="ddl-schemas-portability">
@@ -2352,7 +2392,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
23522392
<para>
23532393
Also, there is no concept of a <literal>public</> schema in the
23542394
SQL standard. For maximum conformance to the standard, you should
2355-
not use(perhaps even remove)the <literal>public</> schema.
2395+
not use the <literal>public</> schema.
23562396
</para>
23572397

23582398
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp