Set Schema for Postgres application database

Get Help
1

Hi I'm trying to migrate my local Metabase H2 application database to production but would like to specify the destination schema so my "public" schema doesn't get flooded w/ Metabase tables.

I see that you can set the "database" via MB_DB_DBNAME environment variable but I'm limited to only the default 'postgres' database and would like to specify a schema instead. Is this possible?

2

Hi @lukep
You can define a non-default schema in the connection string with currentSchema=myschema, ex:

jdbc:postgresql://db.example.com:5432/mydb?user=dbuser&password=dbpassword&currentSchema=myschema

https://www.metabase.com/docs/latest/configuring-metabase/environment-variables#mb_db_connection_uri

1 Like
3

Thank you - this fixed the issue for me!

4

Hi all,
This solution is not working for me.
I'm using the Docker image "metabase:latest" with the following environment variable:

MB_DB_CONNECTION_URI=jdbc:postgresql://REDACTED_HOSTNAME:5432/REDACTED_DB_NAME?user=metabase_user&password=REDACTED_PASSWORD&currentSchema=metabase

Unfortunately I get errors during the server startup that relate to the migrations running on the "public" schema. For example:

INFOCREATE TABLE public.activity (
INFO    id integer NOT NULL,
INFO    topic character varying(32) NOT NULL,
INFO    "timestamp" timestamp with time zone NOT NULL,
INFO    user_id integer,
INFO    model character varying(16),
INFO    model_id integer,
INFO    database_id integer,
INFO    table_id integer,
INFO    custom_id character varying(48),
INFO    details text NOT NULL
INFO)]
....
ERRORCaused by: org.postgresql.util.PSQLException: ERROR: permission denied for schema public
.....
INFO2024-03-03 09:21:54,230 INFO metabase.core :: Metabase Shutting Down ...

I even set the Postgres role default searchpath to "metabase".

It seems like I got the configuration wrong somehow or that there are hard-coded references to the "public" schema in metabases's code or its dependencies.

I'd love help with this.
Thanks!