From: Jerry Sievers <gsievers19@comcast.net>
To: Alan Stange <stange@rentec.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [INTERNET] Re: auto vacuum question
Date: Tue, 29 Aug 2023 22:47:40 -0500
Message-ID: <m03501dwdf.fsf@comcast.net> (raw)
In-Reply-To: <7d901ed4-f5e9-410b-95da-42bcec235b2e@rentec.com>
References: <f04e52fe-cc44-41f8-bc5b-818309bb482f@rentec.com>
<41a740be-a8d7-b5c0-3054-c9bb7df4d362@aklaver.com>
<m0pm36aj7a.fsf@comcast.net>
<7d901ed4-f5e9-410b-95da-42bcec235b2e@rentec.com>
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
>>>>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: gsievers19@comcast.net, stange@rentec.com, adrian.klaver@aklaver.com
Subject: Re: [INTERNET] Re: auto vacuum question
In-Reply-To: <m03501dwdf.fsf@comcast.net>
References: <f04e52fe-cc44-41f8-bc5b-818309bb482f@rentec.com> <41a740be-a8d7-b5c0-3054-c9bb7df4d362@aklaver.com> <m0pm36aj7a.fsf@comcast.net> <7d901ed4-f5e9-410b-95da-42bcec235b2e@rentec.com> <m03501dwdf.fsf@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox