def sql( sql, database=None, # Details for speaking to PostgreSQL via `psql` CLI psql_user=None, psql_password=None, psql_host=None, psql_port=None, ): """ Execute arbitrary SQL against PostgreSQL. + sql: SQL command(s) to execute + database: optional database to execute against + psql_*: global module arguments, see above """ yield make_execute_psql_command( sql, database=database, user=psql_user, password=psql_password, host=psql_host, port=psql_port, )
def role( role, present=True, password=None, login=True, superuser=False, inherit=False, createdb=False, createrole=False, replication=False, connection_limit=None, # Details for speaking to PostgreSQL via `psql` CLI psql_user=None, psql_password=None, psql_host=None, psql_port=None, ): """ Add/remove PostgreSQL roles. + role: name of the role + present: whether the role should be present or absent + password: the password for the role + login: whether the role can login + superuser: whether role will be a superuser + inherit: whether the role inherits from other roles + createdb: whether the role is allowed to create databases + createrole: whether the role is allowed to create new roles + replication: whether this role is allowed to replicate + connection_limit: the connection limit for the role + psql_*: global module arguments, see above Updates: pyinfra will not attempt to change existing roles - it will either create or drop roles, but not alter them (if the role exists this operation will make no changes). **Example:** .. code:: python postgresql.role( name="Create the pyinfra PostgreSQL role", role="pyinfra", password="******", superuser=True, login=True, sudo_user="******", ) """ roles = host.get_fact( PostgresqlRoles, psql_user=psql_user, psql_password=psql_password, psql_host=psql_host, psql_port=psql_port, ) is_present = role in roles # User not wanted? if not present: if is_present: yield make_execute_psql_command( 'DROP ROLE "{0}"'.format(role), user=psql_user, password=psql_password, host=psql_host, port=psql_port, ) roles.pop(role) else: host.noop("postgresql role {0} does not exist".format(role)) return # If we want the user and they don't exist if not is_present: sql_bits = ['CREATE ROLE "{0}"'.format(role)] for key, value in ( ("LOGIN", login), ("SUPERUSER", superuser), ("INHERIT", inherit), ("CREATEDB", createdb), ("CREATEROLE", createrole), ("REPLICATION", replication), ): if value: sql_bits.append(key) if connection_limit: sql_bits.append("CONNECTION LIMIT {0}".format(connection_limit)) if password: sql_bits.append(MaskString("PASSWORD '{0}'".format(password))) yield make_execute_psql_command( StringCommand(*sql_bits), user=psql_user, password=psql_password, host=psql_host, port=psql_port, ) roles[role] = { "super": superuser, "cretedb": createdb, "createrole": createrole, } else: host.noop("postgresql role {0} exists".format(role))
def role( state, host, name, present=True, password=None, login=True, superuser=False, inherit=False, createdb=False, createrole=False, replication=False, connection_limit=None, # Details for speaking to PostgreSQL via `psql` CLI postgresql_user=None, postgresql_password=None, postgresql_host=None, postgresql_port=None, ): ''' Add/remove PostgreSQL roles. + name: name of the role + present: whether the role should be present or absent + password: the password for the role + login: whether the role can login + superuser: whether role will be a superuser + inherit: whether the role inherits from other roles + createdb: whether the role is allowed to create databases + createrole: whether the role is allowed to create new roles + replication: whether this role is allowed to replicate + connection_limit: the connection limit for the role + postgresql_*: global module arguments, see above Updates: pyinfra will not attempt to change existing roles - it will either create or drop roles, but not alter them (if the role exists this operation will make no changes). Example: .. code:: python postgresql.role( {'Create the pyinfra PostgreSQL role'}, 'pyinfra', password='******', superuser=True, login=True, sudo_user='******', ) ''' roles = host.fact.postgresql_roles( postgresql_user, postgresql_password, postgresql_host, postgresql_port, ) is_present = name in roles # User not wanted? if not present: if is_present: yield make_execute_psql_command( 'DROP ROLE {0}'.format(name), user=postgresql_user, password=postgresql_password, host=postgresql_host, port=postgresql_port, ) return # If we want the user and they don't exist if not is_present: sql_bits = ['CREATE ROLE {0}'.format(name)] for key, value in ( ('LOGIN', login), ('SUPERUSER', superuser), ('INHERIT', inherit), ('CREATEDB', createdb), ('CREATEROLE', createrole), ('REPLICATION', replication), ): if value: sql_bits.append(key) if connection_limit: sql_bits.append('CONNECTION LIMIT {0}'.format(connection_limit)) if password: sql_bits.append(MaskString("PASSWORD '{0}'".format(password))) yield make_execute_psql_command( StringCommand(*sql_bits), user=postgresql_user, password=postgresql_password, host=postgresql_host, port=postgresql_port, )
def database( database, present=True, owner=None, template=None, encoding=None, lc_collate=None, lc_ctype=None, tablespace=None, connection_limit=None, # Details for speaking to PostgreSQL via `psql` CLI psql_user=None, psql_password=None, psql_host=None, psql_port=None, ): """ Add/remove PostgreSQL databases. + name: name of the database + present: whether the database should exist or not + owner: the PostgreSQL role that owns the database + template: name of the PostgreSQL template to use + encoding: encoding of the database + lc_collate: lc_collate of the database + lc_ctype: lc_ctype of the database + tablespace: the tablespace to use for the template + connection_limit: the connection limit to apply to the database + psql_*: global module arguments, see above Updates: pyinfra will not attempt to change existing databases - it will either create or drop databases, but not alter them (if the db exists this operation will make no changes). **Example:** .. code:: python postgresql.database( name="Create the pyinfra_stuff database", database="pyinfra_stuff", owner="pyinfra", encoding="UTF8", sudo_user="******", ) """ current_databases = host.get_fact( PostgresqlDatabases, psql_user=psql_user, psql_password=psql_password, psql_host=psql_host, psql_port=psql_port, ) is_present = database in current_databases if not present: if is_present: yield make_execute_psql_command( 'DROP DATABASE "{0}"'.format(database), user=psql_user, password=psql_password, host=psql_host, port=psql_port, ) current_databases.pop(database) else: host.noop( "postgresql database {0} does not exist".format(database)) return # We want the database but it doesn't exist if present and not is_present: sql_bits = ['CREATE DATABASE "{0}"'.format(database)] for key, value in ( ("OWNER", '"{0}"'.format(owner) if owner else owner), ("TEMPLATE", template), ("ENCODING", encoding), ("LC_COLLATE", lc_collate), ("LC_CTYPE", lc_ctype), ("TABLESPACE", tablespace), ("CONNECTION LIMIT", connection_limit), ): if value: sql_bits.append("{0} {1}".format(key, value)) yield make_execute_psql_command( StringCommand(*sql_bits), user=psql_user, password=psql_password, host=psql_host, port=psql_port, ) current_databases[database] = { "template": template, "encoding": encoding, "lc_collate": lc_collate, "lc_ctype": lc_ctype, "tablespace": tablespace, "connection_limit": connection_limit, } else: host.noop("postgresql database {0} exists".format(database))
def database( state, host, name, present=True, owner=None, template=None, encoding=None, lc_collate=None, lc_ctype=None, tablespace=None, connection_limit=None, # Details for speaking to PostgreSQL via `psql` CLI postgresql_user=None, postgresql_password=None, postgresql_host=None, postgresql_port=None, ): ''' Add/remove PostgreSQL databases. + name: name of the database + present: whether the database should exist or not + owner: the PostgreSQL role that owns the database + template: name of the PostgreSQL template to use + encoding: encoding of the database + lc_collate: lc_collate of the database + lc_ctype: lc_ctype of the database + tablespace: the tablespace to use for the template + connection_limit: the connection limit to apply to the database + postgresql_*: global module arguments, see above Updates: pyinfra will not attempt to change existing databases - it will either create or drop databases, but not alter them (if the db exists this operation will make no changes). Example: .. code:: python postgresql.database( {'Create the pyinfra_stuff database'}, 'pyinfra_stuff', owner='pyinfra', encoding='UTF8', sudo_user='******', ) ''' current_databases = host.fact.postgresql_databases( postgresql_user, postgresql_password, postgresql_host, postgresql_port, ) is_present = name in current_databases if not present: if is_present: yield make_execute_psql_command( 'DROP DATABASE {0}'.format(name), user=postgresql_user, password=postgresql_password, host=postgresql_host, port=postgresql_port, ) return # We want the database but it doesn't exist if present and not is_present: sql_bits = ['CREATE DATABASE {0}'.format(name)] for key, value in ( ('OWNER', owner), ('TEMPLATE', template), ('ENCODING', encoding), ('LC_COLLATE', lc_collate), ('LC_CTYPE', lc_ctype), ('TABLESPACE', tablespace), ('CONNECTION LIMIT', connection_limit), ): if value: sql_bits.append('{0} {1}'.format(key, value)) yield make_execute_psql_command( StringCommand(*sql_bits), user=postgresql_user, password=postgresql_password, host=postgresql_host, port=postgresql_port, )