[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [INTERNET] Re: auto vacuum question
From:       Jerry Sievers <gsievers19 () comcast ! net>
Date:       2023-08-30 3:47:40
Message-ID: m03501dwdf.fsf () comcast ! net
[Download RAW message or body]

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?=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 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.=C2=A0 We are r=
unning
>>>> 15.3, and had a login role, lets call it 'red', which had the superuser
>>>> attribute assigned to it. =C2=A0 This was the original owner/creator o=
f 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.=C2=A0 After a couple of weeks and a lot of test=
ing, we
>>>> removed the superuser attribute from the original 'red' account.=C2=A0=
 All
>>>> was working as expected.
>>>> After a bit we noticed some warnings in the postgresql server log
>>>> file
>>>> of the form 'WARNING:=C2=A0=C2=A0=C2=A0 skipping "tablexxx"=C2=A0=C2=
=A0 --- 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.=C2=A0=C2=A0=C2=A0 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?=C2=A0=C2=A0 I googled =
around a
>>>> bit, but didn't come up with anything useful for this.
>>>> Thank you,
>>>> Alan
>>>>


[prev in list] [next in list] [prev in thread] [next in thread]