From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michal Charemza <michal(at)charemza(dot)name> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Extremely slow to establish connection when user has a high number of roles |
Date: | 2024-04-21 15:08:04 |
Message-ID: | 2724165.1713712084@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Michal Charemza <michal(at)charemza(dot)name> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> I'm fairly sure that if that exists it's always noticed first,
>> bypassing the need for any role membership tests. So please
>> confirm whether your production database has revoked PUBLIC
>> connect privilege.
> I realised that in fact we hadn't revoked this. So it sounds like whatever
> the issue, it's not about checking if the user has the CONNECT privilege?
Yeah. I double-checked the code (see aclmask()), and it will detect
holding a privilege via PUBLIC before it performs any role membership
searches. So whatever is happening, it's not that lookup.
>> It could be that the problem is not associated with the
>> database's connect privilege, but with role membership lookups
>> triggered by pg_hba.conf entries. Do you have any entries there
>> that require testing membership (i.e. the role column is not
>> "all")?
> Running `select * from pg_hba_file_rules` it looks like the user column is
> always {all} or {rdsadmin}
You'll need to look closer and figure out which of the HBA rules is
being used for the slow connection attempts. If it's {rdsadmin}
then that would definitely involve a role membership search.
If it's {all} then we're back to square one.
A different line of thought could be that the slow connections
are slow because they are waiting on a lock that some other
process has got and is in no hurry to release. It would be
worth trying to capture the contents of the pg_locks view
(and I guess also pg_stat_activity) while one of these sessions
is stuck, if you can reproduce it often enough to make that
feasible.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Darwin O'Connor | 2024-05-30 01:03:28 | Plan selection based on worst case scenario |
Previous Message | Michal Charemza | 2024-04-21 11:08:11 | Re: Extremely slow to establish connection when user has a high number of roles |