Re: Inserts restricted to a trigger

Thanks for the reply, Adrian.

1. The audit tables (plural) are recording the historical data for a table,
ie., updates and deletes. All the same data as found in a given table along
with the role that performed the operation, the transaction id, and the
time range where this data was in active use.

2. Only thorough a web UI via an API service.

3. Should be limited to web app, but the data scientists may need direct
access in the near future.

PGAudit does not cover our use case. We are making a temporal table system
since PostgreSQL does not support one natively. For example: "What would
this query have returned yesterday at 4:27pm PT?" Access is as expected for
inserts but updates and deletes are logged to history tables. We cannot use
3rd party extensions because we are on AWS managed databases. We are
following the model detailed here (
https://wiki.postgresql.org/wiki/SQL2011Temporal) with some modifications.

Given the model listed in the link, it's not clear how we can prevent user
tampering with history inserts. (History updates and deletes are already
REVOKE restricted.) Since we are going through an API server via REST
and/or GraphQL, the possibility is very unlikely, but we would prefer a
defense in depth approach in case an oversight somehow allowed arbitrary
query access to the database with the web user. For the most part, we're
fairly well locked down, but I just can't quite see how to restrict
aforementioned query access from inserting to the history in an ad-hoc
manner rather than the trigger-based predetermined insert pattern.

On Thu, Jun 20, 2019 at 8:01 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 6/19/19 3:07 PM, Miles Elam wrote:
> > Hi Adrian, thanks for responding.
> >
> > How would I restrict access to the SECURITY DEFINER function? If it can
> > be called by the trigger, it can be called by the user as well I would
> > think. Same issue as access to the table itself only now with a
> > superuser intermediary, right?
> >
>
> Should have also mentioned, if you are not adverse to a third party
> solution there is PGAudit:
>
> https://www.pgaudit.org/
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>