Обсуждение: [BUG] autovacuum may skip tables when session_authorization/role is set on database

"Imseih (AWS), Sami" <simseih@amazon.com> writes:
> A recent case in the field in which a database session_authorization is
> altered to a non-superuser, non-owner of tables via alter database .. set session_authorization ..
> caused autovacuum to skip tables.

That seems like an extremely not-bright idea.  What is the actual
use case for such a setting?  Doesn't it risk security problems?

> Attached is a repro and a patch which sets the session user to the BOOTSTRAP superuser
> at the start of the autovac worker.

I'm rather unimpressed by this proposal, first because there are
probably ten other ways to break autovac with ill-considered settings,
and second because if we do want to consider this a supported case,
what about other background processes?  They'd likely have issues
as well.

            regards, tom lane

> What is the actual
> use case for such a setting? 

I don't have exact details on the use-case, bit this is not a common
use-case.

> Doesn't it risk security problems?

I cannot see how setting it on the database being more problematic than
setting it on a session level.


> I'm rather unimpressed by this proposal, first because there are
> probably ten other ways to break autovac with ill-considered settings,

There exists code in autovac that safeguard for such settings. For example,
statement_timeout, lock_timeout are disabled. There are a dozen or
more other settings that are overridden for autovac.

I see this being just another one to ensure that autovacuum always runs
as superuser.

> and second because if we do want to consider this a supported case,
> what about other background processes? They'd likely have issues
> as well.

I have not considered other background processes, but autovac is the only
one that I can think of which checks for relation permissions.

Regards,

Sami

On Thu, 14 Dec 2023 at 02:13, Imseih (AWS), Sami <simseih@amazon.com> wrote:
>
> Hi,
>
>
>
> A recent case in the field in which a database session_authorization is
>
> altered to a non-superuser, non-owner of tables via alter database .. set session_authorization ..
>
> caused autovacuum to skip tables.
>
>
>
> The issue was discovered on 13.10, and the logs show such messages:
>
>
>
> warning:  skipping "table1" --- only table or database owner can vacuum it
>
>
>
> In HEAD, I can repro, but the message is now a bit different due to [1].
>
>
>
> WARNING:  permission denied to vacuum "table1”, skipping it
>
>
>
> It seems to me we should force an autovacuum worker to set the session userid to
>
> a superuser.
>
>
>
> Attached is a repro and a patch which sets the session user to the BOOTSTRAP superuser
>
> at the start of the autovac worker.

Since there is not much interest on this patch, I have changed the
status with "Returned with Feedback". Feel free to propose a stronger
use case for the patch and add an entry for the same.

Regards,
Vignesh