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

Commite09144e

Browse files
committed
Document security implications of qualified names.
Commit5770172 documented secure schemausage, and that advice suffices for using unqualified names securely.Document, in typeconv-func primarily, the additional issues that arisewith qualified names. Back-patch to 9.3 (all supported versions).Reviewed by Jonathan S. Katz.Discussion:https://postgr.es/m/20180721012446.GA1840594@rfd.leadboat.com
1 parent6bf0bc8 commite09144e

File tree

6 files changed

+147
-33
lines changed

6 files changed

+147
-33
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2380,9 +2380,12 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
23802380
using <literal>ALTER ROLE <replaceable>user</replaceable> SET
23812381
search_path = "$user"</literal>. Everyone retains the ability to
23822382
create objects in the public schema, but only qualified names will
2383-
choose those objects. A user holding the <literal>CREATEROLE</literal>
2384-
privilege can undo this setting and issue arbitrary queries under the
2385-
identity of users relying on the setting. If you
2383+
choose those objects. While qualified table references are fine, calls
2384+
to functions in the public schema <link linkend="typeconv-func">will be
2385+
unsafe or unreliable</link>. Also, a user holding
2386+
the <literal>CREATEROLE</literal> privilege can undo this setting and
2387+
issue arbitrary queries under the identity of users relying on the
2388+
setting. If you create functions or extensions in the public schema or
23862389
grant <literal>CREATEROLE</literal> to users not warranting this
23872390
almost-superuser ability, use the first pattern instead.
23882391
</para>
@@ -2393,8 +2396,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
23932396
Remove the public schema from <varname>search_path</varname> in
23942397
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>.
23952398
The ensuing user experience matches the previous pattern. In addition
2396-
to that pattern's implications for <literal>CREATEROLE</literal>, this
2397-
trusts database owners the same way. If you assign
2399+
to that pattern's implications for functions
2400+
and <literal>CREATEROLE</literal>, this trusts database owners
2401+
like <literal>CREATEROLE</literal>. If you create functions or
2402+
extensions in the public schema or assign
23982403
the <literal>CREATEROLE</literal>
23992404
privilege, <literal>CREATEDB</literal> privilege or individual database
24002405
ownership to users not warranting almost-superuser access, use the

‎doc/src/sgml/ref/create_function.sgml

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -545,8 +545,11 @@ CREATE [ OR REPLACE ] FUNCTION
545545
as for the <xref linkend="sql-load"/> command. The string
546546
<replaceable class="parameter">link_symbol</replaceable> is the
547547
function's link symbol, that is, the name of the function in the C
548-
language source code. If the link symbol is omitted, it is assumed
549-
to be the same as the name of the SQL function being defined.
548+
language source code. If the link symbol is omitted, it is assumed to
549+
be the same as the name of the SQL function being defined. The C names
550+
of all functions must be different, so you must give overloaded C
551+
functions different C names (for example, use the argument types as
552+
part of the C names).
550553
</para>
551554

552555
<para>
@@ -575,10 +578,9 @@ CREATE [ OR REPLACE ] FUNCTION
575578
<productname>PostgreSQL</productname> allows function
576579
<firstterm>overloading</firstterm>; that is, the same name can be
577580
used for several different functions so long as they have distinct
578-
input argument types. However, the C names of all functions must be
579-
different, so you must give overloaded C functions different C
580-
names (for example, use the argument types as part of the C
581-
names).
581+
input argument types. Whether or not you use it, this capability entails
582+
security precautions when calling functions in databases where some users
583+
mistrust other users; see <xref linkend="typeconv-func"/>.
582584
</para>
583585

584586
<para>

‎doc/src/sgml/syntax.sgml

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1518,6 +1518,12 @@ sqrt(2)
15181518
Other functions can be added by the user.
15191519
</para>
15201520

1521+
<para>
1522+
When issuing queries in a database where some users mistrust other users,
1523+
observe security precautions from <xref linkend="typeconv-func"/> when
1524+
writing function calls.
1525+
</para>
1526+
15211527
<para>
15221528
The arguments can optionally have names attached.
15231529
See <xref linkend="sql-syntax-calling-funcs"/> for details.
@@ -2590,6 +2596,8 @@ SELECT CASE WHEN min(employees) > 0
25902596
its argument values in the same order as they are defined in the function
25912597
declaration. In named notation, the arguments are matched to the
25922598
function parameters by name and can be written in any order.
2599+
For each notation, also consider the effect of function argument types,
2600+
documented in <xref linkend="typeconv-func"/>.
25932601
</para>
25942602

25952603
<para>

‎doc/src/sgml/typeconv.sgml

Lines changed: 99 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -246,7 +246,19 @@ search path position.
246246
<para>
247247
Check for an operator accepting exactly the input argument types.
248248
If one exists (there can be only one exact match in the set of
249-
operators considered), use it.
249+
operators considered), use it. Lack of an exact match creates a security
250+
hazard when calling, via qualified name
251+
<footnote id="op-qualified-security">
252+
<!-- If you edit this, consider editing func-qualified-security. -->
253+
<para>
254+
The hazard does not arise with a non-schema-qualified name, because a
255+
search path containing schemas that permit untrusted users to create
256+
objects is not a <link linkend="ddl-schemas-patterns">secure schema usage
257+
pattern</link>.
258+
</para>
259+
</footnote>
260+
(not typical), any operator found in a schema that permits untrusted users to
261+
create objects. In such situations, cast arguments to force an exact match.
250262
</para>
251263

252264
<substeps>
@@ -589,6 +601,26 @@ function. In that case the function appearing earlier in the search path is
589601
used, or if the two functions are in the same schema, the non-variadic one is
590602
preferred.
591603
</para>
604+
<para>
605+
This creates a security hazard when calling, via qualified name
606+
<footnote id="func-qualified-security">
607+
<!-- If you edit this, consider editing op-qualified-security. -->
608+
<para>
609+
The hazard does not arise with a non-schema-qualified name, because a
610+
search path containing schemas that permit untrusted users to create
611+
objects is not a <link linkend="ddl-schemas-patterns">secure schema usage
612+
pattern</link>.
613+
</para>
614+
</footnote>,
615+
a variadic function found in a schema that permits untrusted users to create
616+
objects. A malicious user can take control and execute arbitrary SQL
617+
functions as though you executed them. Substitute a call bearing
618+
the <literal>VARIADIC</literal> keyword, which bypasses this hazard. Calls
619+
populating <literal>VARIADIC "any"</literal> parameters often have no
620+
equivalent formulation containing the <literal>VARIADIC</literal> keyword. To
621+
issue those calls safely, the function's schema must permit only trusted users
622+
to create objects.
623+
</para>
592624
</step>
593625
<step performance="optional">
594626
<para>
@@ -602,6 +634,15 @@ will not be able to determine which to prefer, and so an <quote>ambiguous
602634
function call</quote> error will result if no better match to the call can be
603635
found.
604636
</para>
637+
<para>
638+
This creates an availability hazard when calling, via qualified
639+
name<footnoteref linkend="func-qualified-security"/>, any function found in a
640+
schema that permits untrusted users to create objects. A malicious user can
641+
create a function with the name of an existing function, replicating that
642+
function's parameters and appending novel parameters having default values.
643+
This precludes new calls to the original function. To forestall this hazard,
644+
place functions in schemas that permit only trusted users to create objects.
645+
</para>
605646
</step>
606647
</substeps>
607648
</step>
@@ -610,9 +651,12 @@ found.
610651
<para>
611652
Check for a function accepting exactly the input argument types.
612653
If one exists (there can be only one exact match in the set of
613-
functions considered), use it.
614-
(Cases involving <type>unknown</type> will never find a match at
615-
this step.)
654+
functions considered), use it. Lack of an exact match creates a security
655+
hazard when calling, via qualified
656+
name<footnoteref linkend="func-qualified-security"/>, a function found in a
657+
schema that permits untrusted users to create objects. In such situations,
658+
cast arguments to force an exact match. (Cases involving <type>unknown</type>
659+
will never find a match at this step.)
616660
</para>
617661
</step>
618662

@@ -750,6 +794,57 @@ SELECT round(4.0, 4);
750794
</para>
751795
</example>
752796

797+
<example>
798+
<title>Variadic Function Resolution</title>
799+
800+
<para>
801+
<screen>
802+
CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
803+
LANGUAGE sql AS 'SELECT 1';
804+
CREATE FUNCTION
805+
</screen>
806+
807+
This function accepts, but does not require, the VARIADIC keyword. It
808+
tolerates both integer and numeric arguments:
809+
810+
<screen>
811+
SELECT public.variadic_example(0),
812+
public.variadic_example(0.0),
813+
public.variadic_example(VARIADIC array[0.0]);
814+
variadic_example | variadic_example | variadic_example
815+
------------------+------------------+------------------
816+
1 | 1 | 1
817+
(1 row)
818+
</screen>
819+
820+
However, the first and second calls will prefer more-specific functions, if
821+
available:
822+
823+
<screen>
824+
CREATE FUNCTION public.variadic_example(numeric) RETURNS int
825+
LANGUAGE sql AS 'SELECT 2';
826+
CREATE FUNCTION
827+
828+
CREATE FUNCTION public.variadic_example(int) RETURNS int
829+
LANGUAGE sql AS 'SELECT 3';
830+
CREATE FUNCTION
831+
832+
SELECT public.variadic_example(0),
833+
public.variadic_example(0.0),
834+
public.variadic_example(VARIADIC array[0.0]);
835+
variadic_example | variadic_example | variadic_example
836+
------------------+------------------+------------------
837+
3 | 2 | 1
838+
(1 row)
839+
</screen>
840+
841+
Given the default configuration and only the first function existing, the
842+
first and second calls are insecure. Any user could intercept them by
843+
creating the second or third function. By matching the argument type exactly
844+
and using the <literal>VARIADIC</literal> keyword, the third call is secure.
845+
</para>
846+
</example>
847+
753848
<example>
754849
<title>Substring Function Type Resolution</title>
755850

‎doc/src/sgml/xfunc.sgml

Lines changed: 17 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -764,8 +764,11 @@ SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- doesn't work
764764
<para>
765765
Sometimes it is useful to be able to pass an already-constructed array
766766
to a variadic function; this is particularly handy when one variadic
767-
function wants to pass on its array parameter to another one. You can
768-
do that by specifying <literal>VARIADIC</literal> in the call:
767+
function wants to pass on its array parameter to another one. Also,
768+
this is the only secure way to call a variadic function found in a schema
769+
that permits untrusted users to create objects; see
770+
<xref linkend="typeconv-func"/>. You can do this by
771+
specifying <literal>VARIADIC</literal> in the call:
769772

770773
<screen>
771774
SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
@@ -827,7 +830,10 @@ SELECT mleast(arr =&gt; ARRAY[10, -1, 5, 4.4]);
827830
parameters after a parameter with a default value have to have
828831
default values as well. (Although the use of named argument notation
829832
could allow this restriction to be relaxed, it's still enforced so that
830-
positional argument notation works sensibly.)
833+
positional argument notation works sensibly.) Whether or not you use it,
834+
this capability creates a need for precautions when calling functions in
835+
databases where some users mistrust other users; see
836+
<xref linkend="typeconv-func"/>.
831837
</para>
832838

833839
<para>
@@ -1399,11 +1405,14 @@ $$ LANGUAGE SQL;
13991405
<para>
14001406
More than one function can be defined with the same SQL name, so long
14011407
as the arguments they take are different. In other words,
1402-
function names can be <firstterm>overloaded</firstterm>. When a
1403-
query is executed, the server will determine which function to
1404-
call from the data types and the number of the provided arguments.
1405-
Overloading can also be used to simulate functions with a variable
1406-
number of arguments, up to a finite maximum number.
1408+
function names can be <firstterm>overloaded</firstterm>. Whether or not
1409+
you use it, this capability entails security precautions when calling
1410+
functions in databases where some users mistrust other users; see
1411+
<xref linkend="typeconv-func"/>. When a query is executed, the server
1412+
will determine which function to call from the data types and the number
1413+
of the provided arguments. Overloading can also be used to simulate
1414+
functions with a variable number of arguments, up to a finite maximum
1415+
number.
14071416
</para>
14081417

14091418
<para>

‎src/backend/utils/adt/ruleutils.c

Lines changed: 5 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -10761,16 +10761,11 @@ generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes,
1076110761
* Determine whether VARIADIC should be printed. We must do this first
1076210762
* since it affects the lookup rules in func_get_detail().
1076310763
*
10764-
* Currently, we always print VARIADIC if the function has a merged
10765-
* variadic-array argument. Note that this is always the case for
10766-
* functions taking a VARIADIC argument type other than VARIADIC ANY.
10767-
*
10768-
* In principle, if VARIADIC wasn't originally specified and the array
10769-
* actual argument is deconstructable, we could print the array elements
10770-
* separately and not print VARIADIC, thus more nearly reproducing the
10771-
* original input. For the moment that seems like too much complication
10772-
* for the benefit, and anyway we do not know whether VARIADIC was
10773-
* originally specified if it's a non-ANY type.
10764+
* We always print VARIADIC if the function has a merged variadic-array
10765+
* argument. Note that this is always the case for functions taking a
10766+
* VARIADIC argument type other than VARIADIC ANY. If we omitted VARIADIC
10767+
* and printed the array elements as separate arguments, the call could
10768+
* match a newer non-VARIADIC function.
1077410769
*/
1077510770
if (use_variadic_p)
1077610771
{

0 commit comments

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