如何用Ansible创建PostgreSQL只读用户并解决新表权限问题

问题场景 #

我用以下Ansible代码创建PostgreSQL用户，运行正常：

- name: create database user
  become_user: postgres
  postgresql_user: db={{ db_name }} name=username password=userpassword priv=CONNECT encrypted=yes state=present

但需要创建仅拥有public schema下所有表（包括后续新建表）只读权限的用户，对应的SQL语句可以实现这个需求：

CREATE USER rouser WITH PASSWORD 'pass';
GRANT CONNECT ON DATABASE mydb TO rouser;
GRANT USAGE ON SCHEMA public TO rouser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rouser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rouser;

我尝试用以下Ansible代码实现，但后续其他用户在同一数据库中创建新表时，该只读用户没有这些新表的SELECT权限：

- name: modify user role 
  become_user: postgres 
  postgresql_privs: 
   db: mydb 
   role: qarouser 
   objs: ALL_IN_SCHEMA 
   privs: SELECT

解决方法 #

问题核心是postgresql_privs模块的ALL_IN_SCHEMA仅对已存在的表授权，没有配置默认权限（对应SQL里的ALTER DEFAULT PRIVILEGES）。需要补充默认权限的配置步骤，完整的Ansible任务如下：

1. 创建只读用户并授予数据库连接权限

- name: create read-only database user
  become_user: postgres
  postgresql_user:
    db: "{{ db_name }}"
    name: qarouser
    password: "userpassword"
    priv: "CONNECT"
    encrypted: yes
    state: present

2. 授予public schema的使用权限

- name: grant usage on public schema to read-only user
  become_user: postgres
  postgresql_privs:
    db: "{{ db_name }}"
    role: qarouser
    objs: SCHEMA:public
    privs: USAGE
    state: present

3. 授予现有表的SELECT权限

- name: grant select on existing tables in public schema
  become_user: postgres
  postgresql_privs:
    db: "{{ db_name }}"
    role: qarouser
    objs: ALL_IN_SCHEMA:public
    privs: SELECT
    state: present

4. 设置默认权限，确保后续新建表自动赋予SELECT权限

- name: set default privileges for future tables in public schema
  become_user: postgres
  postgresql_privs:
    db: "{{ db_name }}"
    role: qarouser
    objs: DEFAULT_TABLES_IN_SCHEMA:public
    privs: SELECT
    state: present

关键说明 #

• DEFAULT_TABLES_IN_SCHEMA:public参数对应SQL中的ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rouser;，会自动为后续在public schema创建的所有表赋予只读用户SELECT权限。
• 确保执行任务的postgres用户拥有足够权限修改角色和默认权限。

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