Re: Clarification on Role Access Rights to Table Indexes

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Wed, 2025-09-24 at 12:13 -0400, Tom Lane wrote:
>> Don't we do that intentionally, to make sure someone can't cause DOS
>> on a table they have no privileges on?

> Is this only a problem for strong locks (ShareLock or greater)?

> Strong locks are a problem when you have a pattern like a long running
> query that holds an AccessShareLock, and then an unprivileged user
> requests an AccessExclusiveLock, forcing other queries to queue up
> behind it, and the queue doesn't clear until the long running query
> finishes.

> But weaker locks don't seem to have that problem, right?

I don't think so. Even AccessShareLock is enough to block another
session trying to acquire AccessExclusiveLock, and then not only
have you DoS'd that session, but everything else trying to access
the table will queue up behind the AccessExclusiveLock request.
So it's only not-a-problem if nothing anywhere in the system wants
non-sharable locks.

regards, tom lane