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?

cofiem · Accepted Answer · 2017-02-09 23:26:42Z

3

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.

edited Feb 9, 2017 at 23:26

answered Feb 9, 2017 at 23:20

cofiem

1,4241 gold badge18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

luukvhoudt Over a year ago

so CONNECT/table1:SELECT,UPDATE/table2:SELECT,INSERT

akahunahi · Accepted Answer · 2018-11-07 07:55:07Z

0

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.

answered Nov 7, 2018 at 7:55

akahunahi

1,83224 silver badges21 bronze badges

Comments

Nick · Accepted Answer · 2021-02-05 03:01:40Z

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

ProtheanTom · Accepted Answer · 2018-08-06 17:33:59Z

-1

Try:

priv: "public:USAGE/ALL:SELECT"

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

answered Aug 6, 2018 at 17:33

ProtheanTom

5347 silver badges7 bronze badges

1 Comment

luukvhoudt Over a year ago

This doesn't work, syntax should be TABLENAME:TABLE_PRIVELEGE1,TABLE_PRIVELEGE2/DATABASE_PRIVELEGE

Collectives™ on Stack Overflow

Ansible "postgresql_user" module "priv" parameter syntax clearification

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related