Postgres Error: Trigger is a System Trigger

All I wanted to do was test a little JavaScript.

I’m working on my Ruby on Rails app:  Rails 3.2.8, Rspec 2.11.0, Capybara 1.1.2, and Postgres 8.4.

I added an Rspec/Capybara test that required JavaScript. I added the necessary :js => true to the test’s define header. But when the web page opened in Firefox (using the default Capybara Selenium driver), I could see that my test data wasn’t there.

Did some research, found that I have to disable transactional fixtures so both Capybara and Selenium can see the test data. In spec/spec_helper.rb:

RSpec.configure do |config|
...
config.use_transactional_fixtures = false
...
end

And to keep the test data clean, I need the database-cleaner gem. See this helpful ASCIIcast, near the bottom:  #257 Request Specs and Capybara. It’s also discussed under “Transactions and Database Setup” in the Capybara readme.

The good news was that I could now see the test data in the web page. The bad news was that the test not only failed, but errored out. The trace started with this scintillating message:

PG::Error: ERROR:  permission denied: "RI_ConstraintTrigger_26748" is a system trigger
: ALTER TABLE "schema_migrations" ENABLE TRIGGER ALL;ALTER TABLE...

This led me to issue reports like this one and this one. If I understood correctly, because I have foreign key constraints defined in my database, the database-cleaner gem somehow tries to delete something it’s not allowed to. The options seemed to be 1) monkey-patch ActiveRecord to force Postgres to defer or disable referential integrity checking or 2) let the tests run as a Postgres superuser so it can do whatever it wants.

I opted for 2).

Setting Up a Postgres Superuser for the Test Database

Drawing on my previous article, Set Up PostgreSQL for Rails 3.1, I managed to get a Postgres superuser set up for use with the Rails test database. Once I did that, my test started working. Here is the procedure:

1. Change to default “postgres” admin user (must supply _my_ admin password)

sudo su postgres

2. Use the psql interactive terminal to create a role that can create databases and log in (basically creates a user). Note the semicolon at the end of PostgreSQL commands! I used the password generator lat this site to create a nice long password. On the “create role” line, you must include the “login” option so Postgres will create a user as well as a role. If you’re “stuck” in the PostgreSQL shell, try \q.

psql template1 #starts Postgres interactive shell
# type the next two lines on one line. Make up your own password:
create role myapp_superuser with superuser login
password 'gUmE83AyubrakU9E';
\du # list roles
select * from pg_user;    # Verify user created ("\q" to exit!)
select * from pg_shadow;  # sysid and password hash listed here
\l # list databases
\q # exit Postgres shell
exit # exit "Postgres" admin user

3. To tell PostgreSQL to allow the new user to logon to the test database using a password, we need to update the pg_hba.conf file (note “sudo” to allow opening protected file).

sudo gedit /etc/postgresql/8.4/main/pg_hba.conf

Add this line to pg_hba.conf, before the local all all ident line (the quotation marks are required due to the underscore) :

local "myapp_test"  myapp_superuser    md5

4. Restart PostgreSQL.

sudo /etc/init.d/postgresql-8.4 restart

5. Test logging in to the test database as the new user:

psql myapp_test -U myapp_superuser

This should prompt for a password. If you immediately get the message “Ident authentication failed for user “myapp_superuser””, the pg_hba.conf file is not right. After supplying the password, use \q to exit the Postgres shell.

6. Edit the config/database.yml file and add the password from step 2 to all three databases. Save the file. Here is an excerpt:

test:
adapter: postgresql
encoding: unicode
  database: myapp_test
pool: 5
username: myapp_superuser
password: gUmE83AyubrakU9E

Update October 16, 2012

For reasons I do not understand, the above configuration works fine when running tests, but when running rake db:test:prepare, it fails with the message

FATAL:  Ident authentication failed for user “myapp_superuser”

I thought the issue was that I had not included the DBCREATE privilege when creating myapp_superuser in step 2 above. So I went back in and ran this command to add that privilege:

alter role myapp_superuser with createdb;

But after a database service restart, rake db:test:prepare still failed. It seems that for some reason, the rake command is not picking up the myapp_superuser line in pg_hba.conf (step 3 above) and is falling through to the

local all all ident

line. So I commented out that line and replaced it with md5 authentication for all:

#local all all ident
local all all md5

After that, rake db:test:prepare completed successfully.

Update April 9, 2014

In Postgres 9.3.4, the default connection method is now peer, but still must be modified to get rake db:create:all (and I assume rake db:test:prepare) to work:

#local all all peer
local all all md5

Conclusion and a Warning

That’s it! Your tests now access the database user as a superuser. The tests may fail, but it won’t be because they have limited database access.

Warning Although we have set this up so only the test environment is logging in as a superuser, anyone who figures that out (e.g. by getting your database.yml file) could log on to your Postgres installation as myapp_superuser, then do anything in Postgres, including deleting the production database if it is on the same server.