Web 应用程序的受限 PostgreSQL 权限 [英] Restricted PostgreSQL permissions for web app

问题描述

创建一个包含三个用户的数据库并限制他们的权限(我只是大声思考，所以我的用户分离也可以更正):

Create a database with three users and restrict their privileges (I'm just thinking out loud, so my user separation is also open to correction):

1. 超级用户 - 该用户允许对数据库进行初始配置.创建应用程序数据库，创建其他用户，设置他们的权限.默认 postgres 超级用户对我有用，所以这个就完成了.
2. 管理员 - 此用户只能访问在配置期间创建的数据库.管理员可以CRUD所有表中的所有数据，也可以CRUD表等.仅此数据库的超级用户"类型的情况.在更新应用程序时，管理员是自动化工具用来处理数据库迁移的用户.
3. 应用用户 - 此用户最终是支持网络应用功能的用户.请注意，这与网页等上的用户无关 - 这是服务器用来运行查询、插入和删除数据的用户.我明确不希望该用户能够修改任何权限，也不希望创建/销毁表或索引或任何结构性内容.

1. Superuser - this user allows for the very initial provisioning of the database. Create the application database, create the other users, set their privileges. Default postgres superuser works for me, so this one is done.
2. Administrator - this user has access only to the database that was created during provisioning. Administrator can CRUD all data in all tables, and can also CRUD tables, etc. "Superuser for only this database" type of situation. When the application is being updated, the administrator is the user used by automated tooling to handle database migrations.
3. App user - this user is ultimately the one who supports the web app's functionality. Note this has nothing to do with users on web pages etc - this is the user the server leverages to run queries, insert and remove data. I explicitly do not want this user to be able to modify permissions of anything, nor create/destroy tables or indices or anything structural.

我的尝试

首先，查看(通常非常出色的)PostgreSQL 文档，Grant 上的页面 几乎让我目瞪口呆.在花了几个小时阅读 PostgreSQL 角色和权限之后，我通常感到困惑.我想通过更多的工作，我将能够为管理员用户确定我想要的东西，但我非常坚持应用程序用户".我已经了解了这么多(命名和密码都只是占位符):

What I've tried

First off, looking at the (generally excellent) PostgreSQL documentation, the page on Grant pretty much leaves me cross-eyed. After spending a few hours reading about PostgreSQL roles and privileges I'm generally confused. I think with a bit more work I'll be able to nail down what I want for the admin user, but I'm pretty stuck on the "app user". I've gotten about this far (naming and passwords are all just placeholders):

$ 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's where I get unsure. I feel like the answer I'm trying to avoid is "revoke everything by default then enumerate all the privileges you'll need at all the different levels on all the different objects". I'm trying to avoid that because I straight up don't know what I need there. If that ends up being the answer, then I'll just have to hunker down and read a bunch more, but generally when I start going down paths like that I've missed something.

我如何限制 app-user 的权限，使他们无法修改任何结构数据(例如，无法添加或销毁表)但能够连接行并对行执行任何操作(行级安全性)甚至不在我的雷达上).这种通用的权限模型与 PostgreSQL 所期望的并不真正同步吗?如果我必须遍历授权"页面上的每个选项来完成这样的事情，我觉得我错过了一些东西——无论是我最初做这件事的动机还是我要做的方式

How do I restrict privileges for app-user so they are unable to modify any structural data (e.g. cannot add or destroy tables) but are able to connect and do anything with rows (row level security is not even on my radar). Is this general model of privileges not really in sync with what PostgreSQL expects? I feel like I'm missing something if I have to walk through every option on that "grant" page to accomplish something like this - whether it be my motivation for doing it in the first place or the means by which I'm going about it.

我正在尝试构建我的第一个端到端 Web 应用程序.我已经完成了足够多的通用软件开发和 Web 应用程序开发，现在我正在尝试了解我通常认为理所当然的日常工作.我正在尝试设置 PostgreSQL 服务器，同时牢记最小权限原则.

I'm trying to build my first end-to-end web application. I've done enough general software development and web app development, now I'm trying to understand the pieces that I generally take for granted day to day. I'm trying to set up a PostgreSQL server while keeping the principle of least privilege in mind.

我还没有在我刚加入开发团队的网络应用程序上看到过这种情况，尽管它们通常很小并且没有被大量使用.这样做真的能完成任何事情吗?有没有人有令人信服的理由来解释为什么要做这样的事情，或者为什么这是一个糟糕或无效的想法?我的假设是，如果我最终遇到 SQL 注入漏洞，这将减轻损害，因为数据库用户的访问权限有限.是不是被误导了?

I haven't seen this done on web apps where I have simply joined the development team, although they're generally small and not heavily used. Does doing this actually accomplish anything? Does anyone have compelling reasons for why to do something like this, or why it's a bad or ineffective idea? My assumption was that if I ultimately ended up with a SQL injection vulnerability, this would mitigate the damage because the database user would have limited access. Is that misguided?

• http://www.ibm.com/developerworks/开源/库/os-postgresecurity/index.html
• PDF 警告: https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf
• https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2
• http://blog.2ndquadrant.com/auditing-users-and-roles-in-postgresql/

推荐答案

我来回答你的支线任务"先提问:

I'll answer your “side-quest” question first:

您的担忧和顾虑完全正确，每个设计应用程序的人都应该考虑同样的事情.其他的都是草率和粗心.

you are completely right with your worries and concerns, and everybody who designs an application should think about the same things. Everything else is sloppy and careless.

为了减轻成功的 SQL 注入攻击可能造成的损害，您绝对应该采用最小权限原则.

To mitigate the damage that can be caused by a successful SQL injection attack, you should definitely employ the principle of least privilege.

设置一个符合您要求的系统应该非常简单.

It should be quite simple to set up a system that matches your requirements.

我将使用您示例中的对象名称，但我将使用下划线而不是减号.在对象名称中仅使用小写字母、下划线和数字是一种很好的做法，因为这会让您的生活更轻松.

I'll use the object names from your exaple, except that I'll use underscores instead of minuses. It is good practive to use only lower case letters, underscores and numbers in object names, since it 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;

但是如果您采取最不认真的原则，您应该单独授予表权限，例如不允许 app_user DELETE 和 UPDATE 表中用户不需要这样做的数据.

But if you take the principle of least seriously, you should grant table permissions individually and e.g. not allow app_user to DELETE and UPDATE data in tables where there is no need for the user to do so.

这篇关于Web 应用程序的受限 PostgreSQL 权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！