Metricbeat - PostgreSQL modlue

[stevemw (Steve)]

We are trying to use the PostgreSQL module for metricbeat (v6.6.2), and we are having a problem with authentication. My Postgres module is configured as below:

• module: postgresql
  enabled: true
  metricsets:
  • database
  • bgwriter
  • activity
  • statement
    period: 10s
    hosts: ["postgres://localhost:5432/"]
    username: "username"
    password: "password"

The error we keep getting is "Database 'username' does not exist. However, if I comment the username and password out, the error I get is "Role 'root' does not exist. Has anyone seen this previously? I am currently trying to convince my DBA's that we need a proper postgres role for this, but they are saying I'm crazy. Can anyone give me details on how the Postgres user/role auth should be setup?

EDIT: We have tried all types of hostnames/IP's/clusternames in lue of localhost, they all behave the same

[ruflin]

Could you try to format your config above with 3 ticks before and after. This makes it possible to check if there are potentially some indentation issue.

An other approach you could take to test this is using the connection string with the username in hosts: https://godoc.org/github.com/lib/pq#hdr-Connection_String_Parameters

I looked out our test suite on what we do there but unfortunately we have the same names for the database and username, so in case something is wrong we don't detect it at the moment

[Mario_Castro]

Hi @stevemw

I have just tried your config and it works. Maybe you can give us more info like the Postgres version? I have tried with 9.5.3 and Metricbeat 6.6.2

Also, it seems that you are using SSL to connect to your Postgres instance, can you confirm this?

As far as I can see, if you don't have any user in your config, it will try to connect with the logged in user for that account. Also, double check that you actually don't have a root role in your Postgres. If you don't, create it and check. Just in case

[stevemw (Steve)]

No, we are not using SSL. I can make it work if I call out each database name specifically, which is disappointing because that makes it difficult to scale. The below works:

hosts: ["postgres://localhost:5432/db1","postgres://localhost:5432/db2"]
username: "username"
password: "password"

If I don't specify the database name, I get the error described above. I'd love to be able to do something like postgres://localhost:5432/*, but this doesn't seem to work either. This happens on two different postgres instances, one is v11 and the other v9.5.

[Mario_Castro]

Hi again @stevemw
About the SSL thing, I was asking because it's weird that your connection works with that config. Your host must look like this hosts: ["postgres://localhost:5432?sslmode=disable". With ?sslmode=disable at the end

As per Postgres docs, if you don't use a database name in the connection uri, postgres defaults to user name https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS If you think that using * is a nice feature, just file us a new enhancement request in Github https://github.com/elastic/beats but the current way to go is to use different hosts, one for each database.