Use one correspondig database user for each application user

80 views
Skip to first unread message

guettli

unread,
Jul 11, 2017, 5:40:53 AM7/11/17
to Django users
I guess most applications have exactly one database user.

Why not use one database for each application user?

Example: User "foo" in my web application has a corresponding database user "foo".

This way you could use row level security from the database.

PostgreSQL has a lot of interesting features: https://www.postgresql.org/docs/devel/static/ddl-rowsecurity.html

Use case: Show me all items which user "foo" is allowed to see.

Avraham Serour

unread,
Jul 11, 2017, 5:46:41 AM7/11/17
to django-users
Where would you store the password hashes?

This would mean that no data ever could have relations between users

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/7d1eaa8c-d80a-4390-aaf9-8a95d3fcf6b4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Antonis Christofides

unread,
Jul 11, 2017, 6:11:59 AM7/11/17
to django...@googlegroups.com

Hi,

This was discussed three months ago (the subject was "DATABASE DICTIONARY in Settings.py"), and this was my opinion:

As you know, RDBMS's keep their own list of users and have sophisticated permissions systems with which different users have different permissions on different tables. This is particularly useful in desktop applications that connect directly to the database. Web applications changed that. Instead of the RDBMS managing the users and their permissions, we have a single RDBMS user as which Django connects to the RDBMS, and this user has full permissions on the database. The actual users and their permissions are managed by Django itself (more precisely, by the included Django app django.contrib.auth), using database tables created by Django. What a user can or cannot do is decided by Django, not by the RDBMS. This is a pity because django.contrib.auth (or the equivalent in other web frameworks) largely duplicates functionality that already exists in the RDBMS, and because having the RDBMS check the permissions is more robust and more secure. I believe that the reason web frameworks were developed this way is independence from any specific RDBMS, but I don't really know.

So the canonical way of working is to have a single database user as which Django logs on to the database, with full permissions on the database (including permission to create and delete tables), and many Django users, each one with different permissions. Typically only one Django superuser is created. I call the superuser "admin", which I believe is the common practice.

You can probably do things differently, and maybe there exist custom database backends that would allow you to switch the database user on login, but if there's no compelling reason you should really stick to the canonical way.

Regards,

Antonis

Antonis Christofides
http://djangodeployment.com
--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

guettli

unread,
Jul 11, 2017, 9:27:10 AM7/11/17
to Django users


Am Dienstag, 11. Juli 2017 11:46:41 UTC+2 schrieb Avraham Serour:
Where would you store the password hashes?


I see several possible ways.

 1. use django.contrib.auth for authentication like before. The initial connection to the db gets done via a database-superuser. After auth in django the db-user gets changed.
 2. Forward authentication to postgres ...
  ...


 
 
This would mean that no data ever could have relations between users


Do you mean this:

item1 can be viewed by user1. Item2 can be viewed by user2. User1 can not see item2 and user2 can not see item1. Then you can't have or can't resolve a ForeignKey from item1 to item2....

Is this what you mean?

 

guettli

unread,
Jul 11, 2017, 9:31:04 AM7/11/17
to Django users
Thank you for your answer. Yes, I use one database user since several years, and I guess it will be that way the next years.

Nevertheless I think it is good to talk about things like this from time to time.

Regards,
  Thomas

guettli

unread,
Jul 13, 2017, 4:13:17 AM7/13/17
to Django users
Just for the records, I found a blog post how to use row level security in postgres with one application db-user:

https://blog.2ndquadrant.com/application-users-vs-row-level-security/

Here is the essential code:

SET my.username = 'tomas'

CREATE POLICY chat_policy ON chat
    USING (current_setting('my.username') IN (message_from, message_to))
    WITH CHECK (message_from = current_setting('my.username'))

Regards,
  Thomas Güttler

Antonis Christofides

unread,
Jul 13, 2017, 4:32:26 AM7/13/17
to django...@googlegroups.com

BTW,

another idea for "connecting" to PostgreSQL as different users would be to actually have Django connect to PostgreSQL as a superuser and then (probably in some early middleware) execute SET SESSION AUTHORIZATION to switch permissions. This is probably easier and more robust and it's easier to reuse connections.

Regards,

Antonis

Antonis Christofides
http://djangodeployment.com
--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

guettli

unread,
Jul 17, 2017, 10:46:04 AM7/17/17
to Django users


Am Donnerstag, 13. Juli 2017 10:32:26 UTC+2 schrieb Antonis Christofides:

BTW,

another idea for "connecting" to PostgreSQL as different users would be to actually have Django connect to PostgreSQL as a superuser and then (probably in some early middleware) execute SET SESSION AUTHORIZATION to switch permissions. This is probably easier and more robust and it's easier to reuse connections.

Regards,

Antonis


Yes, this should work. Thank you for this hint.

Fred Stluka

unread,
Jul 21, 2017, 1:55:17 PM7/21/17
to django...@googlegroups.com
Answer:  Connection pooling

Sharing a single DB user for all/multiple Web app users allows
connection pooling.  Otherwise, you have to create a new DB
connection for each HTTP request, or at least for each web app
user.  Creating DB connections is relatively slow.

At least, I learned this reason 20 years ago, and assume it is
still true.  On the other hand, I've never checked to see whether
Django uses a connection pool by default, and it seems pretty
quick.

Does Django use a connection pool?

--Fred
Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

Jani Tiainen

unread,
Jul 21, 2017, 2:04:58 PM7/21/17
to django...@googlegroups.com
Hi,

I think connection pooling is out of Django scope. Django though reuses db connection. In some early versions Django opened and closed connection per request.


To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/35fea627-eda0-3228-4e12-621444b931b9%40bristle.com.

guettli

unread,
Aug 14, 2017, 9:05:32 AM8/14/17
to Django users


Am Freitag, 21. Juli 2017 19:55:17 UTC+2 schrieb Fred Stluka:
Answer:  Connection pooling

Sharing a single DB user for all/multiple Web app users allows
connection pooling.  Otherwise, you have to create a new DB
connection for each HTTP request, or at least for each web app
user.  Creating DB connections is relatively slow.

At least, I learned this reason 20 years ago, and assume it is
still true.  On the other hand, I've never checked to see whether
Django uses a connection pool by default, and it seems pretty
quick.

Does Django use a connection pool?




I don't know if django uses a connection pool. I think not by default.

The wsgi-workers handle one request after the other. I think this
makes the thing fast. At least no complete db connection needs to be established for
every request.

 

Jani Tiainen

unread,
Aug 14, 2017, 12:45:47 PM8/14/17
to django...@googlegroups.com
Hi.

Django doesn't use connection pooling. That's beyond scope of Django.

Normally Django opens connection per worker thread and keeps it open as long as there is no unrecovable error or connection exceeds MAX_AGE setting

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
Reply all
Reply to author
Forward
0 new messages