Monit use of pgsql protocol on sockets results in Postgres errors logged

[Former user]

Monit use of pgsql protocol in a Unix socket test which comes straight out of the monit FAQ check results in Postgres errors logged every cycle:

FATAL:  role "root" does not exist

The result is significant pollution of Postgres logs.

This does not occur in my environments in port tests. In my environments, all TCP/IP users are authenticated with passwords (md5 Postgres authentication), but this is not a default Postgres setup.

What is missing in pgsql protocol is an ability to provide database and user parameters, which Postgres usually initializes using PGDATABASE and PGUSER environment variables.

[Massimo Sala]

@Tildeslash I rewrite the pgsql.c source code to read environment variables PGUSER, PGDATABASE, PGPASSWORD.

Now monit connects to the pgsql server and sends the supplied database and credentials (supported authentication type: none, plain password, MD5).

How can I submit the new pgsql.c file to you?

[Tildeslash]

I think database auth should be similar to how it is done with the MySQL test. That is, credentials should be part of the protocol statement in the .monitrc file. We do plan to include libzdb with Monit which would make database testing and also auth simpler.

[Massimo Sala]

@Tildeslash Beware of the neighborhood shiny green grass!

libzdb seems very good, but

1. it is another dependency
2. its goal is pooling … IMHO a little too more than a simple probe to the database server.

I suggest you to mantain the current code to test mysql and postgres, and let the users decide to - optionally - switch to libzdb.

Mysql test: where can I look at the source code to see how the agent pass the parameters to the test function?

[Massimo Sala]

@Tildeslash

Is it good if I change the test function from

void check_pgsql(Socket_T socket)

to

void check_pgsql(Socket_T socket, const char * szDatabase, const char * szUsername, const char * szPassword)

‌

[Massimo Sala]

pgsql with better user authentication

[Tildeslash]

More details about the problem: https://mmonit.com/wiki/Monit/PostgreSQL

We’ll refactor the test into the mini-postgresql client in the same style we did for src/protocols/mysql.c

[Massimo Sala]

Hi

I refactored the code, now monit accepts for pgsql this syntax:

protocol pgsql [database …] [username …] [password …]

If the connection parameters aren’t defined in the check, the code read this environment variables:

PGDATABASE, PGUSER, PGPASSWORD

Rationale:

• some users don’t want to put password in the monit conf file
• these environments variables are used by every postgres-aware tools

‌

The pros of my routine:

• it doesn’t depend on third party libraries;
• in case of errors, the pgsql check return the full postgres error message.

---

Don’t ask me to use GIT or any other CVS to publish my code.

This is a free lunch. You can make a diff with the 5.26.0 source, review the changes, use it.

Best regards, Massimo

‌

[Former user]

I can volunteer for beta testing of any related fixes.

--

The best way to reach me is via the NETE Slack Workspace at https://neteteam.slack.comhttps://neteteam.slack.com/ (native apps / web / mobile). Sign up with a NETE email, ask for guest access with a non-NETE email.

Disclaimer: The information in this e-mail and any of its attachments is confidential and may contain sensitive information. It should not be used by anyone who is not the original intended recipient. If you have received this e-mail in error, please inform the sender and delete it from your mailbox or any other storage devices. NETE shall not accept liability for any statements that are the sender’s own and not expressly made on behalf of NETE by one of its representatives.

Please consider the environment before printing this email.

[Former user]

I beta tested Massimo’s patch and can report success. Here are details of my configuration:

/etc/monit.d/postgres-12.conf

check process Postgres with pidfile /var/lib/pgsql/12/data/postmaster.pid
  start program = "/usr/bin/systemctl start postgresql-12"
  stop program = "/usr/bin/systemctl stop postgresql-12"
# Monit v5.26.1 patch switches authentication to use `PGUSER` (`postgres` by default), PGDATABASE, PGPASSWORD
# Monit v5.26.0: Postgres would log `FATAL:  role "root" does not exist` every cycle (30 seconds)
  if failed unixsocket /var/run/postgresql/.s.PGSQL.5432 protocol pgsql then restart
# Monit v5.26.0: Postgres would log `FATAL:  password authentication failed for user "root"` every cycle (30 seconds)
# if failed port 5432 protocol pgsql for 20 cycles then restart
# Tolerate down state for up to 10 min to allow for manual restarts
  if 2 restarts within 30 cycles then unmonitor

‌

/var/lib/pgsql/12/data/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Trust Postgres system user to connect locally
local   all             postgres                                trust

# Peer authentication for local server users only, connecting via Unix domain socket connections without a password.
# Obtains the OS user name and checks if it matches the requested database user name.
local   all             all                                     peer

# IPv4 local connections: disabled to support login as system accounts over a tunnel
# Local server users can connect over TCP/IP using "-h localhost" option of "psql" or "pg_dump".
#host    all             all             127.0.0.1/32            ident

# IPv6 local connections: disabled to support login as system accounts over a tunnel
# Local server users can connect over TCP/IP using "-h localhost" option of "psql" or "pg_dump".
#host    all             all             ::1/128                 ident

# SCRAM-SHA-256 authentication for all remote and tunnel users
host    all             all             all                     scram-sha-256

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

‌

[Tildeslash]

fix Issue #715:

The PostgreSQL protocol test was extended with username, password and database options. Example:

if failed port 5432 protocol pgsql username "myusername" password "123456" database "test" then alert

Monit will perform full authentication (curently only 'password' and 'md5' methods are supported). The previous Monit version used hardcoded user=root and database=root, which may trigger thousands of messages like this in the postgresql log a day: root@root FATAL: password authentication failed for user "root" root@root DETAIL: Role "root" does not exist.

If no username option is set, monit will keep using the hardcoded credentials (for backward compatibility).

Note(!): this patch is initial implementation and is pending testing

→ <<cset 6f1f966e67b3>>