[prev in list] [next in list] [prev in thread] [next in thread] List: postgresql-general Subject: [GENERAL] pg_audit_users - Auditing user activity From: Pierre <pinaraf () pinaraf ! info> Date: 2014-12-20 14:32:23 Message-ID: 1539105.VBOGNDHCGT () peanuts2 [Download RAW message or body] [Attachment #2 (multipart/mixed)] --nextPart21182021.2xS7EKBmbB Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="iso-8859-1" Hi I'm working on a web credit card payment solution, so in a PCI-DSS environment, and the auditors gave me trouble with one specific audit point for the PostgreSQL database. They require the list of users in the database that had no activity in the past 90 days to be deleted. So far, it seems the only solution to implement that in PostgreSQL would be to parse the log, hoping not to lose any line. That seems too risky for me, so I wrote my own solution for this issue, and I would like to submit it here for review/suggestion and to help other users facing the same needs. Since it's very small, I've taken the liberty of attaching it to this email. The code is more or less inspired by pg_stat_statements. So far I've not implemented saving upon restarts of the database, I'll probably do it in the next days/weeks. It has been tested against PostgreSQL 9.2 only, but I'll test 9.3 and 9.4 soon. Thanks Pierre --nextPart21182021.2xS7EKBmbB Content-Disposition: attachment; filename="pg_audit_users-0.1.tar.bz2" Content-Transfer-Encoding: base64 Content-Type: application/x-bzip-compressed-tar; name="pg_audit_users-0.1.tar.bz2" QlpoOTFBWSZTWUNh8zgADhd/pPz0IE5/////f/f/7v////4AgAAAgAhgD994UduUwB3Od11F3Pb1 2bzBnYbu5iuwdFFsYQes9nbCgevQlCCIxU9lPU8qn4jTyjTTU2FQenqg21EDRoAHqMjQND1AAaBA JoI0T0ENNNFPKNA0yZGmQAAAAGmgGgDTQlT9U2k0AAAAANAGg0DQAAAAAABJqSEyDUUzRoT09Jqa YgeU0AGmmjR6gDQAADRoAcAA0GhoNABpkGhkDTQAAGQAZAZAASJBAjJoE0ZBMFT9TaKfqJ6po9Hl TanlD1PUwnqaAMJ6J6mh6R9iu/+/3/T/j3qdVWEgHYCQvDyO8zxJJhd951PKFadIYMYEMbSMsShJ JUUCeXqgH0WpZOvYtZGcxpRNQydzG1oXIiH6drKxJdGCKDIKAxEWKIJBWKCIxiKKxFBfBRUQUPr4 4H9Z8cl5Grdk/tuqpUIUyVITbL0Wf8aePL8Ab9AXPAhtH2tafunSaederhctgqpUS+1qtY3ZcFmT QkUiqclSgWBaqs7Hnpe993LV0qxbGmub6FQujsqUt5VIsKqtjc0CWyTQT7Pl6/xb3793hvu4JLZd /VvuaUeElcoLafPxiyDF3jXxZ6NoxmEtYJTVufwOWzmvmTA816KZPQu9rvoFXgheIMAX3aYQjemI EsDi0AUlTAmdUTQR6k1tUKMQaCn68ATqdJhsuUvUbkYMXjY1Y40KXxyiSP/d75xjhcBSOYpUK0St RftZmMbGzzsLZ2ZT7Yx43BRuSsJvO5r83I0WZlIw06uRKmdWjDKh4u88LXdUR3bj89NpwqsDzVZH 48F8sGXDGR7oPkV+q75+52ZZwn/xmMq8a00PrHym1+X0zR27XBmNOGSQc9bVbmYd2/np1ynMmBxM yQyCdSCzum6TKv3mIdIORrCWv4ZvM8UHVlj27st8V0dzg7ejK7jfwXzxBK/4kOaYnuye2S4m+OMI KNAzKNe/ke6nwVlJ88DB5la4zyvhTzlzzIUEQ/sOnLQ8DLANbdpa1zSSep/GLXPY4r2weyy2DsIL FBZJGaBIjGliZunZugGB57qtY64xZWynW974GYSH0cqhhg5M8EpJOKIrpyhIeLsF+K5zKllsfTZz IExtJDOMa8rCcsE+6+c9c6xWaY256vmYhvW1XEZi9pwnRuWyuulDGZA1a0ypaybFUYKjGLktOma6 kZ89d6PUTlhnFiB1uy5YmwsUuQ7KOhTSCTuHCu49Oa2jC/b2e15tmz1jABkD4hRhXR3+5fT3s9uO GM3dNCoih/3aCUTal0UE9Pmgw09JlMXOYsdNbZypWXVHR7cCo1arx9NpP+8uJFhMvwJI1bz61pnY xjFhqGafCb5RPXSwlMkz28XA7rmYcWWWYTD4IMzN6GDFq005FfoHOrVebwrmwD7urgruGV2FJLmn 58lxA1pNJyf3TnSd6mJVz/fFd/sr38hlWhZbd0+xzaWvjLiSd6XiAduwk/CkN062IGy2qtuzKIjq 6B3iBkYxGKKCjBERYIyT12EuScXWiZ58aU4a7ubC/aTF2eCg9ErIiBT1ifGH3cXvb2htjwFW5iRC RTH72Ph+HP1ep0bNGBg+n4DaPI+ODvmyDIZJn+u3WfUgeFARwNvGqadj+KNvlXonUcbUxEANQuFJ K1OTpq1ntsUcZyEcCN4kZh8iOc/ugIcD82S47ZSGUGiqtxiH0dUpG2csoIo6cy/0OAKH/LCOkkC2 MF9n2/ZuQeD8YgZ9Y3Zei4r9smwOA7P12Kyy5SFSto+RKZIlT7X1MQif5Zi6gSLbO0lg5HFUnFEG TDa64pSD8EdW7jp08UM7SRbn6NkkNcddEHQUDUrubzwcdkDb1NUo30dm6jH86DyXFmxl7bCjJrh4 tm9pwMdfMtgUUfIM0sts37Y252PQOVJyoezGkEqpFF+lkiyUQBTkXyBMvVayWcPbabEoVs/0+Q8v QcJsb0wCXOu2DCRKCGJLjxlB8MTsOHdDeKvgsSyUSHetKiCFrNA5wUiuuiswyMgyTWhE6H7Li4zO /SSYTOPWPm4Bwcn0wrC0OMcdsR+b4DhitEUsddLIMQdWiPRGtjLavlwEySFVjprDi/xZTTpnuCFy OedjPhPbtJWs0Fg4eCaSdtjv2I6zbsJIqxBZ+r8ULHCE/hLI3Ukjsjw5uvlOXVbMRyv5OeCaO6Ca fy6cBr0XsLy7+GK52Ln4i6UzEksWQLsya8l6rUVH2A0Z7QmXfihQhyn7TkhyJNvvaRF1aLrATBRc 0yqhPJkKJAQKCZRuxA6y6HamKpyKRkgMlA6CgXAb4az8KbXXf08h+jiRsFXxg1UjG9wFTZzErJIZ 3vw4K8ZXgEeW5cwdE9OWJM3kOwKoVxTSe4Z3vHsinud0enLSSzKFhrKChsi0Vi09NfbcixFy3ItV /cmx8yFmcxTAePa7kwxeBqVZzh2iw3SGyM5sqtHXT5yNx8DbxwXn7VkgaOQvBbTxR0CynrL0fJPX VGyW60XjGaauWHDAONxHn2y6v8j+o3lCWrYmZi9wX8C+js/03CirAuQlllwJAntaKP6R70K86rJt wGKPMSnQipRzKUafW8giDYNJ0PgkG3cso/GBk25GlVjFiq93QaiGGrktou01qD5vF08cmzMnDDUt BcNj1SBZGgeRCSPJf2eINoktUez4T2fpxZU+bYyjYMaf01V4a3q8FebWZK0Mthq+efP860oNiJ5m vYpnRSWdZVqmc5soYVoWlNs7RCdjZrsS7mJNysME1KxXujtZXcXyEaKSktHmg6EkRuEhFp6bsT/N jQc4ygtOVkJjl8qSv8Pi1uex5N6PUVyK0hBfpLACIIWkkAZDTvyUpyKRjUxoJT2XlIZE5VGszzOX ThlxFUuQFLqtf1NgStcRoK3U4UcHm4kwn3d20+U3HRkOw0hqMVISosmiJxSOUOHE0oU1OThTnKU5 TJr8bopMVCCcVnSVDGxYDLxDOQDPWGK+ElKUNYEVGgOk8BdOtNUXkRCCZtYGabC8samKAgvuXREl ZyAMpwJLt7eQ5d7zRDJjdicnCLpUV9gdYYcIo6KT64jnar6NNnymUKh6BzqrYiANzcTsHsni5VIP CcpWVqRIpeTky1pYqQ7TOQbXfW8Q5Hf7j8hAmddglmvGdzksZL8XqBWA0kZZgM3gHKpPLp0Ve53+ 5xoIZRTaEzhAclqDTzuE6C+ubPDASVrV633sZBLCvqSsFy1ijKOGZ4JndlUlOrB1gcDjkDCh1dC1 2q3WHHX/4pQb2igVaSbB9iEsbSJxB0s1tffCrBhLfuTRBtOTDsiStw5mL3J+icS1SssAaaRljF8t VnXCm0utFAF3Pyfm5MkiTA9ZcuDp1JLYkqzwuONs02DGS7YxkmRQYEhhYueTR+t9KeIX6+SpnpUO eGZW8riBFiFnKy/O5al4atMzJG4wDQLTjGSOFSH06EpEDSY0tU5KRLaqQQj0f17yZ62XNiWU3W9X +NQsiBSOtMApUCo0JyoaVCJf98NZL6jXp8DiBd1dZBZ52/J06MLSmKFnb7F4Zdg6GsFAUWe3Uj4r vS49dDsxVT2tH0poXVsKboXvbLguaud+Zy4E80s1BitBx7Drkm1uDV3aepj3wrzsXUWemkxObvGS og6fO2r0GgLVov4IaOFDgaCyXuqZw0SEjghq3LsecTNFFk27+bxAKpoGFBfUKDKjMDqsYaIPTrZY tOyszlHd20L5LE3HJillxzqKbFSS2tGEFJIUMJPD6LC3sRVo0ykKT3FbNStuhL5WtAWYKrOn3M0h rJ0LXMV44JK4J4ApIJ3ziiOjP/2QTaaQXlF6a7TohpBSN/ebriliH2XcrXyazdKikiblB0wvBqFs lje0lmlUutVXS67qDFG+8BLNyW0VCdu+WhoUSmj2rGjCO9ppHzy4UhL004VLpjRlNA2wx6I4sMDQ uSCkcZYAo1AogxIsu7GD4PH512wz7VpfDnA7WZUg9hUFDF1JLb9UNsH7+zyYoYPIW5FzIqePxAEj FITq/j5o04cESG+zm9+mVaOoz4hoYxYcwCyWzQKavrYT2sxwlY5ihCh1a3ttB6u5HMLbH4GnAqpf 14au1GnXqxyQ1OzltymbDcHap0iIZ1yIEDZmQgfK+sw+dLlz08ukg+Mi/BTI+o8T/xiShCSQlFqM hp54EaEFVVChwX0qv9/hv2G1i3b2Hfp3MpvyllnBrnORVtF2Gh3aFMXFeM0yNZaeUQdUMdply71D G5oqlNa1TFc2FhgWEQRhFQOO8t1srbhjRxBhQyk7zKYRDLpMpDgB0JKBsPANcqQ3KAlDFDz9zYu3 aAy0CPPJYLkhsh+Jo6D2RmHMkWOL9v1L+KuoB7PxvVki9pIPV2qe9cctQNCbBbCjQWtTiGwIhgQi kIATpzGKWzscZ5pEgFLUNKiHgHNxNV4KkZIudDJPV3WKgY1UzChwqgCohMyalT0mI3sY9Rs29gLE lFT37jNqRDsJV3SFQKsBXW1PNd/cdkgwEihxiMAOI1DZKFF8tYE12jRRLNrzN3jTUcBsHtv2Gjg6 Iw+FFK4FoZFIPkEQrDEeBvkQosMmwaKKtkgios7dMu0ry6KYVtOzRQvapfK+URvag5aQUPSljoZ6 xWgNgDREQKIrUEqANRCQHInBUFK20usEiMceuqR8yEwAvsNQWrFr38cIgg9cEB9EtBgixZq9iJHR oLgDRY4k0pCKKwkuOZdtCxNj57VYr0NGbOGZw0oViAwSsikgFhzP5tWMkEwW7Sfcw6+p4HlPEFlg tBM2DevFJHNr+nPeCokiNsvoD840kQA/ckufTze+a4z32BYvZgGQakEKR5HuZVHYX5yCFagPwtDv 9JIQFBPVzuktU2rMplEQ2n/Jb0g7YXoJ3c4hP1zVcRH8/lqtt1sh1UeHgEhXCrQdZhAzyfsGpAzR V+55YSx0WQ5m0hTOx1Xf5+Vr9x26KMWGFFEApKwlI7DaCMtwbktsv4g0TCYTZ7n+JAj/8XckU4UJ BDYfM4A --nextPart21182021.2xS7EKBmbB-- This is a multi-part message in MIME format. Hi I'm working on a web credit card payment solution, so in a PCI-DSS environment, and the auditors gave me trouble with one specific audit point for the PostgreSQL database. They require the list of users in the database that had no activity in the past 90 days to be deleted. So far, it seems the only solution to implement that in PostgreSQL would be to parse the log, hoping not to lose any line. That seems too risky for me, so I wrote my own solution for this issue, and I would like to submit it here for review/suggestion and to help other users facing the same needs. Since it's very small, I've taken the liberty of attaching it to this email. The code is more or less inspired by pg_stat_statements. So far I've not implemented saving upon restarts of the database, I'll probably do it in the next days/weeks. It has been tested against PostgreSQL 9.2 only, but I'll test 9.3 and 9.4 soon. Thanks Pierre ["pg_audit_users-0.1.tar.bz2" (pg_audit_users-0.1.tar.bz2)] BZh91AY&SYCa8 N `xQ۔w]Eټ+tQlav Д \ Oe=O*4SaPzz QF4=@ @&4OA 4O(4ɑ@ i BTSi4 @ I!2 E3Fb4z4 4h hh4 ihd 4 d d@"A2hFA0TMMS z z'bzUa \ <<I&}SH`1(I%Eyz EdصƔMC'sZ"!v%т(2 (AX \ "P_PYyd۪BRlƞ<