Django：恢复（迁移）后尝试访问数据库时拒绝权限 [英] Django: permission denied when trying to access database after restore (migration)

问题描述

我有一个django 1.4应用程序，在本地的开发服务器中有一个填充的postgres 9.1数据库。成功部署后，我想将数据从本地数据库移动到在线数据库，所以我使用：

  pg_dump -f dump.sql -Ox数据库
  

然后在服务器上恢复：

  psql -1 -f dump.sql数据库
  

现在尝试在网上登录网站admin throws拒绝关系django_session的权限异常。我尝试使用/不使用-Ox开关和所有组合来转储数据，但没有成功。我也在删除数据库，并在服务器上重新创建它，并在settings.py中设置正确的所有者。

如果我在没有恢复的情况下运行正常的统计信息一切都很好。

我在这里缺少一些东西？

解决方案

事实证明，您应该在恢复后向数据库中的所有对象授予显式所有权。所有者不是超级用户。仅在数据库创建时才设置所有者是不够的。最终的迁移解决方案如下：客户端上的

  pg_dump -f dump.sql -Ox数据库
  

在服务器上：

  su postgres 
 dropdb数据库
 createdb数据库-O用户
 psql数据库-f dump.sql 
  

然后设置权限：

  psql数据库-c将所有表中的所有表格公开给用户; 
 psql数据库-c将所有序列中的所有序列都公开给用户; 
 psql数据库-c授予所有功能在SCHEMA中公开给用户; 
  

请注意，我们可以在psql控制台中运行sql命令，但此表单可轻松嵌入到脚本中等等。

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?

解决方案

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.

这篇关于Django：恢复（迁移）后尝试访问数据库时拒绝权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！