Comparison Predicates - example - documentation seems contradictory?

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/14/functions.html
Description:

https://www.postgresql.org/docs/14/functions-comparison.html

See the heading: "Table 9.2. Comparison Predicates"

"2 BETWEEN 3 AND 1 → f" # ok this is false.

But let's read the example immediately below (the 'not between' example)

2 NOT BETWEEN 1 AND 3 → f   # what? This is also false. how can the negation
also be the same value? I would expect it to be true?

That doesn't seem right?

Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely

[Erki Eessaar]

Hello

I confirmed, that setting search_path is indeed sometimes needed in case of 
SECURITY DEFINER routines that have SQL-standard bodies. See an example at the 
end of the letter.

I suggest the following paragraph to the documentation:

Starting from PostgreSQL 14 SQL-standard bodies can be used in SQL-language 
functions. This form tracks dependencies between the function and objects used 
in the function body. However, there is still a possibility that such function 
calls other code that reacts to search path. Thus, as a best practice, SECURITY 
DEFINER functions with SQL-standard bodies should also override search_path.

Best regards
Erki Eessaar

*
/*Table in the schema public.*/
CREATE TABLE public.A(a INTEGER PRIMARY KEY);

/*Table in the schema pg_temp.*/
CREATE TABLE pg_temp.A(a INTEGER PRIMARY KEY);

/*SECURITY DEFINER function without SQL-standard function body.*/
CREATE OR REPLACE FUNCTION f1 () RETURNS VOID
AS $$
INSERT INTO A(a) VALUES (1);
$$ LANGUAGE sql SECURITY DEFINER;

/*SECURITY DEFINER function with SQL-standard function body that invokes
the function without SQL-standard function body. It does not explicitly set the 
search path.*/
CREATE OR REPLACE FUNCTION f2 () RETURNS VOID
LANGUAGE sql SECURITY DEFINER
BEGIN ATOMIC
SELECT f1();
END;

/*SECURITY DEFINER function with SQL-standard function body that invokes
the function without SQL-standard function body. It does explicitly set the 
search path.*/
CREATE OR REPLACE FUNCTION f2_secure () RETURNS VOID
LANGUAGE sql SECURITY DEFINER
SET search_path = public, pg_temp
BEGIN ATOMIC
SELECT f1();
END;

SELECT f2();

SELECT * FROM public.A;
/*Result 0 rows.*/

SELECT * FROM pg_temp.A;
/*Result 1 row.*/


DELETE FROM pg_temp.A;

SELECT f2_secure();

SELECT * FROM public.A;
/*Result 1 row. SET search_path in the invoking function had an effect to the 
invoked function*/

SELECT * FROM pg_temp.A;
/*Result 0 rows.*/





From: Bruce Momjian 
Sent: Wednesday, September 28, 2022 8:07 PM
To: Erki Eessaar 
Cc: pgsql-docs@lists.postgresql.org 
Subject: Re: SQL-standard function bodies and creating SECURITY DEFINER 
routines securely

On Tue, Aug 16, 2022 at 03:32:36PM -0400, Bruce Momjian wrote:
> On Sat, Dec 25, 2021 at 02:36:27PM +, Erki Eessaar wrote:
> >
> > Hello
> >
> > PostgreSQL 14 added the feature: "Allow SQL-language functions and 
> > procedures
> > to use SQL-standard function bodies."
> >
> > If I understand correctly, then in this case the system  will track
> > dependencies between tables and routines that use the tables. Thus, the
> > SECURITY DEFINER routines that use the new approach do not require the
> > following mitigation, i.e., SET search_path= is not needed. The following 
> > part
> > of documentation does not mention this.
> >
> > https://www.postgresql.org/docs/current/sql-createfunction.html#
> > SQL-CREATEFUNCTION-SECURITY
> >
> > [elephant] PostgreSQL: Documentation: 14: CREATE FUNCTION
> >Overloading. PostgreSQL allows function overloading; that is, the
> >same name can be used for several different functions so long as
> >they have distinct input argument types.Whether or not you use 
> > it,
> >this capability entails security precautions when calling 
> > functions
> >in databases where some users mistrust other users; see Section
> >10.3.. Two functions are considered the same if they have the 
> > same
> >...
> >www.postgresql.org
>
> I have written the attached patch to mention this issue about sql_body
> functions.

The doc patch was reverted based on feedback in this email thread:


https://www.postgresql.org/message-id/flat/AM9PR01MB8268BF5E74E119828251FD34FE409%40AM9PR01MB8268.eurprd01.prod.exchangelabs.com

If you think we should add new wording, please suggest it, thanks.

--
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: Comparison Predicates - example - documentation seems contradictory?

[Alvaro Herrera]

On 2022-Oct-06, PG Doc comments form wrote:

> See the heading: "Table 9.2. Comparison Predicates"
> 
> "2 BETWEEN 3 AND 1 → f" # ok this is false.
> 
> But let's read the example immediately below (the 'not between' example)
> 
> 2 NOT BETWEEN 1 AND 3 → f   # what? This is also false. how can the negation
> also be the same value? I would expect it to be true?

Actually, 2 *is* between 1 and 3.  So if you ask if it's NOT between,
that's false.

The other one is false because the boundaries are reversed, and BETWEEN
does not put them in the proper order before comparing.  BETWEEN
SYMMETRIC does that, as explained in the line below.

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely

[Bruce Momjian]

On Fri, Oct  7, 2022 at 08:05:36AM +, Erki Eessaar wrote:
> Hello
> 
> I confirmed, that setting search_path is indeed sometimes needed in case of
> SECURITY DEFINER routines that have SQL-standard bodies. See an example at the
> end of the letter.
> 
> I suggest the following paragraph to the documentation:
> 
> Starting from PostgreSQL 14 SQL-standard bodies can be used in SQL-language
> functions. This form tracks dependencies between the function and objects used
> in the function body. However, there is still a possibility that such function
> calls other code that reacts to search path. Thus, as a best practice, 
> SECURITY
> DEFINER functions with SQL-standard bodies should also override search_path.

I think this gets back to what Noah said about this section not needing
to explain all the details but rather give general guidance.  I am not
sure adding the reasons for _why_ you should use search path for
SQL-standard bodies is really adding anything.  Noah, is that accurate?

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely

[Bruce Momjian]

On Fri, Oct  7, 2022 at 01:50:14PM +, Erki Eessaar wrote:
> Hello
> 
> Another example where explicit search path is needed.
> 
> CREATE TABLE public.B(b INTEGER);
> CREATE TABLE pg_temp.B(b INTEGER);
> 
> CREATE OR REPLACE FUNCTION f3 () RETURNS VOID
> LANGUAGE sql SECURITY DEFINER
> BEGIN ATOMIC
> INSERT INTO B(b) VALUES (1);
> END;
> 
> SELECT f3();
> 
> SELECT * FROM public.B;
> /*Result has 0 rows.*/
> 
> SELECT * FROM pg_temp.B;
> /*Result has 1 row. Function f3 was associated with pg_temp.B because
> f3() did not have explicitly set search path.*/
> 
> I see now that there are multiple reasons why to still use search path. I 
> agree
> now that this extra paragaraph is perhaps too confusing and is not needed.

Thanks.  I learned a few things in this discussion and we can revisit it
if we feel there is need of improvement.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Minor inconsistencies

[Ekaterina Kiryanova]

Hello!

I've prepared a patch to add some missing periods and tags.
I also suggest the following change in pgbench.sgml:
-   Here is some example output generated with these options:
+   Here is some example output generated with this option:
since I believe that this is the example for the --aggregate-interval 
option.

Please take a look.

--
Ekaterina Kiryanova
Technical Writer
Postgres Professional
the Russian PostgreSQL Companydiff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 342b20ebeb..ad5245abef 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -261,7 +261,7 @@ postgres   27093  0.0  0.0  30096  2752 ?Ss   11:34   0:00 postgres: ser
unnecessary and can be avoided by setting
stats_fetch_consistency to none.
 
-   You can invoke pg_stat_clear_snapshot() to discard the
+   You can invoke pg_stat_clear_snapshot() to discard the
current transaction's statistics snapshot or cached values (if any).  The
next use of statistical information will (when in snapshot mode) cause a
new snapshot to be built or (when in cache mode) accessed statistics to be
@@ -2131,15 +2131,15 @@ postgres   27093  0.0  0.0  30096  2752 ?Ss   11:34   0:00 postgres: ser
  
  
   PgStatsDSA
-  Waiting for stats dynamic shared memory allocator access
+  Waiting for stats dynamic shared memory allocator access.
  
  
   PgStatsHash
-  Waiting for stats shared memory hash table access
+  Waiting for stats shared memory hash table access.
  
  
   PgStatsData
-  Waiting for shared memory stats data access
+  Waiting for shared memory stats data access.
  
  
   SerializableXactHash
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
index e07addaea4..eddf8ed52a 100644
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -368,9 +368,9 @@ DELETE
   An expression to assign to the column.  If used in a
   WHEN MATCHED clause, the expression can use values
   from the original row in the target table, and values from the
-  data_source row.
+  data_source row.
   If used in a WHEN NOT MATCHED clause, the
-  expression can use values from the data_source.
+  expression can use values from the data_source.
  
 

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 40e6a50a7f..1414de8b7b 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -229,7 +229,7 @@ pgbench  options  d
 data is generated in pgbench client and then
 sent to the server. This uses the client/server bandwidth
 extensively through a COPY.
-pgbench uses the FREEZE option with version 14 or later
+pgbench uses the FREEZE option with version 14 or later
 of PostgreSQL to speed up
 subsequent VACUUM, unless partitions are enabled.
 Using g causes logging to print one message
@@ -1098,7 +1098,7 @@ pgbench  options  d
 each SQL command on a single line ending with a semicolon.


-It is assumed that pgbench scripts do not contain incomplete blocks of SQL
+It is assumed that pgbench scripts do not contain incomplete blocks of SQL
 transactions. If at runtime the client reaches the end of the script without
 completing the last transaction block, it will be aborted.

@@ -2616,7 +2616,7 @@ END;
   
 
   
-   Here is some example output generated with these options:
+   Here is some example output generated with this option:
 
 pgbench --aggregate-interval=10 --time=20 --client=10 --log --rate=1000 --latency-limit=10 --failures-detailed --max-tries=10 test
 

Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely

[Erki Eessaar]

Hello

Another example where explicit search path is needed.

CREATE TABLE public.B(b INTEGER);
CREATE TABLE pg_temp.B(b INTEGER);

CREATE OR REPLACE FUNCTION f3 () RETURNS VOID
LANGUAGE sql SECURITY DEFINER
BEGIN ATOMIC
INSERT INTO B(b) VALUES (1);
END;

SELECT f3();

SELECT * FROM public.B;
/*Result has 0 rows.*/

SELECT * FROM pg_temp.B;
/*Result has 1 row. Function f3 was associated with pg_temp.B because
f3() did not have explicitly set search path.*/

I see now that there are multiple reasons why to still use search path. I agree 
now that this extra paragaraph is perhaps too confusing and is not needed.

Best regards
Erki Eessaar


From: Bruce Momjian 
Sent: Friday, October 7, 2022 4:35 PM
To: Erki Eessaar 
Cc: pgsql-docs@lists.postgresql.org ; Noah 
Misch ; Peter Eisentraut 
Subject: Re: SQL-standard function bodies and creating SECURITY DEFINER 
routines securely

On Fri, Oct  7, 2022 at 08:05:36AM +, Erki Eessaar wrote:
> Hello
>
> I confirmed, that setting search_path is indeed sometimes needed in case of
> SECURITY DEFINER routines that have SQL-standard bodies. See an example at the
> end of the letter.
>
> I suggest the following paragraph to the documentation:
>
> Starting from PostgreSQL 14 SQL-standard bodies can be used in SQL-language
> functions. This form tracks dependencies between the function and objects used
> in the function body. However, there is still a possibility that such function
> calls other code that reacts to search path. Thus, as a best practice, 
> SECURITY
> DEFINER functions with SQL-standard bodies should also override search_path.

I think this gets back to what Noah said about this section not needing
to explain all the details but rather give general guidance.  I am not
sure adding the reasons for _why_ you should use search path for
SQL-standard bodies is really adding anything.  Noah, is that accurate?

--
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson