Jcrespo has submitted this change and it was merged.
Change subject: Create script to check that sanitarium filtering is working
......................................................................
Create script to check that sanitarium filtering is working
Bug: T150802
Change-Id: I66e43a1bc7554fd095ddda378a40856da115fdf9
---
A modules/role/files/mariadb/check_private_data.py
1 file changed, 181 insertions(+), 0 deletions(-)
Approvals:
Jcrespo: Looks good to me, approved
jenkins-bot: Verified
diff --git a/modules/role/files/mariadb/check_private_data.py
b/modules/role/files/mariadb/check_private_data.py
new file mode 100644
index 0000000..df4cac5
--- /dev/null
+++ b/modules/role/files/mariadb/check_private_data.py
@@ -0,0 +1,181 @@
+#!/usr/bin/python3
+
+import csv
+import os
+import re
+# requires python3-pymysql
+import pymysql
+
+# TODO:The following are hardcoded paths that will be parametrized
+# location where operations/wmf-config repo is synced
+mediawiki_config_path = '/usr/local/lib/mediawiki-config'
+# location where private_tables from realm.pp is synced to a text file
+private_tables_path = '/etc/mysql/private_tables.txt'
+# location where cols.txt from redactraton is synced to a csv (db, table, col)
+filtered_tables_path = '/etc/mysql/filtered_tables.txt'
+
+# full path for all.dblist
+all_dblist_path = os.path.join(mediawiki_config_path, 'dblists', 'all.dblist')
+# full path for private.dblist
+private_dblist_path = os.path.join(mediawiki_config_path, 'dblists',
+ 'private.dblist')
+# full path for deleted.dblist
+deleted_dblist_path = os.path.join(mediawiki_config_path, 'dblists',
+ 'deleted.dblist')
+
+# ignore the following system or ops-created databases:
+SYSTEM_DBS = ['mysql', 'information_schema', 'performance_schema', 'sys',
+ 'ops', 'percona']
+# ignore the following user-created databases:
+USER_DATABASES_REGEX = '^([su][0-9]+|p[0-9]+g[0-9]+)\_\_?'
+
+
+def parse_db_file(path, format='txt'):
+ """
+ load files to memory arrays
+ """
+ content = []
+ with open(path, 'r') as f:
+ if format == 'txt':
+ content = f.read().splitlines()
+ elif format == 'csv':
+ content = list(csv.reader(f))
+ return content
+
+
+def get_lists(all_dblist_path, private_dblist_path, deleted_dblist_path,
+ filtered_tables_path):
+ """
+ convert data from files to arrays
+ """
+ all_dblist = parse_db_file(all_dblist_path)
+ # we consider deleted wikis a private, too, for filtering purposes
+ private_dblist = (parse_db_file(private_dblist_path)
+ + parse_db_file(deleted_dblist_path))
+ private_tables = parse_db_file(private_tables_path)
+ filtered_tables = parse_db_file(filtered_tables_path, 'csv')
+
+ public_wiki_dbs = [db for db in all_dblist if db not in private_dblist]
+
+ public_dbs = list(public_wiki_dbs)
+ public_dbs.append('centralauth')
+ public_dbs.append('heartbeat')
+ public_view_dbs = [db + '_p' for db in public_dbs]
+
+ # these 2 are public dbs, not wiki views
+ public_dbs.append('information_schema_p')
+ public_dbs.append('meta_p')
+ return (private_dblist, private_tables, filtered_tables, public_wiki_dbs,
+ public_view_dbs, public_dbs)
+
+
+def get_private_databases(db, public_dbs, public_view_dbs, system_dbs,
+ user_databases_regex):
+ """
+ check no private databases are present
+ """
+ cursor = db.cursor()
+ databases = []
+ cursor.execute('SELECT schema_name FROM information_schema.schemata')
+ result = cursor.fetchall()
+ for row in result:
+ databases.append(row[0])
+ cursor.close()
+
+ non_public_wikis_present = ([d for d in databases if d not in public_dbs
+ and d not in public_view_dbs
+ and d not in system_dbs
+ and not re.match(user_databases_regex, d)])
+ return non_public_wikis_present
+
+
+def get_private_tables(db, private_tables, system_dbs, user_databases_regex):
+ """
+ check no private tables are present
+ """
+ cursor = db.cursor()
+ format_in_private_tables = ','.join(['%s'] * len(private_tables))
+ format_in_system_dbs = ','.join(['%s'] * len(system_dbs))
+ query = ("SELECT table_schema, table_name"
+ " FROM information_schema.tables"
+ " WHERE table_name IN ({})"
+ " AND table_schema NOT RLIKE %s"
+ " AND table_schema NOT IN ({})"
+ ).format(format_in_private_tables, format_in_system_dbs)
+ cursor.execute(query,
+ private_tables + [user_databases_regex] + system_dbs)
+ result = cursor.fetchall()
+ cursor.close()
+
+ return result
+
+
+def column_has_private_data(conn, database, table, column):
+ """
+ check that given column is not null or the empty string
+ """
+ cursor = conn.cursor()
+ has_private_data = False
+ try:
+ query = ("SELECT count(*)"
+ " FROM `{}`.`{}`"
+ " WHERE NOT (`{}` IS NOT NULL"
+ " OR `{}` <> '')").format(database, table,
+ column, column)
+ cursor.execute(query)
+ result = cursor.fetchall()
+ if int(result[0][0]) > 0:
+ has_private_data = True
+ # Ignore "table doesn't exist" errors
+ except pymysql.err.ProgrammingError:
+ pass
+ # Ignore "field doesn't exist" errors
+ except pymysql.err.InternalError:
+ pass
+ cursor.close()
+ return has_private_data
+
+
+def get_unfiltered_columns(conn, filtered_tables, public_wiki_dbs):
+ """
+ check no private fields are present
+ """
+ unfiltered_columns = []
+ for line in filtered_tables:
+ if line[2] == 'F':
+ table = line[0]
+ column = line[1]
+ for database in public_wiki_dbs:
+ if column_has_private_data(conn, database, table, column):
+ unfiltered_columns.append([database, table, column])
+ return unfiltered_columns
+
+
+def main():
+ (private_dblist, private_tables, filtered_tables, public_wiki_dbs,
+ public_view_dbs, public_dbs) = get_lists(all_dblist_path,
+ private_dblist_path,
+ deleted_dblist_path,
+ filtered_tables_path)
+ db = pymysql.connect(host='localhost', user='root',
+ unix_socket='/tmp/mysql.sock')
+
+ print('Non-public databases that are present:')
+ private_databases_present = get_private_databases(db, public_dbs,
+ public_view_dbs,
+ SYSTEM_DBS,
+ USER_DATABASES_REGEX)
+ print(private_databases_present)
+
+ print('Non-public tables that are present:')
+ private_tables_present = get_private_tables(db, private_tables, SYSTEM_DBS,
+ USER_DATABASES_REGEX)
+ print(private_tables_present)
+ print('Unfiltered columns that are present:')
+ unfiltered_columns_present = get_unfiltered_columns(db, filtered_tables,
+ public_wiki_dbs)
+ print(unfiltered_columns_present)
+
+
+if __name__ == "__main__":
+ main()
--
To view, visit https://gerrit.wikimedia.org/r/323525
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I66e43a1bc7554fd095ddda378a40856da115fdf9
Gerrit-PatchSet: 16
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Jcrespo <[email protected]>
Gerrit-Reviewer: ArielGlenn <[email protected]>
Gerrit-Reviewer: Jcrespo <[email protected]>
Gerrit-Reviewer: Volans <[email protected]>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
