postgresql - Django: permission denied when trying to access database after restore (migration)

Question

I have a django 1.4 app with a populated postgres 9.1 database in development server locally. After successful deployment, I wanted to move the data from local to online database, so I used:

pg_dump -f dump.sql -Ox database

and then restored on the server with:

psql -1 -f dump.sql database

Now trying to login online to the website admin throws a "permission denied for relation django_session" exception. I've tried to dump the data with/without -Ox switch and all its combinations but without success. I am also dropping the database and recreating it from scratch on the server with the correct owner as set in settings.py.

If I run a normal syndb without a restore then everything works well.

Am I missing something here?

question from:https://stackoverflow.com/questions/12233046/django-permission-denied-when-trying-to-access-database-after-restore-migratio

Answer 1

It turns out that you should grant explicit ownership of all objects in the database to the owner after restore. The owner is not a superuser. It's not enough to only set the owner at database creation time. The final solution for migration goes like this:

on the client:

pg_dump -f dump.sql -Ox database

on the server:

su postgres    
dropdb database
createdb database -O user
psql database -f dump.sql

and then to set the privileges:

psql database -c "GRANT ALL ON ALL TABLES IN SCHEMA public to user;"
psql database -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to user;"
psql database -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to user;"

Note that we could've run the sql command in psql console but this form is easily embeddable in scripts and such.