def test_db_run_query_error(no_plan, mocker): mock_cursor = get_mocked_cursor(mocker) mock_cursor.execute.side_effect = psycopg2.InternalError('Error') result, query_output = db.DB(DSN).run_query('SELECT 1', 0) assert result.status == db.Status.ERROR assert (result.stop - result.start) > 0 assert query_output is None mock_cursor.execute.assert_called_once_with('SELECT 1')
def index_create(cursor, module, idxname, tblname, idxtype, columns, cond, concurrent=True): """Create new index""" changed = False if idxtype is None: idxtype = "BTREE" mode = 'CONCURRENTLY' if not concurrent: mode = '' if cond is None: condition = '' else: condition = 'WHERE %s' % cond if cond is not None: cond = " WHERE %s" % cond for column in columns.split(','): column.strip() query = "CREATE INDEX %s %s ON %s USING %s (%s)%s" % ( mode, idxname, tblname, idxtype, columns, condition) try: if index_exists(cursor, idxname): return False cursor.execute(query) # In any case, even the created index is not valid, # the database schema has been changed: changed = True except psycopg2.InternalError as e: if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror, exception=traceback.format_exc()) return changed else: raise psycopg2.InternalError(e) return changed
def index_drop(cursor, module, idxname, concurrent=True): """Drop index""" changed = False if not index_exists(cursor, idxname): return changed mode = 'CONCURRENTLY' if not concurrent: mode = '' query = 'DROP INDEX %s %s' % (mode, idxname) try: cursor.execute(query) changed = True except psycopg2.InternalError as e: if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror, exception=traceback.format_exc()) else: raise psycopg2.InternalError(e) return changed
def user_alter(cursor, module, user, password, role_attr_flags, encrypted, expires, no_password_changes): """Change user password and/or attributes. Return True if changed, False otherwise.""" changed = False # Note: role_attr_flags escaped by parse_role_attrs and encrypted is a literal if user == 'PUBLIC': if password is not None: module.fail_json(msg="cannot change the password for PUBLIC user") elif role_attr_flags != '': module.fail_json( msg="cannot change the role_attr_flags for PUBLIC user") else: return False # Handle passwords. if not no_password_changes and (password is not None or role_attr_flags != ''): # Select password and all flag-like columns in order to verify changes. query_password_data = dict(password=password, expires=expires) select = "SELECT * FROM pg_authid where rolname=%(user)s" cursor.execute(select, {"user": user}) # Grab current role attributes. current_role_attrs = cursor.fetchone() # Do we actually need to do anything? pwchanging = False if password is not None: if encrypted: if password.startswith('md5'): if password != current_role_attrs['rolpassword']: pwchanging = True else: try: from passlib.hash import postgres_md5 as pm if pm.encrypt( password, user) != current_role_attrs['rolpassword']: pwchanging = True except ImportError: # Cannot check if passlib is not installed, so assume password is different pwchanging = True else: if password != current_role_attrs['rolpassword']: pwchanging = True role_attr_flags_changing = False if role_attr_flags: role_attr_flags_dict = {} for r in role_attr_flags.split(' '): if r.startswith('NO'): role_attr_flags_dict[r.replace('NO', '', 1)] = False else: role_attr_flags_dict[r] = True for role_attr_name, role_attr_value in role_attr_flags_dict.items( ): if current_role_attrs[PRIV_TO_AUTHID_COLUMN[ role_attr_name]] != role_attr_value: role_attr_flags_changing = True expires_changing = (expires is not None and expires == current_roles_attrs['rol_valid_until']) if not pwchanging and not role_attr_flags_changing and not expires_changing: return False alter = [ 'ALTER USER %(user)s' % { "user": pg_quote_identifier(user, 'role') } ] if pwchanging: alter.append("WITH %(crypt)s" % {"crypt": encrypted}) alter.append("PASSWORD %(password)s") alter.append(role_attr_flags) elif role_attr_flags: alter.append('WITH %s' % role_attr_flags) if expires is not None: alter.append("VALID UNTIL %(expires)s") try: cursor.execute(' '.join(alter), query_password_data) except psycopg2.InternalError: e = get_exception() if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror) return changed else: raise psycopg2.InternalError(e) # Grab new role attributes. cursor.execute(select, {"user": user}) new_role_attrs = cursor.fetchone() # Detect any differences between current_ and new_role_attrs. for i in range(len(current_role_attrs)): if current_role_attrs[i] != new_role_attrs[i]: changed = True elif no_password_changes and role_attr_flags != '': # Grab role information from pg_roles instead of pg_authid select = "SELECT * FROM pg_roles where rolname=%(user)s" cursor.execute(select, {"user": user}) # Grab current role attributes. current_role_attrs = cursor.fetchone() role_attr_flags_changing = False if role_attr_flags: role_attr_flags_dict = {} for r in role_attr_flags.split(' '): if r.startswith('NO'): role_attr_flags_dict[r.replace('NO', '', 1)] = False else: role_attr_flags_dict[r] = True for role_attr_name, role_attr_value in role_attr_flags_dict.items( ): if current_role_attrs[PRIV_TO_AUTHID_COLUMN[ role_attr_name]] != role_attr_value: role_attr_flags_changing = True if not role_attr_flags_changing: return False alter = [ 'ALTER USER %(user)s' % { "user": pg_quote_identifier(user, 'role') } ] if role_attr_flags: alter.append('WITH %s' % role_attr_flags) try: cursor.execute(' '.join(alter)) except psycopg2.InternalError: e = get_exception() if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror) return changed else: raise psycopg2.InternalError(e) # Grab new role attributes. cursor.execute(select, {"user": user}) new_role_attrs = cursor.fetchone() # Detect any differences between current_ and new_role_attrs. for i in range(len(current_role_attrs)): if current_role_attrs[i] != new_role_attrs[i]: changed = True return changed
def user_alter(cursor, module, user, password, role_attr_flags, encrypted, expires, no_password_changes): """Change user password and/or attributes. Return True if changed, False otherwise.""" changed = False # Note: role_attr_flags escaped by parse_role_attrs and encrypted is a literal if user == 'PUBLIC': if password is not None: module.fail_json(msg="cannot change the password for PUBLIC user") elif role_attr_flags != '': module.fail_json( msg="cannot change the role_attr_flags for PUBLIC user") else: return False # Handle passwords. if not no_password_changes and (password is not None or role_attr_flags != '' or expires is not None): # Select password and all flag-like columns in order to verify changes. select = "SELECT * FROM pg_authid where rolname=%(user)s" cursor.execute(select, {"user": user}) # Grab current role attributes. current_role_attrs = cursor.fetchone() # Do we actually need to do anything? pwchanging = False if password is not None: # 32: MD5 hashes are represented as a sequence of 32 hexadecimal digits # 3: The size of the 'md5' prefix # When the provided password looks like a MD5-hash, value of # 'encrypted' is ignored. if ((password.startswith('md5') and len(password) == 32 + 3) or encrypted == 'UNENCRYPTED'): if password != current_role_attrs['rolpassword']: pwchanging = True elif encrypted == 'ENCRYPTED': hashed_password = '******'.format( md5(to_bytes(password) + to_bytes(user)).hexdigest()) if hashed_password != current_role_attrs['rolpassword']: pwchanging = True role_attr_flags_changing = False if role_attr_flags: role_attr_flags_dict = {} for r in role_attr_flags.split(' '): if r.startswith('NO'): role_attr_flags_dict[r.replace('NO', '', 1)] = False else: role_attr_flags_dict[r] = True for role_attr_name, role_attr_value in role_attr_flags_dict.items( ): if current_role_attrs[PRIV_TO_AUTHID_COLUMN[ role_attr_name]] != role_attr_value: role_attr_flags_changing = True if expires is not None: cursor.execute("SELECT %s::timestamptz;", (expires, )) expires_with_tz = cursor.fetchone()[0] expires_changing = expires_with_tz != current_role_attrs.get( 'rolvaliduntil') else: expires_changing = False if not pwchanging and not role_attr_flags_changing and not expires_changing: return False alter = [ 'ALTER USER %(user)s' % { "user": pg_quote_identifier(user, 'role') } ] if pwchanging: alter.append("WITH %(crypt)s" % {"crypt": encrypted}) alter.append("PASSWORD %(password)s") alter.append(role_attr_flags) elif role_attr_flags: alter.append('WITH %s' % role_attr_flags) if expires is not None: alter.append("VALID UNTIL %(expires)s") query_password_data = dict(password=password, expires=expires) try: cursor.execute(' '.join(alter), query_password_data) changed = True except psycopg2.InternalError: e = get_exception() if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror) return changed else: raise psycopg2.InternalError(e) elif no_password_changes and role_attr_flags != '': # Grab role information from pg_roles instead of pg_authid select = "SELECT * FROM pg_roles where rolname=%(user)s" cursor.execute(select, {"user": user}) # Grab current role attributes. current_role_attrs = cursor.fetchone() role_attr_flags_changing = False if role_attr_flags: role_attr_flags_dict = {} for r in role_attr_flags.split(' '): if r.startswith('NO'): role_attr_flags_dict[r.replace('NO', '', 1)] = False else: role_attr_flags_dict[r] = True for role_attr_name, role_attr_value in role_attr_flags_dict.items( ): if current_role_attrs[PRIV_TO_AUTHID_COLUMN[ role_attr_name]] != role_attr_value: role_attr_flags_changing = True if not role_attr_flags_changing: return False alter = [ 'ALTER USER %(user)s' % { "user": pg_quote_identifier(user, 'role') } ] if role_attr_flags: alter.append('WITH %s' % role_attr_flags) try: cursor.execute(' '.join(alter)) except psycopg2.InternalError: e = get_exception() if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror) return changed else: raise psycopg2.InternalError(e) # Grab new role attributes. cursor.execute(select, {"user": user}) new_role_attrs = cursor.fetchone() # Detect any differences between current_ and new_role_attrs. changed = current_role_attrs != new_role_attrs return changed
def user_alter(db_connection, module, user, password, role_attr_flags, encrypted, expires, no_password_changes, conn_limit): """Change user password and/or attributes. Return True if changed, False otherwise.""" changed = False cursor = db_connection.cursor(cursor_factory=psycopg2.extras.DictCursor) # Note: role_attr_flags escaped by parse_role_attrs and encrypted is a # literal if user == 'PUBLIC': if password is not None: module.fail_json(msg="cannot change the password for PUBLIC user") elif role_attr_flags != '': module.fail_json( msg="cannot change the role_attr_flags for PUBLIC user") else: return False # Handle passwords. if not no_password_changes and (password is not None or role_attr_flags != '' or expires is not None or conn_limit is not None): # Select password and all flag-like columns in order to verify changes. try: select = "SELECT * FROM pg_authid where rolname=%(user)s" cursor.execute(select, {"user": user}) # Grab current role attributes. current_role_attrs = cursor.fetchone() except psycopg2.ProgrammingError: current_role_attrs = None db_connection.rollback() pwchanging = user_should_we_change_password(current_role_attrs, user, password, encrypted) if current_role_attrs is None: try: # AWS RDS instances does not allow user to access pg_authid # so try to get current_role_attrs from pg_roles tables select = "SELECT * FROM pg_roles where rolname=%(user)s" cursor.execute(select, {"user": user}) # Grab current role attributes from pg_roles current_role_attrs = cursor.fetchone() except psycopg2.ProgrammingError as e: db_connection.rollback() module.fail_json( msg="Failed to get role details for current user %s: %s" % (user, e)) role_attr_flags_changing = False if role_attr_flags: role_attr_flags_dict = {} for r in role_attr_flags.split(' '): if r.startswith('NO'): role_attr_flags_dict[r.replace('NO', '', 1)] = False else: role_attr_flags_dict[r] = True for role_attr_name, role_attr_value in role_attr_flags_dict.items( ): if current_role_attrs[PRIV_TO_AUTHID_COLUMN[ role_attr_name]] != role_attr_value: role_attr_flags_changing = True if expires is not None: cursor.execute("SELECT %s::timestamptz;", (expires, )) expires_with_tz = cursor.fetchone()[0] expires_changing = expires_with_tz != current_role_attrs.get( 'rolvaliduntil') else: expires_changing = False conn_limit_changing = ( conn_limit is not None and conn_limit != current_role_attrs['rolconnlimit']) if not pwchanging and not role_attr_flags_changing and not expires_changing and not conn_limit_changing: return False alter = [ 'ALTER USER %(user)s' % { "user": pg_quote_identifier(user, 'role') } ] if pwchanging: if password != '': alter.append("WITH %(crypt)s" % {"crypt": encrypted}) alter.append("PASSWORD %(password)s") else: alter.append("WITH PASSWORD NULL") alter.append(role_attr_flags) elif role_attr_flags: alter.append('WITH %s' % role_attr_flags) if expires is not None: alter.append("VALID UNTIL %(expires)s") if conn_limit is not None: alter.append("CONNECTION LIMIT %(conn_limit)s" % {"conn_limit": conn_limit}) query_password_data = dict(password=password, expires=expires) try: cursor.execute(' '.join(alter), query_password_data) changed = True except psycopg2.InternalError as e: if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror, exception=traceback.format_exc()) return changed else: raise psycopg2.InternalError(e) except psycopg2.NotSupportedError as e: module.fail_json(msg=e.pgerror, exception=traceback.format_exc()) elif no_password_changes and role_attr_flags != '': # Grab role information from pg_roles instead of pg_authid select = "SELECT * FROM pg_roles where rolname=%(user)s" cursor.execute(select, {"user": user}) # Grab current role attributes. current_role_attrs = cursor.fetchone() role_attr_flags_changing = False if role_attr_flags: role_attr_flags_dict = {} for r in role_attr_flags.split(' '): if r.startswith('NO'): role_attr_flags_dict[r.replace('NO', '', 1)] = False else: role_attr_flags_dict[r] = True for role_attr_name, role_attr_value in role_attr_flags_dict.items( ): if current_role_attrs[PRIV_TO_AUTHID_COLUMN[ role_attr_name]] != role_attr_value: role_attr_flags_changing = True if not role_attr_flags_changing: return False alter = [ 'ALTER USER %(user)s' % { "user": pg_quote_identifier(user, 'role') } ] if role_attr_flags: alter.append('WITH %s' % role_attr_flags) try: cursor.execute(' '.join(alter)) except psycopg2.InternalError as e: if e.pgcode == '25006': # Handle errors due to read-only transactions indicated by pgcode 25006 # ERROR: cannot execute ALTER ROLE in a read-only transaction changed = False module.fail_json(msg=e.pgerror, exception=traceback.format_exc()) return changed else: raise psycopg2.InternalError(e) # Grab new role attributes. cursor.execute(select, {"user": user}) new_role_attrs = cursor.fetchone() # Detect any differences between current_ and new_role_attrs. changed = current_role_attrs != new_role_attrs return changed