Best (Better?) Practice for Postgres Schema

Questions
, ,
1

Is it preferable to specify a schema name when using Postgres for the n8n database, or leave the default “public” schema?

Occasionally there have been issues related to using a specific (non-“public”) schema when a value is specified for env/config DB_POSTGRESDB_SCHEMA per: DB Docs
For instance: Table “user” does not exist…

I have been asked not to use the default “public” schema for a self-hosted n8n deployment because of some perception that data in the default/“public” schema is somehow less secure. This may be related to the default permissions given to any user with privileges at the database level per: Schemas and Privileges

The concern I have is that SQL using the public. prefix on names is equivalent to un-prefixed names per: The Public Schema , so, if any DB interactions (code generated by TypeORM or other mechanisms in the n8n source) are missing the configured schema (prefix), it would be a non-issue if the default / “public” schema is used (i.e. no configured DB_POSTGRESDB_SCHEMA). Maybe the question is actually “How thorough are the unit/regression tests for running with and without specifying DB_POSTGRESDB_SCHEMA?”

Information on your n8n setup

  • n8n version: 1.46.0+
  • Database: Postgres 16
  • Running n8n via: Docker
  • Operating system: Linux
2

It looks like your topic is missing some important information. Could you provide the following if applicable.

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:
3

Hey @hubschrauber,

We have a few users using a custom schema name but our main CI test doesn’t use this, Our tests do cover a lot of cases though and can be found in our GitHub repo if you wanted to take a look at them. I don’t think we have had an issue with custom schemas for a while but we probably should include a test for this as well. I will bring it up with the core team now.

4

Thanks for the response. The confirmation that n8n isn’t (yet) explicitly tested with a specified (non-“public”) schema might be enough of a reason to recommend just leaving the default and suggest that whatever access/security concerns there might be should be addressed by revoking privileges to the “public” schema from any “extra” (non-n8n-system) DB users that might get created. Honestly, I don’t know why there is an expectation that any other DB users would exist anyway. The n8n system owns this DB and nothing else should really need access.

1 Like
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.