def main(): argument_spec = postgres_common_argument_spec() argument_spec.update(db=dict(type='str', aliases=['login_db']), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) # Set some default values: cursor = False db_connection = False result = dict( changed=False, is_available=False, server_version=dict(), ) conn_params = get_conn_params(module, module.params, warn_db_default=False) db_connection = connect_to_db(module, conn_params, fail_on_conn=False) if db_connection is not None: cursor = db_connection.cursor(cursor_factory=DictCursor) # Do job: pg_ping = PgPing(module, cursor) if cursor: # If connection established: result["is_available"], result["server_version"] = pg_ping.do() db_connection.rollback() module.exit_json(**result)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( db=dict(type='str', aliases=['login_db']), filter=dict(type='list'), session_role=dict(type='str'), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) filter_ = module.params["filter"] db_conn_obj = PgDbConn(module) # Do job: pg_info = PgClusterInfo(module, db_conn_obj) module.exit_json(**pg_info.collect(filter_))
def test_postgres_common_argument_spec(self): """ Test for postgresql_common_arg_spec() function. The tested function just returns a dictionary with the default parameters and their values for PostgreSQL modules. The return and expected dictionaries must be compared. """ expected_dict = dict( login_user=dict(default='postgres'), login_password=dict(default='', no_log=True), login_host=dict(default=''), login_unix_socket=dict(default=''), port=dict(type='int', default=5432, aliases=['login_port']), ssl_mode=dict(default='prefer', choices=[ 'allow', 'disable', 'prefer', 'require', 'verify-ca', 'verify-full' ]), ca_cert=dict(aliases=['ssl_rootcert']), ) assert pg.postgres_common_argument_spec() == expected_dict
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( name=dict(type='str', required=True), db=dict(type='str', aliases=['login_db']), value=dict(type='str'), reset=dict(type='bool'), session_role=dict(type='str'), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) name = module.params["name"] value = module.params["value"] reset = module.params["reset"] # Allow to pass values like 1mb instead of 1MB, etc: if value: for unit in POSSIBLE_SIZE_UNITS: if unit in value: value = value.upper() if value and reset: module.fail_json( msg="%s: value and reset params are mutually exclusive" % name) if not value and not reset: module.fail_json( msg="%s: at least one of value or reset param must be specified" % name) conn_params = get_conn_params(module, module.params, warn_db_default=False) db_connection = connect_to_db(module, conn_params, autocommit=True) cursor = db_connection.cursor(cursor_factory=DictCursor) kw = {} # Check server version (needs 9.4 or later): ver = db_connection.server_version if ver < PG_REQ_VER: module.warn("PostgreSQL is %s version but %s or later is required" % (ver, PG_REQ_VER)) kw = dict( changed=False, restart_required=False, value_pretty="", prev_val_pretty="", value={ "value": "", "unit": "" }, ) kw['name'] = name db_connection.close() module.exit_json(**kw) # Set default returned values: restart_required = False changed = False kw['name'] = name kw['restart_required'] = False # Get info about param state: res = param_get(cursor, module, name) current_value = res[0] raw_val = res[1] unit = res[2] boot_val = res[3] context = res[4] if value == 'True': value = 'on' elif value == 'False': value = 'off' kw['prev_val_pretty'] = current_value kw['value_pretty'] = deepcopy(kw['prev_val_pretty']) kw['context'] = context # Do job if context == "internal": module.fail_json( msg="%s: cannot be changed (internal context). See " "https://www.postgresql.org/docs/current/runtime-config-preset.html" % name) if context == "postmaster": restart_required = True # If check_mode, just compare and exit: if module.check_mode: if pretty_to_bytes(value) == pretty_to_bytes(current_value): kw['changed'] = False else: kw['value_pretty'] = value kw['changed'] = True # Anyway returns current raw value in the check_mode: kw['value'] = dict( value=raw_val, unit=unit, ) kw['restart_required'] = restart_required module.exit_json(**kw) # Set param: if value and value != current_value: changed = param_set(cursor, module, name, value, context) kw['value_pretty'] = value # Reset param: elif reset: if raw_val == boot_val: # nothing to change, exit: kw['value'] = dict( value=raw_val, unit=unit, ) module.exit_json(**kw) changed = param_set(cursor, module, name, boot_val, context) if restart_required: module.warn("Restart of PostgreSQL is required for setting %s" % name) cursor.close() db_connection.close() # Reconnect and recheck current value: if context in ('sighup', 'superuser-backend', 'backend', 'superuser', 'user'): db_connection = connect_to_db(module, conn_params, autocommit=True) cursor = db_connection.cursor(cursor_factory=DictCursor) res = param_get(cursor, module, name) # f_ means 'final' f_value = res[0] f_raw_val = res[1] if raw_val == f_raw_val: changed = False else: changed = True kw['value_pretty'] = f_value kw['value'] = dict( value=f_raw_val, unit=unit, ) cursor.close() db_connection.close() kw['changed'] = changed kw['restart_required'] = restart_required module.exit_json(**kw)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( schema=dict(type="str", required=True, aliases=['name']), owner=dict(type="str", default=""), database=dict(type="str", default="postgres", aliases=["db", "login_db"]), cascade_drop=dict(type="bool", default=False), state=dict(type="str", default="present", choices=["absent", "present"]), session_role=dict(type="str"), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) schema = module.params["schema"] owner = module.params["owner"] state = module.params["state"] cascade_drop = module.params["cascade_drop"] changed = False conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=True) cursor = db_connection.cursor(cursor_factory=DictCursor) try: if module.check_mode: if state == "absent": changed = not schema_exists(cursor, schema) elif state == "present": changed = not schema_matches(cursor, schema, owner) module.exit_json(changed=changed, schema=schema) if state == "absent": try: changed = schema_delete(cursor, schema, cascade_drop) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) elif state == "present": try: changed = schema_create(cursor, schema, owner) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) except NotSupportedError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) except SystemExit: # Avoid catching this on Python 2.4 raise except Exception as e: module.fail_json(msg="Database query failed: %s" % to_native(e), exception=traceback.format_exc()) db_connection.close() module.exit_json(changed=changed, schema=schema, queries=executed_queries)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( sequence=dict(type='str', required=True, aliases=['name']), state=dict(type='str', default='present', choices=['absent', 'present']), data_type=dict(type='str', choices=['bigint', 'integer', 'smallint']), increment=dict(type='int'), minvalue=dict(type='int', aliases=['min']), maxvalue=dict(type='int', aliases=['max']), start=dict(type='int'), cache=dict(type='int'), cycle=dict(type='bool', default=False), schema=dict(type='str', default='public'), cascade=dict(type='bool', default=False), rename_to=dict(type='str'), owner=dict(type='str'), newschema=dict(type='str'), db=dict(type='str', default='', aliases=['login_db', 'database']), session_role=dict(type='str'), ) module = AnsibleModule(argument_spec=argument_spec, supports_check_mode=True, mutually_exclusive=[ ['rename_to', 'data_type'], ['rename_to', 'increment'], ['rename_to', 'minvalue'], ['rename_to', 'maxvalue'], ['rename_to', 'start'], ['rename_to', 'cache'], ['rename_to', 'cycle'], ['rename_to', 'cascade'], ['rename_to', 'owner'], ['rename_to', 'newschema'], ['cascade', 'data_type'], ['cascade', 'increment'], ['cascade', 'minvalue'], ['cascade', 'maxvalue'], ['cascade', 'start'], ['cascade', 'cache'], ['cascade', 'cycle'], ['cascade', 'owner'], ['cascade', 'newschema'], ]) # Note: we don't need to check mutually exclusive params here, because they are # checked automatically by AnsibleModule (mutually_exclusive=[] list above). # Change autocommit to False if check_mode: autocommit = not module.check_mode # Connect to DB and make cursor object: conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=autocommit) cursor = db_connection.cursor(cursor_factory=DictCursor) ############## # Create the object and do main job: data = Sequence(module, cursor) # Set defaults: changed = False # Create new sequence if not data.exists and module.params['state'] == 'present': if module.params.get('rename_to'): module.fail_json( msg="Sequence '%s' does not exist, nothing to rename" % module.params['sequence']) if module.params.get('newschema'): module.fail_json( msg= "Sequence '%s' does not exist, change of schema not possible" % module.params['sequence']) changed = data.create() # Drop non-existing sequence elif not data.exists and module.params['state'] == 'absent': # Nothing to do changed = False # Drop existing sequence elif data.exists and module.params['state'] == 'absent': changed = data.drop() # Rename sequence if data.exists and module.params.get('rename_to'): if data.name != module.params['rename_to']: changed = data.rename() if changed: data.new_name = module.params['rename_to'] # Refresh information if module.params['state'] == 'present': data.get_info() # Change owner, schema and settings if module.params['state'] == 'present' and data.exists: # change owner if module.params.get('owner'): if data.owner != module.params['owner']: changed = data.set_owner() # Set schema if module.params.get('newschema'): if data.schema != module.params['newschema']: changed = data.set_schema() if changed: data.new_schema = module.params['newschema'] # Rollback if it's possible and check_mode: if module.check_mode: db_connection.rollback() else: db_connection.commit() cursor.close() db_connection.close() # Make return values: kw = dict( changed=changed, state='present', sequence=data.name, queries=data.executed_queries, schema=data.schema, data_type=data.data_type, increment=data.increment, minvalue=data.minvalue, maxvalue=data.maxvalue, start=data.start_value, cycle=data.cycle, owner=data.owner, ) if module.params['state'] == 'present': if data.new_name: kw['newname'] = data.new_name if data.new_schema: kw['newschema'] = data.new_schema elif module.params['state'] == 'absent': kw['state'] = 'absent' module.exit_json(**kw)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( db=dict(type="str", aliases=["login_db"]), name=dict(type="str", aliases=["slot_name"]), slot_type=dict(type="str", default="physical", choices=["logical", "physical"]), immediately_reserve=dict(type="bool", default=False), session_role=dict(type="str"), output_plugin=dict(type="str", default="test_decoding"), state=dict(type="str", default="present", choices=["absent", "present"]), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) name = module.params["name"] slot_type = module.params["slot_type"] immediately_reserve = module.params["immediately_reserve"] state = module.params["state"] output_plugin = module.params["output_plugin"] if immediately_reserve and slot_type == 'logical': module.fail_json( msg= "Module parameters immediately_reserve and slot_type=logical are mutually exclusive" ) # When slot_type is logical and parameter db is not passed, # the default database will be used to create the slot and # the user should know about this. # When the slot type is physical, # it doesn't matter which database will be used # because physical slots are global objects. if slot_type == 'logical': warn_db_default = True else: warn_db_default = False conn_params = get_conn_params(module, module.params, warn_db_default=warn_db_default) db_connection = connect_to_db(module, conn_params, autocommit=True) cursor = db_connection.cursor(cursor_factory=DictCursor) ################################## # Create an object and do main job pg_slot = PgSlot(module, cursor, name) changed = False if module.check_mode: if state == "present": if not pg_slot.exists: changed = True pg_slot.create(slot_type, immediately_reserve, output_plugin, just_check=True) elif state == "absent": if pg_slot.exists: changed = True else: if state == "absent": pg_slot.drop() elif state == "present": pg_slot.create(slot_type, immediately_reserve, output_plugin) changed = pg_slot.changed db_connection.close() module.exit_json(changed=changed, name=name, queries=pg_slot.executed_queries)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( new_owner=dict(type='str', required=True), obj_name=dict(type='str'), obj_type=dict(type='str', aliases=['type'], choices=[ 'database', 'function', 'matview', 'sequence', 'schema', 'table', 'tablespace', 'view' ]), reassign_owned_by=dict(type='list'), fail_on_role=dict(type='bool', default=True), db=dict(type='str', aliases=['login_db']), session_role=dict(type='str'), ) module = AnsibleModule( argument_spec=argument_spec, mutually_exclusive=[ ['obj_name', 'reassign_owned_by'], ['obj_type', 'reassign_owned_by'], ['obj_name', 'fail_on_role'], ['obj_type', 'fail_on_role'], ], supports_check_mode=True, ) new_owner = module.params['new_owner'] obj_name = module.params['obj_name'] obj_type = module.params['obj_type'] reassign_owned_by = module.params['reassign_owned_by'] fail_on_role = module.params['fail_on_role'] conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=False) cursor = db_connection.cursor(cursor_factory=DictCursor) ############## # Create the object and do main job: pg_ownership = PgOwnership(module, cursor, new_owner) # if we want to change ownership: if obj_name: pg_ownership.set_owner(obj_type, obj_name) # if we want to reassign objects owned by roles: elif reassign_owned_by: pg_ownership.reassign(reassign_owned_by, fail_on_role) # Rollback if it's possible and check_mode: if module.check_mode: db_connection.rollback() else: db_connection.commit() cursor.close() db_connection.close() module.exit_json( changed=pg_ownership.changed, queries=pg_ownership.executed_queries, )
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( groups=dict(type='list', aliases=['group', 'source_role', 'source_roles']), target_roles=dict(type='list', aliases=['target_role', 'user', 'users']), fail_on_role=dict(type='bool', default=True), state=dict(type='str', default='present', choices=['absent', 'present']), db=dict(type='str', aliases=['login_db']), session_role=dict(type='str'), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) groups = module.params['groups'] target_roles = module.params['target_roles'] fail_on_role = module.params['fail_on_role'] state = module.params['state'] conn_params = get_conn_params(module, module.params, warn_db_default=False) db_connection = connect_to_db(module, conn_params, autocommit=False) cursor = db_connection.cursor(cursor_factory=DictCursor) ############## # Create the object and do main job: pg_membership = PgMembership(module, cursor, groups, target_roles, fail_on_role) if state == 'present': pg_membership.grant() elif state == 'absent': pg_membership.revoke() # Rollback if it's possible and check_mode: if module.check_mode: db_connection.rollback() else: db_connection.commit() cursor.close() db_connection.close() # Make return values: return_dict = dict( changed=pg_membership.changed, state=state, groups=pg_membership.groups, target_roles=pg_membership.target_roles, queries=pg_membership.executed_queries, ) if state == 'present': return_dict['granted'] = pg_membership.granted elif state == 'absent': return_dict['revoked'] = pg_membership.revoked module.exit_json(**return_dict)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( name=dict(required=True), db=dict(type='str', aliases=['login_db']), state=dict(type='str', default='present', choices=['absent', 'present', 'refresh', 'stat']), publications=dict(type='list'), connparams=dict(type='dict'), cascade=dict(type='bool', default=False), owner=dict(type='str'), subsparams=dict(type='dict'), relinfo=dict(type='bool', default=False), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) # Parameters handling: db = module.params['db'] name = module.params['name'] state = module.params['state'] publications = module.params['publications'] cascade = module.params['cascade'] owner = module.params['owner'] subsparams = module.params['subsparams'] connparams = module.params['connparams'] relinfo = module.params['relinfo'] if state == 'present' and cascade: module.warn('parameter "cascade" is ignored when state is not absent') if state != 'present': if owner: module.warn( "parameter 'owner' is ignored when state is not 'present'") if publications: module.warn( "parameter 'publications' is ignored when state is not 'present'" ) if connparams: module.warn( "parameter 'connparams' is ignored when state is not 'present'" ) if subsparams: module.warn( "parameter 'subsparams' is ignored when state is not 'present'" ) # Connect to DB and make cursor object: pg_conn_params = get_conn_params(module, module.params) # We check subscription state without DML queries execution, so set autocommit: db_connection = connect_to_db(module, pg_conn_params, autocommit=True) cursor = db_connection.cursor(cursor_factory=DictCursor) # Check version: if cursor.connection.server_version < SUPPORTED_PG_VERSION: module.fail_json( msg="PostgreSQL server version should be 10.0 or greater") # Set defaults: changed = False initial_state = {} final_state = {} ################################### # Create object and do rock'n'roll: subscription = PgSubscription(module, cursor, name, db, relinfo) if subscription.exists: initial_state = deepcopy(subscription.attrs) final_state = deepcopy(initial_state) # If module.check_mode=True, nothing will be changed: if state == 'stat': # Information has been collected already, so nothing is needed: pass elif state == 'present': if not subscription.exists: if subsparams: subsparams = convert_subscr_params(subsparams) if connparams: connparams = convert_conn_params(connparams) changed = subscription.create(connparams, publications, subsparams, check_mode=module.check_mode) else: changed = subscription.update(connparams, publications, subsparams, check_mode=module.check_mode) if owner and subscription.attrs['owner'] != owner: changed = subscription.set_owner(owner, check_mode=module.check_mode) elif state == 'absent': changed = subscription.drop(cascade, check_mode=module.check_mode) elif state == 'refresh': if not subscription.exists: module.fail_json( msg="Refresh failed: subscription '%s' does not exist" % name) # Always returns True: changed = subscription.refresh(check_mode=module.check_mode) # Get final subscription info if needed: if state != 'stat': final_state = subscription.get_info() # Connection is not needed any more: cursor.close() db_connection.close() # Return ret values and exit: module.exit_json(changed=changed, name=name, exists=subscription.exists, queries=subscription.executed_queries, initial_state=initial_state, final_state=final_state)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( idxname=dict(type='str', required=True, aliases=['name']), db=dict(type='str', aliases=['login_db']), state=dict(type='str', default='present', choices=['absent', 'present', 'stat']), concurrent=dict(type='bool', default=True), table=dict(type='str'), idxtype=dict(type='str', aliases=['type']), columns=dict(type='list', aliases=['column']), cond=dict(type='str'), session_role=dict(type='str'), tablespace=dict(type='str'), storage_params=dict(type='list'), cascade=dict(type='bool', default=False), schema=dict(type='str'), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) idxname = module.params["idxname"] state = module.params["state"] concurrent = module.params["concurrent"] table = module.params["table"] idxtype = module.params["idxtype"] columns = module.params["columns"] cond = module.params["cond"] tablespace = module.params["tablespace"] storage_params = module.params["storage_params"] cascade = module.params["cascade"] schema = module.params["schema"] if concurrent and cascade: module.fail_json(msg="Cuncurrent mode and cascade parameters are mutually exclusive") if state == 'present': if not table: module.fail_json(msg="Table must be specified") if not columns: module.fail_json(msg="At least one column must be specified") else: if table or columns or cond or idxtype or tablespace: module.fail_json(msg="Index %s is going to be removed, so it does not " "make sense to pass a table name, columns, conditions, " "index type, or tablespace" % idxname) if cascade and state != 'absent': module.fail_json(msg="cascade parameter used only with state=absent") conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=True) cursor = db_connection.cursor(cursor_factory=DictCursor) # Set defaults: changed = False # Do job: index = Index(module, cursor, schema, idxname) kw = index.get_info() kw['query'] = '' # # check_mode start if module.check_mode: if state == 'stat': if index.exists: kw['stat'] = index.get_stat() kw['changed'] = False module.exit_json(**kw) elif state == 'present' and index.exists: kw['changed'] = False module.exit_json(**kw) elif state == 'present' and not index.exists: kw['changed'] = True module.exit_json(**kw) elif state == 'absent' and not index.exists: kw['changed'] = False module.exit_json(**kw) elif state == 'absent' and index.exists: kw['changed'] = True module.exit_json(**kw) # check_mode end # if state == 'stat': if index.exists: kw['stat'] = index.get_stat() kw['changed'] = False module.exit_json(**kw) elif state == "present": if idxtype and idxtype.upper() not in VALID_IDX_TYPES: module.fail_json(msg="Index type '%s' of %s is not in valid types" % (idxtype, idxname)) columns = ','.join(columns) if storage_params: storage_params = ','.join(storage_params) changed = index.create(table, idxtype, columns, cond, tablespace, storage_params, concurrent) if changed: kw = index.get_info() kw['state'] = 'present' kw['query'] = index.executed_query else: changed = index.drop(schema, cascade, concurrent) if changed: kw['state'] = 'absent' kw['query'] = index.executed_query if not kw['valid']: db_connection.rollback() module.warn("Index %s is invalid! ROLLBACK" % idxname) if not concurrent: db_connection.commit() kw['changed'] = changed db_connection.close() module.exit_json(**kw)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( copy_to=dict(type='path', aliases=['to']), copy_from=dict(type='path', aliases=['from']), src=dict(type='str', aliases=['source']), dst=dict(type='str', aliases=['destination']), columns=dict(type='list', aliases=['column']), options=dict(type='dict'), program=dict(type='bool', default=False), db=dict(type='str', aliases=['login_db']), session_role=dict(type='str'), ) module = AnsibleModule(argument_spec=argument_spec, supports_check_mode=True, mutually_exclusive=[ ['copy_from', 'copy_to'], ['copy_from', 'src'], ['copy_to', 'dst'], ]) # Note: we don't need to check mutually exclusive params here, because they are # checked automatically by AnsibleModule (mutually_exclusive=[] list above). if module.params.get('copy_from') and not module.params.get('dst'): module.fail_json(msg='dst param is necessary with copy_from') elif module.params.get('copy_to') and not module.params.get('src'): module.fail_json(msg='src param is necessary with copy_to') # Connect to DB and make cursor object: conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=False) cursor = db_connection.cursor(cursor_factory=DictCursor) ############## # Create the object and do main job: data = PgCopyData(module, cursor) # Note: parameters like dst, src, etc. are got # from module object into data object of PgCopyData class. # Therefore not need to pass args to the methods below. # Note: check mode is implemented inside the methods below # by checking passed module.check_mode arg. if module.params.get('copy_to'): data.copy_to() elif module.params.get('copy_from'): data.copy_from() # Finish: if module.check_mode: db_connection.rollback() else: db_connection.commit() cursor.close() db_connection.close() # Return some values: module.exit_json( changed=data.changed, queries=data.executed_queries, src=data.src, dst=data.dst, )
def __init__(self): self.params = pg.postgres_common_argument_spec() self.err_msg = '' self.warn_msg = ''
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( db=dict(type="str", required=True, aliases=["login_db"]), lang=dict(type="str", required=True, aliases=["name"]), state=dict(type="str", default="present", choices=["absent", "present"]), trust=dict(type="bool", default="no"), force_trust=dict(type="bool", default="no"), cascade=dict(type="bool", default="no"), fail_on_drop=dict(type="bool", default="yes"), session_role=dict(type="str"), owner=dict(type="str"), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) db = module.params["db"] lang = module.params["lang"] state = module.params["state"] trust = module.params["trust"] force_trust = module.params["force_trust"] cascade = module.params["cascade"] fail_on_drop = module.params["fail_on_drop"] owner = module.params["owner"] conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=False) cursor = db_connection.cursor() changed = False kw = {'db': db, 'lang': lang, 'trust': trust} if state == "present": if lang_exists(cursor, lang): lang_trusted = lang_istrusted(cursor, lang) if (lang_trusted and not trust) or (not lang_trusted and trust): if module.check_mode: changed = True else: changed = lang_altertrust(cursor, lang, trust) else: if module.check_mode: changed = True else: changed = lang_add(cursor, lang, trust) if force_trust: changed = lang_altertrust(cursor, lang, trust) else: if lang_exists(cursor, lang): if module.check_mode: changed = True kw['lang_dropped'] = True else: changed = lang_drop(cursor, lang, cascade) if fail_on_drop and not changed: msg = ( "unable to drop language, use cascade " "to delete dependencies or fail_on_drop=no to ignore") module.fail_json(msg=msg) kw['lang_dropped'] = changed if owner and state == 'present': if lang_exists(cursor, lang): if owner != get_lang_owner(cursor, lang): changed = set_lang_owner(cursor, lang, owner) if changed: if module.check_mode: db_connection.rollback() else: db_connection.commit() kw['changed'] = changed kw['queries'] = executed_queries db_connection.close() module.exit_json(**kw)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( database=dict(required=True, aliases=['db', 'login_db']), state=dict(default='present', choices=['present', 'absent']), privs=dict(required=False, aliases=['priv']), type=dict(default='table', choices=['table', 'sequence', 'function', 'database', 'schema', 'language', 'tablespace', 'group', 'default_privs', 'foreign_data_wrapper', 'foreign_server', 'type', ]), objs=dict(required=False, aliases=['obj']), schema=dict(required=False), roles=dict(required=True, aliases=['role']), session_role=dict(required=False), target_roles=dict(required=False), grant_option=dict(required=False, type='bool', aliases=['admin_option']), host=dict(default='', aliases=['login_host']), unix_socket=dict(default='', aliases=['login_unix_socket']), login=dict(default='postgres', aliases=['login_user']), password=dict(default='', aliases=['login_password'], no_log=True), fail_on_role=dict(type='bool', default=True), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) fail_on_role = module.params['fail_on_role'] # Create type object as namespace for module params p = type('Params', (), module.params) # param "schema": default, allowed depends on param "type" if p.type in ['table', 'sequence', 'function', 'type', 'default_privs']: p.schema = p.schema or 'public' elif p.schema: module.fail_json(msg='Argument "schema" is not allowed ' 'for type "%s".' % p.type) # param "objs": default, required depends on param "type" if p.type == 'database': p.objs = p.objs or p.database elif not p.objs: module.fail_json(msg='Argument "objs" is required ' 'for type "%s".' % p.type) # param "privs": allowed, required depends on param "type" if p.type == 'group': if p.privs: module.fail_json(msg='Argument "privs" is not allowed ' 'for type "group".') elif not p.privs: module.fail_json(msg='Argument "privs" is required ' 'for type "%s".' % p.type) # Connect to Database if not psycopg2: module.fail_json(msg=missing_required_lib('psycopg2'), exception=PSYCOPG2_IMP_ERR) try: conn = Connection(p, module) except psycopg2.Error as e: module.fail_json(msg='Could not connect to database: %s' % to_native(e), exception=traceback.format_exc()) except TypeError as e: if 'sslrootcert' in e.args[0]: module.fail_json(msg='Postgresql server must be at least version 8.4 to support sslrootcert') module.fail_json(msg="unable to connect to database: %s" % to_native(e), exception=traceback.format_exc()) except ValueError as e: # We raise this when the psycopg library is too old module.fail_json(msg=to_native(e)) if p.session_role: try: conn.cursor.execute('SET ROLE "%s"' % p.session_role) except Exception as e: module.fail_json(msg="Could not switch to role %s: %s" % (p.session_role, to_native(e)), exception=traceback.format_exc()) try: # privs if p.privs: privs = frozenset(pr.upper() for pr in p.privs.split(',')) if not privs.issubset(VALID_PRIVS): module.fail_json(msg='Invalid privileges specified: %s' % privs.difference(VALID_PRIVS)) else: privs = None # objs: if p.type == 'table' and p.objs == 'ALL_IN_SCHEMA': objs = conn.get_all_tables_in_schema(p.schema) elif p.type == 'sequence' and p.objs == 'ALL_IN_SCHEMA': objs = conn.get_all_sequences_in_schema(p.schema) elif p.type == 'function' and p.objs == 'ALL_IN_SCHEMA': objs = conn.get_all_functions_in_schema(p.schema) elif p.type == 'default_privs': if p.objs == 'ALL_DEFAULT': objs = frozenset(VALID_DEFAULT_OBJS.keys()) else: objs = frozenset(obj.upper() for obj in p.objs.split(',')) if not objs.issubset(VALID_DEFAULT_OBJS): module.fail_json( msg='Invalid Object set specified: %s' % objs.difference(VALID_DEFAULT_OBJS.keys())) # Again, do we have valid privs specified for object type: valid_objects_for_priv = frozenset(obj for obj in objs if privs.issubset(VALID_DEFAULT_OBJS[obj])) if not valid_objects_for_priv == objs: module.fail_json( msg='Invalid priv specified. Valid object for priv: {0}. Objects: {1}'.format( valid_objects_for_priv, objs)) else: objs = p.objs.split(',') # function signatures are encoded using ':' to separate args if p.type == 'function': objs = [obj.replace(':', ',') for obj in objs] # roles if p.roles == 'PUBLIC': roles = 'PUBLIC' else: roles = p.roles.split(',') if len(roles) == 1 and not role_exists(module, conn.cursor, roles[0]): module.exit_json(changed=False) if fail_on_role: module.fail_json(msg="Role '%s' does not exist" % roles[0].strip()) else: module.warn("Role '%s' does not exist, nothing to do" % roles[0].strip()) # check if target_roles is set with type: default_privs if p.target_roles and not p.type == 'default_privs': module.warn('"target_roles" will be ignored ' 'Argument "type: default_privs" is required for usage of "target_roles".') # target roles if p.target_roles: target_roles = p.target_roles.split(',') else: target_roles = None changed = conn.manipulate_privs( obj_type=p.type, privs=privs, objs=objs, roles=roles, target_roles=target_roles, state=p.state, grant_option=p.grant_option, schema_qualifier=p.schema, fail_on_role=fail_on_role, ) except Error as e: conn.rollback() module.fail_json(msg=e.message, exception=traceback.format_exc()) except psycopg2.Error as e: conn.rollback() module.fail_json(msg=to_native(e.message)) if module.check_mode: conn.rollback() else: conn.commit() module.exit_json(changed=changed, queries=executed_queries)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( name=dict(required=True), db=dict(type='str', aliases=['login_db']), state=dict(type='str', default='present', choices=['absent', 'present']), tables=dict(type='list'), parameters=dict(type='dict'), owner=dict(type='str'), cascade=dict(type='bool', default=False), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) # Parameters handling: name = module.params['name'] state = module.params['state'] tables = module.params['tables'] params = module.params['parameters'] owner = module.params['owner'] cascade = module.params['cascade'] if state == 'absent': if tables: module.warn('parameter "tables" is ignored when "state=absent"') if params: module.warn( 'parameter "parameters" is ignored when "state=absent"') if owner: module.warn('parameter "owner" is ignored when "state=absent"') if state == 'present' and cascade: module.warn('parameter "cascade" is ignored when "state=present"') # Connect to DB and make cursor object: conn_params = get_conn_params(module, module.params) # We check publication state without DML queries execution, so set autocommit: db_connection = connect_to_db(module, conn_params, autocommit=True) cursor = db_connection.cursor(cursor_factory=DictCursor) # Check version: if cursor.connection.server_version < SUPPORTED_PG_VERSION: module.fail_json( msg="PostgreSQL server version should be 10.0 or greater") # Nothing was changed by default: changed = False ################################### # Create object and do rock'n'roll: publication = PgPublication(module, cursor, name) if tables: tables = transform_tables_representation(tables) # If module.check_mode=True, nothing will be changed: if state == 'present': if not publication.exists: changed = publication.create(tables, params, owner, check_mode=module.check_mode) else: changed = publication.update(tables, params, owner, check_mode=module.check_mode) elif state == 'absent': changed = publication.drop(cascade=cascade, check_mode=module.check_mode) # Get final publication info: pub_fin_info = {} if state == 'present' or (state == 'absent' and module.check_mode): pub_fin_info = publication.get_info() elif state == 'absent' and not module.check_mode: publication.exists = False # Connection is not needed any more: cursor.close() db_connection.close() # Update publication info and return ret values: module.exit_json(changed=changed, queries=publication.executed_queries, exists=publication.exists, **pub_fin_info)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( query=dict(type='str'), db=dict(type='str', aliases=['login_db']), positional_args=dict(type='list'), named_args=dict(type='dict'), session_role=dict(type='str'), path_to_script=dict(type='path'), autocommit=dict(type='bool', default=False), ) module = AnsibleModule( argument_spec=argument_spec, mutually_exclusive=(('positional_args', 'named_args'), ), supports_check_mode=True, ) query = module.params["query"] positional_args = module.params["positional_args"] named_args = module.params["named_args"] path_to_script = module.params["path_to_script"] autocommit = module.params["autocommit"] if autocommit and module.check_mode: module.fail_json( msg="Using autocommit is mutually exclusive with check_mode") if positional_args and named_args: module.fail_json( msg="positional_args and named_args params are mutually exclusive") if path_to_script and query: module.fail_json(msg="path_to_script is mutually exclusive with query") if positional_args: positional_args = convert_elements_to_pg_arrays(positional_args) elif named_args: named_args = convert_elements_to_pg_arrays(named_args) if path_to_script: try: query = open(path_to_script, 'r').read() except Exception as e: module.fail_json(msg="Cannot read file '%s' : %s" % (path_to_script, to_native(e))) conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=autocommit) cursor = db_connection.cursor(cursor_factory=DictCursor) # Prepare args: if module.params.get("positional_args"): arguments = module.params["positional_args"] elif module.params.get("named_args"): arguments = module.params["named_args"] else: arguments = None # Set defaults: changed = False # Execute query: try: cursor.execute(query, arguments) except Exception as e: cursor.close() db_connection.close() module.fail_json(msg="Cannot execute SQL '%s' %s: %s" % (query, arguments, to_native(e))) statusmessage = cursor.statusmessage rowcount = cursor.rowcount try: query_result = [dict(row) for row in cursor.fetchall()] except Psycopg2ProgrammingError as e: if to_native(e) == 'no results to fetch': query_result = {} except Exception as e: module.fail_json(msg="Cannot fetch rows from cursor: %s" % to_native(e)) if 'SELECT' not in statusmessage: if 'UPDATE' in statusmessage or 'INSERT' in statusmessage or 'DELETE' in statusmessage: s = statusmessage.split() if len(s) == 3: if statusmessage.split()[2] != '0': changed = True elif len(s) == 2: if statusmessage.split()[1] != '0': changed = True else: changed = True else: changed = True if module.check_mode: db_connection.rollback() else: if not autocommit: db_connection.commit() kw = dict( changed=changed, query=cursor.query, statusmessage=statusmessage, query_result=query_result, rowcount=rowcount if rowcount >= 0 else 0, ) cursor.close() db_connection.close() module.exit_json(**kw)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( user=dict(type='str', required=True, aliases=['name']), password=dict(type='str', default=None, no_log=True), state=dict(type='str', default='present', choices=['absent', 'present']), priv=dict(type='str', default=None), db=dict(type='str', default='', aliases=['login_db']), fail_on_user=dict(type='bool', default='yes', aliases=['fail_on_role']), role_attr_flags=dict(type='str', default=''), encrypted=dict(type='bool', default='yes'), no_password_changes=dict(type='bool', default='no'), expires=dict(type='str', default=None), conn_limit=dict(type='int', default=None), session_role=dict(type='str'), groups=dict(type='list'), ) module = AnsibleModule(argument_spec=argument_spec, supports_check_mode=True) user = module.params["user"] password = module.params["password"] state = module.params["state"] fail_on_user = module.params["fail_on_user"] if module.params['db'] == '' and module.params["priv"] is not None: module.fail_json(msg="privileges require a database to be specified") privs = parse_privs(module.params["priv"], module.params["db"]) no_password_changes = module.params["no_password_changes"] if module.params["encrypted"]: encrypted = "ENCRYPTED" else: encrypted = "UNENCRYPTED" expires = module.params["expires"] conn_limit = module.params["conn_limit"] role_attr_flags = module.params["role_attr_flags"] groups = module.params["groups"] if groups: groups = [e.strip() for e in groups] conn_params = get_conn_params(module, module.params, warn_db_default=False) db_connection = connect_to_db(module, conn_params) cursor = db_connection.cursor(cursor_factory=DictCursor) try: role_attr_flags = parse_role_attrs(cursor, role_attr_flags) except InvalidFlagsError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) kw = dict(user=user) changed = False user_removed = False if state == "present": if user_exists(cursor, user): try: changed = user_alter(db_connection, module, user, password, role_attr_flags, encrypted, expires, no_password_changes, conn_limit) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) else: try: changed = user_add(cursor, user, password, role_attr_flags, encrypted, expires, conn_limit) except psycopg2.ProgrammingError as e: module.fail_json( msg="Unable to add user with given requirement " "due to : %s" % to_native(e), exception=traceback.format_exc()) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) try: changed = grant_privileges(cursor, user, privs) or changed except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) if groups: target_roles = [] target_roles.append(user) pg_membership = PgMembership(module, cursor, groups, target_roles) changed = pg_membership.grant() executed_queries.extend(pg_membership.executed_queries) else: if user_exists(cursor, user): if module.check_mode: changed = True kw['user_removed'] = True else: try: changed = revoke_privileges(cursor, user, privs) user_removed = user_delete(cursor, user) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) changed = changed or user_removed if fail_on_user and not user_removed: msg = "Unable to remove user" module.fail_json(msg=msg) kw['user_removed'] = user_removed if changed: if module.check_mode: db_connection.rollback() else: db_connection.commit() kw['changed'] = changed kw['queries'] = executed_queries module.exit_json(**kw)
def main(): argument_spec = postgres_common_argument_spec() argument_spec.update( table=dict(type='str', required=True, aliases=['name']), state=dict(type='str', default="present", choices=["absent", "present"]), db=dict(type='str', default='', aliases=['login_db']), tablespace=dict(type='str'), owner=dict(type='str'), unlogged=dict(type='bool', default=False), like=dict(type='str'), including=dict(type='str'), rename=dict(type='str'), truncate=dict(type='bool', default=False), columns=dict(type='list'), storage_params=dict(type='list'), session_role=dict(type='str'), cascade=dict(type='bool', default=False), ) module = AnsibleModule( argument_spec=argument_spec, supports_check_mode=True, ) table = module.params["table"] state = module.params["state"] tablespace = module.params["tablespace"] owner = module.params["owner"] unlogged = module.params["unlogged"] like = module.params["like"] including = module.params["including"] newname = module.params["rename"] storage_params = module.params["storage_params"] truncate = module.params["truncate"] columns = module.params["columns"] cascade = module.params["cascade"] if state == 'present' and cascade: module.warn("cascade=true is ignored when state=present") # Check mutual exclusive parameters: if state == 'absent' and (truncate or newname or columns or tablespace or like or storage_params or unlogged or owner or including): module.fail_json(msg="%s: state=absent is mutually exclusive with: " "truncate, rename, columns, tablespace, " "including, like, storage_params, unlogged, owner" % table) if truncate and (newname or columns or like or unlogged or storage_params or owner or tablespace or including): module.fail_json(msg="%s: truncate is mutually exclusive with: " "rename, columns, like, unlogged, including, " "storage_params, owner, tablespace" % table) if newname and (columns or like or unlogged or storage_params or owner or tablespace or including): module.fail_json(msg="%s: rename is mutually exclusive with: " "columns, like, unlogged, including, " "storage_params, owner, tablespace" % table) if like and columns: module.fail_json(msg="%s: like and columns params are mutually exclusive" % table) if including and not like: module.fail_json(msg="%s: including param needs like param specified" % table) conn_params = get_conn_params(module, module.params) db_connection = connect_to_db(module, conn_params, autocommit=False) cursor = db_connection.cursor(cursor_factory=DictCursor) if storage_params: storage_params = ','.join(storage_params) if columns: columns = ','.join(columns) ############## # Do main job: table_obj = Table(table, module, cursor) # Set default returned values: changed = False kw = {} kw['table'] = table kw['state'] = '' if table_obj.exists: kw = dict( table=table, state='present', owner=table_obj.info['owner'], tablespace=table_obj.info['tblspace'], storage_params=table_obj.info['storage_params'], ) if state == 'absent': changed = table_obj.drop(cascade=cascade) elif truncate: changed = table_obj.truncate() elif newname: changed = table_obj.rename(newname) q = table_obj.executed_queries table_obj = Table(newname, module, cursor) table_obj.executed_queries = q elif state == 'present' and not like: changed = table_obj.create(columns, storage_params, tablespace, unlogged, owner) elif state == 'present' and like: changed = table_obj.create_like(like, including, tablespace, unlogged, storage_params) if changed: if module.check_mode: db_connection.rollback() else: db_connection.commit() # Refresh table info for RETURN. # Note, if table has been renamed, it gets info by newname: table_obj.get_info() db_connection.commit() if table_obj.exists: kw = dict( table=table, state='present', owner=table_obj.info['owner'], tablespace=table_obj.info['tblspace'], storage_params=table_obj.info['storage_params'], ) else: # We just change the table state here # to keep other information about the dropped table: kw['state'] = 'absent' kw['queries'] = table_obj.executed_queries kw['changed'] = changed db_connection.close() module.exit_json(**kw)
def main(): argument_spec = pgutils.postgres_common_argument_spec() argument_spec.update( db=dict(type='str', required=True, aliases=['name']), owner=dict(type='str', default=''), template=dict(type='str', default=''), encoding=dict(type='str', default=''), lc_collate=dict(type='str', default=''), lc_ctype=dict(type='str', default=''), state=dict(type='str', default='present', choices=['absent', 'dump', 'present', 'restore']), target=dict(type='path', default=''), target_opts=dict(type='str', default=''), maintenance_db=dict(type='str', default="postgres"), session_role=dict(type='str'), conn_limit=dict(type='str', default=''), tablespace=dict(type='path', default=''), ) module = AnsibleModule(argument_spec=argument_spec, supports_check_mode=True) db = module.params["db"] owner = module.params["owner"] template = module.params["template"] encoding = module.params["encoding"] lc_collate = module.params["lc_collate"] lc_ctype = module.params["lc_ctype"] target = module.params["target"] target_opts = module.params["target_opts"] state = module.params["state"] changed = False maintenance_db = module.params['maintenance_db'] session_role = module.params["session_role"] conn_limit = module.params['conn_limit'] tablespace = module.params['tablespace'] raw_connection = state in ("dump", "restore") if not raw_connection: pgutils.ensure_required_libs(module) # To use defaults values, keyword arguments must be absent, so # check which values are empty and don't include in the **kw # dictionary params_map = { "login_host": "host", "login_user": "******", "login_password": "******", "port": "port", "ssl_mode": "sslmode", "ca_cert": "sslrootcert" } kw = dict((params_map[k], v) for (k, v) in iteritems(module.params) if k in params_map and v != '' and v is not None) # If a login_unix_socket is specified, incorporate it here. is_localhost = "host" not in kw or kw["host"] == "" or kw[ "host"] == "localhost" if is_localhost and module.params["login_unix_socket"] != "": kw["host"] = module.params["login_unix_socket"] if target == "": target = "{0}/{1}.sql".format(os.getcwd(), db) target = os.path.expanduser(target) if not raw_connection: try: db_connection = psycopg2.connect(database=maintenance_db, **kw) # Enable autocommit so we can create databases if psycopg2.__version__ >= '2.4.2': db_connection.autocommit = True else: db_connection.set_isolation_level( psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) cursor = db_connection.cursor( cursor_factory=psycopg2.extras.DictCursor) except pgutils.LibraryError as e: module.fail_json(msg="unable to connect to database: {0}".format( to_native(e)), exception=traceback.format_exc()) except TypeError as e: if 'sslrootcert' in e.args[0]: module.fail_json( msg= 'Postgresql server must be at least version 8.4 to support sslrootcert. Exception: {0}' .format(to_native(e)), exception=traceback.format_exc()) module.fail_json(msg="unable to connect to database: %s" % to_native(e), exception=traceback.format_exc()) except Exception as e: module.fail_json(msg="unable to connect to database: %s" % to_native(e), exception=traceback.format_exc()) if session_role: try: cursor.execute('SET ROLE "%s"' % session_role) except Exception as e: module.fail_json(msg="Could not switch role: %s" % to_native(e), exception=traceback.format_exc()) try: if module.check_mode: if state == "absent": changed = db_exists(cursor, db) elif state == "present": changed = not db_matches(cursor, db, owner, template, encoding, lc_collate, lc_ctype, conn_limit, tablespace) module.exit_json(changed=changed, db=db) if state == "absent": try: changed = db_delete(cursor, db) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) elif state == "present": try: changed = db_create(cursor, db, owner, template, encoding, lc_collate, lc_ctype, conn_limit, tablespace) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) elif state in ("dump", "restore"): method = state == "dump" and db_dump or db_restore try: rc, stdout, stderr, cmd = method(module, target, target_opts, db, **kw) if rc != 0: module.fail_json(msg=stderr, stdout=stdout, rc=rc, cmd=cmd) else: module.exit_json(changed=True, msg=stdout, stderr=stderr, rc=rc, cmd=cmd) except SQLParseError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) except NotSupportedError as e: module.fail_json(msg=to_native(e), exception=traceback.format_exc()) except SystemExit: # Avoid catching this on Python 2.4 raise except Exception as e: module.fail_json(msg="Database query failed: %s" % to_native(e), exception=traceback.format_exc()) module.exit_json(changed=changed, db=db)