Re: [INTERNET] Re: auto vacuum question - Mailing list pgsql-general

Alan Stange <stange@rentec.com> writes:

> On 8/28/23 18:35, Jerry Sievers wrote:
>
>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>
>>> On 8/28/23 13:06, Alan Stange wrote:
>>>
>>>> All,
>>>> We recently changed the name of the superuser role in our database,
>> My take on this, is that the *postmaster* user is perhaps the one that
>> the OP cut privileges on, and thus the launcher is (now) spawning
>> workers with less than full SU perms.
>>
>> Just a guess.
>>
>> FWIW
>
> Hypothetically speaking, for a friend, how would one change the
> superuser that was assigned in the initdb command?      Your guess is
> good, and it's clear now that the running database isn't accommodating
> our removal of the superuser attribute from the original default role
> created in the initdb command.

Most sites I"ve worked at do *not* use the initdb user for anything but
administrator usage and create one or more regular users/roles with
appropriate object ownership to individual DBs therein.

NOt uncommonly you might wish to have an *owner* role for deployment
(DDL) activities and a lesser role for application use

Doing so help minimize blast radius in case the application is
compromised.

There are a number of finer details in all of this but essentially,
leave alone the initdb/postmaster user except for DBA like tasks.

HTH

>
> Thank you,
>
> Alan
>
>
>>
>>>> and
>>>> then noticed some issues with the autovacuum processes.  We are running
>>>> 15.3, and had a login role, lets call it 'red', which had the superuser
>>>> attribute assigned to it.   This was the original owner/creator of all
>>>> the database objects, and the login role ended up getting used in some
>>>> ways for which the superuser attribute was no longer appropriate.
>>>> So we elected to make a new role, lets call it 'reddba', which had
>>>> tightly controlled entitlements, and which also had the super user
>>>> attribute added to it.  After a couple of weeks and a lot of testing, we
>>>> removed the superuser attribute from the original 'red' account.  All
>>>> was working as expected.
>>>> After a bit we noticed some warnings in the postgresql server log
>>>> file
>>>> of the form 'WARNING:    skipping "tablexxx"   --- only table of
>>>> database owner can vacuum it" where tablexxx is many of the table names
>>>> in our system.
>>> Are you sure that is coming from autovacuum?
>>>
>>> What are the log lines preceding the WARNING?
>>>
>>> What is the complete warning line?
>>>
>>>> We restored the superuser role to the original 'red' login role and
>>>> these messages went away.
>>>> We are not running any explicit vacuum's.    As far as I can tell,
>>>> these
>>>> warnings were comming from the autovacuum processes, and we did also
>>>> notice that some query stats became stale which I assume is related to
>>>> these same warnings.
>>>> So, I'm wondering how we can move the superuser role from role A to
>>>> B,
>>>> so that the autovacuum process will still work?   I googled around a
>>>> bit, but didn't come up with anything useful for this.
>>>> Thank you,
>>>> Alan
>>>>