postgresql_privs: Impossible to set column-level privileges

[Andersson007]

Copied from https://github.com/ansible-collections/community.general/issues/118 Initially reported by @Andersson007

Copied from https://github.com/ansible/ansible/issues/18955 Initially reported by @jnv

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_privs

ANSIBLE VERSION

ansible 2.3.0.0
  config file = 
  configured module search path = Default w/o overrides
  python version = 2.7.13 (default, Jan 19 2017, 14:48:08) [GCC 6.3.0 20170118]

CONFIGURATION

OS / ENVIRONMENT

Host: Ubuntu 17.04

Target: Ubuntu 14.04, PostgreSQL 9.4

SUMMARY

It is not possible to set privileges for individual columns through the postgresql_privs, expected way to do this (i.e. privs: "SELECT(column,column)") fails. If there is an alternative solution, it is not described in documentation.

STEPS TO REPRODUCE

With PostgreSQL database mydb and table data_sources being setup, I use the following command in my playbook:

- name: Grant privileges to columns in data_sources
  postgresql_privs:
    database: mydb
    roles: mydb_reader
    privs: "SELECT(id,name,type)"
    objs: "data_sources"

EXPECTED RESULTS

Permissions to select only id, name and type columns from table data_sources is granted to the user mydb_reader.

ACTUAL RESULTS

Module fails with the following message:

Invalid privileges specified: frozenset(['TYPE)', 'NAME', 'SELECT(ID'])

NOTES

Attempt to grant privileges through postgresql_user module yields a similar result (Invalid privs specified for table: TYPE) NAME SELECT(ID), which is very similar to the equivalent MySQL's module issue ansible/ansible-modules-core#1120.

I think the solution could be to either do a smarter parsing of the privs parameter, or provide an alternative syntax for column-specific permissions, e.g. : similar to functions objects permissions.

Though I wonder why I can't just pass a list of privileges instead of comma delimeted string? This makes parsing much simpler and can be implemented in backward-compatible manner.

So far the only workaround for me is to execute psql manually.

---

Copied from original issue: ansible/ansible-modules-core#1529

[jnv]

Yay, fourth time!

[klando]

I've look at the code a little bit, I believe the way we evaluate risk and injection is wrong and the solution is do it more carrefuly with psycopg and postgresql utils (quote_ident() and friends).

Will look into that....

[Andersson007]

I've look at the code a little bit, I believe the way we evaluate risk and injection is wrong and the solution is do it more carrefuly with psycopg and postgresql utils (quote_ident() and friends).

Will look into that....

Thanks!

[ilyaevseev]

Still actual. No such problem with mysql module, but our devteam prefers postgresql and needs column-based permissions :-(

[Andersson007]

It wasn't super hard to fix in c.mysql but i have looked at the related postgresql_user code a couple of times and it turned out to be more tricky / dangerous:) If anyone is brave enough for that, i'd be happy to review a corresponding patch though:)