Re: Inserts restricted to a trigger

On 6/20/19 3:30 PM, Miles Elam wrote:
> 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.

Some draft ideas:

1) A session table that among other things records the Web Token/user
combination. Then in the trigger(s) that INSERT into the history table
check that the user has a valid current token.

2) In the inner function use PG_CONTEXT:

https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-CALL-STACK

to determine whether the inner SECURITY DEFINER function is being called
directly or through the outer trigger function.

>
>
>
> On Thu, Jun 20, 2019 at 8:01 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto: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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com