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=''),
dump_extra_args=dict(type='str', default=None),
)
module = AssibleModule(
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']
dump_extra_args = module.params['dump_extra_args']
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 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, executed_commands=executed_commands)
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:
if state == 'dump':
rc, stdout, stderr, cmd = method(module, target, target_opts, db, dump_extra_args, **kw)
else:
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,
executed_commands=executed_commands)
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, executed_commands=executed_commands)
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 = AssibleModule(
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(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 = AssibleModule(
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 value[:-2].isdigit() and unit in value[-2:]:
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(
query=dict(type='str'),
db=dict(type='str', aliases=['login_db']),
positional_args=dict(type='list', elements='raw'),
named_args=dict(type='dict'),
session_role=dict(type='str'),
path_to_script=dict(type='path'),
autocommit=dict(type='bool', default=False),
encoding=dict(type='str'),
)
module = AssibleModule(
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"]
encoding = module.params["encoding"]
if autocommit and module.check_mode:
module.fail_json(
msg="Using autocommit is mutually exclusive with check_mode")
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:
with open(path_to_script, 'rb') as f:
query = to_native(f.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)
if encoding is not None:
db_connection.set_client_encoding(encoding)
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:
if not autocommit:
db_connection.rollback()
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', elements='str'),
comment=dict(type='str', default=None),
)
module = AssibleModule(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]
comment = module.params["comment"]
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() or changed
executed_queries.extend(pg_membership.executed_queries)
if comment is not None:
try:
changed = add_comment(cursor, user, comment) or changed
except Exception as e:
module.fail_json(msg='Unable to add comment on role: %s' %
to_native(e),
exception=traceback.format_exc())
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)