CODEWITHSUNDEEP
djangopostgresqlweb
Michael
Michael

Reputation: 4461

Django + PostgreSQL: creating a database (what privileges to grant)

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

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;

The second one is pretty similar:

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.

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

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.

Upvotes: 1

Views: 3431

Answers (1)

dahrens
dahrens

Reputation: 3959

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.

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.

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. 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

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.

You will most probably need

  • 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.

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.

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.

Upvotes: 4

Related Questions