Thread: Audit Role Connections
We're using Heroku's PG, and it comes with a default, almost super-user. I'm wanting to restrict that account for only our production app servers, and have anyone connecting for personal queries to go through an assigned role by employee (that's all setup and working).
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?
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?
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!"
Attachment
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!"