def sql(
state,
host,
sql,
database=None,
# Details for speaking to MySQL via `mysql` CLI
mysql_user=None,
mysql_password=None,
mysql_host=None,
mysql_port=None,
):
'''
Execute arbitrary SQL against MySQL.
+ sql: SQL command(s) to execute
+ database: optional database to open the connection with
+ mysql_*: global module arguments, see above
'''
yield make_execute_mysql_command(
sql,
database=database,
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
def handle_privileges(action, target, privileges_to_apply):
command = ("{action} {privileges} "
"ON {database}.{table} "
'{target} "{user}"@"{user_hostname}"').format(
privileges=", ".join(sorted(privileges_to_apply)),
action=action,
target=target,
database=database,
table=table,
user=user,
user_hostname=user_hostname,
)
yield make_execute_mysql_command(
command,
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
def user(
state,
host,
name,
# Desired user settings
present=True,
user_hostname='localhost',
password=None,
privileges=None,
# Details for speaking to MySQL via `mysql` CLI via `mysql` CLI
mysql_user=None,
mysql_password=None,
mysql_host=None,
mysql_port=None,
):
'''
Add/remove/update MySQL users.
+ name: the name of the user
+ present: whether the user should exist or not
+ user_hostname: the hostname of the user
+ password: the password of the user (if created)
+ privileges: the global privileges for this user
+ mysql_*: global module arguments, see above
Hostname:
this + ``name`` makes the username - so changing this will create a new
user, rather than update users with the same ``name``.
Password:
will only be applied if the user does not exist - ie pyinfra cannot
detect if the current password doesn't match the one provided, so won't
attempt to change it.
'''
current_users = host.fact.mysql_users(
mysql_user,
mysql_password,
mysql_host,
mysql_port,
)
user_host = '{0}@{1}'.format(name, user_hostname)
is_present = user_host in current_users
# User not wanted?
if not present:
if is_present:
yield make_execute_mysql_command(
'DROP USER "{0}"@"{1}"'.format(name, user_hostname),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
return
# If we want the user and they don't exist
if present and not is_present:
sql_bits = ['CREATE USER "{0}"@"{1}"'.format(name, user_hostname)]
if password:
sql_bits.append('IDENTIFIED BY "{0}"'.format(password))
yield make_execute_mysql_command(
' '.join(sql_bits),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
# If we're here either the user exists or we just created them; either way
# now we can check any privileges are set.
if privileges:
yield _privileges(
state,
host,
name,
privileges,
user_hostname=user_hostname,
mysql_user=mysql_user,
mysql_password=mysql_password,
mysql_host=mysql_host,
mysql_port=mysql_port,
)
def privileges(
state,
host,
user,
privileges,
user_hostname='localhost',
database='*',
table='*',
present=True,
flush=True,
# Details for speaking to MySQL via `mysql` CLI
mysql_user=None,
mysql_password=None,
mysql_host=None,
mysql_port=None,
):
'''
Add/remove MySQL privileges for a user, either global, database or table specific.
+ user: name of the user to manage privileges for
+ privileges: list of privileges the user should have
+ user_hostname: the hostname of the user
+ database: name of the database to grant privileges to (defaults to all)
+ table: name of the table to grant privileges to (defaults to all)
+ present: whether these privileges should exist (False to ``REVOKE)
+ flush: whether to flush (and update) the privileges table after any changes
+ mysql_*: global module arguments, see above
'''
# Ensure we have a list
if isinstance(privileges, six.string_types):
privileges = [privileges]
if database != '*':
database = '`{0}`'.format(database)
if table != '*':
table = '`{0}`'.format(table)
# We can't set privileges on *.tablename as MySQL won't allow it
if database == '*':
raise OperationError((
'Cannot apply MySQL privileges on {0}.{1}, no database provided'
).format(database, table))
database_table = '{0}.{1}'.format(database, table)
user_grants = host.fact.mysql_user_grants(
user,
user_hostname,
mysql_user,
mysql_password,
mysql_host,
mysql_port,
)
has_privileges = False
if database_table in user_grants:
existing_privileges = [
'ALL' if privilege == 'ALL PRIVILEGES' else privilege
for privilege in user_grants[database_table]['privileges']
]
has_privileges = (database_table in user_grants
and all(privilege in existing_privileges
for privilege in privileges))
target = action = None
# No privilege and we want it
if not has_privileges and present:
action = 'GRANT'
target = 'TO'
# Permission we don't want
elif has_privileges and not present:
action = 'REVOKE'
target = 'FROM'
if target and action:
command = ('{action} {privileges} '
'ON {database}.{table} '
'{target} "{user}"@"{user_hostname}"').format(
privileges=', '.join(privileges),
action=action,
target=target,
database=database,
table=table,
user=user,
user_hostname=user_hostname,
).replace('`', '\`')
yield make_execute_mysql_command(
command,
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
if flush:
yield make_execute_mysql_command(
'FLUSH PRIVILEGES',
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
def database(
state,
host,
name,
# Desired database settings
present=True,
collate=None,
charset=None,
user=None,
user_hostname='localhost',
user_privileges='ALL',
# Details for speaking to MySQL via `mysql` CLI
mysql_user=None,
mysql_password=None,
mysql_host=None,
mysql_port=None,
):
'''
Add/remove MySQL databases.
+ name: the name of the database
+ present: whether the database should exist or not
+ collate: the collate to use when creating the database
+ charset: the charset to use when creating the database
+ user: MySQL user to grant privileges on this database to
+ user_hostname: the hostname of the MySQL user to grant
+ user_privileges: privileges to grant to any specified user
+ mysql_*: global module arguments, see above
Collate/charset:
these will only be applied if the database does not exist - ie pyinfra
will not attempt to alter the existing databases collate/character sets.
'''
current_databases = host.fact.mysql_databases(
mysql_user,
mysql_password,
mysql_host,
mysql_port,
)
is_present = name in current_databases
if not present:
if is_present:
yield make_execute_mysql_command(
'DROP DATABASE {0}'.format(name),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
return
# We want the database but it doesn't exist
if present and not is_present:
sql_bits = ['CREATE DATABASE {0}'.format(name)]
if collate:
sql_bits.append('COLLATE {0}'.format(collate))
if charset:
sql_bits.append('CHARSET {0}'.format(charset))
yield make_execute_mysql_command(
' '.join(sql_bits),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
# Ensure any user privileges for this database
if user and user_privileges:
yield privileges(
state,
host,
user,
user_hostname=user_hostname,
privileges=user_privileges,
database=name,
)
def user(
user,
present=True,
user_hostname="localhost",
password=None,
privileges=None,
# MySQL REQUIRE SSL/TLS options
require=None, # SSL or X509
require_cipher=False,
require_issuer=False,
require_subject=False,
# MySQL WITH resource limit options
max_connections=None,
max_queries_per_hour=None,
max_updates_per_hour=None,
max_connections_per_hour=None,
# Details for speaking to MySQL via `mysql` CLI via `mysql` CLI
mysql_user=None,
mysql_password=None,
mysql_host=None,
mysql_port=None,
):
"""
Add/remove/update MySQL users.
+ user: the name of the user
+ present: whether the user should exist or not
+ user_hostname: the hostname of the user
+ password: the password of the user (if created)
+ privileges: the global privileges for this user
+ mysql_*: global module arguments, see above
Hostname:
this + ``name`` makes the username - so changing this will create a new
user, rather than update users with the same ``name``.
Password:
will only be applied if the user does not exist - ie pyinfra cannot
detect if the current password doesn't match the one provided, so won't
attempt to change it.
**Example:**
.. code:: python
mysql.user(
name="Create the pyinfra@localhost MySQL user",
user="******",
password="******",
)
# Create a user with resource limits
mysql.user(
name="Create the pyinfra@localhost MySQL user",
user="******",
max_connections=50,
max_updates_per_hour=10,
)
# Create a user that requires SSL for connections
mysql.user(
name="Create the pyinfra@localhost MySQL user",
user="******",
password="******",
require="SSL",
)
# Create a user that requires a specific certificate
mysql.user(
name="Create the pyinfra@localhost MySQL user",
user="******",
password="******",
require="X509",
require_issuer="/C=SE/ST=Stockholm...",
require_cipher="EDH-RSA-DES-CBC3-SHA",
)
"""
if require and require not in ("SSL", "X509"):
raise OperationError(
'Invalid `require` value, must be: "SSL" or "X509"')
if require != "X509":
if require_cipher:
raise OperationError(
'Cannot set `require_cipher` if `require` is not "X509"')
if require_issuer:
raise OperationError(
'Cannot set `require_issuer` if `require` is not "X509"')
if require_subject:
raise OperationError(
'Cannot set `require_subject` if `require` is not "X509"')
current_users = host.get_fact(
MysqlUsers,
mysql_user=mysql_user,
mysql_password=mysql_password,
mysql_host=mysql_host,
mysql_port=mysql_port,
)
user_host = "{0}@{1}".format(user, user_hostname)
is_present = user_host in current_users
if not present:
if is_present:
yield make_execute_mysql_command(
'DROP USER "{0}"@"{1}"'.format(user, user_hostname),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
current_users.pop(user_host)
else:
host.noop("mysql user {0}@{1} does not exist".format(
user, user_hostname))
return
new_or_updated_user_fact = {
"ssl_type": "ANY" if require == "SSL" else require,
"ssl_cipher": require_cipher,
"x509_issuer": require_issuer,
"x509_subject": require_subject,
"max_user_connections": max_connections,
"max_questions": max_queries_per_hour,
"max_updates": max_updates_per_hour,
"max_connections": max_connections_per_hour,
}
if present and not is_present:
sql_bits = ['CREATE USER "{0}"@"{1}"'.format(user, user_hostname)]
if password:
sql_bits.append(MaskString('IDENTIFIED BY "{0}"'.format(password)))
if require == "SSL":
sql_bits.append("REQUIRE SSL")
if require == "X509":
sql_bits.append("REQUIRE")
require_bits = []
if require_cipher:
require_bits.append('CIPHER "{0}"'.format(require_cipher))
if require_issuer:
require_bits.append('ISSUER "{0}"'.format(require_issuer))
if require_subject:
require_bits.append('SUBJECT "{0}"'.format(require_subject))
if not require_bits:
require_bits.append("X509")
sql_bits.extend(require_bits)
resource_bits = []
if max_connections:
resource_bits.append(
"MAX_USER_CONNECTIONS {0}".format(max_connections))
if max_queries_per_hour:
resource_bits.append(
"MAX_QUERIES_PER_HOUR {0}".format(max_queries_per_hour))
if max_updates_per_hour:
resource_bits.append(
"MAX_UPDATES_PER_HOUR {0}".format(max_updates_per_hour))
if max_connections_per_hour:
resource_bits.append("MAX_CONNECTIONS_PER_HOUR {0}".format(
max_connections_per_hour))
if resource_bits:
sql_bits.append("WITH")
sql_bits.append(" ".join(resource_bits))
yield make_execute_mysql_command(
StringCommand(*sql_bits),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
current_users[user_host] = new_or_updated_user_fact
if present and is_present:
current_user = current_users.get(user_host)
alter_bits = []
if require == "SSL":
if current_user["ssl_type"] != "ANY":
alter_bits.append("REQUIRE SSL")
if require == "X509":
require_bits = []
if require_cipher and current_user["ssl_cipher"] != require_cipher:
require_bits.append('CIPHER "{0}"'.format(require_cipher))
if require_issuer and current_user["x509_issuer"] != require_issuer:
require_bits.append('ISSUER "{0}"'.format(require_issuer))
if require_subject and current_user[
"x509_subject"] != require_subject:
require_bits.append('SUBJECT "{0}"'.format(require_subject))
if not require_bits:
if current_user["ssl_type"] != "X509":
require_bits.append("X509")
if require_bits:
alter_bits.append("REQUIRE")
alter_bits.extend(require_bits)
resource_bits = []
if max_connections and current_user[
"max_user_connections"] != max_connections:
resource_bits.append(
"MAX_USER_CONNECTIONS {0}".format(max_connections))
if max_queries_per_hour and current_user[
"max_questions"] != max_queries_per_hour:
resource_bits.append(
"MAX_QUERIES_PER_HOUR {0}".format(max_queries_per_hour))
if max_updates_per_hour and current_user[
"max_updates"] != max_updates_per_hour:
resource_bits.append(
"MAX_UPDATES_PER_HOUR {0}".format(max_updates_per_hour))
if max_connections_per_hour and current_user[
"max_connections"] != max_connections_per_hour:
resource_bits.append("MAX_CONNECTIONS_PER_HOUR {0}".format(
max_connections_per_hour))
if resource_bits:
alter_bits.append("WITH")
alter_bits.append(" ".join(resource_bits))
if alter_bits:
sql_bits = ['ALTER USER "{0}"@"{1}"'.format(user, user_hostname)]
sql_bits.extend(alter_bits)
yield make_execute_mysql_command(
StringCommand(*sql_bits),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
current_user.update(new_or_updated_user_fact)
else:
host.noop("mysql user {0}@{1} exists".format(user, user_hostname))
# If we're here either the user exists or we just created them; either way
# now we can check any privileges are set.
if privileges:
yield from _privileges(
user,
privileges,
user_hostname=user_hostname,
mysql_user=mysql_user,
mysql_password=mysql_password,
mysql_host=mysql_host,
mysql_port=mysql_port,
)
def privileges(
user,
privileges,
user_hostname="localhost",
database="*",
table="*",
flush=True,
with_grant_option=False,
# Details for speaking to MySQL via `mysql` CLI
mysql_user=None,
mysql_password=None,
mysql_host=None,
mysql_port=None,
):
"""
Add/remove MySQL privileges for a user, either global, database or table specific.
+ user: name of the user to manage privileges for
+ privileges: list of privileges the user should have (see also: ``with_grant_option`` argument)
+ user_hostname: the hostname of the user
+ database: name of the database to grant privileges to (defaults to all)
+ table: name of the table to grant privileges to (defaults to all)
+ flush: whether to flush (and update) the privileges table after any changes
+ with_grant_option: whether the grant option privilege should be set
+ mysql_*: global module arguments, see above
"""
# Ensure we have a list
if isinstance(privileges, str):
privileges = {privileges}
if isinstance(privileges, list):
privileges = set(privileges)
if with_grant_option:
privileges.add("GRANT OPTION")
if database != "*":
database = "`{0}`".format(database)
if table != "*":
table = "`{0}`".format(table)
# We can't set privileges on *.tablename as MySQL won't allow it
if database == "*":
raise OperationError((
"Cannot apply MySQL privileges on {0}.{1}, no database provided"
).format(
database,
table,
), )
database_table = "{0}.{1}".format(database, table)
user_grants = host.get_fact(
MysqlUserGrants,
user=user,
hostname=user_hostname,
mysql_user=mysql_user,
mysql_password=mysql_password,
mysql_host=mysql_host,
mysql_port=mysql_port,
)
existing_privileges = set()
if database_table in user_grants:
existing_privileges = {
"ALL" if privilege == "ALL PRIVILEGES" else privilege
for privilege in user_grants[database_table]
}
else:
user_grants[database_table] = set()
def handle_privileges(action, target, privileges_to_apply):
command = ("{action} {privileges} "
"ON {database}.{table} "
'{target} "{user}"@"{user_hostname}"').format(
privileges=", ".join(sorted(privileges_to_apply)),
action=action,
target=target,
database=database,
table=table,
user=user,
user_hostname=user_hostname,
)
yield make_execute_mysql_command(
command,
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
# Find / revoke any privileges that exist that do not match the desired state
privileges_to_revoke = existing_privileges.difference(privileges)
# Find / grant any privileges that we want but do not exist
privileges_to_grant = privileges - existing_privileges
# No privilege and we want it
if privileges_to_grant:
yield from handle_privileges("GRANT", "TO", privileges_to_grant)
user_grants[database_table].update(privileges_to_grant)
if privileges_to_revoke:
yield from handle_privileges("REVOKE", "FROM", privileges_to_revoke)
user_grants[database_table] -= privileges_to_revoke
if privileges_to_grant or privileges_to_revoke:
if flush:
yield make_execute_mysql_command(
"FLUSH PRIVILEGES",
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
else:
host.noop("mysql privileges are already correct")
def database(
database,
# Desired database settings
present=True,
collate=None,
charset=None,
user=None,
user_hostname="localhost",
user_privileges="ALL",
# Details for speaking to MySQL via `mysql` CLI
mysql_user=None,
mysql_password=None,
mysql_host=None,
mysql_port=None,
):
"""
Add/remove MySQL databases.
+ name: the name of the database
+ present: whether the database should exist or not
+ collate: the collate to use when creating the database
+ charset: the charset to use when creating the database
+ user: MySQL user to grant privileges on this database to
+ user_hostname: the hostname of the MySQL user to grant
+ user_privileges: privileges to grant to any specified user
+ mysql_*: global module arguments, see above
Collate/charset:
these will only be applied if the database does not exist - ie pyinfra
will not attempt to alter the existing databases collate/character sets.
**Example:**
.. code:: python
mysql.database(
name="Create the pyinfra_stuff database",
database="pyinfra_stuff",
user="******",
user_privileges=["SELECT", "INSERT"],
charset="utf8",
)
"""
current_databases = host.get_fact(
MysqlDatabases,
mysql_user=mysql_user,
mysql_password=mysql_password,
mysql_host=mysql_host,
mysql_port=mysql_port,
)
is_present = database in current_databases
if not present:
if is_present:
yield make_execute_mysql_command(
"DROP DATABASE {0}".format(database),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
current_databases.pop(database)
else:
host.noop("mysql 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)]
if collate:
sql_bits.append("COLLATE {0}".format(collate))
if charset:
sql_bits.append("CHARSET {0}".format(charset))
yield make_execute_mysql_command(
" ".join(sql_bits),
user=mysql_user,
password=mysql_password,
host=mysql_host,
port=mysql_port,
)
current_databases[database] = {
"collate": collate,
"charset": charset,
}
else:
host.noop("mysql database {0} exists".format(database))
# Ensure any user privileges for this database
if user and user_privileges:
yield from privileges(
user,
user_hostname=user_hostname,
privileges=user_privileges,
database=database,
mysql_user=mysql_user,
mysql_password=mysql_password,
mysql_host=mysql_host,
mysql_port=mysql_port,
)
