1#!/usr/bin/python
2# -*- coding: utf-8 -*-
3# Copyright: Ansible Project
4# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)
5
6from __future__ import absolute_import, division, print_function
7__metaclass__ = type
8
9
10ANSIBLE_METADATA = {'metadata_version': '1.1',
11                    'status': ['preview'],
12                    'supported_by': 'community'}
13
14
15DOCUMENTATION = """
16---
17module: vertica_user
18version_added: '2.0'
19short_description: Adds or removes Vertica database users and assigns roles.
20description:
21  - Adds or removes Vertica database user and, optionally, assigns roles.
22  - A user will not be removed until all the dependencies have been dropped.
23  - In such a situation, if the module tries to remove the user it
24    will fail and only remove roles granted to the user.
25options:
26  name:
27    description:
28      - Name of the user to add or remove.
29    required: true
30  profile:
31    description:
32      - Sets the user's profile.
33  resource_pool:
34    description:
35      - Sets the user's resource pool.
36  password:
37    description:
38      - The user's password encrypted by the MD5 algorithm.
39      - The password must be generated with the format C("md5" + md5[password + username]),
40        resulting in a total of 35 characters. An easy way to do this is by querying
41        the Vertica database with select 'md5'||md5('<user_password><user_name>').
42  expired:
43    description:
44      - Sets the user's password expiration.
45    type: bool
46  ldap:
47    description:
48      - Set to true if users are authenticated via LDAP.
49      - The user will be created with password expired and set to I($ldap$).
50    type: bool
51  roles:
52    description:
53      - Comma separated list of roles to assign to the user.
54    aliases: ['role']
55  state:
56    description:
57      - Whether to create C(present), drop C(absent) or lock C(locked) a user.
58    choices: ['present', 'absent', 'locked']
59    default: present
60  db:
61    description:
62      - Name of the Vertica database.
63  cluster:
64    description:
65      - Name of the Vertica cluster.
66    default: localhost
67  port:
68    description:
69      - Vertica cluster port to connect to.
70    default: 5433
71  login_user:
72    description:
73      - The username used to authenticate with.
74    default: dbadmin
75  login_password:
76    description:
77      - The password used to authenticate with.
78notes:
79  - The default authentication assumes that you are either logging in as or sudo'ing
80    to the C(dbadmin) account on the host.
81  - This module uses C(pyodbc), a Python ODBC database adapter. You must ensure
82    that C(unixODBC) and C(pyodbc) is installed on the host and properly configured.
83  - Configuring C(unixODBC) for Vertica requires C(Driver = /opt/vertica/lib64/libverticaodbc.so)
84    to be added to the C(Vertica) section of either C(/etc/odbcinst.ini) or C($HOME/.odbcinst.ini)
85    and both C(ErrorMessagesPath = /opt/vertica/lib64) and C(DriverManagerEncoding = UTF-16)
86    to be added to the C(Driver) section of either C(/etc/vertica.ini) or C($HOME/.vertica.ini).
87requirements: [ 'unixODBC', 'pyodbc' ]
88author: "Dariusz Owczarek (@dareko)"
89"""
90
91EXAMPLES = """
92- name: creating a new vertica user with password
93  vertica_user: name=user_name password=md5<encrypted_password> db=db_name state=present
94
95- name: creating a new vertica user authenticated via ldap with roles assigned
96  vertica_user:
97    name=user_name
98    ldap=true
99    db=db_name
100    roles=schema_name_ro
101    state=present
102"""
103import traceback
104
105PYODBC_IMP_ERR = None
106try:
107    import pyodbc
108except ImportError:
109    PYODBC_IMP_ERR = traceback.format_exc()
110    pyodbc_found = False
111else:
112    pyodbc_found = True
113
114from ansible.module_utils.basic import AnsibleModule, missing_required_lib
115from ansible.module_utils._text import to_native
116
117
118class NotSupportedError(Exception):
119    pass
120
121
122class CannotDropError(Exception):
123    pass
124
125# module specific functions
126
127
128def get_user_facts(cursor, user=''):
129    facts = {}
130    cursor.execute("""
131        select u.user_name, u.is_locked, u.lock_time,
132        p.password, p.acctexpired as is_expired,
133        u.profile_name, u.resource_pool,
134        u.all_roles, u.default_roles
135        from users u join password_auditor p on p.user_id = u.user_id
136        where not u.is_super_user
137        and (? = '' or u.user_name ilike ?)
138    """, user, user)
139    while True:
140        rows = cursor.fetchmany(100)
141        if not rows:
142            break
143        for row in rows:
144            user_key = row.user_name.lower()
145            facts[user_key] = {
146                'name': row.user_name,
147                'locked': str(row.is_locked),
148                'password': row.password,
149                'expired': str(row.is_expired),
150                'profile': row.profile_name,
151                'resource_pool': row.resource_pool,
152                'roles': [],
153                'default_roles': []}
154            if row.is_locked:
155                facts[user_key]['locked_time'] = str(row.lock_time)
156            if row.all_roles:
157                facts[user_key]['roles'] = row.all_roles.replace(' ', '').split(',')
158            if row.default_roles:
159                facts[user_key]['default_roles'] = row.default_roles.replace(' ', '').split(',')
160    return facts
161
162
163def update_roles(user_facts, cursor, user,
164                 existing_all, existing_default, required):
165    del_roles = list(set(existing_all) - set(required))
166    if del_roles:
167        cursor.execute("revoke {0} from {1}".format(','.join(del_roles), user))
168    new_roles = list(set(required) - set(existing_all))
169    if new_roles:
170        cursor.execute("grant {0} to {1}".format(','.join(new_roles), user))
171    if required:
172        cursor.execute("alter user {0} default role {1}".format(user, ','.join(required)))
173
174
175def check(user_facts, user, profile, resource_pool,
176          locked, password, expired, ldap, roles):
177    user_key = user.lower()
178    if user_key not in user_facts:
179        return False
180    if profile and profile != user_facts[user_key]['profile']:
181        return False
182    if resource_pool and resource_pool != user_facts[user_key]['resource_pool']:
183        return False
184    if locked != (user_facts[user_key]['locked'] == 'True'):
185        return False
186    if password and password != user_facts[user_key]['password']:
187        return False
188    if (expired is not None and expired != (user_facts[user_key]['expired'] == 'True') or
189            ldap is not None and ldap != (user_facts[user_key]['expired'] == 'True')):
190        return False
191    if roles and (sorted(roles) != sorted(user_facts[user_key]['roles']) or
192                  sorted(roles) != sorted(user_facts[user_key]['default_roles'])):
193        return False
194    return True
195
196
197def present(user_facts, cursor, user, profile, resource_pool,
198            locked, password, expired, ldap, roles):
199    user_key = user.lower()
200    if user_key not in user_facts:
201        query_fragments = ["create user {0}".format(user)]
202        if locked:
203            query_fragments.append("account lock")
204        if password or ldap:
205            if password:
206                query_fragments.append("identified by '{0}'".format(password))
207            else:
208                query_fragments.append("identified by '$ldap$'")
209        if expired or ldap:
210            query_fragments.append("password expire")
211        if profile:
212            query_fragments.append("profile {0}".format(profile))
213        if resource_pool:
214            query_fragments.append("resource pool {0}".format(resource_pool))
215        cursor.execute(' '.join(query_fragments))
216        if resource_pool and resource_pool != 'general':
217            cursor.execute("grant usage on resource pool {0} to {1}".format(
218                resource_pool, user))
219        update_roles(user_facts, cursor, user, [], [], roles)
220        user_facts.update(get_user_facts(cursor, user))
221        return True
222    else:
223        changed = False
224        query_fragments = ["alter user {0}".format(user)]
225        if locked is not None and locked != (user_facts[user_key]['locked'] == 'True'):
226            if locked:
227                state = 'lock'
228            else:
229                state = 'unlock'
230            query_fragments.append("account {0}".format(state))
231            changed = True
232        if password and password != user_facts[user_key]['password']:
233            query_fragments.append("identified by '{0}'".format(password))
234            changed = True
235        if ldap:
236            if ldap != (user_facts[user_key]['expired'] == 'True'):
237                query_fragments.append("password expire")
238                changed = True
239        elif expired is not None and expired != (user_facts[user_key]['expired'] == 'True'):
240            if expired:
241                query_fragments.append("password expire")
242                changed = True
243            else:
244                raise NotSupportedError("Unexpiring user password is not supported.")
245        if profile and profile != user_facts[user_key]['profile']:
246            query_fragments.append("profile {0}".format(profile))
247            changed = True
248        if resource_pool and resource_pool != user_facts[user_key]['resource_pool']:
249            query_fragments.append("resource pool {0}".format(resource_pool))
250            if user_facts[user_key]['resource_pool'] != 'general':
251                cursor.execute("revoke usage on resource pool {0} from {1}".format(
252                    user_facts[user_key]['resource_pool'], user))
253            if resource_pool != 'general':
254                cursor.execute("grant usage on resource pool {0} to {1}".format(
255                    resource_pool, user))
256            changed = True
257        if changed:
258            cursor.execute(' '.join(query_fragments))
259        if roles and (sorted(roles) != sorted(user_facts[user_key]['roles']) or
260                      sorted(roles) != sorted(user_facts[user_key]['default_roles'])):
261            update_roles(user_facts, cursor, user,
262                         user_facts[user_key]['roles'], user_facts[user_key]['default_roles'], roles)
263            changed = True
264        if changed:
265            user_facts.update(get_user_facts(cursor, user))
266        return changed
267
268
269def absent(user_facts, cursor, user, roles):
270    user_key = user.lower()
271    if user_key in user_facts:
272        update_roles(user_facts, cursor, user,
273                     user_facts[user_key]['roles'], user_facts[user_key]['default_roles'], [])
274        try:
275            cursor.execute("drop user {0}".format(user_facts[user_key]['name']))
276        except pyodbc.Error:
277            raise CannotDropError("Dropping user failed due to dependencies.")
278        del user_facts[user_key]
279        return True
280    else:
281        return False
282
283# module logic
284
285
286def main():
287
288    module = AnsibleModule(
289        argument_spec=dict(
290            user=dict(required=True, aliases=['name']),
291            profile=dict(default=None),
292            resource_pool=dict(default=None),
293            password=dict(default=None, no_log=True),
294            expired=dict(type='bool', default=None),
295            ldap=dict(type='bool', default=None),
296            roles=dict(default=None, aliases=['role']),
297            state=dict(default='present', choices=['absent', 'present', 'locked']),
298            db=dict(default=None),
299            cluster=dict(default='localhost'),
300            port=dict(default='5433'),
301            login_user=dict(default='dbadmin'),
302            login_password=dict(default=None, no_log=True),
303        ), supports_check_mode=True)
304
305    if not pyodbc_found:
306        module.fail_json(msg=missing_required_lib('pyodbc'), exception=PYODBC_IMP_ERR)
307
308    user = module.params['user']
309    profile = module.params['profile']
310    if profile:
311        profile = profile.lower()
312    resource_pool = module.params['resource_pool']
313    if resource_pool:
314        resource_pool = resource_pool.lower()
315    password = module.params['password']
316    expired = module.params['expired']
317    ldap = module.params['ldap']
318    roles = []
319    if module.params['roles']:
320        roles = module.params['roles'].split(',')
321        roles = filter(None, roles)
322    state = module.params['state']
323    if state == 'locked':
324        locked = True
325    else:
326        locked = False
327    db = ''
328    if module.params['db']:
329        db = module.params['db']
330
331    changed = False
332
333    try:
334        dsn = (
335            "Driver=Vertica;"
336            "Server={0};"
337            "Port={1};"
338            "Database={2};"
339            "User={3};"
340            "Password={4};"
341            "ConnectionLoadBalance={5}"
342        ).format(module.params['cluster'], module.params['port'], db,
343                 module.params['login_user'], module.params['login_password'], 'true')
344        db_conn = pyodbc.connect(dsn, autocommit=True)
345        cursor = db_conn.cursor()
346    except Exception as e:
347        module.fail_json(msg="Unable to connect to database: {0}.".format(e))
348
349    try:
350        user_facts = get_user_facts(cursor)
351        if module.check_mode:
352            changed = not check(user_facts, user, profile, resource_pool,
353                                locked, password, expired, ldap, roles)
354        elif state == 'absent':
355            try:
356                changed = absent(user_facts, cursor, user, roles)
357            except pyodbc.Error as e:
358                module.fail_json(msg=to_native(e), exception=traceback.format_exc())
359        elif state in ['present', 'locked']:
360            try:
361                changed = present(user_facts, cursor, user, profile, resource_pool,
362                                  locked, password, expired, ldap, roles)
363            except pyodbc.Error as e:
364                module.fail_json(msg=to_native(e), exception=traceback.format_exc())
365    except NotSupportedError as e:
366        module.fail_json(msg=to_native(e), ansible_facts={'vertica_users': user_facts})
367    except CannotDropError as e:
368        module.fail_json(msg=to_native(e), ansible_facts={'vertica_users': user_facts})
369    except SystemExit:
370        # avoid catching this on python 2.4
371        raise
372    except Exception as e:
373        module.fail_json(msg=to_native(e), exception=traceback.format_exc())
374
375    module.exit_json(changed=changed, user=user, ansible_facts={'vertica_users': user_facts})
376
377
378if __name__ == '__main__':
379    main()
380