Use case - Need to log the authenticated user(not the database user) from the Python/Flask/Sqlalchemy in the Postgres logs.

At the moment I have enabled the Postgres logs with postgres.conf with the following format.

log_line_prefix = 'time=%t, %q db=%d, user=%u, ip=%h, app=%a' 

This will log following format in the postgres.csv.

2021-11-17 09:56:18.856 GMT,"db_user_name","database_name",4962,"10.0.0.2:52068",6194d1c2.1362,16,"idle in transaction",2021-11-17 09:56:18 GMT,3/12392,0,LOG,00000,"statement: Query,,,,,,,,,"AppName"

I thought of following ways:

1. Concatenate username to application name dynamically.
2. Tried to find way to add some metadata(username) to the query.
3. Write a sql function to execute before an each query.

I tried to concatenate the username to app_name as following ways.

self.session.bind.url.query.update({'application_name': 'TEST_USERNAME'})
self.engine.url.query.update({'application_name': 'TEST_USERNAME'})

But this is not reflecting or updating the application name in the logs.

And also I tried to add some metadata on the query as well but it also not reflecting on logs.

For the sql function, I think it's an unnecessary overhead.

I would be curious to know is there anyone handle this use case or appreciate if someone can guide to me to find a solution.

CodePudding user response：

You can certainly use application_name for that.

You can either set that parameter in your connect string, or you can change the parameter any time by running

SET application_name = 'whatever';

or

SELECT set_config('application_name', 'whatever', FALSE);