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 1sPkFt-00687y-IT for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 14:49:45 +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 1sPkFr-00AdNk-Gl for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 14:49:44 +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 1sPkFr-00AdN5-5H for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 14:49:43 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sPkFl-000ZRi-GO for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 14:49:43 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-5b9a35a0901so593221eaf.0 for ; Fri, 05 Jul 2024 07:49:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720190975; x=1720795775; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=1aYH8aTGJSWe6kkyIu4OXhjVWL/Ej3uNhWBezPT51As=; b=iW4o/gbDaHMlDTXZEI9pTiDMkRIeSjZrrYRzDVO9eypQlG+FBxi/T4A/M8SEj9fTOL E6tp6fEchDiU8Z5WqYy2wkrf+4GXZBVsAOI9kKx2wDKNNMS7rLFhX7jrLDNu8qh/sf4e WbpkerR0WpjtVKLLEgKvcoSksH63ps0pAD7p2G5XFoUBvoXkbAkEVx9Gx79ZlYS0mp7s bbrpdrHHWv9PjHQEXCKFgWoHUCAhJKKUt4CcySBA8cZBeXUvp67rHaewO1sKEWWj0chU ZSJiXfg+eS0nYzdSTeQTW11LZ6oRVEpAbcYFkKL0n3y5zf2+XHXDYeAXjoFA9ItUdcPO gQDA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720190975; x=1720795775; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=1aYH8aTGJSWe6kkyIu4OXhjVWL/Ej3uNhWBezPT51As=; b=Y9xCrlEBbRsWnK1yAIvjbxsoY/vakTbIz9ZEStGFjVSx/TNiUfDsk8gA1FJgT3mUvj nCXIW6XGpW2m00mCUg1KcdpNAd2+YAPVH0EsSE4V8Uuo372Khu0gSDE6KpzacSpXFkrC sH5mldiY7eeRn5WFlMG9nazfZ+WPjq8ZNDPRwwvK2oc9rV2vjfP1dpRUeUTQtynBi9pf fQGouC+35iYa3NgjdAYjwyr6WBA4KE5fCYZv5D1kJMBnJVE2QZNEeYaqoIx8vvgCW3ia ts2ZTV8YDtI7lsDDeNLNOpkt6ffGWZW+We616g5LLPjxWNgF7FV1cFwCpxA6IlRZ7RBf 8dLA== X-Gm-Message-State: AOJu0YzLYoAQWfGR6/MAPiGHZ0qailtLGF8ShvU+sMYWuKjNSO8YGnwH 065foKqGakt5PGm3+lJ5BMvtAToZsb7oAP2PwRv8H48SyJmviib/B4slESt9ww19DoVI4pLklMQ nnGj3g3DFdImBmXSXTevaCjYSlN0= X-Google-Smtp-Source: AGHT+IGrn9WBl2uOoUQnQA+6kMK1l1SquBhbcPeHoKX5DBFz5yJpuYQ+OGuTpCtqnl5TGPnATHMc+Fg3gpIE1EiigM8= X-Received: by 2002:a4a:ad49:0:b0:5bd:c2b0:f599 with SMTP id 006d021491bc7-5c6470b4f29mr5687242eaf.9.1720190975120; Fri, 05 Jul 2024 07:49:35 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:ac9:6850:0:b0:539:aa10:6c7 with HTTP; Fri, 5 Jul 2024 07:49:34 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Fri, 5 Jul 2024 07:49:34 -0700 Message-ID: Subject: Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC To: "Tefft, Michael J" Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000049de9061c812c55" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000049de9061c812c55 Content-Type: text/plain; charset="UTF-8" On Friday, July 5, 2024, Tefft, Michael J wrote: > I am trying to remove the default grant of EXECUTE on all > functions/procedures to PUBLIC. > > From my reading, there is no straightforward way to do this. For example, > > ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; > > Does not apply this across the entire cluster (or database) but only > applies to the role who issued it (and objects yet to be created by that > role) . > > So I am arriving at the conclusion that I need to alter the default > privileges for every existing role (which I expected), and ensure that > default privileges are altered for every new role that is created going > forward. > > > > Have I analyzed this correctly? > > > Only those roles that have create privilege on one or more schemas. That should be a reasonably finite and static set. David J. --000000000000049de9061c812c55 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Friday, July 5, 2024, Tefft, Michael J <Michael.J.Tefft@snapon.com> wrote:

I am trying to remove the default grant of EXECUTE o= n all functions/procedures to PUBLIC.

From my reading, there is no straightforward way to = do this. For example,

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS= FROM PUBLIC;

Does not apply this across the entire cluster (or da= tabase) but only applies to the role who issued it (and objects yet to be c= reated by that role) .

So I am arriving at the conclusion that I need to al= ter the default privileges for every existing role (which I expected), and = ensure that default privileges are altered for every new role that is creat= ed going forward.

=C2=A0

Have I analyzed this correctly?



Only those roles that have create privilege on one or more schemas.= =C2=A0 That should be a reasonably finite and static set.

David J.

--000000000000049de9061c812c55--