Archive for May, 2010

After restoring a PostgreSQL database, I recently ran into “PGError: ERROR: permission denied”:

$ rake db:create
$ sudo su postgresql # actually, I was restoring a number of development databases after re-installing Ubuntu recently, so I was already logged in as user postgresql in one of my screens, and I just used that to restore several databases at once
postgresql$ psql db_name < backup.sql

But then, when I attempted to apply further migrations:

$ rake db:create
rake aborted!
PGError: ERROR: permission denied for relation schema_migrations
: SELECT version FROM schema_migrations

The problem is that, although the database was owned by the correct PostgreSQL user (handled by rake db:create and the database.yml file), when I restored the database contents, I was logged in as the postgresql super user, so the tables within the database were owned by postgresql, not the user who owned the database. The solution was to restore databases using the user who owns the database:

$ rake db:drop
$ rake db:create
$ psql -d db_name -U db_owner_username -W < backup.sql

Read Full Post »