Re: pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

Stephen,

I meant it to go to the list, but hit the wrong button.

> On Feb 17, 2015, at 7:01 PM, Stephen Frost <sfrost@snowman.net> wrote:
>
> Neil,
>
> I noticed that you email'd me directly on this reply.  Not sure if you
> intended to or not, but I'm fine with my response going to the list.
>
> * Neil Tiffin (neilt@neiltiffin.com) wrote:
>>> On Feb 17, 2015, at 1:10 PM, Stephen Frost <sfrost@snowman.net> wrote:
>>> If the DB account isn't a superuser then everything changes.  There's no
>>> point fighting with the OS user- they can run some other PG binary which
>>> they've copied over locally and run SQL with that, or copy all the files
>>> over to another server which they have complete access to.  The fact
>>> that they can also connect to the DB and run SQL isn’t really an issue.
>>
>> Thats the point. If this environment matters then the db superuser would not be an authorized os superuser (with
rootor even close privileges). And no, they could not be running some other binary. 
>>
>> One way to do pg superuser auditing is to utilize a file (outside of the pg data directory, which probably violates
somethingin pg) like /etc/pg_su_audit that has os root rw and r for all others (or the equivalent for other os’s)
containinga URL.  If this file is present, send all db superuser usage to the URL.  If this file is present with the
wrongprivileges, then don’t start pg. Done.  No configuration in pg config files, no GUCs, no nothing for the pg
superuserto mess around with, not tables, no ability for any pg superuser to configure or control.   
>
> This approach doesn't violate anything in PG and can be used with any of
> the pgaudit approaches being discussed.  The fact that it's
> postgresql.conf, which represents GUCs, doesn't change anything
> regarding what you’re getting it.
>

It changes everything, pg superusers have complete control of files in the pg data directory.  If set up correctly pg
superusershave no control in /etc.  

>> If they can copy or install a PG binary, then the OS auditing and security failed. PG code need not be concerned.
>>
>> Sure someone could still break access to the URL, but again, not PG’s concern.  Other security and auditing would
haveresponsibility to pick that up. 
>>
>> It is a really simple use case, record everything any db superuser does and send it to the audit system.  Done.
Thena designated role can control what gets audited in production.  As long as everything the db superuser does can be
writtento an audit log, then it no longer matters technically if the db superuser can change the rest of the auditing.
Ifthey do and it violates policy, then when the audit picks it up, they lose their job plus depending on the
environment. 
>
> The issue is really around what we claim to provide with this auditing.
> We can't claim to provide *full* superuser auditing with any of these
> approaches since the superuser can disable auditing.  We can, however,
> provide auditing up until that happens, which is likely to be sufficient
> in many environments.  For those environments where full superuser
> auditing is required, an independent system must be used.
>
> Of course, it's important that any auditing mechanism which is used to
> audit superusers be impossible for the superuser to modify after the
> fact, meaning that syslog or similar needs to be used.
>

I’m still confused since you do do not differentiate between db superuser and os superuser and what you mean by an
independentsystem? 

With the scheme I described above, how does the db superuser disable auditing without os root privileges?  If they can,
thenpg security is fundamentally broken, and I don’t believe it is. 

How can an independent system monitor what commands are issued inside the database?

I understand my comments do not cover what is being proposed or worked on and that is fine.  But saying it does not
havevalue because the superuser could disable any system in pg, is wrong IMO.  Being able to reliability log db
superuserswithout their ability to change the logging would be a fundamentally good security tool as companies become
moreserious about internal security.  This is, and will be more, important since a lot of people consider insider
breachesthe biggest security challenge today. 

Neil