[bitnami/postgresql] permission denied for function pg_ls_waldir

[wroge]

Name and Version

bitnami/postgres

What architecture are you using?

None

What steps will reproduce the bug?

Error in Postgres container:

permission denied for function pg_ls_waldir

Error in Metrics container:

ts=2023-10-16T09:48:46.558Z caller=collector.go:199 level=error msg="collector failed" name=wal duration_seconds=0.01408122 err="pq: permission denied for function pg_ls_waldir"

Are you using any custom parameters or values?

No response

What is the expected behavior?

This command should be executed in init container by default.

GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO <User>

What do you see instead?

User doesn't have the permission to use the function.

Additional information

No response

[javsalgar]

Hi,

Could you share the values and steps to reproduce the issue?

[wroge]

These are my values, nothing special. The database user (not admin) simply does not have the permission to use the function.

# postgresql
postgresql:
  auth:
    existingSecret: ...
    database: ...
    username: ...
  architecture: standalone
  serviceAccount:
    create: true
    automountServiceAccountToken: false
  tls:
    enabled: true
    autoGenerated: true
  metrics:
    enabled: true
    prometheusRule:
      enabled: false
    serviceMonitor:
      enabled: true
      labels:
        release: prometheus
    resources:
      limits:
        cpu: 300m
        memory: 64Mi
      requests:
        cpu: 1m
        memory: 16Mi
    containerSecurityContext:
      enabled: true
      runAsUser: 1001
      privileged: false
      runAsNonRoot: true
      readOnlyRootFilesystem: true
      allowPrivilegeEscalation: false
      capabilities:
        drop:
          - ALL
      seccompProfile:
        type: RuntimeDefault
     ...

[migruiz4]

Hi @wroge,

Thank you for reporting this issue. Although adding permissions for the custom user to execute pg_ls_waldir() would resolve the issue, it is not a good practice to have users with more permissions than necessary.

Instead, I have created an internal task to address this issue by implementing some changes in the chart to create a metrics user with only required permissions to obtain the metrics.

If you would like to contribute to this feature, feel free to send a PR and we will be happy to help with anything needed.

[michalwilk96]

Upvoting this, all bitnami/postgresql helm charts starting from version 13.0.x > are having this issue

[thevops]

I confirm. The issue exists

[thevops]

Quick (and not safe as @migruiz4 wrote) workaround. Execute: GRANT EXECUTE ON FUNCTION pg_ls_waldir TO mycustomuser;

[michalwilk96]

@migruiz4 any plans to fix this major issue? As mentioned earlier - all bitnami/postgresql helm charts starting from version 13.0.x > are having this issue. It's blocker, because no one wants to do a workaround with security gap.

[jouve]

this is part of upstream postgres_exporter (https://github.com/prometheus-community/postgres_exporter/pull/858)

There is flag to control the collectors https://github.com/prometheus-community/postgres_exporter#flags :

[no-]collector.wal Enable the wal collector (default: enabled).

[thedarkside]

@jouve for some reason this doesnt work for me. Still seeing those logs.

metrics:
  enabled: true
  collectors:
    wal: false

Chart version: 13.2.14

[thedarkside]

Oh wow i totally missed that this was a recent change and i was some versions behind. After upgrading to the latest chart it is fixed!

Thank you!

[thevops]

I wanted to try that fix, but I can't revoke access that I previously granted to my user.

I tried: REVOKE EXECUTE ON FUNCTION pg_ls_waldir TO mycustomuser and also REVOKE EXECUTE ON FUNCTION pg_ls_waldir TO public, but both didn't work. Do you know how to revoke this?

[wroge]

You have to REVOKE ... FROM

REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM mycustomuser;

[thevops]

You have to REVOKE ... FROM

REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM mycustomuser;

Ah, sorry. I tried also with "FROM" and still my user can run SELECT pg_ls_waldir();

[thevops]

It's weird. I had to re-create db. It was test env so nothing lost.

[str1k3r]

I would say that the issue is still there with latest chart and disabled metrics. I haven't tried to delete all databases since they are in use.

Any other suggestions?

[masikrus]

GRANT pg_monitor TO readonly_user;

[FraPazGal]

Hi all! Thank you for providing your workarounds for this bug. Seeing the issue activity I have increased the priority of our internal task related to this ticket. That said, if anyone wants to contribute with a fix feel free to create a PR and the team will gladly review it.

[jouve]

since I implemented #21162 , I use this to disable the wal collector:

metrics:
  collectors:
    wal: false

maybe it should be set as a chart default

[cnwaldron]

This also needs to be fixed on postgresql-ha chart as well.

[kakaNo1]

i encountered the same problem

[ecamper]

same problem on postgresql-ha

[Astolincres]

I used ALTER USER postgres_exporter WITH SUPERUSER; and it worked. After that, I also ran GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO postgres_exporter;. Additionally, I learned that pg_monitor is the role that uses pg_ls_waldir, so either the user needs to be a superuser or granted the pg_monitor role.

[meysam81]

+1 for this :pray:

[hiteshnayak305]

:+1: +1

[mike667]

+1 for this 🙏

[zentavr]

since I implemented #21162 , I use this to disable the wal collector:

metrics:
  collectors:
    wal: false

maybe it should be set as a chart default

Don't know why that had not been added to the HA chart version. probably WAL monitoring is required when Posgre is in HA, who knows. As a workaround is to mount the sql sqript inside the postgresql pod and add the permissions.

# ...helm values
extraDeploy:
  - apiVersion: v1
    kind: ConfigMap
    metadata:
      name: pg-initdb-dot-d
    data:
      01-pg_ls_waldir-grant.sql: |
        GRANT EXECUTE ON FUNCTION pg_ls_waldir TO your-user-here;

postgresql:
  extraVolumes:
    - name:  pg-initdb-cm
      configMap:
        name: pg-initdb-dot-d
  extraVolumeMounts:
    - name: pg-initdb-cm
      mountPath: /docker-entrypoint-initdb.d
  extraEnvVars:
    - name: "POSTGRESQL_INITSCRIPTS_USERNAME"
      value: "postgres"
    - name: "POSTGRESQL_INITSCRIPTS_PASSWORD"
      value: "password"