sqlpostgresqlflyway

Postgresql permissions for disable/enable trigger all

I need to disable triggers and constraints/foreign key relations for my tables when migrating. I'm using flyway for migrations.

So, I want to create a different user other than the superuser and give the new user the ability to disable/enable all trigger/constraints for the given schema.

So I tried this:

CREATE USER "MY_USER" WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE postgres to "MY_USER";

create schema "MY_SCHEMA";

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "MY_SCHEMA" TO "MY_USER";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "MY_SCHEMA" TO "MY_USER";
GRANT ALL PRIVILEGES ON SCHEMA "MY_SCHEMA" TO "MY_USER";

But when I use the MY_USER and try to run this script:

ALTER TABLE "MY_SCHEMA".TBLTABLE
     DISABLE TRIGGER ALL;

I get

ERROR: permission denied: "RI_ConstraintTrigger_c_16434" is a system trigger.

When I try this script with the superuser I can run it, but I can't run it with the user which I created. Any suggestions?

Solution

  • You need to be a superuser to disable “system triggers” on a table.

    The reason is that these triggers implement foreign key relationships, and disabling such triggers exposes the database to inconsistencies. Mere mortals are not trusted to perform such actions.

    What you can do is disable each normal trigger by name.

    If your goal is to disable foreign key constraints, you are out of luck: the only way a non-superuser can do that is by dropping the constraint.