From: "Tefft, Michael J" <Michael.J.Tefft@snapon.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Removing the default grant of EXECUTE on functions/procedures to
 PUBLIC
Thread-Topic: Removing the default grant of EXECUTE on functions/procedures to
 PUBLIC
Thread-Index: AdrO6KiHbmGi+ZIhRy266wuZu91e0g==
Date: Fri, 5 Jul 2024 14:45:11 +0000
Message-ID: 
 <BN8PR04MB6289E694A40BE7DF913754A1D0DF2@BN8PR04MB6289.namprd04.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?0yyCE28EvniyzcmpSYdE6b7y9OIyhuH69/vIWqbcV/t6TizCvWX+bc6wbFlj?=
 =?us-ascii?Q?jhueTDBShB6JvVDh0zLEXSq0AvRPoh5MxXoik494ku1jEH90T3Ywh9A8s0Bj?=
 =?us-ascii?Q?SnuO6J3o4C+dVa0suOcH7jqcOGDBq+cI8/pR2T4mZO09IdUgxNRfQkPeqVBq?=
 =?us-ascii?Q?p8Ds3frfgPDBLqTJPlJ00WN4bUouuqPa3UxrJYnQIYoHH9WKhrZhl31+Mt+t?=
 =?us-ascii?Q?AkN3V+U/PR5hQjEhaiOBqeMdNwrcJ1wwzRzNBlCo99GzfbVhhMlq9835D4rI?=
 =?us-ascii?Q?s4QO8X0GcVb994DzpgEgV8rKyJQqTOmvkXC2oUkO5PYC5Ie0mV1Ec185+nxI?=
 =?us-ascii?Q?DtdCly3UlO74vbJCeLi7whA1goi/nSTJrOT1e6jmTTcVBgEzXz9K0lYS59RL?=
 =?us-ascii?Q?FXuz4OTjwFjBoT9VqBa1NrKq8KVmzqa5yX1RdkMnV5lUozBJeZjWi+mmNxQU?=
 =?us-ascii?Q?DFIqxNWQ7EVGEG6axXX3RFlXv1WkjYEDkPpX5iYww+1RjtFJI639DCfk2ytM?=
 =?us-ascii?Q?HaxAG6zKOlc6ZEYWUIINWIjGlC+c2ZWEiEanuMGov2bSvl40bf++sLqgyCjN?=
 =?us-ascii?Q?GMEzhSZ9n6lM/SR4WiD7Dp4TaO8U2Hvlttyh8EREki32CX/24N8b/nlh0jJc?=
 =?us-ascii?Q?uquzd7unQolIM4cvKtF91LiZC5LUL56gi/xVqNbBm3ElB6Kq/Xr2EGGbepSn?=
 =?us-ascii?Q?zMpMmnsglDho/SbnqYVcNfYXdfQJcW5tYYh5BAh8QOLHoV7ko6i5o9wLuSFu?=
 =?us-ascii?Q?ztPN0I+NiiSunu4/IOQFer2lKoRzHIaFeimBShunnseTyt+pTSEl+kQdHTPq?=
 =?us-ascii?Q?qILHHOLylOCiYqmOYkji3XnZmND8NSzJ6RASnsHxzVyFVnYJFMEpFyHgF0pz?=
 =?us-ascii?Q?dnxnoeZkHkD8eqMDIdNfC6kyqllNT4+d1cbbbCo3sf+8fdovo67ViwhtQQJT?=
 =?us-ascii?Q?UxZdsL/7w6Qt3t0FZKtEl5HFfJQxQqVCuQRTiStf5DhaKbS25OiaJOg1fUXV?=
 =?us-ascii?Q?PLlhp4fan+dy0HOGaxFB+RxKJ8Wy/I5ucH12WPzoW8pwi+xBnBiKdcSBLiwu?=
 =?us-ascii?Q?4uV5Hd2VeHNHfUKGbb3pMhgPCek/glZbUGBYteQeeNssxFUX4/Yen8mKT4DS?=
 =?us-ascii?Q?e58t4m25fKhT0er7mOp7R9nCP8lmoI6J1DJSwjI1INqrKiuRhRaW2R17HyH2?=
 =?us-ascii?Q?25/zGAeBVji9OysA/hYBuXizenOWWAqshAUfbClF5hY9uSrpHNJ21C9jrsq4?=
 =?us-ascii?Q?/W664o3kdi9goceZmn6TUFCbaDDAWftdlUCiN489ug3MN4+biarDMfszALLT?=
 =?us-ascii?Q?eZbUS4ec8Nl0rKMzfc5IpFMkuVNviEdP7OWVET5WXmNqdQ/u3xVdm1gXV33G?=
 =?us-ascii?Q?gU/D90HESA3SN5mTkWjTnJyVJxK95OeIaYgHIgqo8CBxKgiB0+/oeQME9A7S?=
 =?us-ascii?Q?F6ZEVTA1zhJKXYZKcQ6dt54kgRCF0Tt1pMc8NewXPmtfyF2ite/M/xUlkVcR?=
 =?us-ascii?Q?hKsTSFO0QoJNL5WZpGfmpB6FUyCdW5Fhqs6q57OXNIs0BPzFgeLnofLg6ml+?=
 =?us-ascii?Q?z4ObKAfTNOvIivsXvGl01c6dtil+Kar8QjQoqZzs?=
Content-Type: multipart/alternative;
	boundary="_000_BN8PR04MB6289E694A40BE7DF913754A1D0DF2BN8PR04MB6289namp_"
MIME-Version: 1.0
X-OriginatorOrg: snapon.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BN8PR04MB6289.namprd04.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: b569cf32-07c2-495f-03b7-08dc9d0112ff
X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Jul 2024 14:45:11.8641
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9cbb18c7-7b64-4d6a-953b-09ec023c8c08
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: o9NcSLJBNE067BpZjLv7sICZ7dhytgvLOEh7bzrHDnyVx22Ck8772A+svCRB1sGdoKJ4Al/nfOxrk8zVakOwROZ7xOtEgzp5RQJmokjCPt0=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CO6PR04MB7812
X-Proofpoint-GUID: C7YcBqXUsIuNbfJ-17MqY-V9gBMsrT_l
X-Proofpoint-ORIG-GUID: C7YcBqXUsIuNbfJ-17MqY-V9gBMsrT_l
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16
 definitions=2024-07-05_10,2024-07-05_01,2024-05-17_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 priorityscore=1501
 suspectscore=0 lowpriorityscore=0 adultscore=0 mlxscore=0 impostorscore=0
 phishscore=0 bulkscore=0 malwarescore=0 clxscore=1011 mlxlogscore=338
 spamscore=0 classifier=spam adjust=0 reason=mlx scancount=1
 engine=8.19.0-2406140001 definitions=main-2407050106
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/BN8PR04MB6289E694A40BE7DF913754A1D0DF2%40BN8PR04MB6289.namprd04.prod.outlook.com>
Precedence: bulk

--_000_BN8PR04MB6289E694A40BE7DF913754A1D0DF2BN8PR04MB6289namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I am trying to remove the default grant of EXECUTE on all functions/procedu=
res 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 applie=
s 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 privile=
ges for every existing role (which I expected), and ensure that default pri=
vileges are altered for every new role that is created going forward.

Have I analyzed this correctly?

Thanks,
Mike Tefft

--_000_BN8PR04MB6289E694A40BE7DF913754A1D0DF2BN8PR04MB6289namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">I am trying to remove the default grant of EXECUTE o=
n all functions/procedures to PUBLIC.
<o:p></o:p></p>
<p class=3D"MsoNormal">From my reading, there is no straightforward way to =
do this. For example,<o:p></o:p></p>
<p class=3D"MsoNormal">ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS=
 FROM PUBLIC;<o:p></o:p></p>
<p class=3D"MsoNormal">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) .<o:p></o:p></p>
<p class=3D"MsoNormal">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.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Have I analyzed this correctly?<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Thanks,<o:p></o:p></p>
<p class=3D"MsoNormal">Mike Tefft<o:p></o:p></p>
</div>
</body>
</html>

--_000_BN8PR04MB6289E694A40BE7DF913754A1D0DF2BN8PR04MB6289namp_--