Configure PostgreSQL monitoring for netdata

[jpmckinney]

The prerequisites listed in the Netdata docs are insufficient: https://learn.netdata.cloud/docs/collecting-metrics/databases/postgresql#create-netdata-user

Those steps just yield:

2024-07-11 16:55:39.067 UTC [3939044] postgres@postgres LOG:  provided user name (postgres) and authenticated user name (netdata) do not match
2024-07-11 16:55:39.067 UTC [3939044] postgres@postgres FATAL:  Peer authentication failed for user "postgres"
2024-07-11 16:55:39.067 UTC [3939044] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 5: "local  all     postgres                peer"
2024-07-11 16:55:39.069 UTC [3939045] netdata@postgres FATAL:  role "netdata" is not permitted to log in
2024-07-11 16:55:39.072 UTC [3939046] netdata@postgres FATAL:  password authentication failed for user "netdata"
2024-07-11 16:55:39.072 UTC [3939046] netdata@postgres DETAIL:  User "netdata" has no password assigned.
    Connection matched pg_hba.conf line 12: "host   all     all     127.0.0.1/32        md5"

I don’t see a way in Netdata to configure the default netdata user with a password. I’m not sure if another authentication method needs to be configured in PostgreSQL.

Keep in mind that on ocp23, PostgreSQL is an open port, so we don’t want a passwordless netdata user to be able to login from the internet.

I had started with this:

# https://learn.netdata.cloud/docs/collecting-metrics/databases/postgresql#setup
netdata_sql_user:
  postgres_user.present:
    - name: netdata
    - groups:
      - pg_monitor
    - require:
      - service: postgresql

[dogsbody-josh]

Netdata recommends using the standard Postgres DSN syntax for connections, which handles providing credentials. From that Postgres DSN link for example, the Netdata config would contain similar to:

postgresql://user:secret@localhost

A fuller example for a Postgres Collector job might look similar to this, where 'secret' is the password:

jobs:
  - name: local
    dsn: 'postgresql://netdata:secret@127.0.0.1:5432/postgres'

As a consequence, Postgres users may be created in the standard way with standard password authentication and can be used via the provided DSN syntax to ensure password authentication is enforced.

[jpmckinney]

Okay, I added commit b3825177, modelled after the configuration on ocp23, and deployed it to ocp13, ocp22 and ocp24.

[dogsbody-josh]

The syntax for salt/postgres/files/netdata.conf will need to be altered to match the Netdata recommendations. Mark used an older syntax for the current config.

I have asked Mark earlier today to look into this so he should make the required changes shortly.

[dogsbody-mark]

Further to Josh's notes, I have updated the configuration on ocp23.

The new config is much more compact but includes all of the previous directives and covers all of the databases.

I'm sorry for creating you extra work with this.

If you have any questions, please do let me know.

[jpmckinney]

Thanks, I updated the Salt config and deployed it to ocp13, ocp22 and ocp24.