Mark Berry September 13, 2012
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.
: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
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
: ALTER TABLE "schema_migrations" ENABLE TRIGGER ALL;ALTER
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
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
\du # list roles
select * from
pg_user; # Verify user created ("\q" to exit!)
from pg_shadow; # sysid and password hash listed here
\l # list
\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) :
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:
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.