New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

add support for group roles to postgresql_user #11035

Closed
tom-clx opened this Issue May 19, 2015 · 7 comments

Comments

Projects
None yet
9 participants
@tom-clx
Contributor

tom-clx commented May 19, 2015

Issue Type: Feature Idea
Ansible Version: 1.9.0.1
Ansible Configuration: Nothing unusual
Environment: N/A
Summary:

The postgresql_user module doesn't have any provision to manage role membership within other roles (groups). This makes it impossible to manage complex role structures using postgresql_user.

This is typically handled with a grant, or create/alter role syntax:

GRANT role_name [, ...] TO role_name [, ...]

-or-

CREATE ROLE name [ [ WITH ] option [ ... ] ]
...snip...
IN ROLE role_name [, ...]

Relevant documentation links:
http://www.postgresql.org/docs/9.2/static/sql-createrole.html
http://www.postgresql.org/docs/9.2/static/sql-grant.html

Steps To Reproduce:
Here is the expected playbook, with 'in_roles' parameter added to the last play.

- hosts: dev_database

  vars_prompt:
  - name: pg_user
    prompt: "PostgreSQL username"
    private: no

  - name: pg_pass
    prompt: "PostgreSQL password"
    private: yes

  vars:
    myuserprivs: LOGIN,NOREPLICATION
    pggroups:
      - {name: 'mygroup'}
    pgusers:
      - {name: 'myuser', pass: 'apassword', privs: "{{ myuserprivs }}" }

  - name: create postgres group roles.
    with_items: pggroups
    postgresql_user: >
      login_user={{ pg_user }}
      login_password={{ pg_pass }}
      name={{ item.name }}
      db=postgres

  - name: create postgres pgusers.
    with_items: pgusers
    postgresql_user: >
      login_user={{ pg_user }}
      login_password={{ pg_pass }}
      name={{ item.name }}
      db=postgres
      password={{ item.pass }}
      role_attr_flags={{ item.privs }}
      in_roles=mygroup

Expected Results:

I would expect that ansible generate SQL equivalent to the following:

CREATE ROLE mygroup;
CREATE ROLE myuser WITH LOGIN NOREPLICATION PASSWORD 'apassword' IN ROLE mygroup;

Actual Results:

@bcoca bcoca changed the title from postgresql_user doesn't handle "group" roles. to add support for group roles to postgresql_user May 26, 2015

@jimi-c jimi-c removed the P4 label Dec 7, 2015

@kamikaze

This comment has been minimized.

kamikaze commented Jul 18, 2016

Yeah, please do this

@MannerMan

This comment has been minimized.

MannerMan commented Sep 7, 2016

It's already possible to create/manage group roles with postgresql_user and postgresql_privs, although it's not too intuitive.

#Create postgres role "role1"
  - name: Create postgres role
    postgresql_user: >-
      name=role1
      role_attr_flags="NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION"
      state=present
    become: yes
    become_user: "{{ postgresql_admin_user }}"

#Create postgres user "user1"
  - name: Create postgres user
    postgresql_user: >-
      name=user1
      password=user1
      encrypted=True
      state=present
    become: yes
    become_user: "{{ postgresql_admin_user }}"

#Make "user1" member of "role1"
  - name: Setup user memberships
    postgresql_privs: >
        database=postgres
        roles=user1
        objs=role1
        type=group
    become: yes
    become_user: "{{ postgresql_admin_user }}"

#revoke access to public group from "database1"
  - name: Revoke public access to database
    postgresql_privs: >
      db=postgres
      state=absent
      priv=CONNECT,TEMPORARY
      obj=database1
      role=public
      type=database
    become: yes
    become_user: "{{ postgresql_admin_user }}"

#Grant "role1" (and thereby "user1") full access to "database1"
  - name: Setup group privileges
    postgresql_privs: >
        db=postgres
        role=role1
        objs=database1
        privs=ALL
        type=database
    become: yes
    become_user: "{{ postgresql_admin_user }}"

@bcoca bcoca removed the triage label Nov 7, 2016

@jimi-c

This comment has been minimized.

Member

jimi-c commented Jan 6, 2017

Per the above, it seems the postgresql modules writers have made the design decision to separate priv creation from user creation, so I'm going to close this at this time.

If you have any further questions, please let us know by stopping by one of the two mailing lists, as appropriate:

Because this project is very active, we're unlikely to see comments made on closed tickets, but the mailing list is a great way to ask questions, or post if you don't think this particular issue is resolved.

Thank you!

@jimi-c jimi-c closed this Jan 6, 2017

@elgow

This comment has been minimized.

elgow commented Feb 15, 2017

Since I'm on github but not the ansible mailing list I'll comment here in spite of the advice above.

I think it is a mistake to close this issue as a valid design choice on the part of the module authors. The authors of the postgresql database clearly made the opposite choice. They provide the feature of group roles with inheritance for a purpose, and it is widely used for that purpose. It is an extremely unwise design choice to make this feature inaccessible from the ansible module.

@kamikaze

This comment has been minimized.

kamikaze commented Feb 15, 2017

this is stupid

@elgow

This comment has been minimized.

elgow commented Feb 15, 2017

The third example in the comment by MannerMan was just pointed out to me. This does support the use of the postgresql feature, so that solves the main problem. The remaining problem, which should be regarded as a documentation bug, is that the ansible documents do not describe the usage shown in the example. The use of the objs parameter to hold a group role name is not mentioned in the documents. Assuming it works, which I've not yet tried, this important case should be documented.

@3manuek

This comment has been minimized.

3manuek commented Dec 5, 2017

The problem is how the module implements groups as a privilege, not as an object by itself. It will be more intuitive to count with a postgresql_group, which can internally inherit the postgresql_user implementation but using the corresponding defaults on the database.

Although, it must be mentioned that the current implementation actually relies more on the concept of role over user as stated in the postgresql docs:

The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions
 before 8.1, users and groups were distinct kinds of entities, but now there are only roles. 
Any role can act as a user, a group, or both.

@ansibot ansibot added feature and removed feature_idea labels Mar 2, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment