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

by

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.

More Related Contents:

  1. IntegrityError duplicate key value violates unique constraint – django/postgres
  2. Django bulk_create with ignore rows that cause IntegrityError?
  3. How to install libpq-fe.h?
  4. How to reset the sequence for IDs on PostgreSQL tables
  5. Django: What are the best practices to migrate a project from sqlite to PostgreSQL
  6. Migrating existing auth.User data to new Django 1.5 custom user model?
  7. Django and postgresql schemas
  8. Docker-compose with django could not translate host name “db” to address: Name or service not known
  9. Different db for testing in Django?
  10. Dynamically adding a form to a Django formset
  11. How to customize user profile when using django-allauth
  12. Django: Group by date (day, month, year)
  13. Django: How can I protect against concurrent modification of database entries
  14. Need to convert a string to int in a django template
  15. How can I have two foreign keys to the same model in Django?
  16. Putting a django login form on every page
  17. Django connection to postgres by docker-compose
  18. Django: How to get current user in admin forms?
  19. Docker app server ip address 127.0.0.1 difference of 0.0.0.0 ip
  20. Accessing “Media” files in Django
  21. How to add data into ManyToMany field?
  22. Raw SQL queries in Django views
  23. Django forms request.user
  24. Fighting client-side caching in Django
  25. Django query filter with variable column
  26. How to dynamically provide lookup field name in Django query? [duplicate]
  27. Heroku: Django database file lost on every dyno restart
  28. How to add a sortable count column to the Django admin of a model with a many-to-one relation?
  29. Django Forms: pass parameter to form
  30. Django – forms.FileField() initial value

Leave a Comment