Plugin does not work with db schemas other than "public"

[aplavsa]

Bug report

Describe the bug

If Strapi is using postgres db schema other than the default public or the one named the same as the db user e.g. DB_USERNAME=strapi SCHEMA_NAME=strapi, it fails in places where custom SQL queries are written, e.g. beforeUpdate.js lifecycle hook or beforeDelete.js hook.

Steps to reproduce the behavior

1. Setup Strapi to use db schema other than 'public'
2. Go to any versioned and localized content type
3. Click on 'Delete Entry' or try to update and publish versioned content!
4. An error occurs

Expected behavior

User should be able to delete or update/publish versioned entry

Code snippets

 // Relink logic for localizations
  if (isLocalized) {
    const latestInLocales = (await strapi.db.connection.raw(
      `SELECT DISTINCT ON (locale) id, locale, version_number, published_at FROM ${collectionName} // missing schema name
        WHERE vuid='${item.vuid}' AND id!='${item.id}' 
        ORDER BY locale, published_at DESC NULLS LAST, version_number DESC`

same applies for beforeUpdate.js

System

• Node.js version: 16.8.1
• NPM version: 8.19.2
• Strapi version: 4.5.6
• Database: postgresql
• Operating system: Linux

[aplavsa]

After some further investigation, it seems that it works with different schemas as well but only if it the same as the db username. Postgres has a feature where it searches automatically for schema names public and $user so that's why it only works in these cases. It still won't work for any other arbitrary schema name. The other thing that I found out while investigating this issue on our side is that in the code snippet above, you are using knex.js raw query without using prepared statements. This is a HUGE issue on it's own and you should fix it ASAP even though you are not assigning user-input data to collectionName and item.vuid variables.

[aplavsa]

I found a workaround for this issue by applying this configuration in strapi db configuration file:

module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      host: "localhost",
      port: 5432,
      database: "mystrapidb",
      user: "strapiuser",
      password: "strapipwd",
      schema: "strapischema",
    },
    searchPath: ["strapiuser", "strapischema", "public"], // add your schema name to this array and also your db username to preserve default functionality
    debug: false,
  },

[jpizzle34]

I think that's an important point you raised about replacing raw sql queries with the knex query builder. I wonder if it's possible to go a step further and use the strapi ORM instead and if performance permits this. Because this will make it easier to maintain the plugin especially when breaking changes are made to strapi/core. Or maybe this isn't the route to take as the ORM will trigger lifecycle methods.

What were your design considerations here in terms of bypassing the strapi orm for a sql query? Just trying to gain an understanding of the project as I may contribute in the future.

Thanks for open-sourcing this plugin btw. It is very valuable to me :)

[omikulcik]

Hi @jpizzle34, I will be taking over the maintenance of the plugin. I definitely agree with you that replacing the sql raw queries with something else will make it easier to maintain the plugin. This should be also one of our priorities once we solve the patch package problem.

[0xivb]

Can you guys please add "Doesn't work under schemas other than public" under Known Limitations?
I just wasted a bunch of time trying to figure out why it didn't work when I followed the instructions to the tee.

[omikulcik]

Hi, @IvanCodeslol will do :) Thank you for the suggestion