Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qbCBb-00BMEt-Mo for pgsql-general@arkaria.postgresql.org; Wed, 30 Aug 2023 03:48:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qbCBa-00EC8F-4s for pgsql-general@arkaria.postgresql.org; Wed, 30 Aug 2023 03:48:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qbCBZ-00EC7n-5Y for pgsql-general@lists.postgresql.org; Wed, 30 Aug 2023 03:48:05 +0000 Received: from resqmta-c1p-024061.sys.comcast.net ([2001:558:fd00:56::6]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qbCBQ-001iR7-Pp for pgsql-general@postgresql.org; Wed, 30 Aug 2023 03:48:02 +0000 Received: from resomta-c1p-022584.sys.comcast.net ([96.102.18.233]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resqmta-c1p-024061.sys.comcast.net with ESMTP id bBapqgz5I4GOcbCBNqU0Uj; Wed, 30 Aug 2023 03:47:53 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1693367273; bh=bn0XHReKxECAIp4cPEP1kJCXtCyW+mxaZmWPYjspwOM=; h=Received:Received:Received:From:To:Subject:Date:Message-ID: MIME-Version:Content-Type:Xfinity-Spam-Result; b=zrcaQYmOHC7Hfo4EirE2xIE2kIcxVJixLtP4Rly7XJFSiEjhEKF++xYoRRJ6WeUKq 7zQcuIBTss/xJ3Z5EdJRS3mfRVtuYHSTWyJnKYRchQqix7l6DN3QZoyw/NYO10DSmv Sjx3Hm8fm5eXHrXLifQYLytJmm4dpD7x2sgZPWQr9bM2rffhomDJSq5subVyLJJo6W AWo9GD+o9Iyl6CxqYArpyQ8n4SW+bQxs0hoeQB/JEV604E7VhYV4rNmz80+Kq7yJYx GlzJYjXwCt7+TZ4/1t0KrMcPRre4yoTpGVJPP6g6dfzeArxIn04sV+Xfyc8Jm+1Y6r zgupZeabF9q2w== Received: from R9N0YC2TC3.localdomain ([134.238.215.18]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resomta-c1p-022584.sys.comcast.net with ESMTPA id bCBAqCAl7mmGVbCBCqfm3G; Wed, 30 Aug 2023 03:47:51 +0000 Received: by R9N0YC2TC3.localdomain (Postfix, from userid 503) id 82F5E221B3A8; Tue, 29 Aug 2023 22:47:40 -0500 (CDT) From: Jerry Sievers To: Alan Stange Cc: Adrian Klaver , pgsql-general General Subject: Re: [INTERNET] Re: auto vacuum question References: <41a740be-a8d7-b5c0-3054-c9bb7df4d362@aklaver.com> <7d901ed4-f5e9-410b-95da-42bcec235b2e@rentec.com> Date: Tue, 29 Aug 2023 22:47:40 -0500 In-Reply-To: <7d901ed4-f5e9-410b-95da-42bcec235b2e@rentec.com> (Alan Stange's message of "Tue, 29 Aug 2023 16:09:16 -0400") Message-ID: User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.1 (darwin) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Alan Stange writes: > On 8/28/23 18:35, Jerry Sievers wrote: > >> Adrian Klaver 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 >>>>