Ansible "postgresql_user" module "priv" parameter syntax clearification

Question

The documentation for the postgresql_user module on how privileges for a user should be defined conflicts with itself regarding the format. The format is described as such in the options table:

priv | PostgreSQL privileges string in the format: table:priv1,priv2

However, the examples given below use another format

priv: "CONNECT/products:ALL"
priv: "ALL/products:ALL"
# Example privileges string format
INSERT,UPDATE/table:SELECT/anothertable:ALL

The blog post Ansible Loves PostgreSQL mentions yet another format:

priv: Privileges in “priv1/priv2” or table privileges in “table:priv1,priv2,…” format

I'm having trouble creating users with read-only access, i.e. SELECT privilege on all tables.

Could someone shed some light on the correct format to use, exemplified by giving a user read-only access on all tables?

Answer 1

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.

Answer 2

Try:

priv: "public:USAGE/ALL:SELECT"

This grants USAGE privileges for "public" schema and SELECT privileges for ALL tables in the schema

Answer 3

Completely agree. See here too:

https://github.com/ansible/ansible-modules-core/blob/devel/database/postgresql/postgresql_user.py#L201

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.

Answer 4

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