Missing documentation

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/libpq-connect.html
Description:

On this page:
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

There is a list of keywords that can be specified as part of the connection
string.
Specifying `jit=off`  or `jit=on` is functional in practice, but `jit` is
not included in the documentation.

Version 14/15 documentation Section "Alter Default Privileges"

[David Burns]

To Whom It May Concern;

Some additional clarity in the versions 14/15 documentation would be helpful 
specifically surrounding the "target_role" clause for the ALTER DEFAULT 
PRIVILEGES command.  To the uninitiated, the current description seems vague.  
Maybe something like the following would help:

target_role
   The name of an existing role of which the current role is a 
member.  Default privileges are only applied to objects created by the targeted 
role/user (FOR ROLE target_role).  If the FOR ROLE clause is omitted, the 
targeted user defaults to the current user executing the ALTER DEFAULT 
PRIVILEGES command.  The result can be seen using the following query:

select   table_catalog as database
 ,table_schema
 ,table_name
 ,privilege_type
 ,grantee
 ,'revoke '||privilege_type||' on '||table_schema||'.'||table_name||' 
from '||grantee||';' as revoke_stmt
from information_schema.table_privileges
wheretable_schema = 'my_schema'
and  table_name = 'my_table'
order by 1,2,3,5,4;


Also, additional explanation about the differences between global defaults 
versus schema-level defaults, and how to identify them, would be helpful.

Additional explanation about exactly what is happening would help to put this 
command into perspective.  On successful execution with the correct parameter 
values, and using both the FOR ROLE and IN SCHEMA clauses, I also received 
privilege grants directed to the user executing the ALTER DEFAULT PRIVILEGES 
command.  This was in addition to the expected privileges specified in the 
command.  I'm not sure why this occurred or how to eliminate it, in the 
interest of establishing "least privilege" permissions.

Thank you.


David E. Burns, Jr. | Domain Architect | FedEx Services IT | Dock and Edge 
Services | Mobile 412.304.8303
1000 FedEx Drive, Moon Township, PA 15108 | 
david.bu...@fedex.com

Re: Version 14/15 documentation Section "Alter Default Privileges"

[Laurenz Albe]

On Wed, 2022-11-02 at 19:29 +, David Burns wrote:
> To Whom It May Concern;

It concerns me, because I often see questions from people who misunderstand 
this.

> Some additional clarity in the versions 14/15 documentation would be helpful 
> specifically
> surrounding the "target_role" clause for the ALTER DEFAULT PRIVILEGES command.
> To the uninitiated, the current description seems vague.  Maybe something 
> like the following would help:
>  
> target_role
>    The name of an existing role of which the current role is a member.
>Default privileges are only applied to objects created by the targeted 
> role/user (FOR ROLE target_role).
>    If the FOR ROLE clause is omitted, the targeted user defaults to the 
> current user executing the
>ALTER DEFAULT PRIVILEGES command.

+1

I like the wording, except that I would replace "targeted role/user (FOR ROLE 
target_role)" with
"target role" for added clarity.

>   The result can be seen using the following query:
>  
> select   table_catalog as database
>  ,table_schema
>  ,table_name
>  ,privilege_type
>  ,grantee
>  ,'revoke '||privilege_type||' on '||table_schema||'.'||table_name||' 
> from '||grantee||';' as revoke_stmt
> from information_schema.table_privileges
> where    table_schema = 'my_schema'
> and  table_name = 'my_table'
> order by 1,2,3,5,4;

I am not so happy with that query; I thinks that is going too far.
Perhaps we can say that the "psql" command "\ddp" can be used to view default 
privileges.

> Also, additional explanation about the differences between global defaults 
> versus
> schema-level defaults, and how to identify them, would be helpful.

The examples already cover that in some detail.

> Additional explanation about exactly what is happening would help to put this 
> command into perspective.
> On successful execution with the correct parameter values, and using both the 
> FOR ROLE and
> IN SCHEMA clauses, I also received privilege grants directed to the user 
> executing the
> ALTER DEFAULT PRIVILEGES command.  This was in addition to the expected 
> privileges specified in the command.
> I'm not sure why this occurred or how to eliminate it, in the interest of 
> establishing "least privilege" permissions.

ALTER DEFAULT PRIVILEGES does nothing like that...

Yours,
Laurenz Albe

Re: list of flags that pg_settings_get_flags reports

[Justin Pryzby]

On Thu, Jul 21, 2022 at 12:19:59PM +0900, Fujii Masao wrote:
> On 2022/07/20 16:26, Michael Paquier wrote:
> > On Wed, Jul 20, 2022 at 01:51:36PM +0900, Fujii Masao wrote:
> > > Attached is the updated version of the patch. It separates the list
> > > for GUC flags from the table entry for pg_settings_get_flags() and
> > > adds the table for it at the bottom of the existing function table.
> > 
> > Thanks a lot for sending a patch.  This looks fine to me.
> 
> Pushed. Thanks!

f2d0c7f18 should be backpatched to v15.

The existing docs are not good.
https://www.postgresql.org/docs/current/functions-info.html#id-1.5.8.32.19.2.2.25.1.1.1

-- 
Justin

Re: Missing documentation

[Tom Lane]

PG Doc comments form  writes:
> On this page:
> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

> There is a list of keywords that can be specified as part of the connection
> string.
> Specifying `jit=off`  or `jit=on` is functional in practice, but `jit` is
> not included in the documentation.

Really?  Doesn't work for me:

$ psql "host=localhost port=5432 jit=off"
psql: error: invalid connection option "jit"

regards, tom lane

Re: Missing documentation

[Stefan Badenhorst]

This is working:
postgresql://USERNAME:PASSWORD@server
:5432/the_database?sslmode=disable&jit=off
Maybe I was looking at the wrong documentation page?

On Thu, 3 Nov 2022 at 14:58, Tom Lane  wrote:

> PG Doc comments form  writes:
> > On this page:
> >
> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>
> > There is a list of keywords that can be specified as part of the
> connection
> > string.
> > Specifying `jit=off`  or `jit=on` is functional in practice, but `jit` is
> > not included in the documentation.
>
> Really?  Doesn't work for me:
>
> $ psql "host=localhost port=5432 jit=off"
> psql: error: invalid connection option "jit"
>
> regards, tom lane
>

-- 


Email
Disclaimer

  

This email is 
proprietary to MI-C3 and is intended only for the person to whom it is 
addressed. It may contain confidential and privileged  information. If you 
have received this email in error, please notify the sender and delete this 
email, which must not be copied, distributed or disclosed to any other 
person. Any views or opinions presented are solely those of the author and 
do not necessarily represent those of MI-C3. Since this communication was 
affected via email, MI-C3 cannot guarantee that it is secure or error-free 
as information could be intercepted, corrupted, lost, destroyed, arrive 
late or incomplete, or contain viruses. MI-C3 does not accept liability for 
any errors or omissions in the contents of this message or for any damage 
resulting from the opening of this message should it contain a virus.
  

Re: Missing documentation

[Tom Lane]

Stefan Badenhorst  writes:
> This is working:
> postgresql://USERNAME:PASSWORD@server
> :5432/the_database?sslmode=disable&jit=off

Again, not for me:

$ psql 'postgresql://postgres@localhost:5432/mydb?sslmode=disable&jit=off'
psql: error: invalid URI query parameter: "jit"

I suspect something in your client environment is stripping off the &...
stuff.

regards, tom lane

Re: Missing documentation

[Tom Lane]

Stefan Badenhorst  writes:
> I am using a prometheus exporter:
> https://github.com/prometheus-community/postgres_exporter/

Hmm, well, maybe prometheus is doing something with that part of the URL.
There's no such behavior in Postgres itself, though.

regards, tom lane

Re: Missing documentation

[Stefan Badenhorst]

I am using a prometheus exporter:
https://github.com/prometheus-community/postgres_exporter/
Turning JIT off definitely made a big difference to all our environments.
When using JIT it causes OOM on postgres server after a few days. Adding
jit=off resolved the issue.
I have tested this now for several weeks and memory usage stays stable when
jit=off.
You can see the related issue here:
https://github.com/prometheus-community/postgres_exporter/issues/694

On Thu, 3 Nov 2022 at 16:01, Tom Lane  wrote:

> Stefan Badenhorst  writes:
> > This is working:
> > postgresql://USERNAME:PASSWORD@server
> > :5432/the_database?sslmode=disable&jit=off
>
> Again, not for me:
>
> $ psql 'postgresql://postgres@localhost:5432/mydb?sslmode=disable&jit=off'
> psql: error: invalid URI query parameter: "jit"
>
> I suspect something in your client environment is stripping off the &...
> stuff.
>
> regards, tom lane
>

-- 


Email
Disclaimer

  

This email is 
proprietary to MI-C3 and is intended only for the person to whom it is 
addressed. It may contain confidential and privileged  information. If you 
have received this email in error, please notify the sender and delete this 
email, which must not be copied, distributed or disclosed to any other 
person. Any views or opinions presented are solely those of the author and 
do not necessarily represent those of MI-C3. Since this communication was 
affected via email, MI-C3 cannot guarantee that it is secure or error-free 
as information could be intercepted, corrupted, lost, destroyed, arrive 
late or incomplete, or contain viruses. MI-C3 does not accept liability for 
any errors or omissions in the contents of this message or for any damage 
resulting from the opening of this message should it contain a virus.
  

Re: Missing documentation

[Alvaro Herrera]

On 2022-Nov-03, Tom Lane wrote:

> Stefan Badenhorst  writes:
> > I am using a prometheus exporter:
> > https://github.com/prometheus-community/postgres_exporter/
> 
> Hmm, well, maybe prometheus is doing something with that part of the URL.
> There's no such behavior in Postgres itself, though.

Yeah, that part of the connection URI accepts libpq options, not
Postgres-server options.  The only way that you can pass options to the
backend is to use "options=-c foo=bar" in the URI, but both the
whitespace and the second equals sign need to be URI-escaped, so you end
up with something like this:

?sslmode=disable&options=-c%20jit%3Doff

This is explained (to some extent) in "34.1.1.2 Connection URIs",
https://www.postgresql.org/docs/current/libpq-connect.html#id-1.7.3.8.3.6

and later "34.1.2 Parameter Key Words" explains that the parameter
"options" is used to pass command-line (!!) options to the server.

Maybe this bit of documentation is not explicit enough; some additional
examples might clarify things.

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

Re: Missing documentation

[Stefan Badenhorst]

Thanks! This helps.

On Thu, Nov 3, 2022, 18:40 Alvaro Herrera  wrote:

> On 2022-Nov-03, Tom Lane wrote:
>
> > Stefan Badenhorst  writes:
> > > I am using a prometheus exporter:
> > > https://github.com/prometheus-community/postgres_exporter/
> >
> > Hmm, well, maybe prometheus is doing something with that part of the URL.
> > There's no such behavior in Postgres itself, though.
>
> Yeah, that part of the connection URI accepts libpq options, not
> Postgres-server options.  The only way that you can pass options to the
> backend is to use "options=-c foo=bar" in the URI, but both the
> whitespace and the second equals sign need to be URI-escaped, so you end
> up with something like this:
>
> ?sslmode=disable&options=-c%20jit%3Doff
>
> This is explained (to some extent) in "34.1.1.2 Connection URIs",
> https://www.postgresql.org/docs/current/libpq-connect.html#id-1.7.3.8.3.6
>
> and later "34.1.2 Parameter Key Words" explains that the parameter
> "options" is used to pass command-line (!!) options to the server.
>
> Maybe this bit of documentation is not explicit enough; some additional
> examples might clarify things.
>
> --
> Álvaro Herrera   48°01'N 7°57'E  —
> https://www.EnterpriseDB.com/
>

-- 


Email
Disclaimer

  

This email is 
proprietary to MI-C3 and is intended only for the person to whom it is 
addressed. It may contain confidential and privileged  information. If you 
have received this email in error, please notify the sender and delete this 
email, which must not be copied, distributed or disclosed to any other 
person. Any views or opinions presented are solely those of the author and 
do not necessarily represent those of MI-C3. Since this communication was 
affected via email, MI-C3 cannot guarantee that it is secure or error-free 
as information could be intercepted, corrupted, lost, destroyed, arrive 
late or incomplete, or contain viruses. MI-C3 does not accept liability for 
any errors or omissions in the contents of this message or for any damage 
resulting from the opening of this message should it contain a virus.
  

Re: list of flags that pg_settings_get_flags reports

[Tom Lane]

Justin Pryzby  writes:
> f2d0c7f18 should be backpatched to v15.

Agreed, done.

regards, tom lane