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