Django + PostgreSQL：创建数据库（授予什么特权） [英] Django + PostgreSQL: creating a database (what privileges to grant)

问题描述

我设法在Internet上找到的所有内容都像这样：
postgreSQL.app：创建数据库

Everything I have managed to find in the Internet looks like this: postgreSQL.app : create database

https://www.digitalocean.com/community/tutorials/how-to-use-postgresql-with-your-django-application -on-ubuntu-14-04

因此，第一个链接说我们必须像这样为Django创建数据库：

So, the first link say we have to create a database for Django like this:

CREATE USER testfor_psl_user WITH password 'pass';
CREATE DATABASE testfor_psl ENCODING 'UTF8' TEMPLATE template0 OWNER testfor_psl_user;

第二个非常相似：

CREATE DATABASE myproject;
GRANT ALL PRIVILEGES ON DATABASE myproject TO myprojectuser;

在两种情况下，我们都可以看到所有特权都授予了用户。

In both cases we can see that all privileges are granted to the user.

为什么要这么做？ Django使用两个特权：选择和插入。
授予所有特权并不安全。

Why do they do that? Django uses two privileges: select and insert. Granting all privileges is not safe.

我现在想到的是：
1）使postgres成为数据库的所有者。
2）创建myprojectuser并向其授予选择和插入权限。

I'm now thinking of: 1) making postgres the owner of the database. 2) creating myprojectuser and granting select and insert privileges to him.

您能否对此问题发表评论并分享创建数据库的经验。
您能否指出与此相关的有用链接。

Could you comment on this question and share your experience of creating a database. Could you point at a useful link on this matter.

推荐答案

如果需要，Django需要完全访问基础数据库您想使用其所有功能，例如迁移。因此，建议在文档中授予所有权限。

Django needs full access to the underlying database, if you want to use all its features like migrations. Therefore it is recommended in the docs to grant all privileges.

在某些情况下，您可能希望限制访问权限-例如如果您有一个非托管架构，该架构已与其他应用共享。但这很特别。这些主题未包含在文档中，而是由DBA留给您。

There are use cases in which you might want to restrict the access - e.g. if you have an unmanaged schema, which is shared with other apps. But this is quite special. Those topics are not covered by the docs and left for you as the DBA.

如果您知道django需要哪些特权-只需根据需要授予它们。

If you know which privileges are needed by your django - just grant them as you like it.

如果您不知道需要哪些特权，请使用以下过程：

If you don't know which privileges are needed, use the following procedure:

1. 使用完全授权的用户在开发上下文中设置项目。


2. 创建新用户，并且不授予任何权限


3. 切换您的django使用该用户


4. 使用其功能启动应用程序并等待SQL错误。


5. 授予必要的权限

1. Setup your project in development context, using a full granted user.
2. Create a new user, and do not grant any permissions
3. Switch your django to use that user
4. Start your application use its features and wait for SQL errors.
5. Grant the necessary permissions

只要一切正常，请重复步骤4.和5.-将所有授权写入一个sql文件，以便以后可以重现。当然，如果您已经知道，则可以通过在前面授予一些东西来加快此过程。

Repeat step 4. and 5. as long as everything works - write all grants into one sql file, to be able to reproduce this later. Of course you can accelerate the process, by granting stuff in front, if you already know, that it is needed.

您很可能需要

• SELECT 几乎在任何情况下


• INSERT 如果用户应该能够创建模型


• UPDATE 如果用户应该能够进行修改模型


• 删除，如果用户应该能够删除模型


• 参考如果用户创建的模型具有对另一个模型的外键约束，则该模型需要参考。



• SELECT in nearly any case
• INSERT if users should be able to create a model
• UPDATE if users should be able to modify a model
• DELETE if users should be able to delete a model
• REFERENCES if users create a model with foreign key constraints to another model - REFERENCES is needed for this another model.

在您的应用程序中仅需要知道的内容。当您仅提供对数据的可读访问权限时，也许 SELECT 就足够了。

What is needed in case of your application is something only you know. Maybe SELECT is enough, when you just provide readable access to your data.

当您那样工作时，您应该有一个用于部署的单独用户，该用户具有执行迁移的适当权限（在这种情况下，授予所有特权是有意义的）。这适用于每个版本，并且应该是自动IMO。

When you work like that, you should have a separate user, used for deployment, which has proper rights to execute your migrations (grant all privileges in this case makes sense). This applies for each release and should be automated IMO.

这篇关于Django + PostgreSQL：创建数据库（授予什么特权）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！