Ansible "postgresql_user" module "priv" parameter syntax clearification
In the source for postgresl_user there is a parse_privs function. That seems to be the best source for the expected format of priv:
Format: privileges[/privileges/...]Where: privileges := DATABASE_PRIVILEGES[,DATABASE_PRIVILEGES,...] | TABLE_NAME:TABLE_PRIVILEGES[,TABLE_PRIVILEGES,...]It looks like / is the separator for privileges, and : is the separator for a table name, and the privilege(s) for that table. , separates the privileges for a table.
Completely agree. See here too:
VALID_PRIVS = dict(table=frozenset(('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER', 'ALL')), database=frozenset(('CREATE', 'CONNECT', 'TEMPORARY', 'TEMP', 'ALL')), )Permissions for database must be in that list. I was trying to specify table perms for database.
It looks like this can't be done with just the postgresql_user ansible module. It needs a separate call to the postgresql_privs module. Here's an example to create a read only user on a database and assign it the ability to only connect to the database and read data
# leaving any "become" calls out of here, but you should add them as needed to become # postgres or another user that can assign privs - name: Create user and ensure it has access to the database itself postgresql_user: db: "your_db_name" name: "your_db_username" password: "your_db_password" priv: "CONNECT" # make this user able to read, but not see anything else state: present - name: Grant SELECT to the read only user for default privileges postgresql_privs: db: "your_db_name" privs: SELECT objs: TABLES,SEQUENCES type: default_privs role: "your_db_username" grant_option: no - name: Grant USAGE to the read only user on the specified schema itself postgresql_privs: db: "your_db_name" obj: your_db_schema type: schema privs: USAGE role: "your_db_username" grant_option: no - name: Grant SELECT to user as schema defaults (I think this is still needed, despite above) postgresql_privs: db: "your_db_name" schema: your_db_schema privs: SELECT objs: TABLES,SEQUENCES type: default_privs role: "your_db_username" grant_option: no - name: Grant SELECT to read only user on tables in schema postgresql_privs: db: "your_db_name" privs: SELECT objs: ALL_IN_SCHEMA type: table schema: your_db_schema role: "your_db_username" grant_option: no - name: Grant SELECT to read only user on sequences in schema postgresql_privs: db: "your_db_name" privs: SELECT objs: ALL_IN_SCHEMA type: sequence schema: your_db_schema role: "your_db_username" grant_option: no