Support for using different PostgreSQL schema's

[siepkes]

By default Tolgee will use the public schema and the ee schema in PostgreSQL. Using the public PostgreSQL makes it hard to use Tolgee in PostgreSQL when other application also use the same PostgreSQL instance.

In PostgreSQL a schema is comparable to what is a "database" in MySQL. The "CREATE DATABASE" functionality in PostgreSQL essentially creates an entirely new PostgreSQL installation. This means; With it's own data directory, with it's own processes, requiring it's own back-up configuration (for example WAL-G), requiring it's own high availability setup (for example Patroni), requiring it's own monitoring setup (for example pg_exporter with Prometheus), etc.

There are also other drawbacks to using the public schema. For example the public schema is by default readable and writable for all users in PostgreSQL. Also if multiple applications use the public schema this could also lead to collisions.

Changing the PostgreSQL schema which an application uses is often not that hard. One simply creates a user and schema in PostgreSQL and changes the default schema search path. Like this:

CREATE ROLE "foo" WITH LOGIN PASSWORD "bar";
CREATE SCHEMA "foo";
GRANT ALL PRIVILEGES ON SCHEMA "foo" TO "foo";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "foo" TO "foo";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "foo" TO "foo";
GRANT USAGE ON SCHEMA "foo" TO "foo";
GRANT CREATE ON SCHEMA "foo" TO "foo";

ALTER ROLE "foo" SET search_path = foo,public,pg_catalog;

The last command (ALTER ROLE "foo" SET search_path = foo,public,pg_catalog;) is the magic bit. It ensures the PostgreSQL user foo will always try to do everything in the foo schema instead of the public schema.

This doesn't work for Tolgee because Tolgee has the use of the public schema hard coded in various location. For example in a query like this "select st.time from public.forced_server_date_time st where st.id = 1",. If the query looked like this: "select st.time from forced_server_date_time st where st.id = 1", (i.e. no explicitly public schema reference) it would work.

Removing the explicit public schema references everywhere is quite simple and is also backwards compatible since existing installations simply will continue to use the public schema since it's always in PostgreSQL's default search path. If one patches Tolgee to remove the public schema hard coded references and create a tolgee and ee schema in PostgreSQL this works. However I would like to create a proper patch which would allow one to specify which schema to use. Ideally for both the ee and public schema. However the ee schema might be a bit harder because of how it is used.

Would you folks be open to such a PR? For example by adding an option to the spring boot configuration allowing one to specify the Tolgee schema?

[JanCizmar]

Hello, yes! Feel free to submit such PR! 🚀 Thanks a lot!

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 14 days since being marked as stale.

[siepkes]

Yeah I know, bot, I need to get off my lazy behind and clean-up my patch to upstream it :wink: .

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[VanderElton]

Hello, any updates on this issue?

I had the same problem in my application while using a shared PostgreSQL. I can assist with the PR.

[JanCizmar]

Hi @VanderElton that would be great 🚀. I've reopened this issue and assigned it to you. :) Thanks a lot!

[siepkes]

The reason why I haven't gotten around to creating a PR is because it needs polishing and I haven't had the time to dive in to it.

You need to be able to configure both the schema which the "ee" part uses and the "normal" part. So the main issue being you need to be able to use multiple schema's. Normally I would simply remove all the references to the public schema (to decouple it from the schema) and then use PostgreSQL's search path. But that won't work in this scenario.

So that means you need to either;

• Move all the "ee" stuff in to the "normal" schema.
• Find a way to make annotations like @Table(schema = "tolgee_ee") configurable.
• Something else?

This is the patch I currently use: https://gist.github.com/siepkes/8ca3202e917e7cb57e25b90b3d2a2391