Make inclusion of schema 'public' in search_path configurable

[boonen]

Since version 1.6.0 of the Postgres Operator the public schema is not part of the search_path of default users of a prepared database (also see issue #1129). This change of behaviour causes trouble when using the postgis extension. Many 3rd party applications assume that the functions are installed in a schema that is part of the search path (usually public) and don't use the fully qualified name.

A work-around is to set the search_path using an SQL query, but that means that we have to use an extended Spilo image. I'd like to propose to make the inclusion of the public schema configurable via the Custom Resource Definition so that the user can decide for each cluster whether it is necessary to expand the search_path of the default users or not.

---

Please, answer some short questions which should help us to understand your problem / question better?

• Which image of the operator are you using? registry.opensource.zalan.do/acid/postgres-operator:v1.6.0
• Where do you run it - cloud or metal? Kubernetes or OpenShift? GKE
• Are you running Postgres Operator in production? yes
• Type of issue? feature request

[boonen]

Temporary work-around: it is possible to add the public schema to the search_path by adding the schema to the preparedDatabases and not creating default users for this schema. E.g. see the snippet of a CR manifest below:

  preparedDatabases:
    mydb:
      defaultUsers: true
      extensions:
        postgis: public
        uuid-ossp: public
        pg_partman: public
      schemas:
        bar:
          defaultUsers: true
        public:
          defaultUsers: false

[FxKu]

Good to know one can use this workaround. We should document it. It's true many extensions and users work with the public schema, although it's regarded as bad practice by postgres DBAs. Therefore, I'm not sure if we need yet another config option.

[synchris]

Many frameworks work with search_path and break the compatibility with those frameworks.