[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
E3Fb4z44hhh4ihd
4dd@"A2hFA0TMMS z z'bzUa \
<<I&}SH`1(I%Eyz EdصƔMC'sZ"!v%т(2 (AX \
"P_PYyd۪BRlƞ<o<mkZ~i^-TKjpYBE"R`ZǞrҬ[kT.ʔH[$O[߿w$][iGi \
5gha-`չ[90<ע=^^ ݦ%L	Q4MmPh)tlKnF^65c \
_HcR9T+DEY;gfSxnJ o;܍fR0ӫ*gV2<-wTGvi \
VG|e>E~~fY ʼkM|\8dsnf)̘Lu \
n*!Fo3VX|WGs+|昞.&(3(׿YIV+O9s̅C<
 mZ4z-s⽰{, I Hibfٺ纭c1el[ʡLJI8+(Hxs*Yl}6s \
Lm$3k r>=sVi\Fb'F岺C5kLk&Q-:f=wNXg u.X:	; \
=9{^m=ca]ێ4*"	DڗE \
OIbMmYuGGVi?.$XLHռ֙1af	Q=t3qea0 `ūM9:^o \
> ]$qZM''NtbUw+eZ[wOͥw۰7N \
l۳(F1`a.I։|iN  \
JȈohmV$BE1~^F><IY xPƩcoz'Q@ \
BI+Sgg!$f"9d픆Ph)g, \
:s/8@0_gnAb}cv^lج!R)%O1嘺";I`qTQL6┃GV:tC;I$5]t
 Jo<v@(Gf1<le&xoi_2Ql߶6c9RrƐJ_,@_ \
LVYi(V/A loL\$JbKA8wCx,Hw* 9H+02MhD~ˋ$L>n \
88bE,u1VF2ھ\!UËYM:g!r9c>۴X8x&v؎n \
HY,pI#<9N]VG+9; .bع3K@2kzEG \
	~(P)NHr$iuhL\*< $
	nv*FHp)O!8UU#MĬ8+WGӖ$;W{w{"tzrK2ȴV-=5܋r܋Uɱ!fsx^YtlOY \
h/G@|TlEXp8GlyBZ&f/p_(%\	{Z(Bmb1)Њs)F["
 I$w,nFXŊwA-5>oO3'5-c \
Y	#g6-QOgŕ>m`ƟUxkzdyJ \
fgE%eZs(aZ;D'cfbMԬW;Y]惡$F!?͍8NVBcʒޏQ\_ \
CN"Lh%=DQ3NqKS`J \
G0wv7Hj1Rɢ'P҅598S9LnLT \
VtlXC9XbR5Nfi]%g \
KD2cv''TWaj6|Pz:b  \
R	VVHZX9}ow@`kw9,dFYO.{e8@rZO;/lIZ}d \
-bggvU%:u0еڭu(i&!,m"qK5 \
K~ӓȒf/r~ĵJie_-Vu \
Kw?'"LYrԒؒl`K1dP`HabG})䩞xfV!g+/xj3$n0 \
ӌd!JD &4NJDA^g6%oW,)P*4'*T"_Y/ק]dyt \
Ҙgoxe:@QgR>+.=t;1U=Jh][ nl.j~g.K5+Aǰmn
]z ]ELNn;jբhC \
^Djܻq3EM@*(20:aNXQB,M&)e:lTф0谷h) \
Oq[5+nV*!\x') \
#?M^Qzk膐R7)beܭ|Ң&L/l7iTWKQm	۾ZJhh \
;i<RӅK4e4 Ǣ8й qP(,l3Z_p;Y \
]I-T6<[s"#>hӇHoߦUψhc,pb(uk{m \
_׆i׫) \
ڧHg\6fB>tsˤ"$ZxUP}*avw2YsVvx25QT1e˽C*ֵLW6alcGaC);̦L8В
 r7(	C<͋h`! \
FȃcKY"Wj-@ЛAkSl"1[;gDR4sq5^ \
.t2OWuT(p̚=&#{6ĔT3jD;	Wt@]mO5vH0(q#P(Q|54Q,7xQl8:#H>A1&H"2+ˢVӳEڥQڃPzh
 4D@+PJ5PR|LPZk\D"Ś \
E$Ґ+	.9mcXCFlJ) ՌL'ïyOY`6 $sks
"64?rKO7k/fjA
GeQ_ -Sj̦QoH;az	!?\qZ[!GHW
f3Wya,tYfUk(ņQ%#pnKl4L&g$w$S	63


["signature.asc" (application/pgp-signature)]

[prev in list] [next in list] [prev in thread] [next in thread] 

Configure | About | News | Add a list | Sponsored by KoreLogic