Subject: [BUG] autovacuum may skip tables when session_authorization/role is set on database

From: "Imseih (AWS), Sami" <[email-masked]>
Date: Wed, 13 Dec 2023 20:42:45 +0000

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.

Thoughts?

Regards,

Sami
Amazon Web Services (AWS)

[1] https://postgr.es/m/[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Wed, 13 Dec 2023 16:18:22 -0500

> 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?

> 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

---

From: vignesh C <[email-masked]>
Date: Thu, 11 Jan 2024 21:02:29 +0530

> 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