I follow the instructions on URL below to collect database metrics and display on a Grafana dashboard. I use Prometheus to intercept these metrics, as suggests the tutorial.
After deploying the services postgres_exporter, prometheus and grafana_server on my VPS, and following instructions on above URL content, I am still unable to stablish connection to database. The resulting error log is below.
Aug 29 16:20:12 MyServer postgres_exporter[1888996]: time="2024-08-29T16:20:12-03:00" level=error msg="Error opening connection to database (postgresql://myuser:my_password@remote_server:5432/my_database?sslmode=disable): pq: unknown authentication response: 10" source="postgres_exporter.go:1336"
I found some references on stack overflow, most related to md5 and sha checksum, but I am still unable to acquire the required metrics from desired database.
Any help is appreciated!
The client could not connect to PostgreSQL server due to an unsupported encryption. Likely due to version of scram-sha-256 used by PostgreSQL.
If you are using Azure please read here https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-connect-scram
If you are using AWS please read here https://aws.amazon.com/blogs/database/scram-authentication-in-rds-for-postgresql-13/
For On-Prem server upgrade to scram-sha256 to upgrade an existing installation from md5 to scram-sha-256. you ned to make sure that all client libraries in use are new enough to support SCRAM. If this is a new project that would not be a problem.
The steps will be;
Set password_encryption = 'scram-sha-256' in postgresql.conf.
Make all users set new passwords, and change.
The authentication method specifications in pg_hba.conf to
scram-sha-256.
Again of course, you can change the authentication to MD5 based on your preference. But it will downgrade the security of the application.