Using a non-default schema on a custom postgres DB

[cyrill.lippuner]

Hi, I have been using the local docker setup for fusionauth with a local postgres db until now. Now I wanted to switch to a hosted DB solution, where I do not want to give the root passwords to the fusionauth app, but instead set up the DB schema manually. This worked so far without any problems, but I was wondering whether I could install the DB schema as a non-default schema instead of using the public schema which is provided by the DB at launch?

I was able to set it up and create a user, but the connection url jdbc:postgresql://db:5432/fusionauth and the config files do not provide the possibility to select a specific schema on the DB and default automatically to public.

Such as

CREATE SCHEMA my_fusionauth_schema
    AUTHORIZATION postgres;

ALTER DEFAULT PRIVILEGES IN SCHEMA my_fusionauth_schema
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES TO fusionauth;

Thank you for your answer ansd clarifications.

Cyrill

[dan]

Hmm. I don't know. Is there a way to specify the schema on the JDBC URL?

https://stackoverflow.com/a/4820666/203619 looks promising, that's what I'd try.

[cyrill.lippuner]

This is something I have already tried ad did not work. At least not with my setup. I have changed my setup and use now two databases instead of two schemas.

[dan]

Excellent, thanks for looping back. If you'd like, feel free to file an issue about supporting non public postgresql schemas: https://github.com/fusionauth/fusionauth-issues/issues

[jackehedaya]

I was able to get this working with:

jdbc:postgresql://db:5432/mydb?currentSchema=auth