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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
