Postgres connection: Identifying the originator (user)

[StefanM]

My problem is to identify the user who is responsible for the postgres connection(s).

I see many connections but they are all from a technical user not the real one.

Following the connection I identified (in this case) a sas process running with a(nother) technical user id too:

sasexe/sas -memsize 0 -cpucount ACTUAL -noterminal -nox cmd -netencryptalgorithm SASProprietary -metaserver xxx -metaport 8561 -metarepository Foundation -locale en_US -objectserver -objectserverparms protocol=bridge spawned spp=35155 cid=191 dnsmatch=xxx pb classfactory=440196 D4-90F0-11D0-9F41-00A024BB830C server=OMSOBJ:SERVERCOMPONENT/A53WXQBY.AZ000009 cel=credentials recon

What is this? How I can find the real user who is responsible?

Are there technical documentation avail for linux/prostgres admins?

I'm pretty new to SAS but know that all user interactions are running as stored procedures.

[AnandVyas]

Hi @StefanM 

You can use pgAdmin to monitor different aspects of the PostgreSQL database. It has a Dashboard with different metrics, out of which there is one for 'Server Activity' which has a section for sessions.

Could you also please clarify what do you mean by technical user?

In SAS to run stored process there is a managed account 'sassrv' (also called service or system accounts. Default is 'sassrv' can vary at your site) which is the owner of the SAS stored process session and if required it can make a connection to the DB which is normal. You will see the connection with this user itself.

[StefanM]

I know, pgadmin shows me the last statement on top but like "ps -ef" only the technical user, means all connections have the same userid

So ever postgres process is shown with the same unix (technical sasuser) user and the client with sassrv, started from Stored Process server.

But behind this is a real user which I want to identify who pressed the button to initiate the stored process. 

How this can be archived?

[AnandVyas]

Since all the STP session are triggered by STP account 'sassrv' I don't think there is any OOTB feature that can create a mapping or audit trail to the actual end-user. You can write custom code to filter these information from the application server logs, may be. But the STP can be triggered in a number of ways, catering to all of that could be tedious.

If have exposed STP as BI service, I think you can check the number of requests in the Web Server logs.