Replication using mTLS issue
Hi Postgres team, I’m receiving an issue matching pg_hba rules that I can’t seem to sort out. I am trying to use mtls certificate authentication for physical replication connections but keep receiving the following error… pg_receivewal: error: FATAL: no pg_hba.conf entry for replication connection from host "100.84.12.223", user "pgrepmgr_nonprod", SSL on My pg_hba.conf file contains hostssl replication pgrepmgr_nonprod 100.0.0.0/8 cert map=pgrepmgr_nonprod_map I’ve made sure the rule comes first in my pg_hba.conf file or last. I’ve read the pg_hba documentation fully and tried with all values for hostssl field, user field and ip field. Still no luck. Is cert authentication supported for replication connections?
Re: Postgresql python in upgraded version 16.2
Hello, I get the install packages from this repository: https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.9-ppc64le/ Michal Od: Adrian Klaver Odesláno: čtvrtek 20. června 2024 17:03 Komu: Šika Michal ; pgsql-gene...@postgresql.org Předmět: Re: Postgresql python in upgraded version 16.2 On 6/19/24 22:05, Šika Michal wrote: > TLP:AMBER > > > Hello all, > I upgraded version of Postgres from 13.5 to newest 16.2. But Python was > not upgraded. > > I create procedure pyver(): > CREATE OR REPLACE FUNCTION pyver () > RETURNS TEXT > AS $$ > import sys > pyversion = sys.version > return pyversion > $$ LANGUAGE 'plpython3u'; > > On the Postgres 13.5 I get this result: > # psql -d database > psql (13.5) > Type "help" for help. > > postgres@database # select pyver(); >pyver > - > 3.6.8 (default, Aug 13 2020, 07:36:02) + > [GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] > (1 řádka) > > postgres@database # > > On the Postgres 16.2 (upgraded from version 13.5) I get this result: > # psql -d database > psql (16.2) > Type "help" for help. > > database=# select pyver(); >pyver > - > 3.6.8 (default, Jan 5 2024, 09:14:44) + > [GCC 8.5.0 20210514 (Red Hat 8.5.0-20)] > (1 row) > > database=# > > It seems python was not upgraded when I upgrade PostgreSQL. Is possible > upgrade the python to actual version 3.9.x installed on RedHat 8.5 ? Where are you getting the packages from, the Red Hat repo or the PGDG repo? > > Thanks > Michal > > TLP:AMBER > -- Adrian Klaver adrian.kla...@aklaver.com
RowDescription for a function does not include table OID
Hello, I am working on a meta-programming use-case where I need to scrape some detailed information about the results of a function that "RETURNS TABLE (LIKE physical_table)", which ends up with prorettype = 'physical_table'::regtype. The problem is that for the query "SELECT * FROM my_function()" the RowDescription that is sent back shows 0 for Table OID and Column Index. >From Wireshark: PostgreSQL Type: Row description Length: 219 Field count: 7 Column name: table_id Table OID: 0 Column index: 0 Type OID: 20 Column length: 8 Type modifier: -1 Format: Binary (1) I would expect that the Table OID contains the relation OID of this table, as it would do for a typical statement like "SELECT * FROM my_table". It would seem there is a bug here that is preventing PostgreSQL from connecting the dots. Regards, Maxwell.
Re: RowDescription for a function does not include table OID
On Fri, Jun 21, 2024 at 7:42 AM Maxwell Dreytser < maxwell.dreyt...@assistek.com> wrote: > I am working on a meta-programming use-case where I need to scrape some > detailed information about the results of a function that "RETURNS TABLE > (LIKE physical_table)" > Yes, but the bug is yours. The definition you want is: RETURNS SETOF physical_table (not tested though) What you did was produce a one-column table whose column type is a composite (and whose name is like - what with case-folding of unquoted identifiers). Since that table doesn't exist anywhere in the catalogs it has no TableOID. David J.
Re: RowDescription for a function does not include table OID
On Friday, June 21, 2024 10:48 AM David G. Johnston wrote: >Yes, but the bug is yours. The definition you want is: RETURNS SETOF >physical_table (not tested though) >What you did was produce a one-column table whose column type is a composite >(and whose name is like - what with case-folding of unquoted identifiers). >Since that table doesn't exist anywhere in the catalogs it has no TableOID. SETOF also does not return correct RowDescription data. Table OID and column number are still both 0. Both versions have the exact same pg_proc.prorettype. If I join this onto pg_type, the pg_type.typrelid = 'physical_table'::regclass. Regards, Maxwell
Re: RowDescription for a function does not include table OID
On Fri, Jun 21, 2024 at 8:04 AM Maxwell Dreytser < maxwell.dreyt...@assistek.com> wrote: > On Friday, June 21, 2024 10:48 AM David G. Johnston < > david.g.johns...@gmail.com>wrote: > > >Yes, but the bug is yours. The definition you want is: RETURNS SETOF > physical_table (not tested though) > >What you did was produce a one-column table whose column type is a > composite (and whose name is like - what with case-folding of unquoted > identifiers). Since that table doesn't exist anywhere in the catalogs it > has no TableOID. > > SETOF also does not return correct RowDescription data. Table OID and > column number are still both 0. > Both versions have the exact same pg_proc.prorettype. If I join this onto > pg_type, the pg_type.typrelid = 'physical_table'::regclass. > > Interesting, then I suppose it is semantics. There is no table involved - you are referencing the type of that name, not the table - so no TableOID. There is no guarantee the row you are holding came from a table - and I'd interpret the current behavior as conveying that fact. Though the current wording: "If the field can be identified as a column of a specific table, the object ID of the table; otherwise zero."; and the observation that at least a human "can identify" a related column, leads one to reasonably infer the system should be able to make such an identification as well. I would expect you'd be able to find the pg_type.oid value somewhere in the RowDescription given those specifications, but not the pg_type.typrelid value. But since the header has no allowance for a row type oid this information does seem to be missing. In short, the system doesn't generate the information you need, where you need it, to tie these pieces together. Modifying existing elements of the backend protocol is not presently in the cards. David J.
Re: RowDescription for a function does not include table OID
On Friday, June 21, 2024 11:28 AM David G. Johnston wrote: > Interesting, then I suppose it is semantics. There is no table involved - > you are referencing the type of that name, not the table - so no TableOID. > There is no guarantee the row you are holding came from a table - and I'd > interpret the current behavior as conveying that fact. Though the current > wording: "If the field can be identified as a column of a specific table, the > object ID of the table; otherwise zero."; and the observation that at least a > human "can identify" a related column, leads one to reasonably infer the > system should be able to make such an identification as well. This is exactly my point. If the return type of the function is strongly linked (directly in the function schema) to the table according to pg_catalog, the field can obviously be tied to a specific column of that specific table. The RowDescription not having that value filled in is a violation of that promise. > In short, the system doesn't generate the information you need, where you > need it, to tie these pieces together. Modifying existing elements of the > backend protocol is not presently in the cards. >From my perspective this is clearly a bug as there is no way to define a >function in a way that provides enough data to the reader. Regards, Maxwell.
Re: RowDescription for a function does not include table OID
Maxwell Dreytser writes: > I am working on a meta-programming use-case where I need to scrape some > detailed information about the results of a function that "RETURNS TABLE > (LIKE physical_table)", which ends up with prorettype = > 'physical_table'::regtype. > The problem is that for the query "SELECT * FROM my_function()" the > RowDescription that is sent back shows 0 for Table OID and Column Index. Yes, that's expected. You're selecting from a function, not a table. > I would expect that the Table OID contains the relation OID of this > table, as it would do for a typical statement like "SELECT * FROM > my_table". The PG wire protocol specification [1] defines these fields thus: If the field can be identified as a column of a specific table, the object ID of the table; otherwise zero. If the field can be identified as a column of a specific table, the attribute number of the column; otherwise zero. My reading of that is that we should populate these fields only for the case of direct selection from a table. If you go further than that, then first off you have a ton of definitional issues (should it "look through" views, for example?), and second you probably break applications that are expecting the existing, longstanding definition. regards, tom lane [1] https://www.postgresql.org/docs/current/protocol-message-formats.html
Re: RowDescription for a function does not include table OID
On Fri, Jun 21, 2024 at 8:41 AM Maxwell Dreytser < maxwell.dreyt...@assistek.com> wrote: > On Friday, June 21, 2024 11:28 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: > > > In short, the system doesn't generate the information you need, where > you need it, to tie these pieces together. Modifying existing elements of > the backend protocol is not presently in the cards. > > From my perspective this is clearly a bug as there is no way to define a > function in a way that provides enough data to the reader. > Quick search turned up this prior thread: https://www.postgresql.org/message-id/19323.1245720832%40sss.pgh.pa.us Based upon that unargued point the only bug here is in the documentation, leaving the reader to assume that some effort will be made to chain together a function returns clause to a physical table through that table's automatically-generated composite type. We don't and never will modify the existing protocol message semantics in that respect. David J.
Re: RowDescription for a function does not include table OID
On Fri, Jun 21, 2024 at 8:51 AM Tom Lane wrote: > > The PG wire protocol specification [1] defines these fields thus: > > If the field can be identified as a column of a specific > table, the object ID of the table; otherwise zero. > > If the field can be identified as a column of a specific > table, the attribute number of the column; otherwise zero. > > My reading of that is that we should populate these fields only for > the case of direct selection from a table. > s/can be identified as/is/g ? Experience shows people are inferring a lot from "can be identified" so we should remove it. "is" maybe over-simplifies a bit but in the correct direction. David J.
Re: RowDescription for a function does not include table OID
"David G. Johnston" writes: > Based upon that unargued point the only bug here is in the documentation, > leaving the reader to assume that some effort will be made to chain > together a function returns clause to a physical table through that table's > automatically-generated composite type. Hmm, I read the documentation as making minimal promises about how much effort will be expended, not maximal ones. But in any case, I repeat the point that you can't open this can of worms without having a lot of definitional slipperiness wriggle out. Here is an example: regression=# create table foo(a int, b int); CREATE TABLE regression=# create table bar(x int, y int, z int); CREATE TABLE regression=# create function f(int) returns setof foo stable begin atomic select y, z from bar where x = $1; end; CREATE FUNCTION What labeling would you expect for "select * from f(...)", and on what grounds? It is by no stretch of the imagination a select from table foo. Moreover, the system has fully enough information to perceive the query as a select from bar after inlining the function call: regression=# explain verbose select * from f(42); QUERY PLAN Seq Scan on public.bar (cost=0.00..35.50 rows=10 width=8) Output: bar.y, bar.z Filter: (bar.x = 42) (3 rows) In fact, if we implemented this labeling at the tail end of planning rather than early in parsing, it'd be fairly hard to avoid labeling the output columns as bar.* rather than foo.*. But we don't, and I'm not seeing an upside to redefining how that works. I've long forgotten the alleged JDBC connection that David mentions, but it's surely just the tip of the iceberg of client-side code that we could break if we change how this works. regards, tom lane
Re: RowDescription for a function does not include table OID
"David G. Johnston" writes: > On Fri, Jun 21, 2024 at 8:51 AM Tom Lane wrote: >> The PG wire protocol specification [1] defines these fields thus: >> If the field can be identified as a column of a specific >> table, the object ID of the table; otherwise zero. > s/can be identified as/is/g ? > Experience shows people are inferring a lot from "can be identified" so we > should remove it. "is" maybe over-simplifies a bit but in the correct > direction. I dunno, that seems to me to be just as open to argument if not more so. Perhaps some phrasing like "can be directly identified"? The real point IMV is that it's based purely on parse analysis, without looking into the behavior of views or functions (which could change between parsing and execution, anyway). regards, tom lane
Re: Replication using mTLS issue
Drew Zoellner writes: > Hi Postgres team, I’m receiving an issue matching pg_hba rules that I can’t > seem to sort out. I am trying to use mtls certificate authentication for > physical replication connections but keep receiving the following error… > pg_receivewal: error: FATAL: no pg_hba.conf entry for replication > connection from host "100.84.12.223", user "pgrepmgr_nonprod", SSL on > My pg_hba.conf file contains > hostssl replication pgrepmgr_nonprod 100.0.0.0/8 cert > map=pgrepmgr_nonprod_map Hm, the match failure must be on user name. What certificate are you using on the client side, and what user name does pgrepmgr_nonprod_map map it to? Does it succeed if you weaken the hba entry to hostssl replication all 100.0.0.0/8 cert map=pgrepmgr_nonprod_map > Is cert authentication supported for replication connections? Should be. But you might find it easier to debug the auth failure in a non-replication context, ie add hostssl all pgrepmgr_nonprod 100.0.0.0/8 cert map=pgrepmgr_nonprod_map and then see if you can connect with the same credentials from psql or your favorite other client. BTW, don't forget you have to signal the postmaster to reload configuration after any change in these files. regards, tom lane
Re: Replication using mTLS issue
Drew Zoellner writes: > So the same user is able to connect using a non replication connection > using the same mtls certificate and pg_ident.conf map. So it seems like the > cert & map are working for this user. Hmph. I tried to reproduce your problem, and it works for me: I can create a replication connection that's authenticated by certificate and relies on a username map to map from the CN in the client certificate to the database username that's mentioned in the "hostssl replication" entry. All I can suggest at this point is to go over your configuration with a fine-tooth comb, looking for probably-silly mistakes such as inconsistent spellings. One thing I can think of to mention in particular is to be sure that the standby's primary_conninfo explicitly includes "user=pgrepmgr_nonprod", as that's likely not the user name it'd default to. Another idea could be to enable log_connections on the primary, and see if the incoming connection request looks different than you were expecting. regards, tom lane
RE: Autovacuum, dead tuples and bloat
Hi,
Thanks for the suggestions. I found the following details to our autovacuum
(see below). The related toast-table of my table shows some logs related the
vacuum. This toast seems to consume all the data (27544451 pages * 8kb ≈ 210GB )
Any thoughts on this?
Best regards,
Manuel
Autovacuum details
Details from pg_stat_all_tables:
{
"analyze_count": 0,
"autoanalyze_count": 11,
"autovacuum_count": 60,
"idx_scan": 1925218,
"idx_tup_fetch": 1836820,
"last_analyze": null,
"last_autoanalyze": "2024-06-19T09:39:50.680818+00:00",
"last_autovacuum": "2024-06-19T09:41:50.58592+00:00",
"last_vacuum": null,
"n_dead_tup": 120,
"n_live_tup": 9004,
"n_mod_since_analyze": 474,
"n_tup_del": 84,
"n_tup_hot_upd": 5,
"n_tup_ins": 118,
"n_tup_upd": 15180,
"relid": "27236",
"relname": "my_tablename",
"schemaname": "public",
"seq_scan": 2370,
"seq_tup_read": 18403231,
"vacuum_count": 0
}
From the server logs, I found autocacuum details for my toast table
(pg_toast_27236):
{
"category": "PostgreSQLLogs",
"operationName": "LogEvent",
"properties": {
"errorLevel": "LOG",
"message": "2024-06-19 17:45:02 UTC-66731911.22f2-LOG: automatic
vacuum of table
\"0ecf0241-aab3-45d5-b020-e586364f810c.pg_toast.pg_toast_27236\":
index scans: 1
pages: 0 removed, 27544451 remain, 0
skipped due to pins, 27406469 skipped frozen
tuples: 9380 removed, 819294 remain, 0
are dead but not yet removable, oldest xmin: 654973054
buffer usage: 318308 hits, 311886
misses, 2708 dirtied
avg read rate: 183.934 MB/s, avg write
rate: 1.597 MB/s
system usage: CPU: user: 1.47 s,
system: 1.43 s, elapsed: 13.24 s",
"processId": 8946,
"sqlerrcode": "0",
"timestamp": "2024-06-19 17:45:02.564 UTC"
},
"time": "2024-06-19T17:45:02.568Z"
}
Best regards,
Manuel
From: Achilleas Mantzios
Sent: 20 June 2024 19:10
To: pgsql-general@lists.postgresql.org
Subject: Re: Autovacuum, dead tuples and bloat
You don't often get email from
a.mantz...@cloud.gatewaynet.com. Learn
why this is important
Στις 20/6/24 19:46, ο/η Shenavai, Manuel έγραψε:
Hi everyone,
we can see in our database, that the DB is 200GB of size, with 99% bloat. After
vacuum full the DB decreases to 2GB.
DB total size: 200GB
DB bloat: 198 GB
DB non-bloat: 2GB
We further see, that during bulk updates (i.e. a long running transaction), the
DB is still growing, i.e. the size of the DB growth by +20GB after the bulk
updates.
My assumption is, that after an autovacuum, the 99% bloat should be available
for usage again. But the DB size would stay at 200GB. In our case, I would only
expect a growth of the DB, if the bulk-updates exceed the current DB size (i.e.
220 GB).
How could I verify my assumption?
I think of two possibilities:
1. My assumption is wrong and for some reason the dead tuples are not
cleaned so that the space cannot be reused
2. The bulk-update indeed exceeds the current DB size. (Then the growth is
expected).
Your only assumption should be the official manual, and other material such as
books, articles from reputable sources, even reading the source as a last
resort could be considered.
For starters : do you have autovacuum enabled ? If not, then enable this.
Then monitor for vacuum via pg_stat_user_tables, locate the tables that you
would expect vacuum to have happened but did not, then consider autovacuum
tuning.
Watch the logs for lines such as :
dead row
versions cannot be removed yet, oldest xmin:
those
are held from being marked as removed, due to being visible by long running
transactions. Monitor for those transactions.
You
have to monitor (if this is the case) about autovacuum being killed and not
allowed to do its job.
Can you help me to verify these assumptions? Are there any statistics available
that could help me with my verification?
Thanks in advance &
Best regards,
Manuel
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt (as agents only)
RE: Autovacuum, dead tuples and bloat
Here some more details related to the toast table:
{
"analyze_count": 0,
"autoanalyze_count": 0,
"autovacuum_count": 22,
"idx_scan": 1464881,
"idx_tup_fetch": 363681753,
"last_analyze": null,
"last_autoanalyze": null,
"last_autovacuum": "2024-06-19T17:45:02.564937+00:00",
"last_vacuum": null,
"n_dead_tup": 12,
"n_live_tup": 819294,
"n_mod_since_analyze": 225250407,
"n_tup_del": 112615126,
"n_tup_hot_upd": 0,
"n_tup_ins": 112635281,
"n_tup_upd": 0,
"relid": "27240",
"relname": "pg_toast_27236",
"schemaname": "pg_toast",
"seq_scan": 0,
"seq_tup_read": 0,
"vacuum_count": 0
}
From: Shenavai, Manuel
Sent: 21 June 2024 21:31
To: Achilleas Mantzios ;
pgsql-general@lists.postgresql.org
Subject: RE: Autovacuum, dead tuples and bloat
Hi,
Thanks for the suggestions. I found the following details to our autovacuum
(see below). The related toast-table of my table shows some logs related the
vacuum. This toast seems to consume all the data (27544451 pages * 8kb ≈ 210GB )
Any thoughts on this?
Best regards,
Manuel
Autovacuum details
Details from pg_stat_all_tables:
{
"analyze_count": 0,
"autoanalyze_count": 11,
"autovacuum_count": 60,
"idx_scan": 1925218,
"idx_tup_fetch": 1836820,
"last_analyze": null,
"last_autoanalyze": "2024-06-19T09:39:50.680818+00:00",
"last_autovacuum": "2024-06-19T09:41:50.58592+00:00",
"last_vacuum": null,
"n_dead_tup": 120,
"n_live_tup": 9004,
"n_mod_since_analyze": 474,
"n_tup_del": 84,
"n_tup_hot_upd": 5,
"n_tup_ins": 118,
"n_tup_upd": 15180,
"relid": "27236",
"relname": "my_tablename",
"schemaname": "public",
"seq_scan": 2370,
"seq_tup_read": 18403231,
"vacuum_count": 0
}
From the server logs, I found autocacuum details for my toast table
(pg_toast_27236):
{
"category": "PostgreSQLLogs",
"operationName": "LogEvent",
"properties": {
"errorLevel": "LOG",
"message": "2024-06-19 17:45:02 UTC-66731911.22f2-LOG: automatic
vacuum of table
\"0ecf0241-aab3-45d5-b020-e586364f810c.pg_toast.pg_toast_27236\":
index scans: 1
pages: 0 removed, 27544451 remain, 0
skipped due to pins, 27406469 skipped frozen
tuples: 9380 removed, 819294 remain, 0
are dead but not yet removable, oldest xmin: 654973054
buffer usage: 318308 hits, 311886
misses, 2708 dirtied
avg read rate: 183.934 MB/s, avg write
rate: 1.597 MB/s
system usage: CPU: user: 1.47 s,
system: 1.43 s, elapsed: 13.24 s",
"processId": 8946,
"sqlerrcode": "0",
"timestamp": "2024-06-19 17:45:02.564 UTC"
},
"time": "2024-06-19T17:45:02.568Z"
}
Best regards,
Manuel
From: Achilleas Mantzios
mailto:a.mantz...@cloud.gatewaynet.com>>
Sent: 20 June 2024 19:10
To:
pgsql-general@lists.postgresql.org
Subject: Re: Autovacuum, dead tuples and bloat
You don't often get email from
a.mantz...@cloud.gatewaynet.com. Learn
why this is important
Στις 20/6/24 19:46, ο/η Shenavai, Manuel έγραψε:
Hi everyone,
we can see in our database, that the DB is 200GB of size, with 99% bloat. After
vacuum full the DB decreases to 2GB.
DB total size: 200GB
DB bloat: 198 GB
DB non-bloat: 2GB
We further see, that during bulk updates (i.e. a long running transaction), the
DB is still growing, i.e. the size of the DB growth by +20GB after the bulk
updates.
My assumption is, that after an autovacuum, the 99% bloat should be available
for usage again. But the DB size would stay at 200GB. In our case, I would only
expect a growth of the DB, if the bulk-updates exceed the current DB size (i.e.
220 GB).
How could I verify my assumption?
I think of two possibilities:
1. My assumption is wrong and for some reason the dead tuples are not
cleaned so that the space cannot be reused
2. The bulk-update indeed exceeds the current DB size. (Then the growth is
expected).
Your only assumption should be the official manual, and other material such as
books, articles from reputable sources, even reading the source as a last
resort could be considered.
For starters : do you have autovacuum enabled ? If not, then enable this.
Then monitor for vacuum via pg_stat_user_tables, locate the tables that you
would expect vacuum to have happened but did not, then consider autovacuum
tuning.
Watch the logs for lines such as :
dead row
versions cannot be removed yet, oldest xmin:
those
are held from being marked as removed, due to being visible by long running
transactions. Monitor for those transactions.
You
have to monitor (if this is the case) about autovacuum being killed and no
Re: Autovacuum, dead tuples and bloat
On 6/21/24 12:31, Shenavai, Manuel wrote: Hi, Thanks for the suggestions. I found the following details to our autovacuum (see below). The related toast-table of my table shows some logs related the vacuum. This toast seems to consume all the data (27544451 pages * 8kb ≈ 210GB ) Those tuples(pages) are still live per the pg_stat entry in your second post: "n_dead_tup": 12, "n_live_tup": 819294 So they are needed. Now the question is why are they needed? 1) All transactions that touch that table are done and that is the data that is left. 2) There are open transactions that still need to 'see' that data and autovacuum cannot remove them yet. Take a look at: pg_stat_activity: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW and pg_locks https://www.postgresql.org/docs/current/view-pg-locks.html to see if there is a process holding that data open. Any thoughts on this? Best regards, Manuel -- Adrian Klaver adrian.kla...@aklaver.com
Re: Replication using mTLS issue
Hi Tom, thanks for the response! So the same user is able to connect using a non replication connection using the same mtls certificate and pg_ident.conf map. So it seems like the cert & map are working for this user. hostssl all pgrepmgr_nonprod 100.0.0.0/8 cert map=pgrepmgr_nonprod_map This above seems to be the rule that matched the non replication connection which was successful. I have tried relaxing the pg_hba.conf line to all like you suggested for the username and also for IPs and other combinations, unfortunately nothing was working. I have been sure to use SELECT pg_reload_conf(); to update changes made to the pg_hba.conf. I have additionally used SELECT pg_hba_file_rules(); to verify the rules are showing up as expected from the live DB perspective. Since non replication connections are working, and the only change to HBA conf for the replication connection is just all -> replication , it seems like it should be matching. Any other suggestions? Thanks, Drew. On Fri, Jun 21, 2024 at 11:46 AM Tom Lane wrote: > Drew Zoellner writes: > > Hi Postgres team, I’m receiving an issue matching pg_hba rules that I > can’t > > seem to sort out. I am trying to use mtls certificate authentication for > > physical replication connections but keep receiving the following error… > > > pg_receivewal: error: FATAL: no pg_hba.conf entry for replication > > connection from host "100.84.12.223", user "pgrepmgr_nonprod", SSL on > > > My pg_hba.conf file contains > > hostssl replication pgrepmgr_nonprod 100.0.0.0/8 cert > map=pgrepmgr_nonprod_map > > Hm, the match failure must be on user name. What certificate are you > using on the client side, and what user name does pgrepmgr_nonprod_map > map it to? Does it succeed if you weaken the hba entry to > > hostssl replication all 100.0.0.0/8 cert map=pgrepmgr_nonprod_map > > > Is cert authentication supported for replication connections? > > Should be. But you might find it easier to debug the auth failure > in a non-replication context, ie add > > hostssl all pgrepmgr_nonprod 100.0.0.0/8 cert > map=pgrepmgr_nonprod_map > > and then see if you can connect with the same credentials from psql > or your favorite other client. > > BTW, don't forget you have to signal the postmaster to reload > configuration after any change in these files. > > regards, tom lane >
