[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Audit Role Connections
From:       Chris Morris <chris () mysteryscience ! com>
Date:       2020-05-29 19:44:53
Message-ID: CALrUc2WODU9Bdov3w+LbvYriYtJ7Eo9V498W4Yxv3k3fK+ftiQ () mail ! gmail ! com
[Download RAW message or body]

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@hjp.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@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"
>

[Attachment #3 (text/html)]

<div dir="ltr">Ah, I do appear to have that enabled (inside Heroku&#39;s config), but \
I can&#39;t find anything like that in the logs, so I&#39;ve opened a ticket with \
them. Thx a lot!</div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Fri, May 29, 2020 at 2:25 PM Peter J. Holzer &lt;<a \
href="mailto:hjp-pgsql@hjp.at">hjp-pgsql@hjp.at</a>&gt; wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">On 2020-05-29 12:42:47 -0500, Chris Morris \
wrote:<br> &gt; We&#39;re using Heroku&#39;s PG,<br>
[...]<br>
&gt; Other than polling pg_stat_activity (which isn&#39;t 100% accurate depending \
on<br> &gt; timing), is there a good way to audit connections? To detect which roles \
are<br> &gt; being used for connections?<br>
<br>
Do you have access to the log files?<br>
<br>
If you log_connections is on, you get messages like these:<br>
<br>
<br>
2020-05-29 21:00:02 CEST [27995]: [2-1] user=w*****,db=wds,pid=27995 LOG:   \
connection authorized: user=w***** database=wds<br> 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]<br> 2020-05-29 21:07:14 CEST [7481]: [2-1] \
user=u*****,db=wds,pid=7481 LOG:   connection authorized: user=u***** \
database=wds<br> 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]<br> 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)<br> 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<br> <br>
(user names and IP addresses censored for privacy reasons)<br>
<br>
            hp<br>
<br>
-- <br>
     _   | Peter J. Holzer      | Story must make more sense than reality.<br>
> _|_) |                              |<br>
> > > <a href="mailto:hjp@hjp.at" target="_blank">hjp@hjp.at</a>              |      \
> > > -- Charles Stross, &quot;Creative writing<br>
__/     | <a href="http://www.hjp.at/" rel="noreferrer" \
target="_blank">http://www.hjp.at/</a> |           challenge!&quot;<br> \
</blockquote></div>



[prev in list] [next in list] [prev in thread] [next in thread]