1

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.

Community
  • 1
  • 1
Michael
  • 4,273
  • 3
  • 40
  • 69

1 Answers1

3

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.

dahrens
  • 3,879
  • 1
  • 20
  • 38
  • Where in the documentation is it written about all privileges? Ok, I forgot about migrations. Let creation privilege be the third one. Maybe to alter tables as well. But for what does Django need privileges to drop tables? And even migrations is a bit strange explanation. It is executed once or twice, but the security breech will be constantly here. – Michael Feb 05 '17 at 09:21
  • migrations are capable to do drop tables - they make use of it, when you remove a model. – dahrens Feb 05 '17 at 09:27
  • So, grant the necessary privileges, migrate, revoke the privileges. Could you give a simple example with commands. For a simple imaginary project. – Michael Feb 05 '17 at 09:29
  • This is a way to go. I would recommend setting up two users, one that is used to do migrations, while deploying your app, and a second one used in production, that only has access, which is needed. – dahrens Feb 05 '17 at 09:31
  • If you start hardening your production setups security, you need to check all necessary grants yourself. There is no "this is what you need to grant" as this depends on which features are used. – dahrens Feb 05 '17 at 09:32
  • dahrens, reasonable solution. I've thought of that. But I lack experience with Django. Could you offer your answer with list of necessary commands and steps? – Michael Feb 05 '17 at 09:34
  • It seems to be dangerous for one's career, isn't it? Waiting for errors... We are usually have time constraints. We are working with third party applications. Waiting for errors means manually testing everything. Who will do that? In how much time? – Michael Feb 05 '17 at 09:53
  • By the way, will the admin site work with select and insert privileges only? – Michael Feb 05 '17 at 09:55
  • So, the boss will tell you: aren't you a developer? Really dangerous. But! Maybe these 4 privileges will be enough for the most applications. It is the admin site that troubles me. Will these be enough for admin site to work? – Michael Feb 05 '17 at 10:08
  • @dahrens I have a quick question with regards to your answer. I have a Django 2.x setup with 2 DBs, one of which is an external MySQL (the other is default, Postgres) My MySQL user has SELECT privileges but whenever I try to do a DB query within Django with `with connections['name-of-db'].cursor() as cursor: # ...`, I get `(1045, "Access denied for user ...` and the problem is that I cannot extend my privileges to ALL. Logging in to the DB via bash works and doing normal queries from there works also. Is Django doing something in the background with cursor for which I need extra privileges? – tech4242 Feb 19 '18 at 08:38
  • Does the bash run on the same machine as the django instance? In MySQL privileges are bound to the tuple user@hostname - if you grant access for foobar@localhost it is not the same as foobar@example.com - if this is not your issue, please ask a separate question and provide more details. – dahrens Feb 19 '18 at 11:09
  • @dahrens Thanks for the reply! And no, my Django app is currently running in localhost on a Docker container and MySQL is on AWS. I did `SHOW GRANTS FOR user;` and all of the SELECT grants have `'user'@'%'`, which should alleviate the difference given the wildcard argument right? And yes, I'll make a question if this doesn't do it either :) – tech4242 Feb 19 '18 at 11:36
  • revoking all/most privileges from `user` in postgres: `ALTER DEFAULT PRIVILEGES FOR ROLE user REVOKE ALL PRIVILEGES ON TABLES FROM user;` `ALTER DEFAULT PRIVILEGES FOR ROLE user REVOKE ALL PRIVILEGES ON SEQUENCES FROM user;` `ALTER DEFAULT PRIVILEGES FOR ROLE user REVOKE ALL PRIVILEGES ON FUNCTIONS FROM user;` `ALTER DEFAULT PRIVILEGES FOR ROLE user REVOKE ALL PRIVILEGES ON TYPES FROM user;` – groovecoder Jun 02 '20 at 18:27