[Feature Request] Custom Schemas For Auto-Setup (Postgres)

[ghost]

Is your feature request related to a problem? Please describe.

We use Supabase (i.e., built on top of Postgres) as our database provider and backend-as-a-service. As part of their service, they automatically backup (for a price) the postgres database. Currently, we are able to set the database using docker-compose and changing our docker-compose.yml to:

temporal:
    container_name: temporal
    ...
    environment:
      - DB=postgresql
      - DBNAME=postgres
      - VISIBILITY_DBNAME=postgres
      - DB_PORT=5432
      - POSTGRES_USER=postgres
      - POSTGRES_PWD=
      - POSTGRES_SEEDS=db.<supabase-project-id>.supabase.co
      ...

The issue we're facing is that we need to have everything inside of the postgres database, but the auto-setup.sh automatically uses the public schema inside of whatever database is set, without the option to override this via an environment variable set in the docker-compose.yml. We need to be able to set the schema of the temporal database and the temporal visibility database.

Describe the solution you'd like

I would like to be able to pass the environment variables DB_SCHEMA and VISIBILITY_DB_SCHEMA, or something like them (such as just SCHEMA and VISIBILITY_SCHEMA) in the docker-compose.yml file.

These two variables would be passed down to the auto-setup script, and would execute the named schemas when the auto-setup script runs the temporal-sql-tool. It looks like, from here, that the commands would need to be changed to look something like:

SQL_DATABASE=temporal ./temporal-sql-tool --ca search_path=temporal setup-schema -v 0.0

Additional context

It also looks like this change would need to be reflected in the server itself, I just don't know enough about Docker to give nay suggestion. The earlier link seems to suggest it, but I'm just not sure.

Edit 1

From further research, it just looks like there would need to be a way to pass down the --ca flag to the temporal-sql-tool, but I could be wrong. I also saw that the connectAttributes for temporal and visibility will likely need to be changed to reflect the same search_path=SCHEMA_NAME_HERE, but I'm not sure how to incorporate that into this mix.