Patch proposal: New hooks in the connection path

bdrouvotAWS <bdrouvot@amazon.com>

From: "Drouvot, Bertrand" <bdrouvot@amazon.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-06-30T08:01:00Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Add pg_stat_database counters for sessions and session time

Attachments

Hi hackers,

While commit 960869da08 added some information about connections that 
have been successfully authenticated, there is no metrics for 
connections that have not (or did not reached the authentication stage).

Adding metrics about failed connections attempts could also help, for 
example with proper sampling, to:

  * detect spikes in failed login attempts
  * check if there is a correlation between spikes in successful and
    failed connection attempts

While the number of successful connections could also already been 
tracked with the ClientAuthentication_hook (and also the ones that 
failed the authentication) we are missing metrics about:

  * why the connection failed (could be bad password, bad database, bad
    user, missing CONNECT privilege...)
  * number of times the authentication stage has not been reached
  * why the authentication stage has not been reached (bad startup
    packets, timeout while processing startup packet,...)

Those missing metrics (in addition to the ones that can be already 
gathered) could provide value for:

  * security investigations
  * anomalies detections
  * tracking application misconfigurations

In an attempt to be able to provide those metrics, please find attached 
a patch proposal to add new hooks in the connection path, that would be 
fired if:

  * there is a bad startup packet
  * there is a timeout while processing the startup packet
  * user does not have CONNECT privilege
  * database does not exist

For safety those hooks request the use of a const Port parameter, so 
that they could be used only for reporting purpose (for example, we are 
working on an extension to record detailed login metrics counters).

Another option could be to add those metrics in the engine itself 
(instead of providing new hooks to get them), but the new hooks option 
gives more flexibility on how to render and exploit them (there is a lot 
of information in the Port Struct that one could be interested with).

I’m adding this patch proposal to the commitfest.
Looking forward to your feedback,

Regards,
Bertrand