End Point

News

Welcome to End Point's blog

Ongoing observations by End Point people.

Postgres system triggers error: permission denied

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 named - 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!

1 comment:

Jon Jensen said...

It is strange that as a relation owner you can't disable foreign key constraint triggers even though you can drop the constraints entirely ...