Better support for external postgres instance

[kenske]

I'd like to connect splitpro to an existing postgresql instance, instead of having a separate postgres container just for splitpro.

The first issue I encountered is needing to install pg_cron, which is fine, but then the app expects to have full root access to the instance, which is not an option in a multi-tenant instance.

Proposed solution

• App is able to use a regular DB user to set up schema
• pg_cron is replaced by in-app scheduling

[krokosik]

I'll have to research how to set pg_cron in a way that does not require root. I am not that familiar with pg extensions so help is welcome.

The latter option is a definite no go for me.

[stephan-merkli]

Assuming pg_cron is only used for recurring expenses, one option would be to make recurring expenses configurable and allow the feature to be disabled. This would make it possible to run SplitPro on a standard PostgreSQL instance without requiring pg_cron.

That could simplify installation further and may not be a disadvantage for users who do not need recurring expenses functionality.

[Artur-Su]

Following. please comment when it will be integrated.

[m-vandeneede]

+1 on this. Also trying to set up a shared postgres instances for all my deployments. Giving splitpro superuser permissions to install an extension is not an option.

[krokosik]

Can you check if the current version of the app deploys properly with a shared instance and non-superuser account, provided the following preparation steps have been taken by an admin?

-- create user and splitpro db with proper privileges scoped to the user

-- for pg_cron
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO <app_user>;
GRANT SELECT, REFERENCES ON cron.job TO <app_user>;
GRANT SELECT ON cron.job_run_details TO <app_user>;

on top of setting the configuration options:

shared_preload_libraries = 'pg_cron'
cron.database_name = '<your_splitpro_db>'
cron.timezone = 'UTC'

[Tiago-FM]

I've tried with the said steps as admin however on first boot I still got:

prisma:error Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
prisma:error Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
prisma:error Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
prisma:error Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
prisma:error Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
prisma:error 
Invalid `prisma.$executeRawUnsafe()` invocation:


Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
prisma:error Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
prisma:error Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`
Failed to prepare server [Error [PrismaClientKnownRequestError]: An error occurred while loading instrumentation hook: 
Invalid `prisma.$executeRawUnsafe()` invocation:


Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`] {
  code: 'P2010',
  meta: [Object],
  clientVersion: '6.19.3'
}
[Error [PrismaClientKnownRequestError]: An error occurred while loading instrumentation hook: 
Invalid `prisma.$executeRawUnsafe()` invocation:


Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`] {
  code: 'P2010',
  meta: [Object],
  clientVersion: '6.19.3'
}
 ⨯ unhandledRejection:  [Error [PrismaClientKnownRequestError]: An error occurred while loading instrumentation hook: 
Invalid `prisma.$executeRawUnsafe()` invocation:


Raw query failed. Code: `42501`. Message: `ERROR: permission denied to create extension "pg_cron"
HINT: Must be superuser to create this extension.`] {
  code: 'P2010',
  meta: [Object],
  clientVersion: '6.19.3'
}

Granting SUPERUSER for the first boot and removing it after make the app start with no issues