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 1qawo1-00Akpg-HM for pgsql-general@arkaria.postgresql.org; Tue, 29 Aug 2023 11:22:46 +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 1qawo0-006UDc-04 for pgsql-general@arkaria.postgresql.org; Tue, 29 Aug 2023 11:22:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qawnz-006UDT-Hi for pgsql-general@lists.postgresql.org; Tue, 29 Aug 2023 11:22:43 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qawnv-001ixo-3M for pgsql-general@lists.postgresql.org; Tue, 29 Aug 2023 11:22:42 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4RZlQJ6pYpz9sQL; Tue, 29 Aug 2023 13:22:32 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1693308153; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=aJ5WgWkf7dDvw6Zn28kHEQuqwbNcr1PTI3BZyOTaUAg=; b=fRie7lrRezkaP7PmE4rt+0nxUK0XhmcWizPlLYupxdK+0DpCsL8I064hsjC6983+k0pBqS gov7+mOO13+A9rcF6n0W3X+KJ4BgwTbSotAP160M3orhKaNW1n1vxf6FsCz2MZ+t7tijn2 yWc4ZGHXFFqncgAQBSC5YSkhftykV+g/2Qv9aiqygnAXESqAdItHpnKrd46pFo93fgXAF/ hb9o/6GkSsUDjGqVIposqsvKn1jLAdGKu5x0VCDoutQUMAB2e9kLeC/1skzw5tZTr/soQ2 VN+8uqVtkDBE5tYQD5Ec8ifWDgesZAEFEWNRDtAgBvuGVXfXl0uaLgjZc17Tow== Date: Tue, 29 Aug 2023 13:22:31 +0200 (CEST) From: Erik Wienhold To: Stuart McGraw , pgsql-general@lists.postgresql.org Message-ID: <1166048641.281962.1693308151542@office.mailbox.org> In-Reply-To: <96d6885a-5e25-9ae8-4a1a-d7e557a5fe9c@mtneva.com> References: <96d6885a-5e25-9ae8-4a1a-d7e557a5fe9c@mtneva.com> Subject: Re: Restoring default privileges on objects MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4RZlQJ6pYpz9sQL List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 29/08/2023 03:23 CEST Stuart McGraw wrote: > > If I've done a GRANT or REVOKE on some of the tables, how do I restore > the default privileges so that the =E2=80=9CAccess privileges=E2=80=9D ap= pears empty > again? I re-granted what I think are the default privileges but the > "Access privileges" column for that table contains "user1=3DarwdDxt/user1= " > rather than being blank. This is Postgresql-14. Yes, "user1=3DarwdDxt/user1" matches the default privileges if user1 is the= table owner. Function acldefault('r', 'user1'::regrole) [1] gives you the defaul= t privileges for tables. You could set pg_class.relacl to NULL to restore the default privileges, bu= t messing with pg_catalog is at your own risk. Besides that I don't know of = any way to restore the default privileges other than revoking all privileges be= fore granting whatever acldefault gives you. Changing the table owner will then also change the grantee and grantor in pg_class.relacl to the new owner. [1] https://www.postgresql.org/docs/14/functions-info.html#FUNCTIONS-ACLITE= M-FN-TABLE -- Erik