pgaudit - an auditing extension for PostgreSQL

On 05/01/2014 11:19 PM, Ian Barwick wrote:

Here is an initial version of an auditing extension for Postgres to
generate log output suitable for compiling a comprehensive audit trail
of database operations.

Cool! Looking forward to seeing it around the 9.5 cycle.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Ian,

* Ian Barwick (ian@2ndquadrant.com) wrote:

Here is an initial version of an auditing extension for Postgres to
generate log output suitable for compiling a comprehensive audit trail
of database operations.

Neat stuff.

Why auditing?

Yeah, we really need to improve here. I've been hoping to make progress
on this and it looks like I'll finally have some time to.

pgaudit uses Event Triggers to log unambiguous representation of DDL,
as well as a combination of executor and utility hooks for other
commands (DML, including SELECT, as well as other utility commands):

While certainly a good approach to minimize the changes needed to the
backend, I'd really like to see us be able to, say, log to a table and
have more fine-grained control over what is logged, without needing an
extension.

1. pgaudit logs fully-qualified relation names, so you don't have to
wonder if "SELECT * FROM x" referred to "public.x" or "other.x".

Yeah, that's definitely an issue for any kind of real auditing.

2. pgaudit creates a log entry for each affected object, so you don't
have to wonder which tables "SELECT * FROM someview" accessed, and
it's easy to identify all accesses to a particular table.

Interesting- I'm a bit on the fence about this one. Perhaps you can
elaborate on the use-case for this?

3. pgaudit allows finer-grained control over what is logged. Commands
are classified into read, write, etc. and logging for these classes
can be individually enabled and disabled (either via pgaudit.log in
postgresql.conf, or as a per-database or per-user setting).

This is something I've been mulling over for a couple of years (you can
see notes from the discussion at the 2011 hacker meeting on the wiki
about how we might change our logging system to allow for better
filtering).

Planned future improvements include:

1. Additional logging facilities, including to a separate audit
log file and to syslog, and potentially logging to a table
(possibly via a bgworker process). Currently output is simply
emitted to the server log via ereport().

Using the existing logging collector will almost certainly be a
contention point- we've seen that before. I've had thoughts about
an option to log to individual files from each backend (perhaps based
on that backend's position in the proc table) or directly from each
backend to a remote service (eg: rabbitMQ/AMQP or something).

Regarding background worker processes, a thought that's been kicked
around a bit is to actually change our existing logging collector to
be a background worker (or perhaps be able to have multiple?) which
is fed from a DSM queue and then logs to a file (or maybe files), or
a table or something else.

2. To implement per-object auditing configuration, it would be nice to use
extensible reloptions (or an equivalent mechanism)

Yeah, that's another interesting challenge. This kind of auditing is
often about specific information (and therefore specific objects) and
it'd be ideal to have that set up and managed alongside the table
definition. Having the auditing done in core instead of through an
extension would make this easier to address though.

Thanks,

Stephen

On 05/02/2014 11:04 AM, Stephen Frost wrote:

This is something I've been mulling over for a couple of years (you can
see notes from the discussion at the 2011 hacker meeting on the wiki
about how we might change our logging system to allow for better
filtering).

Logging hooks. We really need some contrib/ modules which take
advantage of these.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Josh Berkus (josh@agliodbs.com) wrote:

Logging hooks. We really need some contrib/ modules which take
advantage of these.

I'm aware and I really am not convinced that pushing all of this to
contrib modules using the hooks is the right approach- for one thing, it
certainly doesn't seem to me that we've actually gotten a lot of
traction from people to actually make use of them and keep them updated.
We've had many of those hooks for quite a while.

What 2Q has done here is great, but they also point out problems with
building this as a contrib module using the hooks. As we add more
capabilities and improve the overall PG system (new objects, etc), I'm
rather unconvinced that having to go, independently, update the contrib
modules to understand each new object is going to be a terribly workable
long-term solution.

Additionally, using triggers (either on the tables or the event
triggers), while good for many use-cases, doesn't completely answer the
auditing requirements (SELECT being the great example, but there are
others) and having to combine event triggers with various hooks just
doesn't strike me as a great design. (I don't intend to knock what 2Q
has done here at all- they're using a minimal-backend-hacking approach,
and under that constraint they've done exactly what makes sense).

Thanks,

Stephen

At 2014-05-02 14:04:27 -0400, sfrost@snowman.net wrote:

I'd really like to see us be able to, say, log to a table and have
more fine-grained control over what is logged, without needing an
extension.

There were several factors we considered in our work:

1. We did the minimum possible to produce something that gives us
demonstrably more than «log_statement=all» in 9.3/9.4/9.5.

2. We wanted to produce something that could be used *now*, i.e. with
9.3 and soon 9.4, to get wider feedback based on actual usage. I'm
hoping that by the time we make a submission for 9.5, we'll have a
clearer picture of what Postgres auditing should look like.

3. We steered clear of implementing different log targets. We know that
ereport() doesn't cut it, but decided that doing anything else would
be better after some feedback and wider discussion. Any suggestions
in this regard are very welcome.

(Stephen, I can see from your mail that you've already inferred at least
some of the above, so it's more a general statement of our approach than
a response to what you said.)

2. pgaudit creates a log entry for each affected object […]

Interesting- I'm a bit on the fence about this one. Perhaps you can
elaborate on the use-case for this?

Who accessed public.x last month?

Answering that question would become much more difficult if one had to
account for every view that might refer to public.x. And did the view
refer to public.x before the schema change on the first Wednesday of
last month?

We don't have a "deparsed" representation of DML, so "select * from x"
is logged differently from "select * from other.x". Same with potential
complications like how exactly a join is written.

The way pgaudit does it, you can just "grep public.x" in your audit log
and be sure (modulo bugs, of course) you're seeing everything relevant.

This kind of auditing is often about specific information (and
therefore specific objects) and it'd be ideal to have that set
up and managed alongside the table definition.

Yes, exactly.

-- Abhijit

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

At 2014-05-02 14:22:23 -0400, sfrost@snowman.net wrote:

I'm aware and I really am not convinced that pushing all of this to
contrib modules using the hooks is the right approach- for one thing,
it certainly doesn't seem to me that we've actually gotten a lot of
traction from people to actually make use of them and keep them
updated.

For what it's worth, I greatly appreciate *having* the hooks. Without
them, it would have been much more difficult to prototype pgaudit, and
it would have been impossible to do so in a way that could be used with
9.3/9.4.

As for whether auditing as a feature *should* be an extension, I do not
have a strong opinion yet. If a consensus formed around a better design
in-core, I certainly wouldn't object.

I'm rather unconvinced that having to go, independently, update the
contrib modules to understand each new object is going to be a
terribly workable long-term solution.

(I am not expressing any opinion at this time on this larger question.)

having to combine event triggers with various hooks just doesn't
strike me as a great design.

Suggestions are welcome, but I have to say that I'm not a big fan of
reinventing what event trigger give us in the way of deparsing either.

-- Abhijit

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Abhijit,

* Abhijit Menon-Sen (ams@2ndquadrant.com) wrote:

3. We steered clear of implementing different log targets. We know that
ereport() doesn't cut it, but decided that doing anything else would
be better after some feedback and wider discussion. Any suggestions
in this regard are very welcome.

I'm not anxious to try and replace ereport() either, but I don't see
that as necessary to have multiple log targets (we already have that,
after all..). The design that I had discussed w/ Magnus and at the
hacker's meeting in 2011 was around the notion of 'tags' and a
structured interface to the logging collector. That fits in nicely with
the idea of using a DSM queue, I'd think.

Who accessed public.x last month?

Answering that question would become much more difficult if one had to
account for every view that might refer to public.x. And did the view
refer to public.x before the schema change on the first Wednesday of
last month?

This also addresses things like anonymous DO blocks and functions
then..? With enough information to be useful for forensics?

We don't have a "deparsed" representation of DML, so "select * from x"
is logged differently from "select * from other.x". Same with potential
complications like how exactly a join is written.

This seems like an independently useful thing (would be nice to have in
our logs and in pg_stat_statements, imv..).

This kind of auditing is often about specific information (and
therefore specific objects) and it'd be ideal to have that set
up and managed alongside the table definition.

Yes, exactly.

We'd need to also consider permissions and how these are managed.
Presumably, the 'owner' of a relation would be able to define and modify
its audit parameters, but it would be useful to have that capability
independently grant'able and also be sure that any changes made to the
auditing are clearly logged.

This gets into a much larger area of discussion around what can be
granted and what must be owner-only or superuser-only.

Thanks,

Stephen

* Abhijit Menon-Sen (ams@2ndquadrant.com) wrote:

At 2014-05-02 14:22:23 -0400, sfrost@snowman.net wrote:

I'm aware and I really am not convinced that pushing all of this to
contrib modules using the hooks is the right approach- for one thing,
it certainly doesn't seem to me that we've actually gotten a lot of
traction from people to actually make use of them and keep them
updated.

For what it's worth, I greatly appreciate *having* the hooks. Without
them, it would have been much more difficult to prototype pgaudit, and
it would have been impossible to do so in a way that could be used with
9.3/9.4.

I'm perfectly fine w/ having the hooks and they're great for exactly the
reasons you point out- it's at least *possible* to add some of this
without having to custom compile the backend. That doesn't mean it's
what we should hang our hat on as the 'one true solution'.

having to combine event triggers with various hooks just doesn't
strike me as a great design.

Suggestions are welcome, but I have to say that I'm not a big fan of
reinventing what event trigger give us in the way of deparsing either.

No, I wouldn't want us to reinvent or duplicate code either.

Thanks,

Stephen

At 2014-05-04 08:52:42 -0400, sfrost@snowman.net wrote:

This also addresses things like anonymous DO blocks and functions
then..? With enough information to be useful for forensics?

For DML, it addresses anything that goes through InitPlan (which, via
ExecCheckRTPerms, calls the ExecutorCheckPerms_hook). Yes, that does
include table accesses within DO blocks:

LOG: [AUDIT],2014-05-04 19:15:06.130771+05:30,ams,ams,ams,WRITE,DELETE,TABLE,public.a,do $$begin delete from a; end;$$;

…and functions:

LOG: [AUDIT],2014-05-04 19:16:20.336499+05:30,ams,ams,ams,DEFINITION,CREATE FUNCTION,,,create function x(int) returns text as $$select b from a where a=$1;$$ language sql;
LOG: [AUDIT],2014-05-04 19:16:48.112725+05:30,ams,ams,ams,FUNCTION,EXECUTE,FUNCTION,public.x,select * from x(3);
LOG: [AUDIT],2014-05-04 19:16:48.112922+05:30,ams,ams,ams,READ,SELECT,TABLE,public.a,select * from x(3);

We'd need to also consider permissions and how these are managed.

Yes. For pgaudit, we kept it simple and made pgaudit.log
superuser-only[1].

I haven't given much thought to this area, because I didn't know what
mechanism to use to set per-object auditing parameters. For a contrib
module, extensible reloptions would have been convenient. But in-core
auditing support could use a proper reloption, I suppose. It's a pity
that extensions can't add reloptions.

Personally, I don't think it's important to support GRANT-ing the
ability to set audit parameters. I think it would be reasonable even to
say that only the superuser could do it (but I can imagine people being
unhappy if the owner couldn't[2]).

Definitely lots to discuss.

-- Abhijit

1. I wish it were possible to prevent even the superuser from disabling
audit logging once it's enabled, so that if someone gained superuser
access without authorisation, their actions would still be logged.
But I don't think there's any way to do this.

2. On the other hand, I can also imagine a superuser being justifiably
annoyed if she were to carefully configure auditing, and random users
then enabled audit-everything for their newly-created tables and
filled the audit table with junk.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Abhijit Menon-Sen (ams@2ndquadrant.com) wrote:

At 2014-05-04 08:52:42 -0400, sfrost@snowman.net wrote:

This also addresses things like anonymous DO blocks and functions
then..? With enough information to be useful for forensics?

For DML, it addresses anything that goes through InitPlan (which, via
ExecCheckRTPerms, calls the ExecutorCheckPerms_hook). Yes, that does
include table accesses within DO blocks:

Right, I figured, but wanted to clarify the usefullness of this goes
beyond just views.

We'd need to also consider permissions and how these are managed.

Yes. For pgaudit, we kept it simple and made pgaudit.log
superuser-only[1].

Yeah, for an initial version that makes sense, but I'm sure we'll need
more.

I haven't given much thought to this area, because I didn't know what
mechanism to use to set per-object auditing parameters. For a contrib
module, extensible reloptions would have been convenient. But in-core
auditing support could use a proper reloption, I suppose. It's a pity
that extensions can't add reloptions.

Another reloption is one option, or an extension on the ACL system (for
that piece of it), or we could make a new catalog for it (ala
pg_seclabel), or perhaps add it on to one (pg_seclabel but rename it to
pg_security..?).

Personally, I don't think it's important to support GRANT-ing the
ability to set audit parameters. I think it would be reasonable even to
say that only the superuser could do it (but I can imagine people being
unhappy if the owner couldn't[2]).

I disagree. Perhaps it could be a role-level permission instead of one
which is per-table, but I don't think this should be superuser-only.

1. I wish it were possible to prevent even the superuser from disabling
audit logging once it's enabled, so that if someone gained superuser
access without authorisation, their actions would still be logged.
But I don't think there's any way to do this.

Their actions should be logged up until they disable auditing and
hopefully those logs would be sent somewhere that they're unable to
destroy (eg: syslog). Of course, we make that difficult by not
supporting log targets based on criteria (logging EVERYTHING to syslog
would suck).

I don't see a way to fix this, except to minimize the amount of things
requiring superuser to reduce the chances of it being compromised, which
is something I've been hoping to see happen for a long time.

2. On the other hand, I can also imagine a superuser being justifiably
annoyed if she were to carefully configure auditing, and random users
then enabled audit-everything for their newly-created tables and
filled the audit table with junk.

While I understand your concern, I'm not sure it's really what we should
be designing for. One of the oft-commented on distinctions is to have
roles which are specifically auditors- and they have very limited access
beyond that. To that point, a role-level attribute for 'can modify
auditing' seems key, but we wouldn't want such a role to also be an
owner of every relation they need to be able to modify auditing for, yet
it would be valuable to constrain the auditor to only being able to
modify auditing on certain sets of tables.

That seems to boil down to a GRANT'able option, since that gives the
per-table granularity. That would also make the role-level attribute
unnecessary, which is appealing. The downside of this is that the owner
ends up being untimately in control here- but then, the owner could
presumably drop and recreate the object anyway, and where would we be
then? Perhaps having it be up to the owner isn't such a bad approach.

The implementation-level concern here comes from the way we actually
store and manage those permissions. We're awful short of bits when we
start thinking about all the things we might want to make independently
grant'able.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Abhijit Menon-Sen (ams@2ndquadrant.com) wrote:

1. I wish it were possible to prevent even the superuser from disabling
audit logging once it's enabled, so that if someone gained superuser
access without authorisation, their actions would still be logged.
But I don't think there's any way to do this.

Their actions should be logged up until they disable auditing and
hopefully those logs would be sent somewhere that they're unable to
destroy (eg: syslog). Of course, we make that difficult by not
supporting log targets based on criteria (logging EVERYTHING to syslog
would suck).

I don't see a way to fix this, except to minimize the amount of things
requiring superuser to reduce the chances of it being compromised, which
is something I've been hoping to see happen for a long time.

Prohibiting actions to the superuser is a fundamentally flawed concept.
If you do that, you just end up having to invent a new "more super"
kind of superuser who *can* do whatever it is that needs to be done.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

At 2014-05-04 11:03:56 -0400, sfrost@snowman.net wrote:

Another reloption is one option, or an extension on the ACL system
(for that piece of it), or we could make a new catalog for it (ala
pg_seclabel), or perhaps add it on to one (pg_seclabel but rename
it to pg_security..?).

I'll look into those possibilities, thanks.

Perhaps it could be a role-level permission instead of one which is
per-table, but I don't think this should be superuser-only.

I like the idea of a role-level permission, or a (db,role)-level
permission (i.e. "role x is auditor for database y"), but I don't
feel I know enough about real-world auditing requirements to make
an informed decision here.

Ian did some research into how auditing is handled in other systems.
He's on vacation right now, and I'm not sure how much detail his report
has on this particular subject, but I'll have a look and try to present
a summary soon.

-- Abhijit

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On May 4, 2014, at 10:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Abhijit Menon-Sen (ams@2ndquadrant.com) wrote:

1. I wish it were possible to prevent even the superuser from disabling
audit logging once it's enabled, so that if someone gained superuser
access without authorisation, their actions would still be logged.
But I don't think there's any way to do this.

Their actions should be logged up until they disable auditing and
hopefully those logs would be sent somewhere that they're unable to
destroy (eg: syslog). Of course, we make that difficult by not
supporting log targets based on criteria (logging EVERYTHING to syslog
would suck).

I don't see a way to fix this, except to minimize the amount of things
requiring superuser to reduce the chances of it being compromised, which
is something I've been hoping to see happen for a long time.

Prohibiting actions to the superuser is a fundamentally flawed concept.
If you do that, you just end up having to invent a new "more super"
kind of superuser who *can* do whatever it is that needs to be done.

In getting approval for FDA validated systems, IIRC, they wanted to see the audit change permissions completely independent of the technical roles and responsibilities. Meaning that superuser or owner roles could not change the audit requirements once established and the audit role could not change any data or data definitions except add, change or remove auditing rules. Everything the auditor role did was logged, no exceptions.

If an owner or superuser dropped a table the auditors were completely fine with a log entry that the table/column was dropped or created by someone. The audit reporting system (external to the database) had notifications for these types of events. For example, by procedure these changes should have been done in conjunction with the auditors and the initial audit requirements should already have been improved by the auditors when the column/table was added back. Dropping a table/column without getting approval ahead of time was a procedure violation that could result in termination. Of course, there were a lot more details.

By monitoring creation/delete DDL events along with non changeable (by technical staff) audit rules the auditors were happy that they could manage the audit conformance.

And yes, the audit logs had to be written in a way they could not be easily tampered with. At the time we used an approved append only, read only hardware file/reporting system.

In considering how this might apply to PostgreSQL, it seems that once formal auditing is turned on, basic non-changeable level of audit reporting should be in place (i.e. log all create/drop/rename tables/columns/roles and log all superuser/audit role actions) and this basic audit reporting cannot be turned off or have the destination changed without considerable headache (something like init'ing the database?). Then data monitoring auditing rules can be added/changed/removed as necessary within the authorization framework. Formal auditing might also require other functionality like checksums.

Until these or similar requirements (for formal auditing) are in core, it makes no sense (to me) to not allow the superuser to manage auditing because any conformance requirements have to be procedure based, not system based. People often forget that procedure/people based audit conformance worked just fine before computers existed.

Neil

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Neil,

Thanks for sharing- sounds very similar to what I've heard also. Your
input and experience with this is very much sought and appreciated-
please continue to help us understand, so we're able to provide
something concrete and useful. Further comments inline.

* Neil Tiffin (neilt@neiltiffin.com) wrote:

In considering how this might apply to PostgreSQL, it seems that once formal auditing is turned on, basic non-changeable level of audit reporting should be in place (i.e. log all create/drop/rename tables/columns/roles and log all superuser/audit role actions) and this basic audit reporting cannot be turned off or have the destination changed without considerable headache (something like init'ing the database?). Then data monitoring auditing rules can be added/changed/removed as necessary within the authorization framework. Formal auditing might also require other functionality like checksums.

Any system where there exists a role similar to 'superuser' in the PG
sense (a user who is equivilant to the Unix UID under which the rest of
the system is run) would be hard-pressed to provide a solution to this
issue. With SELinux it may be possible and I'd love to see an example
from someone who feels they've accomplished it. That said, if we can
reduce the need for a 'superuser' role sufficiently by having the
auditing able to be managed independently, then we may have reached the
level of "considerable headache".

As many have pointed out previously, there is a certain amount of risk
associated with running without *any* superuser role in the system
(though it's certainly possible to do so), as it becomes much more
difficult to do certain kinds of analysis and forensics associated with
trying to recover a corrupt system. Still, that risk may very well be
acceptable in some environments. I'd certainly like to see us get to a
point where a superuser role isn't absolutely required once the system
is up and running.

Until these or similar requirements (for formal auditing) are in core, it makes no sense (to me) to not allow the superuser to manage auditing because any conformance requirements have to be procedure based, not system based. People often forget that procedure/people based audit conformance worked just fine before computers existed.

I do understand this and I expect we will always allow the roles which
are 'superuser' to modify these procedures, but we'll get to a point
where such a role doesn't have to exist (or it's a considerable headache
to get one into place) and that'll get us to the point which is required
to check the "formal auditing" box for the organizations which are
interested and willing to accept those trade-offs.

Thanks,

Stephen

On May 4, 2014, at 3:17 PM, Stephen Frost <sfrost@snowman.net> wrote:

Neil,

Thanks for sharing- sounds very similar to what I've heard also. Your
input and experience with this is very much sought and appreciated-
please continue to help us understand, so we're able to provide
something concrete and useful. Further comments inline.

* Neil Tiffin (neilt@neiltiffin.com) wrote:

In considering how this might apply to PostgreSQL, it seems that once formal auditing is turned on, basic non-changeable level of audit reporting should be in place (i.e. log all create/drop/rename tables/columns/roles and log all superuser/audit role actions) and this basic audit reporting cannot be turned off or have the destination changed without considerable headache (something like init'ing the database?). Then data monitoring auditing rules can be added/changed/removed as necessary within the authorization framework. Formal auditing might also require other functionality like checksums.

Any system where there exists a role similar to 'superuser' in the PG
sense (a user who is equivilant to the Unix UID under which the rest of
the system is run) would be hard-pressed to provide a solution to this
issue.

Not sure I understand which issue you are referring to. If you are referring to 'cannot be turned off', I would think a reasonable first pass would be to handle it similar to '--data-checksums' in 'initdb'. For example, "This option can only be set during initialization, and cannot be changed later. If set, basic auditing is on for all objects, in all databases."

With SELinux it may be possible and I'd love to see an example
from someone who feels they've accomplished it. That said, if we can
reduce the need for a 'superuser' role sufficiently by having the
auditing able to be managed independently, then we may have reached the
level of "considerable headache".

As many have pointed out previously, there is a certain amount of risk
associated with running without *any* superuser role in the system

If all of the superuser's actions are logged and it's not possible to turn off the logging (without considerable headache) then it may not matter what the superuser can do. If the superuser makes changes and they are logged then the auditors have sufficient information to see if the correct procedures were followed. Validated systems are based on tracking, not necessarily prohibiting. Select individuals that should be able to be trusted (which should apply to superusers) should be able to perform the actions necessary to support the organization.

(though it's certainly possible to do so), as it becomes much more
difficult to do certain kinds of analysis and forensics associated with
trying to recover a corrupt system. Still, that risk may very well be
acceptable in some environments. I'd certainly like to see us get to a
point where a superuser role isn't absolutely required once the system
is up and running.

Until these or similar requirements (for formal auditing) are in core, it makes no sense (to me) to not allow the superuser to manage auditing because any conformance requirements have to be procedure based, not system based. People often forget that procedure/people based audit conformance worked just fine before computers existed.

I do understand this and I expect we will always allow the roles which
are 'superuser' to modify these procedures, but we'll get to a point
where such a role doesn't have to exist (or it's a considerable headache
to get one into place) and that'll get us to the point which is required
to check the "formal auditing" box for the organizations which are
interested and willing to accept those trade-offs.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Neil Tiffin (neilt@neiltiffin.com) wrote:

On May 4, 2014, at 3:17 PM, Stephen Frost <sfrost@snowman.net> wrote:

Any system where there exists a role similar to 'superuser' in the PG
sense (a user who is equivilant to the Unix UID under which the rest of
the system is run) would be hard-pressed to provide a solution to this
issue.

Not sure I understand which issue you are referring to. If you are referring to 'cannot be turned off', I would think a reasonable first pass would be to handle it similar to '--data-checksums' in 'initdb'. For example, "This option can only be set during initialization, and cannot be changed later. If set, basic auditing is on for all objects, in all databases."

Well, except that a superuser *could* effectively turn off checksums by
changing the the control file and doing a restart (perhaps modulo some
other hacking; I've not tried). That kind of trivial 'hole' isn't
acceptable from a security standpoint though and given that we couldn't
prevent a superuser from doing an LD_PRELOAD and overriding any system
call we make from the backend, it's kind of hard to see how we could
plug such a hole.

With SELinux it may be possible and I'd love to see an example
from someone who feels they've accomplished it. That said, if we can
reduce the need for a 'superuser' role sufficiently by having the
auditing able to be managed independently, then we may have reached the
level of "considerable headache".

As many have pointed out previously, there is a certain amount of risk
associated with running without *any* superuser role in the system

If all of the superuser's actions are logged and it's not possible to turn off the logging (without considerable headache) then it may not matter what the superuser can do. If the superuser makes changes and they are logged then the auditors have sufficient information to see if the correct procedures were followed. Validated systems are based on tracking, not necessarily prohibiting. Select individuals that should be able to be trusted (which should apply to superusers) should be able to perform the actions necessary to support the organization.

Fair enough- the question is just a matter of what exactly that level of
"headache" is.

Thanks!

Stephen

On May 4, 2014, at 5:27 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Neil Tiffin (neilt@neiltiffin.com) wrote:

On May 4, 2014, at 3:17 PM, Stephen Frost <sfrost@snowman.net> wrote:

Any system where there exists a role similar to 'superuser' in the PG
sense (a user who is equivilant to the Unix UID under which the rest of
the system is run) would be hard-pressed to provide a solution to this
issue.

Not sure I understand which issue you are referring to. If you are referring to 'cannot be turned off', I would think a reasonable first pass would be to handle it similar to '--data-checksums' in 'initdb'. For example, "This option can only be set during initialization, and cannot be changed later. If set, basic auditing is on for all objects, in all databases."

Well, except that a superuser *could* effectively turn off checksums by
changing the the control file and doing a restart (perhaps modulo some
other hacking; I've not tried). That kind of trivial 'hole' isn't
acceptable from a security standpoint though and given that we couldn't
prevent a superuser from doing an LD_PRELOAD and overriding any system
call we make from the backend, it's kind of hard to see how we could
plug such a hole.

Ah, I thought it would be more difficult than that for checksums, but PostgreSQL does not have to prevent hacking in my experience, that is the responsibility of other systems and procedures. If the core code was such that once on, formal logging could not be turned off with any changes to config files, settings, or SQL then in my experience that would suffice.

With SELinux it may be possible and I'd love to see an example
from someone who feels they've accomplished it. That said, if we can
reduce the need for a 'superuser' role sufficiently by having the
auditing able to be managed independently, then we may have reached the
level of "considerable headache".

As many have pointed out previously, there is a certain amount of risk
associated with running without *any* superuser role in the system

If all of the superuser's actions are logged and it's not possible to turn off the logging (without considerable headache) then it may not matter what the superuser can do. If the superuser makes changes and they are logged then the auditors have sufficient information to see if the correct procedures were followed. Validated systems are based on tracking, not necessarily prohibiting. Select individuals that should be able to be trusted (which should apply to superusers) should be able to perform the actions necessary to support the organization.

Fair enough- the question is just a matter of what exactly that level of
"headache" is.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Neil Tiffin (neilt@neiltiffin.com) wrote:

On May 4, 2014, at 5:27 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Neil Tiffin (neilt@neiltiffin.com) wrote:
Well, except that a superuser *could* effectively turn off checksums by
changing the the control file and doing a restart (perhaps modulo some
other hacking; I've not tried). That kind of trivial 'hole' isn't
acceptable from a security standpoint though and given that we couldn't
prevent a superuser from doing an LD_PRELOAD and overriding any system
call we make from the backend, it's kind of hard to see how we could
plug such a hole.

Ah, I thought it would be more difficult than that for checksums, but PostgreSQL does not have to prevent hacking in my experience, that is the responsibility of other systems and procedures. If the core code was such that once on, formal logging could not be turned off with any changes to config files, settings, or SQL then in my experience that would suffice.

We could set it up similar to how security labels work, where the
config file (which could be owned by 'root' and therefore unable to be
changed by a superuser) has an auditing setting and changing it requires
a restart (meaning that the config file would have to be modified to
change it, and the database restarted). However, it might be possible
for a superuser to configure and start an independent postmaster with a
different configuration that points to the same database (or a copy of
it).

That's for a system-wide auditing setting, but if we actually want the
auditing to only be on certain database objects, it gets worse. We
need to track what objects need the auditing and we'd do that using the
catalog, which a superuser can modify. Security labels have
more-or-less the same issue, of course.

This is why we don't try to protect against superusers (and why I'm
hopeful that we can reduce the need for a superuser role to exist).
Again, we have to consider that a superuser essentially has a full shell
on the DB server as the user that the database runs under.

Thanks,

Stephen

On 5/2/14, 2:22 PM, Stephen Frost wrote:

I'm aware and I really am not convinced that pushing all of this to
contrib modules using the hooks is the right approach- for one thing, it
certainly doesn't seem to me that we've actually gotten a lot of
traction from people to actually make use of them and keep them updated.
We've had many of those hooks for quite a while.

What is there to update? The stuff works and doesn't necessarily need
any changes.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers