Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Tom Lane]

Bryn Llewellyn  writes:
> I can't agree with you about risks and probability, though. The general 
> literature of security threats often makes the point that disgruntled 
> employees (current or very recently former) who know the code in question do 
> sometimes wreak havoc—sometimes just for sport. The general risk that the 
> unrestricted ability to use "pg_terminate_backend()" to kill sessions started 
> by one's peers is ordinary denial of service—notwithstanding the possibility 
> for automatic re-connect. It still steals time and resources.

I'm not sure that this argument has much to recommend it.  If you
are able to issue arbitrary SQL commands, you can cause effective
denials-of-service in many ways.  A couple of random examples:

* launch a query that will eat indefinite amounts of CPU and/or disk.

* "LOCK TABLE some-important-table" and leave for lunch.

* leave a transaction open and go on vacation.

Yeah, you can progressively lock down a system against all of these
sorts of hazards, but it will get progressively less useful for
anyone who wants to do actual work on it.

In the end, the default settings have to represent a compromise
that we think is generally useful.  You're welcome to lock your
own installation down more than that, but it doesn't follow that
everybody else should too.

regards, tom lane

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Tom Lane]

Bryn Llewellyn  writes:
> My  non-superuser normalrole with direct login, "u1", is *still* able to 
> invoke pg_terminate_backend() and kill other "u1" sessions—even after this 
> (as a super-user):

Really?

I did this in 14.5:

regression=# revoke execute on function pg_terminate_backend from public;
REVOKE
regression=# select proacl from pg_proc where proname = 'pg_terminate_backend';
proacl 
---
 {postgres=X/postgres}
(1 row)

(as expected, the superuser's own execute permission is all that remains)

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> select pg_terminate_backend(42);
ERROR:  permission denied for function pg_terminate_backend

> It very much looks as if what I have describe was deemed to be a bug (after 
> that behavior had survived from at least version 11) and that it's now been 
> fixed!

No, it very much looks like pilot error.  But you've not shown
us exactly what your test consisted of, so it's hard to say just
where it went off the rails.

regards, tom lane

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn]

x...@thebuild.com wrote:
> 
> b...@yugabyte.com wrote:
>> 
>> The implication is that every client program must follow every database call 
>> with defensive code to detect error "57P01" and programmatically re-try.
> 
> That situation exists even without the ability for a role to kill other 
> sessions authorized to the same role.  A superuser (or role granted 
> pg_signal_backend) could have terminated it, the connection could have broken 
> due to a network failure (which caused the backend to roll back and 
> terminate), or the server could have crashed.
> 
> Pragmatically, the only real additional risk cases here are...

Thanks, Christophe. David Johnston said something similar. I'll happily concede 
that my thinking about the tedium of writing client code to detect a 
disconnected backend and then to retry was blinkered. Yes, of course that's a 
general risk—and so that code is needed anyway.

I can't agree with you about risks and probability, though. The general 
literature of security threats often makes the point that disgruntled employees 
(current or very recently former) who know the code in question do sometimes 
wreak havoc—sometimes just for sport. The general risk that the unrestricted 
ability to use "pg_terminate_backend()" to kill sessions started by one's peers 
is ordinary denial of service—notwithstanding the possibility for automatic 
re-connect. It still steals time and resources.

Anyway... David (separately) just said that "revoke execute on function 
pg_terminate_backend(int, bigint) from public" has the effect that reading the 
statement leads you to expect—in version 16. But my tests show that it does 
*not* have this effect in version 14.5.

This indicates that the regime that I complained about was deemed to be a 
bug—and that I can simply say "case closed".

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Christophe Pettus]

> On Sep 12, 2022, at 20:44, Bryn Llewellyn  wrote:
> Version 16? Thus might be the clue, then.

It behaves as David describes on:

PostgreSQL 14.5 on x86_64-apple-darwin19.6.0, compiled by Apple clang 
version 12.0.0 (clang-1200.0.32.29), 64-bit 

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> revoke execute on function pg_terminate_backend(int, bigint) from public;
> 
> I just did this very thing in v16 (head-ish) and it worked as expected, 
> preventing the non-superuser role from executing the function:
> 
> Session 1 - superuser
> postgres=# revoke execute on function pg_terminate_backend from public;
> REVOKE
> 
> Session 2 - non-superuser (normalrole with direct login)
> postgres=> select pid, usename, query, state from pg_stat_activity;
>   pid   |  usename   |  query 
>   | state
> ++--+
>  43 ||
>   |
>  44 | vagrant|
>   |
>  470387 | normalrole | select pid, usename, query, state from 
> pg_stat_activity; | active
>  470391 | normalrole | select pg_sleep(1000); 
>   | active
>  470412 | vagrant|
>   |
>  40 ||
>   |
>  466659 ||
>   |
>  42 ||
>   |
> (8 rows)
> 
> postgres=> select pg_terminate_backend(470391);
> ERROR:  permission denied for function pg_terminate_backend

Version 16? Thus might be the clue, then. Here's the result of "select 
version()" with my macOS PG :

PostgreSQL 14.5 (Homebrew) on x86_64-apple-darwin20.6.0, compiled by Apple ...

The current PG doc says "PostgreSQL 14.5 Documentation". And it does seem to be 
a reasonable policy for me, an ordinary end user, to arrange always to use the 
current non-Beta software as the doc advertises it to be. I repeated my test to 
be doubly sure. My  non-superuser normalrole with direct login, "u1", is 
*still* able to invoke pg_terminate_backend() and kill other "u1" sessions—even 
after this (as a super-user):

revoke execute on function pg_terminate_backend from public;
revoke execute on function pg_terminate_backend from u1;

It very much looks as if what I have describe was deemed to be a bug (after 
that behavior had survived from at least version 11) and that it's now been 
fixed!

Can you (or anybody) please confirm this? And if this is confirmed, then 
obviously I'll shut up just wait patiently until Version 16 is supported 
version.

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[David G. Johnston]

On Mon, Sep 12, 2022 at 6:08 PM Bryn Llewellyn  wrote:

>
> *revoke execute on function pg_terminate_backend(int, bigint) from public;*
>

I just did this very thing in v16 (head-ish) and it worked as expected,
preventing the non-superuser role from executing the function:

Session 1 - superuser
postgres=# revoke execute on function pg_terminate_backend from public;
REVOKE

Session 2 - non-superuser (normalrole with direct login)
postgres=> select pid, usename, query, state from pg_stat_activity;
  pid   |  usename   |  query
| state
++--+
 43 || 
|
 44 | vagrant| 
|
 470387 | normalrole | select pid, usename, query, state from
pg_stat_activity; | active
 470391 | normalrole | select pg_sleep(1000);
| active
 470412 | vagrant| 
|
 40 || 
|
 466659 || 
|
 42 || 
|
(8 rows)

postgres=> select pg_terminate_backend(470391);
ERROR:  permission denied for function pg_terminate_backend

David J.

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> …I'm troubled by the notion that (as it seems) one session that authorizes 
>> as the role "r1" can easily list all other concurrent sessions that are also 
>> authorized as "r1"—and kill them all without restriction. (The doc does say 
>> "Use of these functions is usually restricted to superusers, with noted 
>> exceptions." So I s'pose that I'm talking about one of these noted 
>> exceptions.)
> 
> You can always choose to limit that function to explicitly granted roles if 
> you wish. And write security definer functions if you desire some different 
> rules… There are only a couple of relevant functions so revoking default 
> privileges and granting them explicitly gives you the same outcome as adding 
> the pg_signal_backend predefined role… I'll agree it is an insecure default…

This sounds like exactly what I want—and what I was asking about. But how do I 
"limit pg_terminate_backend() to explicitly granted roles?" Out of the box, any 
role can execute it. So yes… "revoking default privileges and granting them 
explicitly" is what I want. But how? I'd already tried authorizing as a 
superuser and doing this:

revoke execute on function pg_terminate_backend(int, bigint) from public;

It ran without error. But even so, a freshly created non-super role was still 
able to kill sessions that had authorized as the same role. So I assumed that 
there was something hard-wired about the accessibility of 
"pg_terminate_backend()".

In other words, where can I read about this and learn how to do what you 
propose? (Of course I see that with this achieved, "security definer" 
subprograms would then bring their usual value.)

> having a login privilege for the database comes with power and responsibility


I can't accept that having a login privilege will give any session that logs on 
unlimited power to act. The notions of object ownership, privileges, "security 
definer" subprograms, and so on support my stance.

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Christophe Pettus]

> On Sep 12, 2022, at 15:51, Bryn Llewellyn  wrote:
> The implication is that every client program must follow every database call 
> with defensive code to detect error "57P01" and programmatically re-try.

That situation exists even without the ability for a role to kill other 
sessions authorized to the same role.  A superuser (or role granted 
pg_signal_backend) could have terminated it, the connection could have broken 
due to a network failure (which caused the backend to roll back and terminate), 
or the server could have crashed.

Pragmatically, the only real additional risk cases here are:

(a) An intrusion using that role, 
(b) A client program that for some reason can issue a legitimate 
pg_terminate_backend() call, but that has a bug that causes it to use it 
inappropriately.

In the case of (a), pg_terminate_backend() is the least of your worries, and I 
have a hard time seeing (b) as a real-world risk that requires a new PostgreSQL 
feature to defending again.

Also pragmatically, it would be a *very* significant behavior shift if roles 
could not by default signal other sessions authorized to the same role, so it 
would be unwise to introduce that feature and have it be revoked from 
non-superusers by default.  And, if it's not revoked by default, it's not going 
be very widely used except for ultra-locked-down environments.  I don't think 
it would hurt anything to introduce it, but I'm not sure the utility is there.

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[David G. Johnston]

On Mon, Sep 12, 2022 at 3:51 PM Bryn Llewellyn  wrote:

> I'll use "kill" here a shorthand for using the "pg_terminate_backend()"
> built-in function. I read about it in the "Server Signaling Functions"
> section of the enclosing "System Administration Functions" section of the
> current doc:
>
> www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL
>
> And I tried a few tests. All of the outcomes were just as the doc promised.
>
> I'm troubled by the notion that (as it seems) one session that authorizes
> as the role "r1" can easily list all other concurrent sessions that are
> also authorized as "r1"—and kill them all without restriction. (The doc
> does say "Use of these functions is usually restricted to superusers, with
> noted exceptions." So I s'pose that I'm talking about one of these noted
> exceptions.)
>

You can always choose to limit that function to explicitly granted roles if
you wish.  And write security definer functions if you desire some
different rules.


> The implication is that every client program must follow every database
> call with defensive code to detect error "57P01" and programmatically
> re-try.


You gotta learn to accept that life involves risk.  OTOH, resilient code
should already be doing this kind of stuff since this kind of interruption
in more probable scenarios than this.  Either way, this doesn't seem to
meaningfully change the risk profile.  Those who feel differently have
options.


> (Maybe some drivers can do this automatically. But I haven't found out if
> whatever psql uses can do this. Nor have I found out how to write re-try
> code in psql.)
>
> Does anybody else find all this as troubling as I do?


No, having a login privilege for the database comes with power and
responsibility.  In the continuum between usability and locked-down this
seems reasonable.

There are only a couple of relevant functions so revoking default
privileges and granting them explicitly gives you the same outcome as
adding the pg_signal_backend predefined role.

I'll agree it is an insecure default, though, and I suspect most setups
would rather rely on user roles holding pg_signal_backend to deal with any
misbehaving process (which leans me toward not wanting to introduce yet
another predefined role).  Likely combined with pg_read_all_stats so
viewing pg_stat_activity gives them a complete picture.  It seems
reasonable, though, to remove the default function execute grant from
PUBLIC for these; or if that doesn't work consider a documentation patch if
you feel the typical DBA would remain under-informed even after reviewing
the documentation (though without a dedicate section discussing such best
practices I suspect such material would go unread by those who would most
need it).

David J.

Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn]

I'll use "kill" here a shorthand for using the "pg_terminate_backend()" 
built-in function. I read about it in the "Server Signaling Functions" section 
of the enclosing "System Administration Functions" section of the current doc:

www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

And I tried a few tests. All of the outcomes were just as the doc promised.

I'm troubled by the notion that (as it seems) one session that authorizes as 
the role "r1" can easily list all other concurrent sessions that are also 
authorized as "r1"—and kill them all without restriction. (The doc does say 
"Use of these functions is usually restricted to superusers, with noted 
exceptions." So I s'pose that I'm talking about one of these noted exceptions.)

It's common to design a three tier app so that the middle tier always 
authorizes as just a single role—say, "client"—and where the operations that 
"client" can perform are limited as the overall design specifies. The maximal 
example of this paradigm defines the API to the database functionality by 
granting "execute" to just the designed set of subprograms. Here, the 
subprograms and the tables that they access all have owners other than 
"client". (The authorization of external principals, and ow their identity is 
mapped to a unique key for use within that database, is outside the scope of 
what I write about here.)

It seems far-fetched to think that the requirements spec for every such design 
would deliberately specify:

— Must be possible for any "client" session to kill all other concurrent 
"client" sessions.

Yet the paradigm is that the database API expresses exactly and only what the 
design says that it should. Ergo, the paradigm is, in general, unimplementable.

I appreciate that (while the privileges that "client" has are unchanged) a 
just-killed session can easily reconnect by trying what they had just tried 
again. But not before suffering the fatal "57P01: terminating connection due to 
administrator command" error.

The implication is that every client program must follow every database call 
with defensive code to detect error "57P01" and programmatically re-try. (Maybe 
some drivers can do this automatically. But I haven't found out if whatever 
psql uses can do this. Nor have I found out how to write re-try code in psql.)

Does anybody else find all this as troubling as I do? And, if so, might a 
remedy be possible? Maybe something like this:

— Define a new privilege as a cousin to "pg_signal_backend". I'll call it 
"pg_signal_backend_for_self_role" here. This would govern the possibility that 
a session can kill another session that authorized as the same role as itself.

— Document the fact that "pg_signal_backend_for_self_role" is implicitly 
granted to a newly-created role (just as it's documented that "execute… to 
public" is implicitly granted to a newly created subprogram).

— Allow "revoke pg_signal_backend_for_self_role from…"—by all means with extra 
rules like only a superuser can do this.

Re: how to check specific user_name have “SELECT ON ALL TABLES IN SCHEMA ” privilege or not ?

[Joe Conway]

On 9/11/22 12:03, David G. Johnston wrote:
On Sunday, September 11, 2022, yanliang lei > wrote:


Dear everyone，
how to check specific   have “SELECT ON ALL TABLES IN
SCHEMA ”  privilege or not ?


This is not an appropriate list to ask general usage questions.  We have 
a -general list for those.


(therefore moving thread to pgsql-general)

But, that isn’t a privilege.  Its a grant action.  The privilege is just 
select on a table in a schema.  So you have to check every table in the 
schema individually.  The functions and the catalogs documented to 
retrieve this information - start putting them together into a query.  
I’m unable to go look for the specific details right now.


For an extension that will help you with this, see
 https://github.com/CrunchyData/crunchy_check_access
Either use that directly, or see how it gathers the info you are 
interested in:


https://github.com/CrunchyData/crunchy_check_access/blob/master/sql/check_access.sql

HTH,

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: unable to install pldebugger

[shashidhar Reddy]

Thank you Julien! It worked

On Mon, 12 Sep, 2022, 10:19 pm Julien Rouhaud,  wrote:

> On Mon, Sep 12, 2022 at 10:14:20PM +0530, shashidhar Reddy wrote:
> > Hello Julien,
> >
> > Can I use this link to install in community edition postgres or is it
> > specific to enterpriseDB
>
> It should work with the standard community edition.
>

Re: pgBackRest on MacOS

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 9/10/22 07:31, Marc wrote:
> >Has anybody pgbackrest running on MacOS ( Monterey ? )
> >
> >If yes are you willing to share the how to ?
> >
> >Or can anybody guide us to an “easy” how to ?
> 
> It's written in Perl, so installation from source should be easy.

This hasn't been the case in a long, long time.

That said, we've certainly had folks build it on MacOS before and had
success with it.  I wouldn't consider it to be a common deployment
platform though.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: unable to install pldebugger

[Julien Rouhaud]

On Mon, Sep 12, 2022 at 10:14:20PM +0530, shashidhar Reddy wrote:
> Hello Julien,
> 
> Can I use this link to install in community edition postgres or is it
> specific to enterpriseDB

It should work with the standard community edition.

Re: Missing query plan for auto_explain.

[Julien Rouhaud]

On Mon, Sep 12, 2022 at 05:34:37PM +0100, Matheus Martin wrote:
> Understood. I have run a prepared statement with the query in question
> through `psql` and JIT was not used (see plan below), however please note
> that the long response times were never reproducible from `psql`, they only
> happen from our JDBC application.

Do you have pg_stat_statements enabled?  If yes you could check what are the
maximum execution and planning time for that query, which may give an answer.

Re: unable to install pldebugger

[shashidhar Reddy]

Hello Julien,

Can I use this link to install in community edition postgres or is it
specific to enterpriseDB

On Mon, 12 Sep, 2022, 7:01 pm Julien Rouhaud,  wrote:

> Hi,
>
> On Mon, Sep 12, 2022 at 06:49:21PM +0530, shashidhar Reddy wrote:
> >
> > I am in a process of upgrading postgres 12 to 13 on ubuntu. When I am
> > running the test getting error could not load library
> > "$libdir/plugin_debugger": ERROR:  could not load library
> > "/usr/lib/postgresql/13/lib/plugin_debugger.so":
> > /usr/lib/postgresql/13/lib/plugin_debugger.so: undefined symbol:
> elog_finish
>
> It looks like you're trying to load the version compiled for pg 12, as
> elog_finish was removed in pg13.
>
> > When I am trying to install pldebugger from
> > https://git.postgresql.org/git/pldebugger.git getting that repository
> not
> > found. Could you please help me to fix this issue.
>
> As far as I know the pldebugger repository is available at
> https://github.com/EnterpriseDB/pldebugger.
>

Re: Missing query plan for auto_explain.

[Matheus Martin]

Understood. I have run a prepared statement with the query in question
through `psql` and JIT was not used (see plan below), however please note
that the long response times were never reproducible from `psql`, they only
happen from our JDBC application.


  QUERY PLAN
---
 Limit  (cost=31.41..31.41 rows=1 width=707) (actual time=4.421..4.442
rows=23 loops=1)
   Buffers: shared hit=827 dirtied=1
   ->  Sort  (cost=31.41..31.41 rows=1 width=707) (actual time=4.416..4.434
rows=23 loops=1)
 Sort Key: users.unique_identifier, user_realm.user_realm_id
 Sort Method: quicksort  Memory: 31kB
 Buffers: shared hit=827 dirtied=1
 ->  Nested Loop Left Join  (cost=7.84..31.40 rows=1 width=707)
(actual time=1.154..4.232 rows=23 loops=1)
   Buffers: shared hit=824 dirtied=1
   ->  Nested Loop  (cost=7.55..24.94 rows=1 width=704) (actual
time=1.090..3.725 rows=23 loops=1)
 Join Filter:
((acl_allowed_user_realm_category.user_realm_category_id)::text =
(user_realm_category.user_realm_category_id)::text)
 Buffers: shared hit=757 dirtied=1
 ->  Nested Loop  (cost=7.42..24.75 rows=1 width=209)
(actual time=1.015..3.564 rows=23 loops=1)
   Buffers: shared hit=711 dirtied=1
   ->  Nested Loop  (cost=7.29..23.95 rows=1
width=204) (actual time=0.950..3.399 rows=23 loops=1)
 Buffers: shared hit=665 dirtied=1
 ->  Nested Loop  (cost=7.15..23.73 rows=1
width=200) (actual time=0.911..3.228 rows=23 loops=1)
   Buffers: shared hit=619 dirtied=1
   ->  Nested Loop  (cost=6.72..23.22
rows=1 width=200) (actual time=0.803..2.481 rows=23 loops=1)
 Buffers: shared hit=527
 ->  Nested Loop
 (cost=6.44..22.42 rows=1 width=191) (actual time=0.669..2.249 rows=23
loops=1)
   Buffers: shared hit=480
   ->  Nested Loop
 (cost=6.17..21.55 rows=1 width=194) (actual time=0.616..2.091 rows=23
loops=1)
 Join Filter:
((organisation.organisation_id)::text =
(acl_allowed_organisation.organisation_id)::text)
 Buffers: shared
hit=411
 ->  Nested Loop
 (cost=5.88..20.16 rows=1 width=203) (actual time=0.514..1.863 rows=23
loops=1)
   Buffers:
shared hit=364
   ->  Nested
Loop  (cost=5.61..19.73 rows=1 width=187) (actual time=0.474..1.682 rows=23
loops=1)

 Buffers: shared hit=295
 ->
 Nested Loop  (cost=5.33..18.82 rows=1 width=142) (actual time=0.424..1.446
rows=23 loops=1)

 Buffers: shared hit=226

 ->  Hash Join  (cost=4.91..10.44 rows=1 width=80) (actual
time=0.340..1.148 rows=23 loops=1)

 Hash Cond: (users.affiliate_id = acl_allowed_affiliate.affiliate_id)

 Buffers: shared hit=134

 ->  Index Scan using users_ix01 on users  (cost=0.43..5.45 rows=189
width=72) (actual time=0.090..0.822 rows=166 loops=1)

   Index Cond: (((unique_identifier)::text ~>=~ 'robinson06'::text)
AND ((unique_identifier)::text ~<~ 'robinson07'::text))

   Filter: (((type)::text = ANY ('{LOCAL,EXTERNAL}'::text[])) AND
((unique_identifier)::text ~~ 'robinson06%'::text))

   Buffers: shared hit=127

 ->  Hash  (cost=4.45..4.45 rows=2 width=8) (actual time=0.135..0.136
rows=1 loops=1)

   Buckets: 1024  Batches: 1  Memory Usage: 9kB

   Buffers: shared hit=4

   ->  Index Only Scan using pk_acl_allowed_affiliate on
acl_allowed_affiliate  (cost=0.41..4.45 rows=2 width=8) (actual
time=0.115..0.117 rows=1 loops=1)

 Index Cond: ((user_id = '1468137'::bigint) AND (permission
= 'READ'::text))

 Heap Fetches: 0

 Buffers: shared hit=4

 ->  Index Scan using pk_user_profile on user_profile  (cost=0.43..8.38
rows=1 width=62) (actual time=0.011..0.011 rows=1 loops=23)

 Index Cond: (user_id = users.user_id)

 Buffers: shared hit=92
 ->
 Index Scan using pk_user_realm on user_realm  (cost=0.27..0.91 rows=1
width=60) (actual time=0.008..0.008 rows=1 loops=23)

 Index Cond: ((user_realm_id)::text = 

Re: Support for dates before 4713 BC

[Tom Lane]

Simon Riggs  writes:
> On Mon, 12 Sept 2022 at 16:00, Tom Lane  wrote:
>> No, it applies because we aren't sure that the math would operate
>> correctly with negative Julian day numbers --- for instance, division
>> roundoffs might happen in the wrong direction.  If somebody wanted to go
>> through and check/fix all that, we could probably relax the restriction.

> OK, challenge seems clear.

FWIW, I think this decision dates to a time when we still worried about
C89's lack of specificity about which direction integer division will
round negative results in.  Now that we're targeting only C99-compliant
platforms, that part should be easier.

regards, tom lane

Re: Support for dates before 4713 BC

[Simon Riggs]

On Mon, 12 Sept 2022 at 16:00, Tom Lane  wrote:
>
> Simon Riggs  writes:
> > On Sun, 21 Aug 2022 at 19:04, Tom Lane  wrote:
> >> There are existing equations for calculating Gregorian month/day/year from
> >> Julian day count [1].  They work back to Julian day zero, at least if
> >> you grant that proleptic Gregorian dates are sensible that far back.
> >> Nobody around here has looked into whether they'd work for negative Julian
> >> day numbers (I suspect not though, at least not without work that seems
> >> rather pointless).
>
> > Sounds reasonable. So the 4713BC limit applies because of the
> > resolution of 1 day.
>
> No, it applies because we aren't sure that the math would operate
> correctly with negative Julian day numbers --- for instance, division
> roundoffs might happen in the wrong direction.  If somebody wanted to go
> through and check/fix all that, we could probably relax the restriction.

OK, challenge seems clear.

> I'm still failing to see the point though.  As already discussed upthread,
> the SQL datetime types aren't very suitable for dealing with approximate
> dates, multiple calendars, etc.

The problem is that support for custom datatypes is hard. First, you
have to find a cloud provider that will allow user extensions. Second,
you have to make that work with all the other software that isn't
expecting them.

Not insurmountable, but big enough to make it a barrier for something
fairly small like this.

-- 
Simon Riggshttp://www.EnterpriseDB.com/

Re: Resolving host to IP address

[Francisco Olarte]

Hi  Sebastien:

On Mon, 12 Sept 2022 at 16:40, Sebastien Flaesch
 wrote:

> I think I got it: PostgreSQL should listen to the real, non-loopback network 
> interface.

Not really. Your problem is not where postgres listens, but where your
host line resolves, the 127.0.1.1 stuff, plus your rules.

In many OS you can bind a listening TCP socket to IN_ADDR_ANY plus a
port. The good thing of doing it is it will work even if you add /
delete new IP addresses. Postgres does this and, IMO, is a good thing.
You seem to have it configured that way-

> Just for info (local dev config, not prod):
> sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ grep listen_addresses postgresql.conf
> listen_addresses = 'localhost,toro.strasbourg.4js.com'

No, you do not, you list your interfaces.. Unless you have a
complex setup, which does not seem to be the case, I would recommend
just using the default "*", specially if you intend to run your
servers firewalled ( which 10.xxx hints to ). This would lead to
something like ..

tcp0  0 0.0.0.0:54320.0.0.0:*   LISTEN
tcp6   0  0 :::5432 :::*LISTEN

( not done with pg, done with nc -l, but it worked the same last time
I checked it )

> sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W | grep 5437
> tcp0  0 localhost:5437  0.0.0.0:*   LISTEN
> tcp0  0 toro.strasbourg.4js.com:5437 0.0.0.0:*   
> LISTEN
> tcp6   0  0 localhost:5437  [::]:*  LISTEN

There is a piece of info missing here, where does your localhost resolve to.

> sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W --numeric-hosts | 
> grep 5437
> tcp0  0 127.0.0.1:5437  0.0.0.0:*   LISTEN
> tcp0  0 127.0.1.1:5437  0.0.0.0:*   LISTEN
> tcp6   0  0 ::1:5437:::*LISTEN

But from this it seems, if you have restored the 127.0.1.1 hosts line,
to 127.0.0.1+::1

> sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ ip route get 127.0.1.1
> local 127.0.1.1 dev lo src 127.0.0.1 uid 1000
> cache 
This is expected.

IMO your problem arises from your pg_hba.conf, and your bizarre ( IMO,
I use debian too and one of the first things I do after installing is
dropping the 127.0.1.1 line, but I used fixed IP on a desktop, so YMMV
).
If you just lists localhost as origin ( which means you have to
duplicate the pg_hba.conf lines, listen_address=* will work for both )
it will work right, whatever your listen_adresses is ). You can also
RTFM a bit and notice the line "You can also write all to match any IP
address, samehost to match any of the server's own IP addresses, or
samenet to match any address in any subnet that the server is directly
connected to." ( it is easy to miss, I had to search for it, even if I
knew it existed ).

Note: localhost is a name, like toro, and is resolved,
samehost/samenet/all are magic.


Sumarizing, I would:

- Use listen_adresses=*
- Use samehost in your rules.

Which will lead to a lean config which probably does what you want.

Francisco Olarte.

Re: Support for dates before 4713 BC

[Tom Lane]

Simon Riggs  writes:
> On Sun, 21 Aug 2022 at 19:04, Tom Lane  wrote:
>> There are existing equations for calculating Gregorian month/day/year from
>> Julian day count [1].  They work back to Julian day zero, at least if
>> you grant that proleptic Gregorian dates are sensible that far back.
>> Nobody around here has looked into whether they'd work for negative Julian
>> day numbers (I suspect not though, at least not without work that seems
>> rather pointless).

> Sounds reasonable. So the 4713BC limit applies because of the
> resolution of 1 day.

No, it applies because we aren't sure that the math would operate
correctly with negative Julian day numbers --- for instance, division
roundoffs might happen in the wrong direction.  If somebody wanted to go
through and check/fix all that, we could probably relax the restriction.
I'm still failing to see the point though.  As already discussed upthread,
the SQL datetime types aren't very suitable for dealing with approximate
dates, multiple calendars, etc.

regards, tom lane

Re: Resolving host to IP address

[Sebastien Flaesch]

Thank you all for your comments.

I think I got it: PostgreSQL should listen to the real, non-loopback network 
interface.

Just for info (local dev config, not prod):

sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ grep listen_addresses postgresql.conf

listen_addresses = 'localhost,toro.strasbourg.4js.com'


sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W | grep 5437
tcp0  0 localhost:5437  0.0.0.0:*   LISTEN
tcp0  0 toro.strasbourg.4js.com:5437 0.0.0.0:*   LISTEN
tcp6   0  0 localhost:5437  [::]:*  LISTEN


sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W --numeric-hosts | grep 
5437
tcp0  0 127.0.0.1:5437  0.0.0.0:*   LISTEN
tcp0  0 127.0.1.1:5437  0.0.0.0:*   LISTEN
tcp6   0  0 ::1:5437:::*LISTEN


sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ ip route get 127.0.1.1
local 127.0.1.1 dev lo src 127.0.0.1 uid 1000
cache 


Seb

Re: Support for dates before 4713 BC

[Simon Riggs]

On Sun, 21 Aug 2022 at 19:04, Tom Lane  wrote:
>
> "Watzinger, Alexander"  writes:
> > Any chance to add support for dates before 4713 BC? We really would 
> > appreciate that.
>
> I'm a little skeptical of the value of applying the Gregorian calendar
> before 1582 AD, let alone thousands of years before the Romans invented
> anything looking even vaguely like it.
>
> Are you sure there's going to be any point whatsoever in trying to name
> individual days that far back?  ISTM you'd be lucky to assign a year
> accurately.
>
> > The 4713 BC limit feels very arbitrary, what is the reason for this exact 
> > limit?
>
> There are existing equations for calculating Gregorian month/day/year from
> Julian day count [1].  They work back to Julian day zero, at least if
> you grant that proleptic Gregorian dates are sensible that far back.
> Nobody around here has looked into whether they'd work for negative Julian
> day numbers (I suspect not though, at least not without work that seems
> rather pointless).

Sounds reasonable. So the 4713BC limit applies because of the
resolution of 1 day.

Could we allow dates earlier than that, as long as we fix them to the
1st day of any particular month, so the effective resolution becomes
"1 month" before 4713BC? (With various and appropriate restrictions).

-- 
Simon Riggshttp://www.EnterpriseDB.com/

Re: Resolving host to IP address

[Francisco Olarte]

A little off topic but may be useful for someone:

On Mon, 12 Sept 2022 at 16:11, Tom Lane  wrote:
...
> Linux doesn't seem to show its routing of the loopback domain in netstat
> output, but it's probably much like what macOS shows explicitly:

Among other things it can be shown by "ip route show", although it is
a little too verbose:

$ ip route show table all
default via 192.168.0.1 dev enp39s0 onlink
192.168.0.0/24 dev enp39s0 proto kernel scope link src 192.168.0.2
broadcast 127.0.0.0 dev lo table local proto kernel scope link src 127.0.0.1
local 127.0.0.0/8 dev lo table local proto kernel scope host src 127.0.0.1
local 127.0.0.1 dev lo table local proto kernel scope host src 127.0.0.1
broadcast 127.255.255.255 dev lo table local proto kernel scope link
src 127.0.0.1
broadcast 192.168.0.0 dev enp39s0 table local proto kernel scope link
src 192.168.0.2
local 192.168.0.2 dev enp39s0 table local proto kernel scope host src
192.168.0.2
broadcast 192.168.0.255 dev enp39s0 table local proto kernel scope
link src 192.168.0.2
::1 dev lo proto kernel metric 256 pref medium
fe80::/64 dev enp39s0 proto kernel metric 256 pref medium
local ::1 dev lo table local proto kernel metric 0 pref medium
local fe80::2d8:61ff:fe9f:267b dev enp39s0 table local proto kernel
metric 0 pref medium
multicast ff00::/8 dev enp39s0 table local proto kernel metric 256 pref medium

( table local trims it a bit ).

For debugging this things my first go is to "ip route get", which
gives the selected one:

$ ip route get 127.1.2.3
local 127.1.2.3 dev lo src 127.0.0.1 uid 1000
cache 

Francisco Olarte.

Re: Resolving host to IP address

[Francisco Olarte]

On Mon, 12 Sept 2022 at 14:23, Sebastien Flaesch
 wrote:
> I managed to establish the secure connection, by using 
> DN=root.strasbourg.4js.com for the self-signed root CA, and 
> DN=toro.strasbourg.4js.com for the server certificate, DN=pgsuser for the 
> client certificate.
> I have created my client certificate by using the root CA.
> pg_hba.conf:
> hostssl all pgsuser toro.strasbourg.4js.com  md5 
> clientcert=verify-ca
>
> Server and client are on the same Debian 11 machine.
> It works, if I comment out the /etc/hosts line set by Debian Linux for my 
> host name:
> # 127.0.1.1   toro.strasbourg.4js.com toro

> The name "toro" is then resolved to the IP address provided by my DHCP server:
> root@toro:~# host toro
> toro.strasbourg.4js.com has address 10.0.40.61
>
> root@toro:~# host toro.strasbourg.4js.com
> toro.strasbourg.4js.com has address 10.0.40.61
>

> However, if I put the 127.0.1.1 line back in /etc/hosts, re-create all 
> certificates (is this actually needed? I guess no), restart the PostgreSQL 
> server, I get this error:
> $ psql 
> 'postgresql://toro.strasbourg.4js.com:5437/test1?user=pgsuser=verify-ca=./root.crt=./client.crt=./client.key'
> psql: error: connection to server at "toro.strasbourg.4js.com" (127.0.1.1), 
> port 5437 failed: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user 
> "pgsuser", database "test1", SSL encryption

> What looks strange to me in this error is that first it mentions 127.0.1.1 
> (ok) but then, 127.0.0.1
(not having your full data, guessing a bit on typical configs here ).

Your loopback interface, "lo" which is used to connect to net 127.*
has probably the address localhost=127.0.0.1.

Postgres is probably binding to wilcard address. So when you tell psql
to connect to 127.0.1.1 it starts the tcp connection selecting the
interface address as source, per the route table, so your connection
is source=127.0.0.1, destination 127.0.1.1.

The error message up to the "failed:" is probably psql telling you
where it sent the connection, to toro=127.0.1.1.

The second part is the server telling you where it sees the connection
comming from.

> What am I missing here?

Probably some tcp tracing to see it in action. If you bind to *:5437
you can receive connections on any 127.* address. Your hosts uses this
trick for unknown reasons.

When you zap the host line everything works well because your
interface is probably 10.0.40.61, so the route table says use
10.0.40.61 as source.

You would probably experience the same problem if you added a second
IP address, say 1.2.3.4 to your interface and told dhcp to resolve
toro to it. In localhost you do not have to do anything because any
127 address can be used as source or connected to in loopback, it is
magic.

Also, if you want it to work you would need a second hostssl line
listing localhost as the source address, or, IIRC, you can try to
force the source address for connections to be toro using some deep
magic jujus, as psql does not seem to suipport setting it.

Francisco Olarte.

Re: Resolving host to IP address

[Tom Lane]

Sebastien Flaesch  writes:
> $ psql 
> 'postgresql://toro.strasbourg.4js.com:5437/test1?user=pgsuser=verify-ca=./root.crt=./client.crt=./client.key'
> psql: error: connection to server at "toro.strasbourg.4js.com" (127.0.1.1), 
> port 5437 failed: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user 
> "pgsuser", database "test1", SSL encryption

> What looks strange to me in this error is that first it mentions 127.0.1.1 
> (ok) but then, 127.0.0.1

Linux doesn't seem to show its routing of the loopback domain in netstat
output, but it's probably much like what macOS shows explicitly:

$ netstat -r -n
DestinationGatewayFlags   Netif Expire
...
127127.0.0.1  UCS   lo0   
127.0.0.1  127.0.0.1  UHlo0   
...

That is, the entire 127/8 range is "routed" through 127.0.0.1 and
will look like that to the server.  I see similar entries on NetBSD.

Confirming that guess, I see this behavior on RHEL8,
with no particular OpenSSL involvement:

$ psql -h 127.0.1.1
psql (16devel)
Type "help" for help.

postgres=# select client_addr from pg_stat_activity;
 client_addr 
-
 ...
 127.0.0.1
 ...

Moral: don't try to use addresses in that range as real addresses.

regards, tom lane

Re: lippq client library and openssl initialization: PQinitOpenSSL()

[Tom Lane]

Daniel Gustafsson  writes:
> On 12 Sep 2022, at 13:21, Peter Eisentraut 
>  wrote:
>> AFAICT, RHEL 7 ships with an older version. I think that's still pretty 
>> widespread.

> Fair enough.  That doesn't however IMO prevent us from adding a note that 
> 1.1.0
> and onwards are different.  The attached keeps the strong wording of the main
> para, but adds a note after the related functions.

Personally I'd put this up front, more like

   have been initialized by your application, so that
   libpq will not also initialize those libraries.
+  However, this is unnecessary when using OpenSSL 1.1.0 or later,
+  as duplicate initializations are no longer problematic.
  

If you do use wording that specifically mentions PQinitOpenSSL,
it should also mention PQinitSSL, just for clarity.

regards, tom lane

Re: unable to install pldebugger

[Julien Rouhaud]

Hi,

On Mon, Sep 12, 2022 at 06:49:21PM +0530, shashidhar Reddy wrote:
>
> I am in a process of upgrading postgres 12 to 13 on ubuntu. When I am
> running the test getting error could not load library
> "$libdir/plugin_debugger": ERROR:  could not load library
> "/usr/lib/postgresql/13/lib/plugin_debugger.so":
> /usr/lib/postgresql/13/lib/plugin_debugger.so: undefined symbol: elog_finish

It looks like you're trying to load the version compiled for pg 12, as
elog_finish was removed in pg13.

> When I am trying to install pldebugger from
> https://git.postgresql.org/git/pldebugger.git getting that repository not
> found. Could you please help me to fix this issue.

As far as I know the pldebugger repository is available at
https://github.com/EnterpriseDB/pldebugger.

Re: unable to install pldebugger

[Pavel Stehule]

Hi

po 12. 9. 2022 v 15:19 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:

> Hello,
>
> I am in a process of upgrading postgres 12 to 13 on ubuntu. When I am
> running the test getting error could not load library
> "$libdir/plugin_debugger": ERROR:  could not load library
> "/usr/lib/postgresql/13/lib/plugin_debugger.so":
> /usr/lib/postgresql/13/lib/plugin_debugger.so: undefined symbol: elog_finish
>
> When I am trying to install pldebugger from
> https://git.postgresql.org/git/pldebugger.git getting that repository not
> found. Could you please help me to fix this issue.
>

PostgreSQL extension should be compiled against one selected major
release.  So you cannot to use plugin_debugger for PostgreSQL 12 for
PostgreSQL 13. You should to install plugin_debugger for PostgreSQL 13.

Regards

Pavel

>
> --
> Shashidhar
>

unable to install pldebugger

[shashidhar Reddy]

Hello,

I am in a process of upgrading postgres 12 to 13 on ubuntu. When I am
running the test getting error could not load library
"$libdir/plugin_debugger": ERROR:  could not load library
"/usr/lib/postgresql/13/lib/plugin_debugger.so":
/usr/lib/postgresql/13/lib/plugin_debugger.so: undefined symbol: elog_finish

When I am trying to install pldebugger from
https://git.postgresql.org/git/pldebugger.git getting that repository not
found. Could you please help me to fix this issue.

-- 
Shashidhar

Re: Support for dates before 4713 BC

[Watzinger, Alexander]

Dear Adrian,

Thank you for your insights and taking the time. It is always very interesting 
to see where other software projects limitations come from.

All the best,

Alex


--
Alexander Watzinger

Austrian Academy of Sciences
Austrian Centre for Digital Humanities and Cultural Heritage
Bäckerstraße 13, 1010 Vienna, Austria

alexander.watzin...@oeaw.ac.at | www.oeaw.ac.at/acdh




From: Adrian Klaver 
Sent: Sunday, August 21, 2022 19:15
To: Watzinger, Alexander; pgsql-general@lists.postgresql.org
Subject: Re: Support for dates before 4713 BC

On 8/20/22 07:05, Watzinger, Alexander wrote:
> Hi all,
>
> I'm working on the open source project OpenAtlas (https://openatlas.eu
> ) which is used to enter historical and
> archeological data.
> Although we really appreciate the wonderful PostgreSQL software, the
> limitation of not being able to use dates before 4713 BC is problematic
> in regard to archeological data.
> The only reason for this limitation I found was that the Julian Calendar
> wasn't created with this in mind. The only suggestion I found was to
> write an own implementation just using integers for years. But building
> a parallel date system in this case would be way to cumbersomeand error
> prone, we really like using the database for date operations.
>
>
> So my questions are:
> Any chance to add support for dates before 4713 BC? We really would
> appreciate that.
>
> The 4713 BC limit feels very arbitrary, what is the reason for this
> exact limit?

 From include/datatype/timestamp.h:

/*
  * Julian date support.
  *
  * date2j() and j2date() nominally handle the Julian date range 0..INT_MAX,
  * or 4714-11-24 BC to 5874898-06-03 AD.  In practice, date2j() will
work and
  * give correct negative Julian dates for dates before 4714-11-24 BC as
well.
  * We rely on it to do so back to 4714-11-01 BC.  Allowing at least one
day's
  * slop is necessary so that timestamp rotation doesn't produce dates that
  * would be rejected on input.  For example, '4714-11-24 00:00 GMT BC' is a
  * legal timestamptz value, but in zones east of Greenwich it would
print as
  * sometime in the afternoon of 4714-11-23 BC; if we couldn't process
such a
  * date we'd have a dump/reload failure.  So the idea is for
IS_VALID_JULIAN
  * to accept a slightly wider range of dates than we really support, and
  * then we apply the exact checks in IS_VALID_DATE or IS_VALID_TIMESTAMP,
  * after timezone rotation if any.  To save a few cycles, we can make
  * IS_VALID_JULIAN check only to the month boundary, since its exact
cutoffs
  * are not very critical in this scheme.
  *
  * It is correct that JULIAN_MINYEAR is -4713, not -4714; it is defined to
  * allow easy comparison to tm_year values, in which we follow the
convention
  * that tm_year <= 0 represents abs(tm_year)+1 BC.
  */

#define JULIAN_MINYEAR (-4713)
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
#define JULIAN_MAXMONTH (6)
#define JULIAN_MAXDAY (3)

#define IS_VALID_JULIAN(y,m,d) \
 (((y) > JULIAN_MINYEAR || \
   ((y) == JULIAN_MINYEAR && ((m) >= JULIAN_MINMONTH))) && \
  ((y) < JULIAN_MAXYEAR || \
   ((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE2451545 /* == date2j(2000, 1, 1) */

/*
  * Range limits for dates and timestamps.
  *
  * We have traditionally allowed Julian day zero as a valid datetime value,
  * so that is the lower bound for both dates and timestamps.
  *
  * The upper limit for dates is 5874897-12-31, which is a bit less than
what
  * the Julian-date code can allow.  For timestamps, the upper limit is
  * 294276-12-31.  The int64 overflow limit would be a few days later;
again,
  * leaving some slop avoids worries about corner-case overflow, and
provides
  * a simpler user-visible definition.
  */

/* First allowed date, and first disallowed date, in Julian-date form */
#define DATETIME_MIN_JULIAN (0)
#define DATE_END_JULIAN (2147483494)/* == date2j(JULIAN_MAXYEAR, 1,
1) */
#define TIMESTAMP_END_JULIAN (109203528)/* == date2j(294277, 1,
1) */

/* Timestamp limits */
#define MIN_TIMESTAMP   INT64CONST(-2118134880)
/* == (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
#define END_TIMESTAMP   INT64CONST(92233713312)
/* == (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */

/* Range-check a date (given in Postgres, not Julian, numbering) */
#define IS_VALID_DATE(d) \
 ((DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) <= (d) && \
  (d) < (DATE_END_JULIAN - POSTGRES_EPOCH_JDATE))

/* Range-check a timestamp */
#define IS_VALID_TIMESTAMP(t)  (MIN_TIMESTAMP <= (t) && (t) < END_TIMESTAMP)

#endif  /*
DATATYPE_TIMESTAMP_H */

>

Resolving host to IP address

[Sebastien Flaesch]

Hi!

I am playing with PostgreSQL TLS/SSL connections using OpenSSL, with server and 
client certificates.

I managed to establish the secure connection, by using 
DN=root.strasbourg.4js.com for the self-signed root CA, and 
DN=toro.strasbourg.4js.com for the server certificate, DN=pgsuser for the 
client certificate.

I have created my client certificate by using the root CA.

pg_hba.conf:

hostssl all pgsuser toro.strasbourg.4js.com  md5 
clientcert=verify-ca


Server and client are on the same Debian 11 machine.

It works, if I comment out the /etc/hosts line set by Debian Linux for my host 
name:

# 127.0.1.1   toro.strasbourg.4js.com toro

The name "toro" is then resolved to the IP address provided by my DHCP server:

root@toro:~# host toro
toro.strasbourg.4js.com has address 10.0.40.61

root@toro:~# host toro.strasbourg.4js.com
toro.strasbourg.4js.com has address 10.0.40.61

However, if I put the 127.0.1.1 line back in /etc/hosts, re-create all 
certificates (is this actually needed? I guess no), restart the PostgreSQL 
server, I get this error:

$ psql 
'postgresql://toro.strasbourg.4js.com:5437/test1?user=pgsuser=verify-ca=./root.crt=./client.crt=./client.key'
psql: error: connection to server at "toro.strasbourg.4js.com" (127.0.1.1), 
port 5437 failed: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user 
"pgsuser", database "test1", SSL encryption


What looks strange to me in this error is that first it mentions 127.0.1.1 (ok) 
but then, 127.0.0.1

What am I missing here?

Thanks!
Seb

Re: lippq client library and openssl initialization: PQinitOpenSSL()

[Daniel Gustafsson]

> On 12 Sep 2022, at 13:21, Peter Eisentraut 
>  wrote:
> On 11.09.22 23:43, Daniel Gustafsson wrote:
>>> On 11 Sep 2022, at 23:35, Tom Lane  wrote:

>>> Should we document these functions as obsolete when using
>>> OpenSSL >= 1.1.0 ?
>> Given that 1.1.0+ is very common, it's probably not a bad idea to document 
>> them
>> as obsolete but harmless. Unless you beat me to it I can propose a patch.
> 
> AFAICT, RHEL 7 ships with an older version. I think that's still pretty 
> widespread.

Fair enough.  That doesn't however IMO prevent us from adding a note that 1.1.0
and onwards are different.  The attached keeps the strong wording of the main
para, but adds a note after the related functions.

--
Daniel Gustafsson   https://vmware.com/



pqinitopenssl.diff
Description: Binary data

Re: lippq client library and openssl initialization: PQinitOpenSSL()

[Peter Eisentraut]

On 11.09.22 23:43, Daniel Gustafsson wrote:

On 11 Sep 2022, at 23:35, Tom Lane  wrote:

Daniel Gustafsson  writes:

On 11 Sep 2022, at 17:08, Tom Lane  wrote:

Don't believe so.  The HAVE_CRYPTO_LOCK stuff is all obsolete and
not compiled if you built against 1.1.0.  The only thing left that
will happen if you don't call PQinitOpenSSL is an extra call to
OPENSSL_init_ssl, which should be harmless as far as I can see
from the OpenSSL docs.



To the best of my knowledge, thats entirely correct.


Should we document these functions as obsolete when using
OpenSSL >= 1.1.0 ?


Given that 1.1.0+ is very common, it's probably not a bad idea to document them
as obsolete but harmless.  Unless you beat me to it I can propose a patch.


AFAICT, RHEL 7 ships with an older version.  I think that's still pretty 
widespread.