Comment by michelpp | Hacker News Reader

Comment by michelpp | Hacker News Reader

Parent Full thread

> 1) what is the cost of a mistake (what if I misconfigure row-based policy vs. misconfigure in the traditional backend)

Good question. Ok let's do a thought experiment between PostgREST and Django. Every single Django production system I've seen logs into Postgres as a superuser (so it can do migrations).

PostgREST logs into the database as a user with NO privileges (because migration is a problem for a migration tool, not a web framework!) and then switches roles based on the (signed) JWT token. The PostgREST user is NEVER a superuser.

So you flub a Django permission and users can now run queries as a superuser and potentially leak everythinv. If you flub a PostgREST configuration, you leak data maybe in one or two tables.

> 2) the size of the attack surface (if we expose DB directly, as opposed to having an additional layer)

This is going to differ depending on who you ask. People talk about attack "surfaces" but I personally think of them as "attack paths". Does more code mean more paths? I think yes. Others think no.

> 3) how many developers are proficient in Postgres enough to avoid #1 and #2 as opposed to traditional code-based security policies

Postgres' security mechanism comes down to "who can see this" it's the same question those developers would be asking in Django. If they're not proficient enough to understand now to GRANT or REVOKE are they going to be proficient enough to understand the implications of their application logging into the database as a superuser?

> Every single Django production system I've seen logs into Postgres as a superuser (so it can do migrations).

then all those production systems do it wrong. all the django DB user needs (even for migrations) is to be the OWNER of the schema you give it (the NAME setting in DATABASES). how you create this database is up to you and it's not django's job.

there are 2 cases i can think of where django needs extra permissions:

1. to run the test suite django will want to create/destroy a `test_projectname` database. for this `CREATE USER ... CREATEDB ...` is sufficient, no superuser needed. this should not be in your production database anyway but a throwaway DB where you run your CI/CD.

2. when some extension needs to be installed (like pg_trgm, etc) you might need superuser, but again, it's debatable if this is django's job. some projects try to do this in their migrations, but i prefer to do this during provisioning. the postgres docs say:

> Loading an extension ordinarily requires the same privileges that would be required to create its component objects. For many extensions this means superuser privileges are needed. However, if the extension is marked trusted in its control file, then it can be installed by any user who has CREATE privilege on the current database.

edit: minor clarification.

> then all those production systems do it wrong.

I agree!

Let's put aside that being the OWNER of a database object means that by default is granted all privileges to it, and that owners aren't (typically) filtered by Row Level Security.

Google "Django Postgres Tutorial" and the first link creates a superuser:

https://www.enterprisedb.com/postgres-tutorials/how-use-post...

The second link does not, but then it goes on to `GRANT ALL PRIVILEGES ON DATABASE` to that user:

https://www.section.io/engineering-education/django-app-usin...

The documentation has a handy guide on creating superusers through the web interface, and touts what a great feature it is!

https://djangocentral.com/creating-super-user-in-django/

To a security conscious DBA, this is insanity. Yes, you can lock down a Django app just like PostgREST in fact, and add some code to do SET ROLE and all that just like PostgREST, but no one does because the documentation on database setup in the Django does doesn't even mention superusers:

https://docs.djangoproject.com/en/3.2/ref/settings/#std:sett...

let's not put it aside. i find it perfectly reasonable that the DB user django uses to connect to its own database has all privileges to that database.

there are ways to limit these privileges depending on the requirements both on django and postgres level, for example to have some read only users, etc.

don't google "Django Postgres Tutorial", the only django tutorial you need is the official django tutorial.

the djangocentral article discusses django superusers, not postgres ones. a django superuser has no postgres superuser privileges, they are 2 different things.

> Django does doesn't even mention superusers

of course it does not mention superusers because django does not recommend creating db superusers for security reasons and it mentions them only in connection with installing postgres extensions, and that is a limitation of postgres, not django.

in my professional opinion a run of the mill web application does not need row level security, setting up schema-wide privileges correctly is hard enough. but if one needs it, it's one google search away that some people have tried to do it with django as well, it's a postgres concept and as such can be implemented in any other framework connecting to it.

Reply on news.ycombinator.com