Re: pg_stat_activity.client_addr sometimes null for host connections

> On 04/05/2023 18:31 CEST Rui DeSousa <rui(at)crazybean(dot)net> wrote:
>
> > On May 4, 2023, at 6:19 AM, Erik Wienhold <ewie(at)ewie(dot)name> wrote:
> >
> > I have a process that regularly queries pg_stat_activity for user connections.
> > Today I noticed an error because sometimes client_addr is null for some user
> > connections. pg_hba.conf only allows host connections. So client_addr should
> > not be null according to [0]. pg_stat_activity is queried as superuser who
> > should see client_addr for all connections, not just his own.
> >
> > This is on v12.14 on Windows Server (regular EnterpriseDB install). Is this a
> > known issue or just an undocumented corner case?
> >
> > [0] https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
> >
> > —
> > Erik
> >
> >
>
> Are those local connections, as those will not have a client_addr? What does
> your pg_hba look like?

Connection type is host for all entries (as I already mentioned). The users
don't even have access to the server to open a local connection.

pg_hba_file_rules:

line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+------+---------------+--------------------+--------------+-----------------------------------------+-------------+---------+-------
80 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
82 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
87 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
88 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
90 | host | {postgres} | {barman} | 10.69.133.98 | 255.255.255.255 | md5 | |
91 | host | {postgres} | {streaming_barman} | 10.69.133.98 | 255.255.255.255 | md5 | |
92 | host | {replication} | {streaming_barman} | 10.69.133.98 | 255.255.255.255 | md5 | |
96 | host | {all} | {all} | 10.49.24.0 | 255.255.255.0 | md5 | |
97 | host | {all} | {all} | 10.49.40.0 | 255.255.255.0 | md5 | |
98 | host | {all} | {all} | 10.49.41.0 | 255.255.255.0 | md5 | |
99 | host | {all} | {all} | 10.49.42.0 | 255.255.255.0 | md5 | |
100 | host | {all} | {all} | 10.49.43.0 | 255.255.255.0 | md5 | |
101 | host | {all} | {all} | 10.49.44.0 | 255.255.255.0 | md5 | |
102 | host | {all} | {all} | 10.49.45.0 | 255.255.255.0 | md5 | |
103 | host | {all} | {all} | 10.49.46.0 | 255.255.255.0 | md5 | |
104 | host | {all} | {all} | 10.49.47.0 | 255.255.255.0 | md5 | |
107 | host | {all} | {all} | 10.63.133.0 | 255.255.252.0 | md5 | |
110 | host | {all} | {all} | 10.70.64.0 | 255.255.254.0 | md5 | |
111 | host | {all} | {all} | 10.70.66.0 | 255.255.255.0 | md5 | |
112 | host | {all} | {all} | 10.70.67.0 | 255.255.255.0 | md5 | |
113 | host | {all} | {all} | 10.70.68.0 | 255.255.255.0 | md5 | |
114 | host | {all} | {all} | 10.70.69.0 | 255.255.255.0 | md5 | |
115 | host | {all} | {all} | 10.70.70.0 | 255.255.255.0 | md5 | |
116 | host | {all} | {all} | 10.70.71.0 | 255.255.255.0 | md5 | |
117 | host | {all} | {all} | 10.70.73.0 | 255.255.255.0 | md5 | |
118 | host | {all} | {all} | 10.70.74.0 | 255.255.255.0 | md5 | |
119 | host | {all} | {all} | 10.70.75.0 | 255.255.255.0 | md5 | |
120 | host | {all} | {all} | 10.70.90.0 | 255.255.255.0 | md5 | |
121 | host | {all} | {all} | 10.70.99.0 | 255.255.255.0 | md5 | |
122 | host | {all} | {all} | 10.70.100.0 | 255.255.255.0 | md5 | |
123 | host | {all} | {all} | 10.120.16.0 | 255.255.255.0 | md5 | |
124 | host | {all} | {all} | 10.125.136.0 | 255.255.255.0 | md5 | |
127 | host | {all} | {***REDACTED***} | 10.181.161.0 | 255.255.255.0 | md5 | |
(33 rows)

The last change and reload of pg_hba.conf was on 2023-04-24. The file has not
been modified since.

I got a log for the error and it occurs as early as 2022-03-01. The server was
running v12.10 at that time.

The first 4 columns are from pg_stat_activity extracted from the log's detail
message.

Notice the short interval between state_change and log_time. The clients all
happen to just change state when querying pg_stat_activity. Could there be
a race condition between executing pg_stat_get_activity(null) and the server
cleaning up closed connections? Closed connections still appear but the socket
address is no longer available?

pid | usesysid | datid | state_change | log_time | log_time - state_change
-------+----------+----------+-------------------------------+----------------------------+-------------------------
11436 | 9034571 | 13643185 | 2022-03-01 14:40:18.193012+01 | 2022-03-01 14:40:18.198+01 | 00:00:00.004988
5028 | 2142762 | 16394 | 2022-05-24 11:38:18.410533+02 | 2022-05-24 11:38:18.415+02 | 00:00:00.004467
3192 | 16393 | 14884324 | 2022-07-22 09:34:18.434034+02 | 2022-07-22 09:34:18.438+02 | 00:00:00.003966
9628 | 34349 | 13805996 | 2022-08-02 07:23:29.302691+02 | 2022-08-02 07:23:29.313+02 | 00:00:00.010309
9596 | 16393 | 13805996 | 2022-08-05 16:08:29.507613+02 | 2022-08-05 16:08:29.512+02 | 00:00:00.004387
6344 | 16393 | 13805996 | 2022-10-20 13:47:28.843096+02 | 2022-10-20 13:47:28.848+02 | 00:00:00.004904
3752 | 16393 | 13805996 | 2022-10-20 14:32:29.311198+02 | 2022-10-20 14:32:29.321+02 | 00:00:00.009802
9784 | 2142762 | 13805996 | 2022-10-20 14:37:29.766515+02 | 2022-10-20 14:37:29.773+02 | 00:00:00.006485
5456 | 16130084 | 16136740 | 2022-11-21 21:45:29.319827+01 | 2022-11-21 21:45:29.324+01 | 00:00:00.004173
5456 | 16130084 | 16136740 | 2022-11-21 21:46:29.278565+01 | 2022-11-21 21:46:29.299+01 | 00:00:00.020435
5456 | 16130084 | 16136740 | 2022-11-21 21:47:29.385187+01 | 2022-11-21 21:47:29.393+01 | 00:00:00.007813
5456 | 16130084 | 16136740 | 2022-11-21 21:48:29.295469+01 | 2022-11-21 21:48:29.3+01 | 00:00:00.004531
9764 | 16130084 | 16136740 | 2022-11-21 22:09:29.858269+01 | 2022-11-21 22:09:29.863+01 | 00:00:00.004731
9764 | 16130084 | 16136740 | 2022-11-21 22:10:29.506984+01 | 2022-11-21 22:10:29.511+01 | 00:00:00.004016
9764 | 16130084 | 16136740 | 2022-11-21 22:11:29.538942+01 | 2022-11-21 22:11:29.544+01 | 00:00:00.005058
9764 | 16130084 | 16136740 | 2022-11-21 22:12:29.43297+01 | 2022-11-21 22:12:29.438+01 | 00:00:00.00503
9764 | 16130084 | 16136740 | 2022-11-21 22:13:29.606039+01 | 2022-11-21 22:13:29.611+01 | 00:00:00.004961
8360 | 16393 | 16978159 | 2023-02-03 16:07:29.660733+01 | 2023-02-03 16:07:29.665+01 | 00:00:00.004267
8360 | 16393 | 16978159 | 2023-02-03 16:08:29.660019+01 | 2023-02-03 16:08:29.665+01 | 00:00:00.004981
8360 | 16393 | 16978159 | 2023-02-03 16:09:29.542798+01 | 2023-02-03 16:09:29.547+01 | 00:00:00.004202
8360 | 16393 | 16978159 | 2023-02-03 16:10:29.511686+01 | 2023-02-03 16:10:29.517+01 | 00:00:00.005314
8360 | 16393 | 16978159 | 2023-02-03 16:11:29.727772+01 | 2023-02-03 16:11:29.737+01 | 00:00:00.009228
8872 | 19920816 | 15526205 | 2023-03-15 18:11:29.567727+01 | 2023-03-15 18:11:29.587+01 | 00:00:00.019273
5844 | 19920816 | 15526205 | 2023-03-15 18:14:29.576651+01 | 2023-03-15 18:14:29.597+01 | 00:00:00.020349
1360 | 2142762 | 16978159 | 2023-03-16 13:30:31.409793+01 | 2023-03-16 13:30:31.418+01 | 00:00:00.008207
4844 | 4324803 | 16978159 | 2023-03-21 09:26:33.968217+01 | 2023-03-21 09:26:33.978+01 | 00:00:00.009783
2008 | 16393 | 16978159 | 2023-03-22 14:33:32.389439+01 | 2023-03-22 14:33:32.398+01 | 00:00:00.008561
5136 | 19920816 | 17673077 | 2023-03-24 22:52:32.635385+01 | 2023-03-24 22:52:32.644+01 | 00:00:00.008615
5148 | 19920816 | 17673077 | 2023-03-24 22:59:33.305081+01 | 2023-03-24 22:59:33.315+01 | 00:00:00.009919
3512 | 19920816 | 17673077 | 2023-03-24 23:00:34.104535+01 | 2023-03-24 23:00:34.111+01 | 00:00:00.006465
2728 | 16393 | 16978159 | 2023-04-11 13:05:32.674562+02 | 2023-04-11 13:05:32.681+02 | 00:00:00.006438
4164 | 16393 | 16978159 | 2023-04-11 13:27:32.761902+02 | 2023-04-11 13:27:32.767+02 | 00:00:00.005098
6248 | 19920816 | 16978159 | 2023-04-12 22:01:31.753148+02 | 2023-04-12 22:01:31.761+02 | 00:00:00.007852
6248 | 19920816 | 16978159 | 2023-04-12 22:02:31.830579+02 | 2023-04-12 22:02:31.838+02 | 00:00:00.007421
6248 | 19920816 | 16978159 | 2023-04-12 22:03:32.545472+02 | 2023-04-12 22:03:32.553+02 | 00:00:00.007528
6248 | 19920816 | 16978159 | 2023-04-12 22:04:31.835557+02 | 2023-04-12 22:04:31.842+02 | 00:00:00.006443
7652 | 19920816 | 16978159 | 2023-04-12 22:44:33.142687+02 | 2023-04-12 22:44:33.158+02 | 00:00:00.015313
7652 | 19920816 | 16978159 | 2023-04-12 22:45:32.22895+02 | 2023-04-12 22:45:32.241+02 | 00:00:00.01205
7652 | 19920816 | 16978159 | 2023-04-12 22:46:32.155199+02 | 2023-04-12 22:46:32.162+02 | 00:00:00.006801
7652 | 19920816 | 16978159 | 2023-04-12 22:47:32.226809+02 | 2023-04-12 22:47:32.234+02 | 00:00:00.007191
7652 | 19920816 | 16978159 | 2023-04-12 22:48:32.249175+02 | 2023-04-12 22:48:32.257+02 | 00:00:00.007825
7652 | 19920816 | 16978159 | 2023-04-12 22:49:32.363036+02 | 2023-04-12 22:49:32.37+02 | 00:00:00.006964
7652 | 19920816 | 16978159 | 2023-04-12 22:50:32.167531+02 | 2023-04-12 22:50:32.175+02 | 00:00:00.007469
7652 | 19920816 | 16978159 | 2023-04-12 22:51:32.169011+02 | 2023-04-12 22:51:32.175+02 | 00:00:00.005989
7652 | 19920816 | 16978159 | 2023-04-12 22:52:32.136483+02 | 2023-04-12 22:52:32.145+02 | 00:00:00.008517
7652 | 19920816 | 16978159 | 2023-04-12 22:53:32.247358+02 | 2023-04-12 22:53:32.255+02 | 00:00:00.007642
7652 | 19920816 | 16978159 | 2023-04-12 22:54:32.225536+02 | 2023-04-12 22:54:32.233+02 | 00:00:00.007464
7652 | 19920816 | 16978159 | 2023-04-12 22:55:32.205943+02 | 2023-04-12 22:55:32.214+02 | 00:00:00.008057
7652 | 19920816 | 16978159 | 2023-04-12 22:56:32.313652+02 | 2023-04-12 22:56:32.327+02 | 00:00:00.013348
7652 | 19920816 | 16978159 | 2023-04-12 22:57:32.310341+02 | 2023-04-12 22:57:32.324+02 | 00:00:00.013659
7652 | 19920816 | 16978159 | 2023-04-12 22:58:32.178025+02 | 2023-04-12 22:58:32.188+02 | 00:00:00.009975
5464 | 29593 | 16978159 | 2023-04-13 09:40:32.163192+02 | 2023-04-13 09:40:32.171+02 | 00:00:00.007808
5008 | 29593 | 16978159 | 2023-04-13 12:52:32.133676+02 | 2023-04-13 12:52:32.147+02 | 00:00:00.013324
10072 | 292899 | 16978159 | 2023-04-25 16:02:32.28129+02 | 2023-04-25 16:02:32.289+02 | 00:00:00.00771
2864 | 55697 | 16978159 | 2023-04-27 09:23:32.54067+02 | 2023-04-27 09:23:32.552+02 | 00:00:00.01133
2864 | 55697 | 16978159 | 2023-04-27 09:24:32.503118+02 | 2023-04-27 09:24:32.51+02 | 00:00:00.006882
9956 | 19920816 | 15526205 | 2023-04-27 17:51:33.165037+02 | 2023-04-27 17:51:33.174+02 | 00:00:00.008963
5448 | 19920816 | 15526205 | 2023-04-27 17:58:33.387277+02 | 2023-04-27 17:58:33.396+02 | 00:00:00.008723
2268 | 16393 | 16978159 | 2023-05-03 09:09:33.176452+02 | 2023-05-03 09:09:33.187+02 | 00:00:00.010548
2268 | 16393 | 16978159 | 2023-05-03 09:10:33.171076+02 | 2023-05-03 09:10:33.181+02 | 00:00:00.009924
3160 | 2142762 | 16978159 | 2023-05-03 13:30:31.911463+02 | 2023-05-03 13:30:31.923+02 | 00:00:00.011537
9696 | 292899 | 13643185 | 2023-05-04 08:30:32.816732+02 | 2023-05-04 08:30:32.834+02 | 00:00:00.017268
8004 | 19920817 | 16978159 | 2023-05-04 08:56:32.430163+02 | 2023-05-04 08:56:32.44+02 | 00:00:00.009837
7152 | 19920817 | 16978159 | 2023-05-04 08:57:32.068336+02 | 2023-05-04 08:57:32.076+02 | 00:00:00.007664
7152 | 19920817 | 16978159 | 2023-05-04 08:58:32.346978+02 | 2023-05-04 08:58:32.356+02 | 00:00:00.009022
7152 | 19920817 | 16978159 | 2023-05-04 08:59:32.149638+02 | 2023-05-04 08:59:32.159+02 | 00:00:00.009362
7476 | 19920817 | 16978159 | 2023-05-04 09:05:36.285914+02 | 2023-05-04 09:05:36.295+02 | 00:00:00.009086
5020 | 19920817 | 16978159 | 2023-05-04 09:09:34.760794+02 | 2023-05-04 09:09:34.775+02 | 00:00:00.014206
1208 | 19920817 | 16978159 | 2023-05-04 09:23:32.33564+02 | 2023-05-04 09:23:32.344+02 | 00:00:00.00836
8824 | 19920817 | 16978159 | 2023-05-04 09:30:32.798865+02 | 2023-05-04 09:30:32.81+02 | 00:00:00.011135
9348 | 19920816 | 13318 | 2023-05-04 09:33:33.213291+02 | 2023-05-04 09:33:33.221+02 | 00:00:00.007709
4512 | 19920817 | 16978159 | 2023-05-04 09:56:32.723868+02 | 2023-05-04 09:56:32.732+02 | 00:00:00.008132
3092 | 19920817 | 16978159 | 2023-05-04 09:59:32.762951+02 | 2023-05-04 09:59:32.772+02 | 00:00:00.009049
8684 | 292899 | 16978159 | 2023-05-04 10:15:32.704223+02 | 2023-05-04 10:15:32.712+02 | 00:00:00.007777
8684 | 292899 | 16978159 | 2023-05-04 10:16:32.671539+02 | 2023-05-04 10:16:32.681+02 | 00:00:00.009461
8684 | 292899 | 16978159 | 2023-05-04 10:17:32.779535+02 | 2023-05-04 10:17:32.789+02 | 00:00:00.009465
8956 | 19920817 | 16978159 | 2023-05-04 10:59:32.241457+02 | 2023-05-04 10:59:32.251+02 | 00:00:00.009543
8956 | 19920817 | 16978159 | 2023-05-04 11:00:32.09836+02 | 2023-05-04 11:00:32.109+02 | 00:00:00.01064
10020 | 19920817 | 16978159 | 2023-05-04 11:02:32.120748+02 | 2023-05-04 11:02:32.129+02 | 00:00:00.008252
10020 | 19920817 | 16978159 | 2023-05-04 11:03:33.004381+02 | 2023-05-04 11:03:33.049+02 | 00:00:00.044619
10020 | 19920817 | 16978159 | 2023-05-04 11:04:32.140172+02 | 2023-05-04 11:04:32.15+02 | 00:00:00.009828
10020 | 19920817 | 16978159 | 2023-05-04 11:05:34.367158+02 | 2023-05-04 11:05:34.378+02 | 00:00:00.010842
10020 | 19920817 | 16978159 | 2023-05-04 11:07:54.337009+02 | 2023-05-04 11:07:54.348+02 | 00:00:00.010991
10020 | 19920817 | 16978159 | 2023-05-04 11:08:32.652642+02 | 2023-05-04 11:08:32.663+02 | 00:00:00.010358
10020 | 19920817 | 16978159 | 2023-05-04 11:09:33.219233+02 | 2023-05-04 11:09:33.229+02 | 00:00:00.009767
10020 | 19920817 | 16978159 | 2023-05-04 11:10:32.707746+02 | 2023-05-04 11:10:32.721+02 | 00:00:00.013254
10020 | 19920817 | 16978159 | 2023-05-04 11:11:34.92758+02 | 2023-05-04 11:11:34.954+02 | 00:00:00.02642
10020 | 19920817 | 16978159 | 2023-05-04 11:12:36.175842+02 | 2023-05-04 11:12:36.187+02 | 00:00:00.011158
10020 | 19920817 | 16978159 | 2023-05-04 11:13:35.14939+02 | 2023-05-04 11:13:35.162+02 | 00:00:00.01261
(89 rows)

--
Erik