Alan Stange <stange@xxxxxxxxxx> writes: > On 8/28/23 18:35, Jerry Sievers wrote: > >> Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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 >>>>
