• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • CasePointer

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Postgres system triggers error: permission denied

    Greg Sabino Mullane

    By Greg Sabino Mullane
    October 25, 2012

    This mystifying Postgres error popped up for one of my coworkers lately while using Ruby on Rails:

    ERROR:  permission denied: "RI_ConstraintTrigger_16410" is a system trigger
    

    On PostgreSQL version 9.2 and newer, the error may look like this:

    ERROR:  permission denied: "RI_ConstraintTrigger_a_32778" is a system trigger
    
    ERROR:  permission denied: "RI_ConstraintTrigger_c_32780" is a system trigger
    

    I labelled this as mystifying because, while Postgres’ error system is generally well designed and gives clear messages, this one stinks. A better one would be something similar to:

    ERROR:  Cannot disable triggers on a table containing foreign keys unless superuser
    

    As you can now guess, this error is caused by a non-superuser trying to disable triggers on a table that is used in a foreign key relationship, via the SQL command:

    ALTER TABLE foobar DISABLE TRIGGERS ALL;
    

    Because Postgres enforces foreign keys through the use of triggers, and because data integrity is very important to Postgres, one must be a superuser to perform such an action and bypass the foreign keys. (A superuser is a Postgres role that has “do anything” privileges). We’ll look at an example of this in action, and then discuss solutions and workarounds.

    Note that if you are not a superuser and you are not the owner of the table, you will get a much better error message when you try to disable all the triggers:

    ERROR:  must be owner of relation foobar
    

    To reproduce the original error, we will create two tables, and then link them together via a foreign key:

    postgres=# create user alice;
    CREATE ROLE
    
    postgres=# \c postgres alice
    You are now connected to database "postgres" as user "alice".
    
    -- Verify that we are not a superuser
    postgres=> select usename, usesuper from pg_user where usename = (select current_user);
     usename | usesuper 
    ---------+----------
     alice   | f
    
    postgres=> create table foo(a int unique);
    NOTICE:  CREATE TABLE / UNIQUE will create implicit index "foo_a_key" for table "foo"
    CREATE TABLE
    
    postgres=> create table bar(b int);
    CREATE TABLE
    
    postgres=> alter table bar add constraint baz foreign key (b) references foo(a);
    ALTER TABLE
    

    Let’s take a look at both tables, and then try to disable triggers on each one. Because the triggers enforcing the foreign key are internal, they will not show up when we do a \d:

    postgres=> \d foo
          Table "public.foo"
     Column |  Type   | Modifiers 
    --------+---------+-----------
     a      | integer | 
    Indexes:
        "foo_a_key" UNIQUE CONSTRAINT, btree (a)
    Referenced by:
        TABLE "bar" CONSTRAINT "baz" FOREIGN KEY (b) REFERENCES foo(a)
    
    postgres=> \d bar
          Table "public.bar"
     Column |  Type   | Modifiers 
    --------+---------+-----------
     b      | integer | 
    Foreign-key constraints:
        "baz" FOREIGN KEY (b) REFERENCES foo(a)
    
    postgres=> alter table foo disable trigger all;
    ERROR:  permission denied: "RI_ConstraintTrigger_41047" is a system trigger
    
    postgres=> alter table bar disable trigger all;
    ERROR:  permission denied: "RI_ConstraintTrigger_41049" is a system trigger
    

    If we try the same thing as a superuser, we have no problem:

    postgres=# \c postgres postgres
    You are now connected to database "postgres" as user "postgres".
    
    postgres=# select usename, usesuper from pg_user where usename = (select current_user);
     usename  | usesuper 
    ----------+----------
     postgres | t
    
    postgres=# alter table foo disable trigger all;
    ALTER TABLE
    
    postgres=# alter table bar disable trigger all;
    ALTER TABLE
    
    -- Don’t forget to re-enable the triggers!
    
    postgres=# alter table foo enable trigger all;
    ALTER TABLE
    
    postgres=# alter table bar enable trigger all;
    ALTER TABLE
    

    So, this error has happened to you - now what? Well, it depends on exactly what you are trying to do, and how much control over your environment you have. If you are using Ruby on Rails, for example, you may not be able to change anything except the running user. As you may imagine, this is the most obvious solution: become a superuser and run the command, as in the example above.

    If you do have the ability to run as a superuser however, it is usually much easier to adjust the session_replication_role. In short, this disables all triggers and rules, on all tables, until it is switched back again. Do NOT forget to switch it back again! Usage is like this:

    postgres=# \c postgres postgres
    You are now connected to database "postgres" as user "postgres".
    
    postgres=# set session_replication_role to replica;
    SET
    
    -- Do what you need to do - triggers and rules will not fire!
    
    postgres=# set session_replication_role to default;
    SET
    

    Note: While you can do “SET LOCAL” to limit the changes to the current transaction, I always feel safer to explicitly set it before and after the changes, rather than relying on the implicit change back via commit and rollback.

    It may be that you are simply trying to disable one or more of the “normal” triggers that appear on the table. In which case, you can simply disable user triggers manually rather than use “all”:

    postgres=# \c postgres alice
    You are now connected to database "postgres" as user "alice".
    
    postgres=> \d bar
          Table "public.bar"
     Column |  Type   | Modifiers 
    --------+---------+-----------
     b      | integer | 
    Foreign-key constraints:
        "baz" FOREIGN KEY (b) REFERENCES foo(a)
    Triggers:
        trunk AFTER INSERT ON bar FOR EACH STATEMENT EXECUTE PROCEDURE funk()
        vupd BEFORE UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE verify_update();
    
    postgres=> alter table bar disable trigger trunk;
    ALTER TABLE
    
    postgres=> alter table bar disable trigger vupd;
    ALTER TABLE
    
    -- Do what you need to do, then:
    
    postgres=> alter table bar enable trigger trunk;
    ALTER TABLE
    
    postgres=> alter table bar enable trigger vupd;
    ALTER TABLE
    

    Another option for a regular user (in other words, a non super-user) is to remove the foreign key relationship yourself. You cannot disable the trigger, but you can drop the foreign key that created it in the first place. Of course, you have to add it back in as well:

    postgres=# \c postgres alice
    You are now connected to database "postgres" as user "alice".
    
    postgres=> alter table bar drop constraint baz;
    ALTER TABLE
    
    -- Do what you need to do then:
    
    postgres=> alter table bar add constraint baz foreign key (b) references foo(a);
    ALTER TABLE
    

    The final solution is to work around the problem. Do you really need to disable triggers on this table? Then you can simply not disable any triggers. Perhaps the action you are ultimately trying to do (e.g. update/delete/insert to the table) can be performed some other way.

    All of these solutions have their advantages and disadvantages. And that’s what charts are good for!:

    Permission denied: "RI_ConstraintTrigger" is a system trigger - now what?
    Solution Good Bad
    Become a superuser Works as you expect it to Locks the table
    Must re-enable triggers
    Adjust session_replication_role No table locks!
    Bypasses triggers and rules on ALL tables
    Must be superuser
    MUST set it back to default setting
    Disable user triggers manually Regular users can perform
    Very clear what is being done
    Less damage if forget to re-enable
    Locks the table
    May not be enough
    Drop the foreign key Regular users can perform
    Very clear what is being done
    Locks the tables
    Must recreate the foreign key
    Not disable any triggers No locking
    Nothing to remember to re-enable
    May not work in all situations

    For the rest of this article, we will tie up two loose ends. First, how can we see the triggers if \d will not show them? Second, what’s up with the crappy trigger name?

    As seen above, the output of \d in the psql program shows us the triggers on a table, but not the internal system triggers, such as those created by foreign keys. Here is how triggers normally appear:

    postgres=# \c postgres postgres
    You are now connected to database "postgres" as user "postgres".
    
    postgres=# create language plperl;
    CREATE LANGUAGE
    
    postgres=# create function funk() returns trigger language plperl as $$ return undef; $$;
    CREATE FUNCTION
    
    postgres=# create trigger trunk after insert on bar for each statement execute procedure funk();
    CREATE TRIGGER
    
    postgres=# \d bar
          Table "public.bar"
     Column |  Type   | Modifiers 
    --------+---------+-----------
     b      | integer | 
    Foreign-key constraints:
        "baz" FOREIGN KEY (b) REFERENCES foo(a)
    Triggers:
        trunk AFTER INSERT ON bar FOR EACH STATEMENT EXECUTE PROCEDURE funk()
    
    postgres=# alter table bar disable trigger all;
    ALTER TABLE
    
    postgres=# \d bar
          Table "public.bar"
     Column |  Type   | Modifiers 
    --------+---------+-----------
     b      | integer | 
    Foreign-key constraints:
        "baz" FOREIGN KEY (b) REFERENCES foo(a)
    Disabled triggers:
        trunk AFTER INSERT ON bar FOR EACH STATEMENT EXECUTE PROCEDURE funk()
    

    Warning: Versions older than 8.3 will not tell you in the \d output that the trigger is disabled! Yet another reason to upgrade as soon as possible because 8.2 and earlier are end of life.

    If you want to see all the triggers on a table, even the internal ones, you will need to look at the pg_trigger table directly. Here is the query that psql uses when generating a list of triggers on a table. Note the exclusion based on the tgisinternal column:

    SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled
    FROM pg_catalog.pg_trigger t
    WHERE t.tgrelid = '32774' AND NOT t.tgisinternal
    ORDER BY 1;
    

    So in our example table above, we should find the trigger we created, as well as the two triggers created by the foreign key. All of them are enabled. Disabled triggers will show as a ‘D’ in the tgenabled column. (O stands for origin, and has to do with session_replication_role).

    postgres=# select tgname,tgenabled,tgisinternal from pg_trigger 
    postgres-#  where tgrelid = 'bar'::regclass;
                tgname            | tgenabled | tgisinternal 
    ------------------------------+-----------+--------------
     RI_ConstraintTrigger_c_32780 | D         | t
     RI_ConstraintTrigger_c_32781 | D         | t
     trunk                        | D         | f
    
    postgres=# alter table bar enable trigger all;
    ALTER TABLE
    
    postgres=# select tgname,tgenabled,tgisinternal from pg_trigger
    postgres-#  where tgrelid = 'bar'::regclass;
                tgname            | tgenabled | tgisinternal 
    ------------------------------+-----------+--------------
     RI_ConstraintTrigger_c_32780 | O         | t
     RI_ConstraintTrigger_c_32781 | O         | t
     trunk                        | O         | f
    
    

    As you recall, the original error — with the system trigger that had a rather non-intuitive name — looked like this:

    ERROR:  permission denied: "RI_ConstraintTrigger_16509" is a system trigger
    

    We can break it apart to see what it is doing. The “RI” is short for “Referential Integrity”, and anyone who manages to figure that out can probably make a good guess as to what it does. The “Constraint” means it is a constraint on the table - okay, simple enough. The “Trigger” is a little redundant, as it is extraordinarily unlikely you will ever come across this trigger without some context (such as the error message above) that tells you it is a trigger. The final number is simply the oid of the trigger itself. Stick them all together and you get a fairly obscure trigger name that is hopefully not as mysterious now!

    database postgres rails


    Comments