如何为Docker Postgres容器配置多用户权限及访问控制

1. 修正你的SQL脚本错误 #

你原来的SQL存在几个问题：

• 表名错误：把products写成了product，还错误地将用户名inventory_planners当成表名进行授权
• 未设置用户密码：无密码的用户无法正常连接数据库
• 缺少序列权限：Postgres中自增列依赖序列，没有对应权限会导致插入操作失败

2. 正确的用户创建与权限授予脚本 #

建议单独创建一个Flyway迁移文件（比如V2__create_restricted_users.sql），将以下内容写入，和表结构迁移脚本分开管理，保持版本可控：

-- 创建用户并设置密码（生产环境请替换为强密码）
CREATE USER product_users WITH PASSWORD 'prod_user_secure_pwd';
CREATE USER inventory_planners WITH PASSWORD 'inv_planner_secure_pwd';
CREATE USER sales_person WITH PASSWORD 'sales_secure_pwd';

-- 给product_users授予products表的权限
GRANT SELECT, INSERT, UPDATE ON TABLE products TO product_users;
-- 授予products表自增序列权限（如果表使用SERIAL/BIGSERIAL类型主键）
GRANT USAGE, SELECT ON SEQUENCE products_id_seq TO product_users;

-- 给inventory_planners授予products和inventory_history表的权限
GRANT SELECT, INSERT, UPDATE ON TABLE products, inventory_history TO inventory_planners;
-- 授予对应序列权限
GRANT USAGE, SELECT ON SEQUENCE products_id_seq, inventory_history_id_seq TO inventory_planners;

-- 给sales_person授予products和purchase_history表的权限
GRANT SELECT, INSERT, UPDATE ON TABLE products, purchase_history TO sales_person;
-- 授予对应序列权限
GRANT USAGE, SELECT ON SEQUENCE products_id_seq, purchase_history_id_seq TO sales_person;

如果你的表没有自增序列（未使用SERIAL类型字段），可以直接去掉所有序列相关的授权语句。

3. Docker Compose配置说明 #

不需要在Postgres服务的配置中额外添加这些用户，因为Flyway会通过超级用户postgres执行迁移脚本，自动完成用户创建和权限授予，保持现有Postgres和Flyway的配置即可。

如果不想硬编码密码到SQL脚本中，可通过环境变量传递：

1. 修改SQL脚本中的密码部分：

CREATE USER product_users WITH PASSWORD '${PRODUCT_USER_PWD}';
CREATE USER inventory_planners WITH PASSWORD '${INV_PLANNER_PWD}';
CREATE USER sales_person WITH PASSWORD '${SALES_PWD}';

2. 在Docker Compose的Flyway服务中添加环境变量：

services:
  flyway:
    environment:
      - PRODUCT_USER_PWD=your_secure_pwd
      - INV_PLANNER_PWD=your_secure_pwd
      - SALES_PWD=your_secure_pwd
      # 其他已有的环境变量

4. 权限验证方法 #

进入Postgres容器，通过以下命令验证权限是否生效：

# 进入Postgres容器
docker exec -it <你的postgres容器名> psql -U postgres -d prod_mgmt

# 切换到product_users用户测试
SET ROLE product_users;
SELECT * FROM products; -- 正常返回结果
SELECT * FROM inventory_history; -- 应提示权限不足
INSERT INTO products (name, price) VALUES ('测试商品', 99); -- 正常插入（需符合表结构）

内容的提问来源于stack exchange，提问作者Dattebayo