extension uuid-ossp installation in database cluster

[kushwahashiv]

Hi I tried to configure extension uuid-ossp in database yml https://github.com/zalando/postgres-operator/blob/master/manifests/minimal-postgres-manifest.yaml but when the cluster was created I don't see the extension installed in postgres default database

Is something missing below here or below one is the right way to configure this extension.

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: acid-minimal-cluster
  namespace: default
spec:
  teamId: "acid"
  volume:
    size: 1Gi
  numberOfInstances: 2
  users:
    zalando:  # database owner
    - superuser
    - createdb
    foo_user: []  # role for application foo
  databases:
    foo: zalando  # dbname: owner
  preparedDatabases:
    bar:
      defaultUsers: true
      extensions:
        uuid-ossp: public
  postgresql:
    version: "12"

/shiv

[Jan-M]

It looks right to me, we quickly skimmed the code and think this should work.

Can you see any errors in Postgres log or in Operator log?

[kushwahashiv]

@Jan-M No I didn't find any such info. I'm pretty sure I checked that.

[mseiwald]

We have a somewhat different but related (?) issue. The extension uuid-ossp seems to be correctly installed and shows up under \dx but the associated functions (e.g. uuid_generate_v4) do not show up under \df and using them in an SQL statement leads to an error. Only the postgres user can see and execute the function. Is there a way to get the extension installed also for the auto-generated (reader/writer/owner) users in addition to postgres?

[mseiwald]

fyi: The problem for me was that the extension was installed into the public schema which is not part of the search_path of the user auto-created by postgres-operator (reader/writer/owner) and therefore not visible. Using the fully qualified name (e.g. public.uuid_generate_v4) solved it for us.