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

Neil,

* Neil Tiffin (neilt@neiltiffin.com) wrote:
> I meant it to go to the list, but hit the wrong button.

No problem.

> > On Feb 17, 2015, at 7:01 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > 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.
> >
> > 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 set up correctly, postgresql.conf is in /etc. :)  That's distribution
specific though.  However, postgresql.auto.conf ends up causing problems
since it can't be disabled and it's forced to live in the PG data
directory.  Even so though, your argument was that, as long as the
system is set up to be auditing initially and whatever action the
superuser takes to disable auditing will be audited, and disabling
auditing is against policy, then it's sufficient to meet the
requirement.  That's true in either case.

> > 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? 

When I'm talking about 'superuser' here, it's the PG superuser, not the
OS superuser.  What I mean by independent system is a process which is
not owned by the same user that the database is running as, and isn't
owned by the user who is connecting, that facilitates the connection
from the user to PG, but which logs everything that happens on that
connection.

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

The DB superuser can modify the running process, through mechanisms as
simple as changing GUCs to creating functions in untrusted languages
(including C) and then using them to change or break more-or-less
anything that's happening in the backend.

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

It can log everything which happens on the connection between the user
and the database because it's in the middle.

> 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. 

It'd be a great tool, certainly, but it's not actually possible to do
completely inside the DB process given the capabilities the PG superuser
has.  Being able to create and run functions in untrusted languages
means that the superuser can completely hijack the process, that's why
those languages are untrusted.
Thanks,
    Stephen