Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 736
Rabih Kodeih Avatar asked Sep 02 '12 02:09

Rabih Kodeih


2 Answers

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.

like image 148
Rabih Kodeih Avatar answered Oct 12 '22 01:10

Rabih Kodeih


Try to do this from postgres user:

sudo su - postgres pg_dump -f dump.sql -Ox database 

Or just pass -U flag:

pg_dump -f dump.sql -Ox database -U postgres 
like image 33
Daniil Ryzhkov Avatar answered Oct 12 '22 01:10

Daniil Ryzhkov