Transcription
Achieving PCI CompliancewithPostgres/Denish PatelDatabase Architect
Who am I ? Lead Database Architect @ OmniTIEmail: denish@omniti.com ; Twitter: @DenishPatelhttp://www.pateldenish.comProviding Solutions for business problems to deliver ScalabilityReliabilityHigh AvailabilityConsistencySecurityWe are hiring!! Apply @ l42.org/lg1
Agendum PCI Introduction Merchant levels PCI compliance requirements How to achieve Common Myths QA2
Introduction & History PCI DSS is a result of the collaboration between all majorcredit card companies (including Visa, MasterCard, JCB,American Express, and Discover) that designed the PCIDSS to establish industry-wide security requirements.The Payment Card Industry (PCI) Data Security Standard(DSS) is a set of specific credit card holder protectionregulations and guidance to combat identity theft.PCI DSS v1.1 introduced in Sept 2006. PCI DSS v2.0effective until December 31st, 2014PCI DSS Version 3 published on Nov 2013 & effectivesince Jan 1st, 20143
PCI DSS ApplicabilityIf a Primary Account Number (PAN) is stored, processed, ortransmitted.Card holderdataData ElementStoragePermittedRenderdataunreadablePrimary Account Number(PAN)YesYes (1)Cardholder NameYesNoService CodeYesNoExpiration DateYesNoNoN/ANoN/ANoN/ASensitiveFull Magnetic Stripe DataauthenticationCAV2/CVC2/CVV2/CIDdata (2)PIN/PIN Block(1) PCI DSS requirements 3.3 and 3.4 apply only to PAN. (2) Sensitive authentication datamust not be stored after authorization (even if encrypted).4
Merchant LevelsLevel 1Level 2Level 3Level 4TXNProcessedAnnually 6 M1-6 M 20,000 & 1M 20,000Site terlyQuarterlySAQAnnualAnnualAnnualAnnual* Reference : s/cisp/merchant-pci-dsscompliance.jsp5
Why Postgres?“By default PostgreSQL is probably the mostsecurity-aware database available”– David Litchfield (The Database Hackers Handbook)PostgreSQL offers encryption at several levels, andprovides flexibility in protecting data from disclosure dueto database server theft, unscrupulous administrators,and insecure networks.SE-PostgreSQL project SE-Linux will provide row levelsecurity features on the par with Oracle – Oracle LabelSecurity and Virtual Private Database6
PCI DSS v3 : ObjectivesBuild and Maintain a Secure NetworkRequirement 1: Install and maintain a firewall configuration toprotect cardholder data.Requirement 2: Do not use vendor-supplied defaults for systempasswords and other security parameters.Protect Cardholder DataRequirement 3: Protect stored cardholder data.Requirement 4: Encrypt transmission of cardholder data acrossopen, public networks.7
PCI DSS v3 : ObjectivesMaintain Vulnerability Management ProgramRequirement 5: Use and regularly update anti-virus software.Requirement 6: Develop and maintain secure systems andapplications.Implement Strong Access Control MeasuresRequirement 7: Restrict access to cardholder data by businessneed-to-know.Requirement 8: Assign a unique ID to each person withcomputer access.Requirement 9: Restrict physical access to cardholder data.8
PCI DSS v3 : ObjectivesRegularly Monitor and Test NetworksRequirement 10: Track and monitor all access to networkresources and cardholder data.Requirement 11: Regularly test security systems andprocesses.Maintain an Information Security PolicyRequirement 12: Maintain a policy that addressesinformation security.9
Requirement # 1Install and maintain a firewall configuration to protectcardholder data.10
Requirement # 11.2 “Build a firewall configuration that denies all traffic from“untrusted” networks and hosts, except for protocolsnecessary for the cardholder data environment.”PostgreSQL helps to achieve this requirement: pg hba.conf built-in security feature helps to deny orlimit database access from IP address ranges that aredeemed “untrusted.”log connections/disconnections settings helps tocentralize database connection logs to be kept atcentralized place11
Requirement # 2Do not use vendor-supplied defaults for systempasswords and other security parameters. Use a password for default “postgres” userDon’t allow trust authentication from any host/user/database in pg hba.conf file. Not even from localhostand postgres user.Revoke remote login access on template1 and postgresdefault databasesGrant limited permissions to monitoring user.Revoke objects privileges from PUBLICBe careful about GRANTSSecurity Definer functions and Views are your friends12
Example: Monitoring UserCREATE ROLE circonusWITH NOSUPERUSER NOCREATEROLENOCREATEDB LOGIN PASSWORD 'XXX’;ALTER ROLE circonus SET search path TOsecure check postgres, pg catalog;SET search path to secure check postgres;CREATE FUNCTION pg ls dir(text) RETURNS SETOF textAS beginreturn query(select pg catalog.pg ls dir('pg xlog'));end LANGUAGE plpgsql SECURITY DEFINER;13
Requirement # 3Protect stored cardholder data.“Protection methods such as encryption, truncation, masking,and hashing are critical components of cardholder dataprotection. If an intruder circumvents other networksecurity controls and gains access to encrypted data,without the proper cryptographic keys, the data isunreadable and unusable to that person .”3.1 a Implement data retention policy (Quarterly)3.1 b Re-encrypt and rehash data whenever an employee withprior access to the system leaves the company3.3 Mask PAN when displayed (the first six and last four digitsare the maximum number of digits to be displayed).14
Requirement # 33.4 Render PAN unreadable anywhere it is stored(including on portable digital media, backup media,and in logs) by using any of the followingapproaches: One-way hashes based on strong cryptography (hashmust be of the entire PAN)Truncation (hashing cannot be used to replace thetruncated segment of PAN) ,Index tokens and pads (pads must be securelystored) Strong cryptography with associated keymanagement processes and procedures15
Requirement # 33.5.1 Restrict access on encrypted keys to limited number ofcustodians3.6.6 Verify that key-management procedures areimplemented to require split knowledge and dualcontrol of keys (for example, requiring two or threepeople, each knowing only their own part of the key, toreconstruct the whole key).16
Requirement # 3 .html Example: pgp pub decrypt(msg bytea, key bytea [, psw text [,options text ]]) returns textpgp pub decrypt bytea(msg bytea, key bytea [, pswtext [, options text ]]) returns byteaNever hash a card without a salt, and preferably usevariable saltsDon't use md5, use something better like AES or SHA-256Column level Encryption using pgcrypto17
Example- Encrypt Card Holder DataCREATE OR REPLACE FUNCTION cc.insert cc(p cc number text)RETURNS bigintLANGUAGE plpgsqlSECURITY DEFINERAS function DECLAREv hashed ccbytea;v key idtext;v newcardidbigint;v oldcardidbigint;v pubkeybytea;v rowrecord;v salttext;v salt filetext;BEGINIF p cc number IS NULL THENRAISE EXCEPTION 'Cannot accept NULL for credit card number';END IF;FOR v row IN SELECT salt file FROM key.hash WHERE active true ORDER BYkey timestamp DESC18
Example- Encrypt Card Holder DataLOOPv salt : pg read file(v row.salt file);v hashed cc : pgcrypto.digest(v salt p cc number, 'sha512');SELECT cc card id INTO v oldcardid FROM cc.creditcard WHEREsalt file v row.salt file AND cc hash v hashed cc;IF v oldcardid IS NOT NULL THENEXIT;END IF;END LOOP;IF v oldcardid IS NOT NULL THENINSERT INTO audit.access log (pid, role, hostname, ipaddr, port,access time, activity type, cc card id)SELECT pid, usename, client hostname, client addr, client port,query start, 'insert cc: returned existing card id', v oldcardidFROM pg stat activityWHERE pid pg backend pid();RETURN v oldcardid;ELSE19
Example- Encrypt Card Holder DataSELECT key id, pubkey INTO v key id, v pubkey FROM key.pgp WHERE active trueORDER BY key timestamp DESC LIMIT 1;SELECT salt file INTO v salt file FROM key.hash WHERE active true ORDER BYkey timestamp DESC LIMIT 1;v salt : pg read file(v salt file);v hashed cc : pgcrypto.digest(v salt p cc number, 'sha512');INSERT INTO cc.creditcard (cc number, cc hash, pgp key id, salt file)VALUES (pgcrypto.pgp pub encrypt(p cc number, v pubkey), v hashed cc, v key id, v salt file) RETURNING cc card id INTO v newcardid;INSERT INTO audit.access log (pid, role, hostname, ipaddr, port, access time,activity type, cc card id)SELECT pid, usename, client hostname, client addr, client port, query start,'insert cc: successfully inserted new card id', v newcardid FROM pg stat activityWHERE pid pg backend pid();RETURN v newcardid;END IF;END; function 20
Example- Decrypt Card Holder DataCREATE OR REPLACE FUNCTION cc.get cc number(p cc card id bigint)RETURNS textLANGUAGE plpgsqlSECURITY DEFINERAS function DECLAREv activity type text;v key idtext;v privkeybytea;v privkey file text;v privkey pwdtext;v privkey store text[][];v unencrypted cc text;BEGINSELECT pgcrypto.pgp key id(cc number) INTO v key id FROM cc.creditcard WHEREcc card id p cc card id;SELECT privkey file INTO v privkey file FROM key.pgp WHEREpgcrypto.pgp key id(pubkey) v key id;v privkey : pg read binary file(v privkey file);21
Example- Decrypt Card Holder DataFOR i IN 1.array length(v privkey store, 1)::int LOOPIF v key id v privkey store[i][1] THENv privkey pwd v privkey store[i][2];EXIT;END IF;END LOOP;SELECT encode(pgcrypto.pgp pub decrypt bytea(cc number, v privkey,v privkey pwd), 'escape') INTO v unencrypted ccFROM cc.creditcardWHERE cc card id p cc card id;IF v unencrypted cc IS NOT NULL THENv activity type : 'get cc number: successfully returned unencrypted cc number';ELSEv activity type : 'get cc number: attempt to get non-existent cc number';END IF;INSERT INTO audit.access log (pid, role, hostname, ipaddr, port, access time,activity type, cc card id)SELECT pid, usename, client hostname, client addr, client port, query start,v activity type, p cc card id FROM pg stat activityWHERE pid pg backend pid();RETURN v unencrypted cc;END function 22
Requirement # 4Encrypt transmission of cardholder data across open,public networks.“Sensitive information must be encrypted during transmissionover networks that are easily accessed by maliciousindividuals .”The traffic between datacenters is encrypted at the networklayer (secure VPN, for example)The pg hba.conf file allows administrators to specify whichhosts can use non-encrypted connections (host) and whichrequire SSL-encrypted connections (hostssl).Encrypt applicable data before insert into database23
Requirement # 5Use and regularly update anti-virus software orprograms.“Anti-virus software must be used on all systems commonlyaffected by malware to protect systems from current andevolving malicious software threats. ”24
Requirement # 6Develop and maintain secure systems and applications.“All critical systems must have the most recently released,appropriate software patches to protect againstexploitation and compromise of cardholder data by maliciousindividuals and malicious software.” Apply all new security patches within one month.PostgreSQL Security releases : http://www.postgresql.org/support/security.html25
Requirement # 66.4 Follow change control procedures for allchanges to system components. Documentation of impactManagement sign-off for changeTesting of operational functionality , resultsRollback procedures26
Requirement # 7Restrict access to cardholder data by business need-toknow. Since PostgreSQL 8.4 provides column level permissionsUse separate schema and revoke all permissions from schemaEasy to revoke permissions on schema using PostgreSQL 9.0schema level permissions featureUse Group Roles with NOLOGIN to avoid group login i.e “devs”pg hba.conf : Fine Grained Access Control27
Requirement # 8Assign a unique ID to each person with computeraccess. Assign unique ID for all users who have access to card holderdata and systems related to itEnsure proper highly secure password policies in place forthe systems storing credit cardUse Two-factor authentication (for example, Duo, tokendevices, smart cards, biometrics, or public keys)authentication method.28
Requirement # 9Restrict physical access to cardholder data.“Any physical access to data or systems that housecardholder data provides the opportunity forindividuals to access devices or data and to removesystems or hardcopies, and should be appropriatelyrestricted ”29
Requirement # 10Track and monitor all access to network resources andcardholder data. Install pg stat statements extension to monitor all queries(SELECT, INSERT, UPDATE, DELETE)Setup monitor to find out suspicious access on PAN holdingtableEnable connection/disconnection loggingEnable Web Server access logsMonitor Postgres logs for unsuccessful login attemptsAutomated log analysis & Access Monitoring using AlertsKeep archive audit and log history for at least one year and forlast 3 months ready available for analysis30
Requirement # 11Regularly test security systems and processes.“System components, processes, and custom software should betested frequently to ensure security controls continue toreflect a changing environment. “Experience Consulting Companies can provide best practicesaround security policy, monitoring, and testing.31
Requirement # 12Maintain a policy that addresses information security.“A strong security policy sets the security tone forthe whole company and informs employees what is expectedof them. All employees should be aware of the sensitivityof data and their responsibilities for protecting it “Security 80% of people and processes 20%technology32
Solution33
Common Myths of PCI DSSMyth 1 – One vendor and product will make us compliantMyth 2 – Outsourcing card processing makes us compliantMyth 3 – PCI compliance is an IT projectMyth 4 – PCI will make us secureMyth 5 – PCI is unreasonable; it requires too muchMyth 6 – PCI requires us to hire a Qualified Security AssessorMyth 7 – We don’t take enough credit cards to be compliantMyth 8 – We completed a SAQ we’re compliantMyth 9 – PCI makes us store cardholder dataMyth 10 – PCI is too hard34
Take away . Security first, Compliance is result.Think beyond credit card data and grow overall security!!Develop “Security and Risk” mindset , not “compliance andaudit” mindset.Security is your goal!!Stop complaining about it and start doing it!!PCI Compliance is business requirement, it’s not an IT issue.35
Conclusion36
Thanks PgConf NYC Conference Committee OmniTi You!!We are hiring!! Apply @ l42.org/lg37
References https://www.pcisecuritystandards.org/documents/PCI DSS ts/DSS and PA-DSS Change /security standards/pci dss supporting s/pciscc ten common myths.pdf38
The Payment Card Industry (PCI) Data Security Standard (DSS) is a set of specific credit card holder protection regulations and guidance to combat identity theft. PCI DSS v1.1 introduced in Sept 2006. PCI DSS v2.0 effective until December 31st, 2014 PCI DSS Version 3 published on Nov 2013 & effective since Jan 1st, 2014 Introduction .
