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