On 2020-05-29 12:42:47 -0500, Chris Morris wrote:
> We're using Heroku's PG,
[...]
> Other than polling pg_stat_activity (which isn't 100% accurate depending on
> timing), is there a good way to audit connections? To detect which roles are
> being used for connections?
Do you have access to the log files?
If you log_connections is on, you get messages like these:
2020-05-29 21:00:02 CEST [27995]: [2-1] user=w*****,db=wds,pid=27995 LOG: connection authorized: user=w*****
database=wds
2020-05-29 21:00:18 CEST [27995]: [9-1] user=w*****,db=wds,pid=27995 LOG: disconnection: session time: 0:00:15.979
user=w*****database=wds host=[local]
2020-05-29 21:07:14 CEST [7481]: [2-1] user=u*****,db=wds,pid=7481 LOG: connection authorized: user=u*****
database=wds
2020-05-29 21:07:14 CEST [7481]: [7-1] user=u*****,db=wds,pid=7481 LOG: disconnection: session time: 0:00:00.016
user=u*****database=wds host=[local]
2020-05-29 21:10:56 CEST [13918]: [2-1] user=m*******,db=wds,pid=13918 LOG: connection authorized: user=m*******
database=wdsSSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, compression=off)
2020-05-29 21:10:56 CEST [13918]: [11-1] user=m*******,db=wds,pid=13918 LOG: disconnection: session time: 0:00:00.117
user=m*******database=wds host=143.130.**.** port=54037
(user names and IP addresses censored for privacy reasons)
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
