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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com