PostgreSQL: missing permissions for creating schema

Questions
1

n8n (Docker) fails to start with permission denied for the database PostgreSQL unless I temporarily make the DB role a Create privilege (on the whole DB). I want to run with least privilege.

I am getting error from n8n: error | permission denied for database
and from DB:
ERROR: permission denied for database
STATEMENT: CREATE SCHEMA IF NOT EXISTS n8n

I added privileges like:

CREATE USER n8n_user WITH PASSWORD '***' LOGIN;
CREATE SCHEMA IF NOT EXISTS n8n AUTHORIZATION n8n_user;

GRANT CONNECT, TEMP ON DATABASE <db_name> TO n8n_user;
GRANT USAGE, CREATE ON SCHEMA n8n TO n8n_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA n8n TO n8n_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA n8n TO n8n_user;

ALTER DEFAULT PRIVILEGES FOR ROLE n8n_user IN SCHEMA n8n
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO n8n_user;
ALTER DEFAULT PRIVILEGES FOR ROLE n8n_user IN SCHEMA n8n
  GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO n8n_user;

ALTER ROLE n8n_user SET search_path = n8n, public;

I don’t want my n8n user to have access to other schemas, thus I would like to limit his privileges as much as possible. I would like my user to have a possibility to use already created n8n schema instead of trying creating new one.

1 Like
2

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