pg_audit

[peterkracik]

Hi,
I have an AWS environment - Postgrest 17.4 in AWS RDS and PostgREST latest as a pod in EKS cluster.
I enabled pg_audit, if I do a direct SQL statement to the DB, I can see an entry about this statement in the AWS CloudWatch.
But if I do an operation (ie. to create an entry) via PostgREST API, nothing is logged.

I use the same user to communicate with the DB for direct SQL queries and PostgREST. The only difference is that the authentication for API calls is done using JWT OAUTH.

I also tried to update the role used for the authentication with pgaudit.log = 'write'

Am I missing something?

[peterkracik]

Even after configuring alter system set pgaudit.log to "all";
I am not getting the insert/update statements. I am only getting a lot of logs without the statement body.

These are all logs after a simple insert via PostgREST, without anything resembling "INSERT INTO ..."

[wolfgangwalther]

I don't think this is related to PostgREST and I have no idea how to fix it.

[peterkracik]

thank you for your answer @wolfgangwalther

I don't think it is not related to PostgREST, because if I do the same insert via an SQL client using the same user, I can see it in cloudwatch

Couldn't it be because of the usage of CTEs?

[wolfgangwalther]

Couldn't it be because of the usage of CTEs?

You can verify that, if you just use CTEs directly in your SQL client.

Or, better, run the exact same queries that PostgREST is running there, too.

[peterkracik]

That's a good idea, but how could I get the same query that PostgREST is running?

[wolfgangwalther]

You could run PostgREST / PostgreSQL locally and enable statement logging for PostgreSQL (log_statement = all), then they will appear in the server log.

(or do that on AWS, if you have access to the server logs, which I don't know)

[steve-chavez]

PostgREST generates queries and executes them using its API roles, it should be transparent towards pgaudit.

I also tried to update the role used for the authentication with pgaudit.log = 'write'

I haven't used pgaudit, but maybe try adding that setting for the anon or webuser too (assuming you named them like that).