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

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.

Leave a Comment

deneme bonusu veren sitelerbahis casino