Re: Audit Role Connections

Ah, I do appear to have that enabled (inside Heroku's config), but I can't
find anything like that in the logs, so I've opened a ticket with them. Thx
a lot!

On Fri, May 29, 2020 at 2:25 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> 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=wds SSL 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(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>