Collector failed: pq: permission denied for function pg_ls_waldir on PG v16.6

[kakuiho]

What did you do?

Start postgres_exporter in docker as following instruction

What did you expect to see?

Everything should be ok, and not error log

What did you see instead? Under which circumstances?

docker logs -f postgres_exporter found error log

time=2024-12-09T03:46:47.575Z level=ERROR source=collector.go:201 msg="collector failed" name=wal duration_seconds=0.008162135 err="pq: permission denied for function pg_ls_waldir"

Create a monitoring account:

CREATE USER postgres_exporter WITH PASSWORD 'secret';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

-- If deploying as non-superuser (for example in AWS RDS), uncomment the GRANT
-- line below and replace <MASTER_USER> with your root user.
-- GRANT postgres_exporter TO <MASTER_USER>;
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

Environment

docker inspect quay.io/prometheuscommunity/postgres-exporter| jq -r '.[].RepoDigests[]' | awk -F@ '{print $2}'
sha256:6999a7657e2f2fb0ca6ebf417213eebf6dc7d21b30708c622f6fcb11183a2bb0

• System information:

  Linux 6.8.0-45-generic x86_64

• postgres_exporter version:

postgres_exporter, version 0.16.0 (branch: HEAD, revision: a324fe37bca5193a293118b940b3df7ab3a8505c)
  build user:       root@6164b8af30fd
  build date:       20241110-21:09:32
  go version:       go1.23.3
  platform:         linux/amd64
  tags:             unknown

• postgres_exporter flags:

insert list of flags used here

• PostgreSQL version:

  16.6

• Logs:

time=2024-12-09T03:46:32.588Z level=ERROR source=collector.go:201 msg="collector failed" name=wal duration_seconds=0.020872977 err="pq: permission denied for function pg_ls_waldir"

[kakuiho]

It is easy to walk around or fix it, grant pg_ls_waldir permission to postgres_exporter:

GRANT ALL ON FUNCTION pg_catalog.pg_ls_waldir() TO postgres_exporter;