PostgreSQL limited permissions for web application

Question

goal

Create a database with three users and restrict their privileges (I just think out loud, so my user separation is also available for correction):

• Superuser - This user allows the initial provision of the database. Create an application database, create other users, set their privileges. By default, postgres superuser works for me, so this is done.
• Administrator - this user has access only to the database that was created during preparation. An administrator can CRUD all the data in all tables, as well as CRUD tables, etc. The situation type is "Superuser for this database only." When the application is updated, the administrator is the user used by the automatic tool to handle database migration.
• Application user - this user is ultimately the one who supports the functionality of the web application. Please note that this has nothing to do with users on web pages, etc. is a user who uses the server to run queries, insert, and delete data. I clearly do not want this user to be able to change permissions for anything, as well as create / destroy tables or indexes or anything structural.

What i tried

First of all, looking at the PostgreSQL documentation (usually excellent), the page

 $ psql -U postgres postgres=# CREATE USER "app-admin" WITH PASSWORD 'password'; CREATE ROLE postgres=# CREATE USER "app-user" WITH PASSWORD 'password'; CREATE ROLE postgres=# CREATE DATABASE "test-database" WITH OWNER "app-admin"; CREATE DATABASE postgres=# \c "test-database" You are now connected to database "test-database" as user "postgres". test-database=# DROP SCHEMA "public"; DROP SCHEMA test-database=# CREATE SCHEMA "app" AUTHORIZATION "app-admin"; CREATE SCHEMA 

And here, where I get insecure. I feel that the answer I am trying to avoid is to "undo everything by default, and then list all the privileges that you will need at all levels on all different objects." I try to avoid this because I don’t directly know what I need there. If this is the answer, then I just need to go downstairs and read a bunch more, but usually, when I start going along these paths, I missed something.

Questions

How to restrict privileges for app-user , so they cannot change any structural data (for example, cannot add or destroy tables), but they can connect and do something with rows (row-level security is not even on my radar) Is this general privilege model really out of sync with what PostgreSQL expects? I feel like I'm missing something if I need to go through each option on this grant page to do something like this: be it my motivation for this in the first place or the ways I'm going to he.

Context

I am trying to create my first end-to-end web application. I've done enough general software development and web application development, now I'm trying to understand the parts that I usually take for granted day after day. I am trying to set up a PostgreSQL server while keeping.

Side quest

I have not seen this in web applications, where I simply joined the development team, although they are usually small and not used to a large extent. Does it really do something? Does anyone have a good reason why one should do something like this or why is this a bad or inefficient idea? My assumption was that if I ended up in the SQL injection vulnerability, it would reduce the damage since the database user would have limited access. Is this a mistake?

The neat articles I found on the topic:

PDF WARNING:

Answer 1

First, I will answer your question "side-quest":

you are completely right with your worries and concerns, and everyone who develops an application should think about the same thing. Everything else is careless and careless.

To reduce the damage that could be caused by a successful SQL injection attack, you should definitely use the least privilege principle.

You must configure a system that meets your requirements.

I will use the object names from your example, except that I use underscores instead of minuses. It is useful to use only lowercase letters, underscores and numbers in the names of objects, as this will make your life easier.

 /* create the database */ \c postgres postgres CREATE DATABASE test_database WITH OWNER app_admin; \c test_database postgres /* drop public schema; other, less invasive option is to REVOKE ALL ON SCHEMA public FROM PUBLIC */ DROP SCHEMA public; /* create an application schema */ CREATE SCHEMA app AUTHORIZATION app_admin; /* further operations won't need superuser access */ \c test_database app_admin /* allow app_user to access, but not create objects in the schema */ GRANT USAGE ON SCHEMA app TO app_user; /* PUBLIC should not be allowed to execute functions created by app_admin */ ALTER DEFAULT PRIVILEGES FOR ROLE app_admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; /* assuming that app_user should be allowed to do anything with data in all tables in that schema, allow access for all objects that app_admin will create there */ ALTER DEFAULT PRIVILEGES FOR ROLE app_admin IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user; ALTER DEFAULT PRIVILEGES FOR ROLE app_admin IN SCHEMA app GRANT SELECT, USAGE ON SEQUENCES TO app_user; ALTER DEFAULT PRIVILEGES FOR ROLE app_admin IN SCHEMA app GRANT EXECUTE ON FUNCTIONS TO app_user; 

But if you take the principle the least seriously, you must grant permissions to tables individually and, for example, do not allow app_user until DELETE and UPDATE data in tables where the user does not need to do this.

Answer 2

For web applications, I divide permissions into three roles, where each role inherits from its predecessor.

• Read Only - Used for SELECT queries and function calls.
• Insert - used for INSERT statements
• Update and delete - they are mainly used for administration, since an external application that addresses the public usually does not change or delete data.

Thus, even if some hackers manage to execute SQL Injection, it is limited by the permissions of the role used, usually only SELECT or INSERT.

My web applications usually do not need more intrusive permissions such as CREATE, DROP, TRUNCATE, etc., so I DO NOT GIVE these permissions for web applications.

In rare cases when the second role needs to be updated or deleted, I either give it permission for this particular table or put the code in a function created using SECURITY DEFINER .

 /** role_read is read-only with SELECT and EXECUTE */ CREATE ROLE role_read; /** role_read_add adds INSERT */ CREATE ROLE role_read_add; /** role_read_add_modify adds UPDATE and DELETE */ CREATE ROLE role_read_add_modify; GRANT USAGE ON SCHEMA <schema> TO role_read; /** for existing objects */ GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO role_read; GRANT SELECT ON ALL SEQUENCES IN SCHEMA <schema> TO role_read; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <schema> TO role_read; /** for future objects */ ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO role_read; ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON SEQUENCES TO role_read; /** role_read_add inherits from role_read */ GRANT role_read TO role_read_add; /** for existing objects */ GRANT INSERT ON ALL TABLES IN SCHEMA <schema> TO role_read_add; GRANT ALL ON ALL SEQUENCES IN SCHEMA <schema> TO role_read; /** for future objects */ ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT INSERT ON TABLES TO role_read_add; ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON SEQUENCES TO role_read_add; /** role_read_add_modify inherits from role_read_add */ GRANT role_read_add TO role_read_add_modify; /** for existing objects */ GRANT UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema> TO role_read_add_modify; /** for future objects */ ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT UPDATE, DELETE ON TABLES TO role_read_add_modify;