“tlsv1 alert unknown ca” with PQconnectdb

[M Tarkeshwar Rao]

Hi all,

I made following changes in my Postgres server which means I am enabling one 
way authentication: (Disabled Mutual authentication. Only client will 
authenticate server.)
hostssl all myuser  0.0.0.0/0   md5 clientcert=0

I am trying psql with following options where I am providing client 
certificates also. It is connected perfectly.
psql "host= 10.10.11.18 sslmode=verify-ca sslrootcert=em-ca-crt.pem 
sslcert=em-client-crt.pem sslkey=em-client-key.pem port=5433 user=postgres 
dbname=postgres"

Same when we used with C api (PQconnectdb((const char *)str);) it is failing 
with following error message.

“tlsv1 alert unknown ca” 


Also we tried with Java test program. It is connecting properly. Can you please 
suggest what could be the reason?

Regards
Tarkeshwar

Re: “tlsv1 alert unknown ca” with PQconnectdb

[Tom Lane]

M Tarkeshwar Rao  writes:
> I am trying psql with following options where I am providing client 
> certificates also. It is connected perfectly.
> psql "host= 10.10.11.18 sslmode=verify-ca sslrootcert=em-ca-crt.pem 
> sslcert=em-client-crt.pem sslkey=em-client-key.pem port=5433 user=postgres 
> dbname=postgres"

You do realize that those certificate parameters are path names, right?

> Same when we used with C api (PQconnectdb((const char *)str);) it is failing 
> with following error message.
> “tlsv1 alert unknown ca” 
> 

I think the most likely theory is that libpq is failing to load the root
cert because the program's current working directory isn't the same as
where you had been running psql.  It does look like libpq will complain
if the given files aren't readable, so maybe the true situation is that
it's finding files by those names but they aren't the right ones.

In any case, you generally want to put absolute pathnames into these
connection parameters.

regards, tom lane

Re: Unexpected block ID found when reading data

[Vijaykumar Jain]

On Tue, 3 Aug 2021 at 20:37, Gilar Ginanjar 
wrote:

> I’m not sure which patch version i used to dump, but i was using postgre
> 12.5 for pg_dump back then.
>
> I’m running pg_restore -f dbdump.backup right now, I think it will take
> some times because it has a large size (around 9 GB). There are no issues
> yet.
>

Did this complete without issues ? or did it throw the same errors ?

Using functions in regexp replace captures

[Tim Uckun]

I want to do something like this

SELECT REGEXP_REPLACE('some_string','(.*)
(.*)',some_function_that_returns_string('\2',' \1'));

Is this possible at all?

Re: Using functions in regexp replace captures

[David G. Johnston]

On Wednesday, August 4, 2021, Tim Uckun  wrote:

> I want to do something like this
>
> SELECT REGEXP_REPLACE('some_string','(.*)
> (.*)',some_function_that_returns_string('\2',' \1'));
>
> Is this possible at all?
>
>
Generally I’d say yes, it is possible to combine multiple subqueries
together to get the desired end result.  Using regexp_match and performing
the conversion on its result is fairly trivial.  In theory then write
regexp_replace like above but ignore the capture groups and just stick in
the column into,which you saved the computed value as the direct and
complete replacement.

But no, you cannot directly write:  f(x, y, g(a)) where a is the
replacement string because you don’t know what a is when the inner function
g is evaluated first.  You need:  f(x, y, g(h(x, y))) where h is the
matching function, g is the transform, f is the replacement of the third
argument into the x source text, and y is the pattern.  I presume the y is
going to be the same value here but that isn’t required.

David J.

Re: Using functions in regexp replace captures

[David G. Johnston]

On Wednesday, August 4, 2021, David G. Johnston 
wrote:

>
> But no, you cannot directly write:  f(x, y, g(a)) where a is the
> replacement string because you don’t know what a is when the inner function
> g is evaluated first.  You need:  f(x, y, g(h(x, y))) where h is the
> matching function, g is the transform, f is the replacement of the third
> argument into the x source text, and y is the pattern.  I presume the y is
> going to be the same value here but that isn’t required.
>
>
“a” is actually probably going to be an array of text here, its evaluation
producing the replacement string that f requires.  a is not the replacement
string itself.

David J.

autovacuum worker started without a worker entry

[Luca Ferrari]

Hi all,
I occasionally see the message "WARNING:  autovacuum worker started
without a worker entry" in the logs.
>From what I can see here
,
the launcher forked a worker and in the meantime the launcher decided
the worker is no more useful. If that is right, I'm guessing why the
worker should not be useful anymore: since a single worker could work
on a table, the only reason I can find is that someone run manually
vacuum within that database/table.

Moreover, I've a question about emergency autovacuum (wraparound).
Since autovacuum workers are started by postmaster on a signal
received by the autovacuum launcher, and since with autovacuum = off
the latter is not running, how does the postmaster decide to start and
emergency autovacuum?
I only found what seems to me a normal autovacuum start at
.

Thanks,
Luca