次の話は、ピジネアジュンの最後の話です。私は非常に慣れています。みなさん、私はマサノリ・オーヤマンです。マサノリはマサルです。私はデータベースエンジニアです。特にポーセスキューウェイです。私は常にカスタマーに質問して、アドバイスを与えます。私はデータベースセキュリティに関して、新のフュージャル、プロセスキューウェイを紹介します。私の興味は、パラクエルです。大きなフュージャルのプロセスキューウェイは9.6です。マサシコスターの提供は、新のフュージャルのプロセスキューウェイは9.6です。私はパラクエルのプロセスキューウェイに連繫しました。PG Conf Azureというサービスのプロセスキューウェイです。私もこのプロセスキューウェイのプロセスキューウェイを紹介します。もし、このプロセスキューウェイに関して、このプロセスキューウェイを紹介します。私は一番興味がありました。私はハドリップエンジニアのNTTデータエンジニアです。I managed big Hadoop cluster which consists 1,000 nodes and has 200 petabyte data in the cluster.I am sometimes surprised by the difference of post SQL Hadoop.I think post SQL is more precision software than Hadoop.It catches my interest.Ok, about NDT.I am a member of NDT,Nippon telegraph and telephone compilation.NDT is the biggest telecom company in Japan.NDT OS Center is offering total support service such as a support desk introduction and maintenance of OSS products to these group companies.We also developing OSS products and related tools.We deal with roughly 60 OSS products.Ok, this is today's agenda.First, I will talk about the introduction of today's presentation.Then, I will talk about these two sections.Database security requirements and how to apply to post SQL.In the first section, I pick out the requirements related to database from PCI DSS.In the second section, I try to apply these requirements to post SQL.And I will talk about post SQL auditing tool developed by us.Ok, let's talk about the introduction.Let me talk about the stance in Japan.Japanese government aim to implement an action plan for strengthening the security of credit card transaction by 2020.For example, make business operators for the search information confirmed to their PCI DSS.And multilayered measures are introduced by retailers dealing with issue transactions.So, some of our customers we choose post SQL have a credit card information.Sometimes we are asked how to confirm to PCI DSS by them.Do you know PCI DSS?PCI DSS is chapter of payment card industry data security standard.It is one of the famous security standard in the world.It is promoted by these measure credit card companies.This is outline of PCI DSS.PCI DSS consist of disturb sections. Each section has some detailed requirements.Then these rules instruct you how to build and operate credit card system.PCI DSS is the requirement of whole system and operations.So, if you want to confirm your database to PCI DSS.Please think about how to apply this requirement to it.There is one question.I am not a security expert or PCI DSS expert.So, the explain of this presentation is not necessary correct.If you have to confirm your system to PCI DSS.Please consult PCI DSS expert called QSW.Okay, let's talk about database security requirements.Please consider these four categories from PCI DSS requirements.Keep your database secure,data encryption and key management.User identification,authentication,authorization,identity management,audit.Keep your database secure.PCI DSS requirements 2 and 6 says.Don't use a default user account and password.Don't use unnecessary modules,function,protocols.Admin control access has to be encrypted.Use latest software version.Okay, there is not difficult.Traditional or basic security practice important.Okay, next,data encryption and key management.PCI DSS requirement 3 says.Pan,primary account number,must be unreadable or encrypted.Pan is typically called credit card number,this one.The card number must be irreversibly unreadable or must be irreversibly encrypted with enough strong encryption.A method of unreadable is one way hash or punctuation.If you want to encrypt the card number,you must use strong encryption with key management process.PCI DSS says the encryption considered enough strong is AES or RSA etc.This requirement is frequently updated.So you should check the PCI DSS grocery and look for the section of strong cryptography.PCI DSS also says encryption key management.The card number must be encrypted by data encrypting key.Then data encrypting key must be encrypted by key encrypting key.So this is called a two-tier encryption. These keys are replaced in a certain period and must be stored separately.Of course,the access of key must be restricted and must be stored in the first location.And you must audit the status of using these keys.The detail of this is written as requirements 3.5,3.6.You can see that next user identification,authentication,authorization,identity management.PCI DSS requirements 7 and 8 tell about identification,authentication,authentication,identity management.Identification.PCI DSS says all user must be identified by unique user ID.It does not allow to share a single user account with several people because each user must take the brain for explaining their actions.authentication.In addition to assigning a unique ID,all user must be also authenticated by the following method.Password,TokenDevice,Biometrics.PCI DSS also instructs some password policies.Require minimum length of at least 7 characters.Change password at least once every 90 days and so on.PC requirement 8.2.All user ID is restricted access to least pre-page necessary to perform job responsibilities.identity management.Identification,authentication,authorization are managed by several policies.There is a user ID of the retired person.Lockout the user ID,which is made a long password 5 times,etc.Four details place the requirement 8.1.OK,last.Audit.PCI DSS requirements 10 tell about audit.Prevent must be audited,especially to audit all operations of administrators.It is important because it prevents an inside crime.However generally this is difficult.Audit logs must be output following information.To audit and protect the audit logs is also important.So you should record who and when access the audit logs and protect it from in-built changes.OK,we have looked at the requirement that PCI DSS require to database.Next,let's look at how this requirement can be adapted to process query from following categories.Keep your database secure.First,don't use post-agress account and change 5,4,3,2,4.Next,use OpenSSL connection with PSQA.Four details,please see this one.Explain all OpenSSL.PJHP Conf is the configuration file of access control.Update binaries to latest minor versions.I say again,traditional or base security process are important.OK,next,data encryption and key management.Postsql has encryption module called PGCrypt.PGCrypt is a good module,good encryption module.Today,I don't talk about PGCrypt deeply for describe this manual.However,there is a difficulty to apply PCI DSS to process query.If you want to confirm to PCI DSS,you have to manage tutorial key encryption by yourself.This makes application development hard.If you think that this program is critical,please consult the process vendors.It seems to me that process query need TDE transparent data encryption and KMS key management service.Today,I don't talk about describe TDE and KMS.But,I think we should start discussion that how to realize TDE and KMS in process query committees.OK?KMS,can I ask question also?Yeah,yes,yes,yes.Between yours and speakers understanding.Yeah,that's why I'm just taking permission.You mentioned key management services,right?This is something already offered as hardware security module,right?One of the systems commonly in financial domain.So,I don't think so oracle,even oracle has this thing,right?They have TDE,but they don't have KMS,right?So,I would like to hear about TDE as we go along.But for KMS,I have this point,I just want to say that.And frankly speaking,nobody would like to use database KMS,right?So,they not only go for hardware.And that hardware is produced normally by KLS or CSNATO or something like that.So,just one point.Yes,I'm taking with KMS.Yeah.Use identification,authentication,authentication and identity management.These are the following important things.Don't use supervisor.Apply PCIDs as password for Z.Manage each user ID.For details and extracts.First,don't use supervisor.Supervisor can do everything.For example,create user,create table,change,prepare,etc.So,but this is not allowed by PCIDsbecause all user ID must be restricted to list pre-pagenecesite to perform job responsibilities.Regarding how to restrict also ID.Please see these manuals.However,some useful SQL functions need to specify the pre-page.For example,create all-time event trigger.Create polling developer.Create table space.Create table functions.We have no choice but to use supervisor.So,I think supervisor must be audited fully.Next,password policy.PCIDs require some password policy.You can use password check module in process SQLand set the expiration date.Please see the manuals.However,these password policies cannot be implemented in process SQL.So,in small tractor,8.2.5 do not allow a new passwordthat is the same as any of the list for password used.8.2.6 set password for first time useand change immediately after the first use.So,I think we need to use the actual service.In that case.Next,manage each user ID.Delete or lockout user ID according to PCID SSrequirements.Use drop role to delete user IDand use alter role with no login to lockout user ID.However,to perform the following requirementit's hard only in process SQL.Lockout in active user ID within 90 daysand require the userand lockout the user through repeated access attemptafter not more than 6 attemptsand require the user to reauthenticateif a session has been idle more than 50 minutes.So,I think we should use object service too.Possess SQL can use LDAP authentication.Today,I introduce the following object serviceApache DS and Apache directory study.Apache DS is a directory service software.Apache directory study is GUI console of directory service.I have not used these software in the production servicebut I think these are nice identity management too.Apache DS has rich password policy configuration,rich lockout policy configurationand can output authentication roleswhich is used for law auditing.Apache DS can execute authenticationand management user ID of process SQLbut there are some attention.Apache DS cannot create user account in process SQLso,you should create a process SQL user accountthen register them to the Apache DS.Apache DS and Apache directory studyyou cannot control the editauthorization process SQL.so,you need to load into process SQLto edit authorization of process SQLlast auditwe can use process SQL server rolefor auditingif you can use these parametersif you set these parametersprocess SQL server roleoutput this event required by apache DSthe access to all the roles should beoutput by OS modulefor example,auditdwhich is relaxed auditing toois good too for this processif you set logline prefixserver role can output the following informationfor description,we see the manualsbut object nameor object IDoutputed by server roleit cause difficulty to auditI will explain on the next slideand to protect all the logsuse syslog to send it another serverand use auditdwhich is introduced previous slidePossess SQL server role has some difficultyto apply pshids to process SQLfirst,server role output the SQLwithout change and do notoutput the information of object nameso,some SQLs are difficult to auditfor example,do statementdo statement can execute SQLor a procedural languagein this dollar quoted materialso,you can execute this SQL worldthis is the output of server roleif we want to know about SQLaccess to important tailit is hard to searchnext,the log side is bigbecause server role does not haveuseful role filtersso,server role output all tables rolesnext,super user canchange server roleserver role settings easilyso,server role is hard to confirmto recommend 10.2.2because server roleconfiguration are aided easily by super usersuper user can change these parametersserver role is running or stoppedso,super user can makeserver role setting to auditin effective without anyone noticingnext,cannot divide a rolenext,cannot divide a rolefor auditing into a rolefor operatingtypically,an audit role containsconfidential informationbecause they are the same as contentsin the databaseso,only userswho have a job related needcan read or draw filesfor example,oiltersbut,server roles are important to operationor process SQLdatabase operators oftenread that filesso,two output rolesto audit with server roleis undesirableso,we should use pg auditinstead of server role for auditingpg audit is developed bysecond quadrant and crunch datathis is role and URLyou can use it easilyby installing and setting parameterssetting pg audit toshared preload libraries in process SQLconfpg audit can reduce audit log sizeset class nameto audit pg audit log parameterin processqa.confif you set pg audit to readonly,this SQLselect values are outputtedif you set writeonly,this SQLoutputted,installed,update,readfor example,if you setpg audit logs to write,dgaand miss,thensqq execute,this SQLbeginselectupdate commitso,this isoutput of the logsit does not outputtedthe log size is suppressed to a minimumyou need itpg audit can outputobject name related to SQLif you set pg audit to readthen executethis SQL,that is explainedprevious,pg audit outputtable name is outputted withschema nameso,to search SQLaccessing to important table iseasilyok,I think you can understand pg auditis good for auditinghowever,it cannot coverthe two pg audit requirements yetfirst,superuser can changepg audit setting easilyjust like server log settingspg audit configurationedited easilyby supervisorbecause pg audit configurationin processquery.confnextcannot divide server loginto pg audit logpg audit log outputwith server logjust like server logthis calls thatdatabase operatorsorder logsthis is undesirableso,I think thatsuperuser mustnot be able to changeaudit setting easilyand divide a server loginto a pg audit logok,so we forkedthe pg audit and add some newfuturesentity audit centerpg audittoday called entity pg auditentity pg audit enhancementpointthisstarting processquery is neededto change audit settingentity pg auditconfig fileis divided fromprocessquery.confso,superuser have tostart processqueryto change entity pg audit configurationthat is noteasy in thepg audit configuration environmentso,superuser cannotchange entity pg auditconfig configurationeasilydivide a server loginto an audit logpg audit can send alog management server by syslogrich log filterpg audit has someuseful log filterso,the log siteis suppressed to minimumI will explain theseweatherI touch entity pg auditconfigation filefor nextexplanationsentity pg audit configurationfile consists of thesesectionsoutput,option and rulein output sectionyou can set loggeryou use server logor syslogoption sectionyou can set somemisconfigationin rule sectionyou can set filter rulewho describewith the readmeok,firstrestarting processqueryis needed to change audit settingsentity pg audit hasonly two parametersin processquery conespeciallyentity pg audit lawentity pg audit haspg audit config file parameterwhich indicatespg audit config file pathother pg audit settingsin pg audit.conffilebecauseonly the auditbecause only the audithas better be able toedit processquery.confedit pg audit.conffor examplethe order log intoaudit user accountthisthen edit pg audit.conffileand change permissionssuper userfor examplepostgres usercannot changeaudit settingsnext,dividea server log into an audit logentity pg auditentity pg audit canexport to server log or sys logthis setting in output sectionif you sendthe audit logto a log management serveronly audit canaccess audit log filewhich log filterentity pg audit hasthese filtersthis setting inrule sectionfor exampleif you sendthe audit log to server user accountonly only server user accountthe log outputin addition to thisif you sendthe audit log to app user accountand object nameto myschema audit tablefor examplemyschema audit tableonly the SQLaccessing by super user 1and the SQLaccessing to myschema audit tableby app user 1are outputtedso the log sizeis suppressed to minimumby entity pg auditI explainthis example use casethere are post SQLprimary serverprimary serveraccessaccessaccessof OLTP applicationfrom application userand secondary serveraccessof analysis applicationfrom VR userdb operatordb administrator executeeach management taskfor OLTPap entitypg auditon the outputread and write SQLaccessing to important tablebecause to minimum to the log sizefor analysisap entitypg audit outputread SQLaccessingto all tablesfor operating taskand administration taskentity pg audit outputall SQLthis log sendsend to theoiter log management serverby syslogfor example frontdauditormanagethe logsoiter manage this entitypg audit configuration filesoiter management isentity pg audit configuration filesand executeaudit taskbyfor exampleelastic searchok,finally,lap-upprocessql canconfirm to pshitts byfollowing thingsset basic configurationuse pgcryptouse adddictory serviceuse entitypg auditand I think processqlshould be betterto implement tdkms apito realize operationof processql resultand realize operationof processql resultsuperizartin pageok,thank you for listening my presentationsir,that should bein the database,right?or it should be a feature provided by databaseor even when usingpgcrypto for exampleinputing your dataand at the same time you're talking aboutauditing and loggingdon't you think your sensitive information is going to getlogged at the very same timeso don't you thinkit should be rather left to the application to encrypt the dataand send the encrypteddata back to the databaseso instead of making itall databases responsibilitydo you think to achievepcigssvery important that application also sharessome responsibilityinstead of databasemaking encrypting the databasesorry databasedoing the encryption of sensitivefield for example if I call apgcrypto function with mycredit card informationand that query is gettinglogged or if I haveaccess to pgstat activityI can see the credit card informationeven thoughthe database is encryptedif I have access to the logor if I have access to pgstat activityI can still see the credit card informationso don't you thinkit is more important to encrypt thedata in the applicationand not loador expect too much out of the database itselfsorryI'm not good at Englishpleasepleaseyeah we have several waysto do things outso we can have an applicationlevel and encryptionbut also databaselevel and encryptionso people's PCI doesn't saythat everything has to be donedo you think KMS is somethingthat should be expectedor do you think there is somethingthat has to beor encrypting sensitive fieldsparticularly do you think that has to beoffloaded to the databaseand application sends an encryptedtailor to be insertedin a column which storesmaybe a large textto dataso you only store encrypted credit cardnumber and notand don't call the pg groupthe function will encrypt itin the databasethe application server hasthat informationthen they have to see a referenceyou should be okaylike no no nonot really encrypt somethingbefore doing that particular operationI can control and I can change thelogging level of my applicationjust for that particular operationand then we do that in the databasecan't you do that in the databaseno because the database has toshow all the operations going onyou can't really mask thatin such a kind of a maskingI'm not sure aboutin first placebut we can do some kind ofasking where thesequence that you writecan you mask itbasicallyI have done PCI compliancefor many applicationsand passedand I can share some experiencefirst of alldatabasethe number one thing is thatofcourse you don't have thatin the combined format availablethat's called trackyour plan and your expiry when combinedthen together they become a trackand that should not be storedthis is the problemthis is basically the extra clausewhat they are complaining PCI guysso we put that in databasethen PCI guys came in and they saidno no district is thatand then we kept them separatelyand then if I'm allowed to talkwe were using oraclename as Imissing the namesomesome transparenttransparent data encryptionoracle data worldoracle data world is somethingthat's like TDE is likeis something you guys are talkingbasically on applicationdoing it on application levelwas very dangerous in factit's a dangerous propositionwe are running an applicationwe really cannot afford to do thisthing every now and thenso we need a very super fast mechanismso what they were suggestingis like on the application sidenot only you get slowerso rather than by the wayPCI just relaxes itit's not like PCI saying that you cannotstore the cardyes you have to store the card numberthat span and expiry in separate fieldsand your if your database is securewith the passwordand everybody is getting in the machinegetting everythingand by the wayI go forward we are logging thetranslation to financial donationthey are logged with all the informationand that data is getting in there as wellso it's not only the customer datathe transition that is also in thereI can see that as welland as far as your serversso I guess both are thingsthe KMS should be therefor if you are going for somethingI guess something similar totransparent retention which Oracle hasthis is something goodbut eventually what we learnedover the timewe didn't go fortransparent retention frankly becausethe clients cannot afford thatit was way expensiveand PCI relaxes this thingand you just keep both things separateand you don't have to print them in the logsas a gentleman was mentioningso you have to control thatin database logs everyone mentionedhow does the database knowthat this SQL is going to contain the track dataso it has no way to figure it outsoI guess encryption of logsand by the way all these PCI guysif you arefirst of all the logs should not beopen at any given point in timeand if you are opening it you are writing a concernor something like that soone thing I likewe should be doingsome transparent retentionbecause it has to be donerightother than that I think KMSpart I guessthat's a very stableor you would sayknown standard in the industry thatthey would use hardwarebased solutions for thatbecause of the load that softwarereally cannot handle so that's afirmware based solutionso my two sense are thatwe should be having something likethat is something missing I guessit's very expensiveby the way they are selling this featureit's telling everyoneinitially when we were doingI remember PCI we didn't know too muchand we were likeand the software was running in the banksand we were having an argument in the bankoh your application is so poorit doesn't want to encrypt the datawhat should I dookay buy this softwareand this software was heavy to expensiveso it is somethingit is very important I guessif we don't have it we should have itwhat I havekey for ityeah I want tothisyou want to yeahyeah for the pgsaucejust a very quick questionyou mentioned about a modified version ofpg audit that you guys havemodified forI was justare you planning tosubmit those back to the originalbecause you found it rightare you planning on doing that orsowesaidpg audit ishere in Githubwe open source codeand we plan to mergeoriginal pg auditso second ground andI'm sorry toask me this because I justtried that url and I was getting afour of four on it so I thought maybeit's a permission issue because Idid not see that sourceprobably I'm missing something here butthat's the context behind the questionthat url does not openfor youthe github we are sayingyeah the chip we take outyes open sourceI wasn't able to open thatit is open sourceyesmy question to both of youdo you have any plan of making itin code or adding it as an extensionwhich is in the codecontrib or four extensionsso what happens is becausemost of these features that areavailable in as comparedthey are normallythere is some sort of a chat going onsomewhere where they are being pitchedon the codethe issue is that it can normally takea longer period of timefor that description to beover becausetremendously all across the globethey keep pitching in with variousrequirements and somebody could suggestchange this or somebody could suggestI don't like this it should be thisfrom that so that kind ofa description normally takessometimes it goes over years and yearsso oncethat discussion is overit is added to the code distributionso that is somewhereon the middleI am not sure when it will be overbut there are many othersuch extensions which are stillavailable ason the source extensionbut they are not on the code distributionso it comes very soonhopefullyand I actually reallyI appreciate the partthat you were able tochange this for yourselfbecause of force it opens upand you were able to do thatand you were still keeping it as open sourceso that somebody else can make for the changesand but another good ideawould be toget a patch on top of itand submit it to the original repositoryso that anybodybecause I was not aware of thisI only got to know about it todayI wasn't aware of any such feature setor any pending patch anywhereso one of the ideas would be to go aheadand create a code requestand submit to a patch thereto the original repositoryof course the original developerswhich isof course we have the second partwe have a discussion around itand if people like itit will get mergedand when that extension gets mergedwith the poster skill code distributionthose pages will be thereand this will not helpI hopeI was able to explainbecause there is some companyin the boardand we canwalk togetherwe shouldwe should presentthe original codeso personallymy preferences areanything that we developany feature setorthe best way to go forwardis if you contributeto the communityso that it is availablefor the general classesand when there are any changesof course people, other people can do thatpromoting your own forththere is one way to do itkeeping your open source is another way to do itbut then if it has contributedback to the boardthat's a general benefitwe are thinking the samebut actually becausewe have a requirementright nowso I don't think we can get this featurein the board as soon as it takesseveral yearsso that means we needkind of a four projectkind of a two projectwhich is not in the boardso it takes timetwo, three is maybe waitingmaking these toolsand then gradually we putsome features in the boardatleast you know likein the contrarythat's the best wayjust from that pointa recent exampleyou would know about the table soundthat remains as an extensionso quite an extended period of timewe've been using thatas part of another productand we were distributing itas part of that particular productand it wasn't part of thePPG LogicalEPG Logical is another exampleit has moved really fasterthen a lot of other extensionsand I would love to see a lot of other great extensionsmoved as fast as PG LogicalI can understand that there was a certain demandfor PG Logical and that's why it won't passit had a portionso it really made senseto keep contributingbut thenthe other aspect is as much importantas creating those featuresto the original productyesI would really appreciateand I really appreciate thatactually modified it