def update_monitored_db(params):
ret = []
sql = """
with q_old as (
/* using CTE to be enable detect if connect info is being changed */
select * from pgwatch2.monitored_db
where md_id = %(md_id)s
)
update
pgwatch2.monitored_db new
set
md_hostname = %(md_hostname)s,
md_port = %(md_port)s,
md_dbname = %(md_dbname)s,
md_include_pattern = %(md_include_pattern)s,
md_exclude_pattern = %(md_exclude_pattern)s,
md_user = %(md_user)s,
md_password = case when %(md_password)s = '***' then new.md_password else %(md_password)s end,
md_is_superuser = %(md_is_superuser)s,
md_sslmode = %(md_sslmode)s,
md_dbtype = %(md_dbtype)s,
md_is_enabled = %(md_is_enabled)s,
md_preset_config_name = %(md_preset_config_name)s,
md_config = %(md_config)s,
md_custom_tags = %(md_custom_tags)s,
md_statement_timeout_seconds = %(md_statement_timeout_seconds)s,
md_last_modified_on = now()
from
q_old
where
new.md_id = %(md_id)s
returning
(q_old.md_hostname, q_old.md_port, q_old.md_dbname, q_old.md_user, q_old.md_password, q_old.md_sslmode) is distinct from
(%(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s,
case when %(md_password)s = '***' then q_old.md_password else %(md_password)s end, %(md_sslmode)s
) as connection_data_changed,
case when %(md_password)s = '***' then q_old.md_password else %(md_password)s end as md_password
"""
cherrypy_checkboxes_to_bool(
params, ['md_is_enabled', 'md_sslmode', 'md_is_superuser'])
cherrypy_empty_text_to_nulls(
params, ['md_preset_config_name', 'md_config', 'md_custom_tags'])
data, err = datadb.execute(sql, params)
if err:
raise Exception('Failed to update "monitored_db": ' + err)
ret.append('Updated!')
if data[0]['connection_data_changed'] or params[
'md_is_enabled']: # show warning when changing connect data but cannot connect
data, err = datadb.executeOnRemoteHost('select 1',
params['md_hostname'],
params['md_port'],
params['md_dbname'],
params['md_user'],
data[0]['md_password'],
sslmode=params['md_sslmode'],
quiet=True)
if err:
ret.append('Could not connect to specified host: ' + str(err))
return ret
def insert_monitored_db(params, cmd_args=None):
ret = []
# to enable adding DBs via POST requests where nonmandatory fields are not specified
expected_monitored_db_params = [('md_port', '5432'), ('md_password', ''),
('md_root_ca_path', ''),
('md_client_cert_path', ''),
('md_client_key_path', ''),
('md_config', ''),
('md_statement_timeout_seconds', '5'),
('md_dbtype', 'postgres'),
('md_only_if_master', False),
('md_custom_tags', ''),
('md_host_config', ''),
('md_include_pattern', ''),
('md_exclude_pattern', ''),
('md_group', 'default'),
('md_password_type', 'plain-text'),
('md_sslmode', 'disable')]
for p, default in expected_monitored_db_params:
if not p in params:
params[p] = default
sql_insert_new_db = """
insert into
pgwatch2.monitored_db (md_unique_name, md_hostname, md_port, md_dbname, md_user, md_password, md_password_type, md_is_superuser,
md_sslmode, md_root_ca_path,md_client_cert_path, md_client_key_path, md_is_enabled, md_preset_config_name, md_config, md_statement_timeout_seconds, md_dbtype,
md_include_pattern, md_exclude_pattern, md_custom_tags, md_group, md_host_config, md_only_if_master)
values
(%(md_unique_name)s, %(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s, %(md_password)s, %(md_password_type)s, %(md_is_superuser)s,
%(md_sslmode)s, %(md_root_ca_path)s, %(md_client_cert_path)s, %(md_client_key_path)s, %(md_is_enabled)s, %(md_preset_config_name)s, %(md_config)s, %(md_statement_timeout_seconds)s, %(md_dbtype)s,
%(md_include_pattern)s, %(md_exclude_pattern)s, %(md_custom_tags)s, %(md_group)s, %(md_host_config)s, %(md_only_if_master)s)
returning
md_id
"""
sql_active_dbs = "select datname from pg_database where not datistemplate and datallowconn"
cherrypy_checkboxes_to_bool(params, [
'md_is_enabled', 'md_sslmode', 'md_is_superuser', 'md_only_if_master'
])
cherrypy_empty_text_to_nulls(params, [
'md_preset_config_name', 'md_config', 'md_custom_tags',
'md_host_config'
])
password_plain = params['md_password']
if password_plain == '***':
raise Exception(
"'***' cannot be used as password, denotes unchanged password")
if params.get('md_password_type') == 'aes-gcm-256':
if not cmd_args.aes_gcm_keyphrase:
ret.append(
"FYI - skipping password encryption as keyphrase/keyfile not specified on UI startup (hint: use the PW2_AES_GCM_KEYPHRASE env. variable or --aes-gcm-keyphrase param)"
)
params['md_password_type'] = 'plain-text'
else:
params['md_password'] = crypto.encrypt(cmd_args.aes_gcm_keyphrase,
password_plain)
if not params['md_dbname'] and params['md_dbtype'] not in [
'postgres-continuous-discovery', 'patroni',
'patroni-continuous-discovery'
]: # add all DBs found
if params['md_dbtype'] == 'postgres':
# get all active non-template DBs from the entered host
active_dbs_on_host, err = datadb.executeOnRemoteHost(
sql_active_dbs,
host=params['md_hostname'],
port=params['md_port'],
dbname='template1',
user=params['md_user'],
password=password_plain,
sslmode=params['md_sslmode'])
if err:
raise Exception(
"Could not read active DBs from specified host!")
active_dbs_on_host = [x['datname'] for x in active_dbs_on_host]
# "subtract" DBs that are already monitored
currently_monitored_dbs, err = datadb.execute(
"select md_dbname from pgwatch2.monitored_db where "
" (md_hostname, md_port) = (%(md_hostname)s, %(md_port)s)",
params)
if err:
raise Exception(
"Could not read currently active DBs from config DB!")
currently_monitored_dbs = [
x['md_dbname'] for x in currently_monitored_dbs
]
params_copy = params.copy()
dbs_to_add = set(active_dbs_on_host) - set(currently_monitored_dbs)
for db_to_add in dbs_to_add:
params_copy['md_unique_name'] = '{}_{}'.format(
params['md_unique_name'], db_to_add)
params_copy['md_dbname'] = db_to_add
retdata, err = datadb.execute(sql_insert_new_db, params_copy)
if err:
raise Exception('Failed to insert into "monitored_db": ' +
err)
if currently_monitored_dbs:
ret.append(
'Warning! Some DBs not added as already under monitoring: '
+ ', '.join(currently_monitored_dbs))
else:
ret.append('{} DBs added: {}'.format(len(dbs_to_add),
', '.join(dbs_to_add)))
elif params['md_dbtype'] == 'pgbouncer':
# get all configured pgbouncer DBs
params['md_dbname'] = 'pgbouncer'
active_dbs_on_host, err = datadb.executeOnRemoteHost(
"show databases",
host=params['md_hostname'],
port=params['md_port'],
dbname='pgbouncer',
user=params['md_user'],
password=password_plain,
sslmode=params['md_sslmode'])
if err:
raise Exception(
"Could not read active DBs from specified host!")
active_dbs_on_host = [x['name'] for x in active_dbs_on_host]
# "subtract" DBs that are already monitored
currently_monitored_dbs, err = datadb.execute(
"select md_dbname from pgwatch2.monitored_db where "
" (md_hostname, md_port) = (%(md_hostname)s, %(md_port)s)",
params)
if err:
raise Exception(
"Could not read currently active DBs from config DB!")
currently_monitored_dbs = [
x['md_dbname'] for x in currently_monitored_dbs
]
params_copy = params.copy()
dbs_to_add = set(active_dbs_on_host) - set(currently_monitored_dbs)
for db_to_add in dbs_to_add:
params_copy['md_unique_name'] = '{}_{}'.format(
params['md_unique_name'], db_to_add)
params_copy['md_dbname'] = db_to_add
retdata, err = datadb.execute(sql_insert_new_db, params_copy)
if err:
raise Exception('Failed to insert into "monitored_db": ' +
err)
if currently_monitored_dbs:
ret.append(
'Warning! Some DBs not added as already under monitoring: '
+ ', '.join(currently_monitored_dbs))
else:
ret.append('{} DBs added: {}'.format(len(dbs_to_add),
', '.join(dbs_to_add)))
else: # only 1 DB
if params['md_dbtype'] in [
'postgres-continuous-discovery', 'patroni-continuous-discovery'
]:
params['md_dbname'] = ''
data, err = datadb.execute(sql_insert_new_db, params)
if err:
raise Exception('Failed to insert into "monitored_db": ' + err)
ret.append('Host with ID {} added!'.format(data[0]['md_id']))
if params['md_dbtype'] in ['patroni', 'patroni-continuous-discovery']:
ret.append(
'Actual DB hosts will be discovered by the metrics daemon via DCS'
) # check if DCS is accessible? would cause more deps...
return ret
if params['md_dbtype'] == 'postgres-continuous-discovery':
params['md_dbname'] = 'template1'
data, err = datadb.executeOnRemoteHost('select 1',
params['md_hostname'],
params['md_port'],
params['md_dbname'],
params['md_user'],
password_plain,
sslmode=params['md_sslmode'],
quiet=True)
if err:
ret.append('Could not connect to specified host: ' + str(err))
return ret
def update_monitored_db(params, cmd_args=None):
ret = []
password_plain = params['md_password']
old_row_data = get_monitored_db_by_id(params['md_id'])
if params.get('md_password_type') == 'aes-gcm-256' and old_row_data.get(
'md_password_type'
) == 'plain-text': # NB! when changing this part also review set_bulk_password()
if not cmd_args.aes_gcm_keyphrase:
ret.append(
"FYI - not enabling password encryption as keyphrase/keyfile not specified on UI startup (hint: use the PW2_AES_GCM_KEYPHRASE env. variable or --aes-gcm-keyphrase param)"
)
params['md_password_type'] = old_row_data['md_password_type']
params['md_password'] = '******'
else:
if params['md_password'] != '***':
params['md_password'] = crypto.encrypt(
cmd_args.aes_gcm_keyphrase, password_plain)
else:
params['md_password'] = crypto.encrypt(
cmd_args.aes_gcm_keyphrase,
old_row_data.get('md_password'))
elif params.get('md_password_type') == 'plain-text' and old_row_data.get(
'md_password_type') == 'aes-gcm-256':
if not cmd_args.aes_gcm_keyphrase:
ret.append(
"FYI - skipping password decryption as keyphrase/keyfile not specified on UI startup (hint: use the PW2_AES_GCM_KEYPHRASE env. variable or --aes-gcm-keyphrase param)"
)
params['md_password_type'] = old_row_data['md_password_type']
params['md_password'] = '******'
else:
if params['md_password'] == '***':
params['md_password'] = crypto.decrypt(
cmd_args.aes_gcm_keyphrase,
old_row_data.get('md_password'))
sql = """
with q_old as (
/* using CTE to be enable detect if connect info is being changed */
select * from pgwatch2.monitored_db
where md_id = %(md_id)s
)
update
pgwatch2.monitored_db new
set
md_group = %(md_group)s,
md_hostname = %(md_hostname)s,
md_port = %(md_port)s,
md_dbname = %(md_dbname)s,
md_include_pattern = %(md_include_pattern)s,
md_exclude_pattern = %(md_exclude_pattern)s,
md_user = %(md_user)s,
md_password = case when %(md_password)s = '***' and %(md_password_type)s = new.md_password_type then new.md_password else %(md_password)s end,
md_password_type = %(md_password_type)s,
md_is_superuser = %(md_is_superuser)s,
md_sslmode = %(md_sslmode)s,
md_root_ca_path = %(md_root_ca_path)s,
md_client_cert_path = %(md_client_cert_path)s,
md_client_key_path = %(md_client_key_path)s,
md_dbtype = %(md_dbtype)s,
md_is_enabled = %(md_is_enabled)s,
md_preset_config_name = %(md_preset_config_name)s,
md_config = %(md_config)s,
md_host_config = %(md_host_config)s,
md_only_if_master = %(md_only_if_master)s,
md_custom_tags = %(md_custom_tags)s,
md_statement_timeout_seconds = %(md_statement_timeout_seconds)s,
md_last_modified_on = now()
from
q_old
where
new.md_id = %(md_id)s
returning
(q_old.md_hostname, q_old.md_port, q_old.md_dbname, q_old.md_user, q_old.md_password,
q_old.md_sslmode, q_old.md_root_ca_path, q_old.md_client_cert_path, q_old.md_client_key_path) is distinct from
(%(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s,
case when %(md_password)s = '***' then q_old.md_password else %(md_password)s end, %(md_sslmode)s,
%(md_root_ca_path)s, %(md_client_cert_path)s, %(md_client_key_path)s
) as connection_data_changed,
case when %(md_password)s = '***' and %(md_password_type)s = q_old.md_password_type then q_old.md_password else %(md_password)s end as md_password
"""
cherrypy_checkboxes_to_bool(params, [
'md_is_enabled', 'md_sslmode', 'md_is_superuser', 'md_only_if_master'
])
cherrypy_empty_text_to_nulls(params, [
'md_preset_config_name', 'md_config', 'md_custom_tags',
'md_host_config'
])
if params['md_dbtype'] == 'postgres-continuous-discovery':
params['md_dbname'] = ''
data, err = datadb.execute(sql, params)
if err:
raise Exception('Failed to update "monitored_db": ' + err)
ret.append('Updated!')
if params['md_dbtype'] in ['patroni', 'patroni-continuous-discovery']:
return ret # check if DCS is accessible?
# check connection if connect string changed or inactive host activated
if data[0]['connection_data_changed'] or (
old_row_data and
(not old_row_data['md_is_enabled'] and params['md_is_enabled'])
): # show warning when changing connect data but cannot connect
if params.get(
'md_password_type'
) == 'aes-gcm-256' and cmd_args.aes_gcm_keyphrase and data[0][
'md_password'] and data[0]['md_password'].find('-') > 0:
password_plain = crypto.decrypt(cmd_args.aes_gcm_keyphrase,
data[0]['md_password'])
else:
password_plain = data[0]['md_password']
data, err = datadb.executeOnRemoteHost(
'select 1',
params['md_hostname'],
params['md_port'],
'template1' if params['md_dbtype']
== 'postgres-continuous-discovery' else params['md_dbname'],
params['md_user'],
password_plain,
sslmode=params['md_sslmode'],
sslrootcert=params['md_root_ca_path'],
sslcert=params['md_client_cert_path'],
sslkey=params['md_client_key_path'],
quiet=True)
if err:
ret.append(
'Could not connect to specified host (ignore if gatherer daemon runs on another host): '
+ str(err))
return ret
def insert_monitored_db(params):
ret = []
sql_insert_new_db = """
insert into
pgwatch2.monitored_db (md_unique_name, md_hostname, md_port, md_dbname, md_user, md_password, md_is_superuser,
md_sslmode, md_is_enabled, md_preset_config_name, md_config, md_statement_timeout_seconds, md_dbtype,
md_include_pattern, md_exclude_pattern, md_custom_tags)
values
(%(md_unique_name)s, %(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s, %(md_password)s, %(md_is_superuser)s,
%(md_sslmode)s, %(md_is_enabled)s, %(md_preset_config_name)s, %(md_config)s, %(md_statement_timeout_seconds)s, %(md_dbtype)s,
%(md_include_pattern)s, %(md_exclude_pattern)s, %(md_custom_tags)s)
returning
md_id
"""
sql_active_dbs = "select datname from pg_database where not datistemplate and datallowconn"
cherrypy_checkboxes_to_bool(
params, ['md_is_enabled', 'md_sslmode', 'md_is_superuser'])
cherrypy_empty_text_to_nulls(
params, ['md_preset_config_name', 'md_config', 'md_custom_tags'])
if not params['md_dbname']: # add all DBs found
if params['md_dbtype'] == 'postgres':
# get all active non-template DBs from the entered host
active_dbs_on_host, err = datadb.executeOnRemoteHost(
sql_active_dbs,
host=params['md_hostname'],
port=params['md_port'],
dbname='template1',
user=params['md_user'],
password=params['md_password'],
sslmode=params['md_sslmode'])
if err:
raise Exception(
"Could not read active DBs from specified host!")
active_dbs_on_host = [x['datname'] for x in active_dbs_on_host]
# "subtract" DBs that are already monitored
currently_monitored_dbs, err = datadb.execute(
"select md_dbname from pgwatch2.monitored_db where "
" (md_hostname, md_port) = (%(md_hostname)s, %(md_port)s)",
params)
if err:
raise Exception(
"Could not read currently active DBs from config DB!")
currently_monitored_dbs = [
x['md_dbname'] for x in currently_monitored_dbs
]
params_copy = params.copy()
dbs_to_add = set(active_dbs_on_host) - set(currently_monitored_dbs)
for db_to_add in dbs_to_add:
params_copy['md_unique_name'] = '{}_{}'.format(
params['md_unique_name'], db_to_add)
params_copy['md_dbname'] = db_to_add
retdata, err = datadb.execute(sql_insert_new_db, params_copy)
if err:
raise Exception('Failed to insert into "monitored_db": ' +
err)
if currently_monitored_dbs:
ret.append(
'Warning! Some DBs not added as already under monitoring: '
+ ', '.join(currently_monitored_dbs))
else:
ret.append('{} DBs added: {}'.format(len(dbs_to_add),
', '.join(dbs_to_add)))
elif params['md_dbtype'] == 'pgbouncer':
# get all configured pgbouncer DBs
params['md_dbname'] = 'pgbouncer'
active_dbs_on_host, err = datadb.executeOnRemoteHost(
"show databases",
host=params['md_hostname'],
port=params['md_port'],
dbname='pgbouncer',
user=params['md_user'],
password=params['md_password'],
sslmode=params['md_sslmode'])
if err:
raise Exception(
"Could not read active DBs from specified host!")
active_dbs_on_host = [x['name'] for x in active_dbs_on_host]
# "subtract" DBs that are already monitored
currently_monitored_dbs, err = datadb.execute(
"select md_dbname from pgwatch2.monitored_db where "
" (md_hostname, md_port) = (%(md_hostname)s, %(md_port)s)",
params)
if err:
raise Exception(
"Could not read currently active DBs from config DB!")
currently_monitored_dbs = [
x['md_dbname'] for x in currently_monitored_dbs
]
params_copy = params.copy()
dbs_to_add = set(active_dbs_on_host) - set(currently_monitored_dbs)
for db_to_add in dbs_to_add:
params_copy['md_unique_name'] = '{}_{}'.format(
params['md_unique_name'], db_to_add)
params_copy['md_dbname'] = db_to_add
retdata, err = datadb.execute(sql_insert_new_db, params_copy)
if err:
raise Exception('Failed to insert into "monitored_db": ' +
err)
if currently_monitored_dbs:
ret.append(
'Warning! Some DBs not added as already under monitoring: '
+ ', '.join(currently_monitored_dbs))
else:
ret.append('{} DBs added: {}'.format(len(dbs_to_add),
', '.join(dbs_to_add)))
else: # only 1 DB
data, err = datadb.execute(sql_insert_new_db, params)
if err:
raise Exception('Failed to insert into "monitored_db": ' + err)
ret.append('Host with ID {} added!'.format(data[0]['md_id']))
data, err = datadb.executeOnRemoteHost('select 1',
params['md_hostname'],
params['md_port'],
params['md_dbname'],
params['md_user'],
params['md_password'],
sslmode=params['md_sslmode'],
quiet=True)
if err:
ret.append('Could not connect to specified host: ' + str(err))
return ret
def update_monitored_db(params, cmd_args=None):
ret = []
password_plain = params['md_password']
if params.get('md_password_type'
) == 'aes-gcm-256' and params['md_password'] != '***':
params['md_password'] = crypto.encrypt(cmd_args.aes_gcm_keyphrase,
password_plain)
old_row_data = get_monitored_db_by_id(params['md_id'])
sql = """
with q_old as (
/* using CTE to be enable detect if connect info is being changed */
select * from pgwatch2.monitored_db
where md_id = %(md_id)s
)
update
pgwatch2.monitored_db new
set
md_group = %(md_group)s,
md_hostname = %(md_hostname)s,
md_port = %(md_port)s,
md_dbname = %(md_dbname)s,
md_include_pattern = %(md_include_pattern)s,
md_exclude_pattern = %(md_exclude_pattern)s,
md_user = %(md_user)s,
md_password = case when %(md_password)s = '***' then new.md_password else %(md_password)s end,
md_password_type = %(md_password_type)s,
md_is_superuser = %(md_is_superuser)s,
md_sslmode = %(md_sslmode)s,
md_root_ca_path = %(md_root_ca_path)s,
md_client_cert_path = %(md_client_cert_path)s,
md_client_key_path = %(md_client_key_path)s,
md_dbtype = %(md_dbtype)s,
md_is_enabled = %(md_is_enabled)s,
md_preset_config_name = %(md_preset_config_name)s,
md_config = %(md_config)s,
md_custom_tags = %(md_custom_tags)s,
md_statement_timeout_seconds = %(md_statement_timeout_seconds)s,
md_last_modified_on = now()
from
q_old
where
new.md_id = %(md_id)s
returning
(q_old.md_hostname, q_old.md_port, q_old.md_dbname, q_old.md_user, q_old.md_password,
q_old.md_sslmode, q_old.md_root_ca_path, q_old.md_client_cert_path, q_old.md_client_key_path) is distinct from
(%(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s,
case when %(md_password)s = '***' then q_old.md_password else %(md_password)s end, %(md_sslmode)s,
%(md_root_ca_path)s, %(md_client_cert_path)s, %(md_client_key_path)s
) as connection_data_changed,
case when %(md_password)s = '***' then q_old.md_password else %(md_password)s end as md_password
"""
cherrypy_checkboxes_to_bool(
params, ['md_is_enabled', 'md_sslmode', 'md_is_superuser'])
cherrypy_empty_text_to_nulls(
params, ['md_preset_config_name', 'md_config', 'md_custom_tags'])
data, err = datadb.execute(sql, params)
if err:
raise Exception('Failed to update "monitored_db": ' + err)
ret.append('Updated!')
# check connection if connect string changed or inactive host activated
if data[0]['connection_data_changed'] or (
old_row_data and
(not old_row_data['md_is_enabled'] and params['md_is_enabled'])
): # show warning when changing connect data but cannot connect
data, err = datadb.executeOnRemoteHost(
'select 1',
params['md_hostname'],
params['md_port'],
params['md_dbname'],
params['md_user'],
password_plain,
sslmode=params['md_sslmode'],
sslrootcert=params['md_root_ca_path'],
sslcert=params['md_client_cert_path'],
sslkey=params['md_client_key_path'],
quiet=True)
if err:
ret.append(
'Could not connect to specified host (ignore if gatherer daemon runs on another host): '
+ str(err))
return ret
