TALLINN UNIVERSITY OF TECHNOLOGY Faculty of Information Technology Department of Software Science Igor Volkov 143772IAPM EXTENDING POSTGRESQL DATABASE MANAGEMENT SYSTEM TO ADD SUPPORT OF DATA MASKING Master's thesis Supervisor: Erki Eessaar PhD Tallinn 2017 TALLINNA TEHNIKAÜLIKOOL Infotehnoloogia teaduskond Tarkvarateaduse instituut Igor Volkov 143772IAPM POSTGRESQL ANDMEBAASISÜSTEEMI LAIENDAMINE ANDMETE MASKIMISE VÕIMALUSTEGA Magistritöö Juhendaja: Erki Eessaar Doktor Tallinn 2017 Author’s declaration of originality I hereby certify that I am the sole author of this thesis. All the used materials, references to the literature and the work of others have been referred to. This thesis has not been presented for examination anywhere else. Author: Igor Volkov 15.05.2017 3 Abstract Data masking is a difficultly reversible process that replaces sensitive and valuable data with data that looks realistic. However, in fact data is generated or otherwise computed and thus cannot be used for unauthorized purposes. PostgreSQL is an advanced SQL database management system (DBMS) that as of May 2017 ranks as the second most popular open source DBMS and fourth most popular DMBS in general [1] . Yet there is an apparent lack of data masking solutions that are free, open source, and native to the DBMS. This work explores the types and techniques for data masking described in the existing literature, implements some of the techniques in PostgreSQL as a proof-of-concept open source extension, and presents a performance test of the implemented solution in case of masking a simple database. As the implemented extension is far from being perfect, its limitations, possibilities for the future development, and necessary refactorings are also presented. The software is licensed with the MIT license. The source code is published at https://gitlab.com/thodt-md/themask/tree/devel . This thesis is written in English and is 90 pages long, including 8 chapters, 9 figures figures and 11 tables. 4 Annotatsioon PostgreSQL andmebaasisüsteemi laiendamine andmete maskimise võimalustega Andmete maskimine on raskesti tagasipööratav protsess, mis asendab tundlikud ja väärtuslikud andmed realistlikuna näivate andmetena. Tegelikkuses on need andmed genereeritud või olemasolevate andmete põhjal arvutatud. Seega ei saa neid andmeid otstarbe (näiteks süsteemi testimine) väliselt kasutada. Leidub hulgaliselt erinevaid andmete maskimise algoritme. Neid realiseerivad mitmed andmebaasisüsteemid ja ka andmebaasisüsteemidest eraldiseisvad programmid. PostgreSQL on võimekas SQL-andmebaasisüsteem (DBMS), mis 2017. aasta mai seisuga on populaarsuselt teine avatud lähtekoodiga andmebaasisüsteem ja populaarsuselt neljas üldises andmebaasisüsteemide populaarsuse pingereas [1] . Samas ei leidu selle jaoks andmete maskeerimislahendusi, mis on tasuta, avatud lähtekoodiga ja integreeritud andmebaasisüsteemi. Antud töö annab kirjandusel põhineva ülevaate andmete maskeerimisvõimalustest, realiseerib osa võimalustest kontseptuaalse prototüübina (proof-of-concept) PostgreSQL avatud lähtekoodiga laiendusena ning mõõdab loodud laienduse abil tehtavate operatsioonide jõudlust lihtsa andmebaasi maskimise põhjal. Kuna loodud laiendus on ideaalist kaugel, siis töös kirjeldatakse ka selle puuduseid, edasisi arendusvõimalusi ning refaktoreerimise vajadusi. Jõudlustestide kohaselt ei muutnud maskeerimisoperatsioonid andmete kopeerimist märgatavalt aeglasemaks. Kõige aeglasemaks operatsiooniks osutus juhuslike isikunimede genereerimine. Selle kood vajab refaktoreerimist. Kuigi jõudlustestid näitavad, et prototüüp on töövõimeline, siis täiuslikuks andmete maskeerimise lahenduseks saamine nõuab lisatööd. 5 Tarkvara on litsenseeritud MIT litsentsiga. Tarkvara lähtekood on publitseeritud https://gitlab.com/thodt-md/themask/tree/devel. Lõputöö on kirjutatud inglise keeles ning sisaldab teksti 90 leheküljel, 8 peatükki, 9 joonist, 11 tabelit. 6 List of abbreviations and terms AES Advanced Encryption Standard – “a specification for the encryption of electronic data established by the U.S. National Institute of Standards and Technology (NIST) in 2001” [2] . CSV Comma-Separated Values – standardized textual format, which is commonly used for storing tabular data [3] . CRUD Create, Read, Update, Delete – basic data manipulation operations. DBA Database Administrator – a person responsible for database maintenance and operation [4] . DBMS Database Management System – “a set of programs that enables users to store, modify and extract data from a database” [5] . DDL Data Definition Language – a subset of a database domain- specific language, meant for altering the structure of the database. SQL operators that are used in its DDL statements include CREATE, ALTER and DROP. DML Data Manipulation Language – a subset of a database domain- specific language, meant for querying, persisting and modifying data in the database without altering its structure. SQL operators that are used in its DML statements include SELECT, INSERT, UPDATE, DELETE and MERGE. FPE Format-Preserving Encryption – an encryption technique that allows the encrypted text to be in the same domain as plaintext. For example, encrypting a valid credit card number will yield another valid credit card number [6] . JSON JavaScript Object Notation – lightweight textual data interchange format, based on a subset of the JavaScript programming language [7] . JSONB Binary data type that can be used in PostgreSQL for a more efficient JSON data storage [8] . LOB Large Object – a term for types of unstructured (in the sense that the DBMS cannot operate on its structure) binary (BLOB) and character (CLOB) data in some SQL DBMSs. The values that belong to the types have typically very high size limits and are stored in a location separate from other data in the database (outside of regular data blocks where data is stored row or column wise). 7 LUT Lookup table – an array-like structure that is used to replace an expensive computation with a simpler array-lookup [9] . PIC Personal Identification Code (Estonian: isikukood) – a numeric code issued by an Estonian governmental agency. The code is formed on the basis of the gender and the date of birth of a natural person and allows the specific identification of the person [10] . PL/pgSQL Procedural Language/PostgreSQL - built-in loadable procedural language for the PostgreSQL DBMS that can be used to perform server-side database management and data processing [11] . RDBMS Relational Database Management System – a DBMS where the data is presented to the database users as values in the relational tables [12] . Internal storage of the data is not prescribed by the relational model. SQL Structured Query Language – a standardized concrete (as opposite to the abstract language like a data model) language for managing databases [13] that has been created according to the underlying data model of SQL. The model is similar to but not equivalent with the relational data model. SQL is a database domain-specific language. SQL DBMS SQL Database Management System – database management system that allows organization of data according to the underlying data model of SQL and offers SQL database language to work with the data. SQL proxy SQL proxy is a type of a computer system or a software application that forwards SQL requests from a SQL DBMS client to the SQL DBMS server and the related responses back to the client. The proxy can cache, alter, or drop the incoming requests. TUT Tallinn University of Technology UML Unified Modeling Language – standardized general-purpose graphical modeling language that can be used to analyze, specify, and document the artifacts in an information-intensive system [14] . XML Extensible Markup Language – textual data interchange format recommended by W3C [15] . 8 Table of Contents 1 Introduction.................................................................................................................14 2 Data Masking...............................................................................................................17 2.1 The Need for Data Masking..............................................................................17 2.2 Data Masking Architectures..............................................................................19 2.2.1 Static Data Masking.......................................................................................19 2.2.2 Dynamic Data Masking.................................................................................20 2.2.3 On-the-Fly Data Masking..............................................................................22 2.3 Data Masking Techniques.................................................................................22 2.3.1 Variable Suppression.....................................................................................23 2.3.2 Truncation or Cropping.................................................................................23 2.3.3 Substitution....................................................................................................23 2.3.4 Shuffling........................................................................................................23 2.3.5 Masking Out..................................................................................................24 2.3.6 Random Noise...............................................................................................24 2.3.7 Encryption.....................................................................................................24 2.3.8 Format-preserving Encryption.......................................................................24 2.3.9 Methods Based on Linear Models.................................................................25 2.4 Risks and Challenges in Data Masking.............................................................25 2.4.1 Risk of Accidental Disclosure.......................................................................26 2.4.2 Masking Synchronization..............................................................................26 2.4.3 Risk of Data Being Unusable........................................................................26 2.4.4 Masking Values that Belong to Structured and Large Object (LOB) Data Types.......................................................................................................................27 2.4.5 Data Integrity.................................................................................................27 2.4.6 Misconfiguration...........................................................................................27 3 Existing Data Masking Implementations....................................................................28 3.1 SQL DBMS Implementations...........................................................................28 3.2 Implementations Done by Independent Software Vendors..............................30 9 4 Implementation Alternatives in PostgreSQL...............................................................33 4.1 PostgreSQL Extension Mechanism..................................................................33 5 Designing and Implementing the PostgreSQL Extension...........................................36 5.1 On Using UML for Modeling PostgreSQL Extensions....................................36 5.2 Functional Requirements..................................................................................37 5.2.1 Add Masking Context....................................................................................38 5.2.2 Modify Masking Context...............................................................................38 5.2.3 Remove Masking Context.............................................................................39 5.2.4 Add Table Policy...........................................................................................39 5.2.5 Modify Table Policy......................................................................................39 5.2.6 Remove Table Policy.....................................................................................39 5.2.7 Add Column Rule..........................................................................................40 5.2.8 Modify Column Rule.....................................................................................40 5.2.9 Remove Column Rule...................................................................................40 5.2.10 View Column Rules.....................................................................................40 5.2.11 Compile Rules.............................................................................................41 5.2.12 Execute Masking Process............................................................................41 5.3 Non-functional Requirements...........................................................................41 5.4 Domain Model..................................................................................................43 5.5 Extension Configuration Tables........................................................................48 5.6 The Main Processes..........................................................................................51 5.6.1 Rule Compilation...........................................................................................51 5.6.2 Masking Execution........................................................................................55 5.7 On Similarity To Commercial Data Masking Solutions...................................56 5.8 Highlights on the Implementation Details........................................................57 5.8.1 Coding Best Practices....................................................................................57 5.8.2 Code Structure...............................................................................................59 5.8.3 Packaging and Distribution...........................................................................61 5.9 Installation.........................................................................................................63 5.10 Issues and Limitations.....................................................................................64 5.10.1 Not Directly Usable by Client Applications................................................64 5.10.2 Incomplete Transfer of Table Details..........................................................64 5.10.3 No Object Types Besides Base Tables........................................................65 10