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:

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':

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:

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 tableMust re-enable triggers

Adjust session_replication_role

No table locks!Bypasses triggers and rules on ALL tables

Must be superuserMUST set it back to default setting

Disable user triggers manually

Regular users can performVery clear what is being doneLess damage if forget to re-enable

Locks the tableMay not be enough

Drop the foreign key

Regular users can performVery clear what is being done

Locks the tablesMust recreate the foreign key

Not disable any triggers

No lockingNothing 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:

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).

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!