[BUG]: hardcoded schema name for Postgres migrations

[tgranqvist]

Describe the bug I am running Activepieces in Docker, my composefile spins up a Postgres server as well. I tried using a custom schema name activepieces in the database, but it fails on the migrations. E.g. 1677286751592-billing.ts has hardcoded the schema public when dropping all the indexes. Same goes for all the migration files I checked, they all hardcode the schema to public.

js await queryRunner.query( 'DROP INDEX "public"."idx_plan_stripe_customer_id"', )

To Reproduce

1. Set up a Postgres database with a custom schema
2. Add a user for Activepieces
3. Alter the DB user for Activepieces to use that schema as default: ALTER ROLE activepieces SET search_path TO activepieces;
4. Configure Docker container/composefile to use above DB user
5. Start activepieces
6. See error

Expected behavior I expect the container to start and do all the migrations. I does create tables, as they are not prefixed with hardcoded public scheme.

Additional context SQL to create user, schema, grant access, and set default schema for said user sql CREATE USER activepieces WITH ENCRYPTED PASSWORD '1234'; CREATE SCHEMA activepieces; GRANT ALL ON SCHEMA activepieces TO activepieces; ALTER ROLE activepieces SET search_path TO activepieces; These tables are created in the correct schema before the migration failure. So the database setup works.

[abuaboud]

Hi @tgranqvist

Interesting bug, Let me check typeorm docs and if there is way to support multiple schemas.

[tgranqvist]

The (to me) obvious solution is that it shouldn't specify the schema at all. As I could see, tt doesn't for anything else, neither creating nor dropping tables or even creating indices. Only when dropping an index it seems to prepend the schema, hardcoded to public. Bug, oversight or intentional, I can't say. Thanks for having a look!

And maybe stating the obvious here: I'm not trying to use multiple schemas with one Activepiece installation. Just another one than the default public.

[abuaboud]

Hi @tgranqvist

I understand, I think the main problem is that typeorm seems designed to generate schema for one environment and not for configurable environments which is the case in open source software, one possible way is to pass the schema as environment variable and use it in their all entity manager and then remove public. from all migration.

I will try to take look soon, I am more than happy if you can contribute but to be honest my hands are full and trying to catch up with other items in the backlog.

Thank you, Mo.

[tgranqvist]

Hi @abuaboud

Really, this is not a priority for me so don't worry. Just leave it on the back burner for now. Just wanted to report the inconsistency. Anyway, this is more a problem with typeorm than with Activepieces. Haven't worked with it before, but will check if I can make a minimal example and perhaps file a bug with them.

[jasonfill]

We ran into this as well. My work around was as follows (Postgres):

1. Create a new schema activepieces to isolate all Activepieces data from our main app.
2. Create a new user so Activepieces could only interact with it's schema and not unintentionally get access to our other data.

CREATE USER activepieces WITH PASSWORD '--your secure password --';
CREATE SCHEMA activepieces;

3. Set the search path for this user to just the activepieces schema. This way the activepieces user will always just look in the activepieces schema

ALTER USER activepieces SET search_path TO activepieces;

4. Find all the usages of public. and "public." in the app (all were in migrations) and remove the references.

5. Modify all the grants for the new Activepieces user so they can fully interact with the tables in the new schema.

I will post a PR for this in the coming days, but that will fix the issue. Going forward it would be suggested that all migrations are schema independent and recommended that search_path be used.

[github-actions[bot]]

⚠️COMMENT VISIBILITY WARNING⚠️

Comments on closed issues are hard for our team to see. If this issue is continuing with the latest stable version of Activepieces, please open a new issue that references this one.