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 1sPmxK-006L5b-Hi for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 17:42: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 1sPmxI-00CE2g-Fw for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 17:42:45 +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 1sPmxH-00CE1T-B2 for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 17:42:44 +0000 Received: from mx0b-004aa801.pphosted.com ([148.163.140.28]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPmxE-000ahc-G6 for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 17:42:42 +0000 Received: from pps.filterd (m0384004.ppops.net [127.0.0.1]) by mx0b-004aa801.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 465EUX5W008083; Fri, 5 Jul 2024 12:42:37 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snapon.com; h=cc :content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=s2048a; bh=r67/T5Pf2mTWMFTHvlu901FmPnU NyUAhc80OwemjRIs=; b=Y5IF/bYSapmtW5xK0fHoHlq/Ok2x1UqlZnuz9UbkbPk pj622DEWtpVdWGIo6rTezvkkQgF5qioBwzPbjTSCMsE8Pi5U14KvVd013CJJgnRq Tx2I3JQsCmiCQ216hHza5tPggASA3rjbE2la2ifC0F/GUr8XV9SEJWVkbUajMPYS wlbAQd1fYJXpKUt3nYpytyvzz8asaN4Ct4ryBDWRCOZX//zz5Qp/sQ8imzkDbELi dzUmm7AgOiZ1zaJrBJkfiOtYARvly4D1WmPOkqZCiSokhIFiJpS1FUcgsdU6h7dV ddoxbTBV7a5TolHSX/IUUFjzE5NkplZwOhW4+USKRVg== Received: from nam12-bn8-obe.outbound.protection.outlook.com (mail-bn8nam12lp2172.outbound.protection.outlook.com [104.47.55.172]) by mx0b-004aa801.pphosted.com (PPS) with ESMTPS id 40326h32v4-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Fri, 05 Jul 2024 12:42:37 -0500 (CDT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=H9yZS2X2W4prbTp33uy0eqX/9Lsw/Y/S40mP0TvQy6PvbqylwxFCJ5bgNvnydLSKJ/gIewO3LqLgdLku/ZOsR4ep63kdzC9zy/pml6HjmkSxmAec83j7yJiLqx9HV5DwkC3kAcbVipr64uim09CRkZO1l40Ix4jYRE44xVC0zRR+6Ago3f9cXFchQTpwSSFBzWuOXsBdYM3fHTUr+p/I2fNEFlutly2UzLsH/bjmhcUL0WaVIZl6pPGGIkehPLPGsijLc03AoxzwqObxDhzdNeuF6fSfaq+7j1mP+dzmgYTHeBQcnDcRqdAAn0xqlKEwknQG1HCi/cEMgMp9a6/8gA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=vUaQkI0H89BPQL9vnPIpJtxQBT4t1unMvfCCQF+jXXk=; b=W8U5zOYhh2+dgVamcpkcbx45kHIXPn8JWAhXMTG3AVyJAEb8csmA4cc6TVzTxq14NUS8oYFFSgURVt/0SR6n3Ac+XdeNCs0kx3mkJ0yjeMJ2ZzenefurOA2TKOMhLJgxwDPNDJpO0ohqo6sS6ciBLS3xQl6A3AVBQNAetLaLtUqJvWJEhOV3QxfO2lTwtTInzoTqtMaAqPxj4hJ8LLo5DUhsQfMh+Cq5dicjZcgAcQ53cHZcC2hXPjQkYvsouy5ZoGnAXzkN2ouS/3okEo3XHly2+yDDKPgiotPk7qkoAHqKl8n6dU9IvEFdqW7TUD1sM7+GzNwlXKyHm7aSihV6JQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=snapon.com; dmarc=pass action=none header.from=snapon.com; dkim=pass header.d=snapon.com; arc=none Received: from BN8PR04MB6289.namprd04.prod.outlook.com (2603:10b6:408:d9::7) by SA6PR04MB9566.namprd04.prod.outlook.com (2603:10b6:806:431::22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7719.32; Fri, 5 Jul 2024 17:42:35 +0000 Received: from BN8PR04MB6289.namprd04.prod.outlook.com ([fe80::63ff:16cf:cb10:259f]) by BN8PR04MB6289.namprd04.prod.outlook.com ([fe80::63ff:16cf:cb10:259f%5]) with mapi id 15.20.7741.027; Fri, 5 Jul 2024 17:42:35 +0000 From: "Tefft, Michael J" To: Tom Lane CC: "pgsql-general@lists.postgresql.org" Subject: RE: 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+ZIhRy266wuZu91e0gAAhgWAAAXde1A= Date: Fri, 5 Jul 2024 17:42:35 +0000 Message-ID: References: <201663.1720191061@sss.pgh.pa.us> In-Reply-To: <201663.1720191061@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-dg-rorf: true x-dg-ref: PG1ldGE+PGF0IGFpPSIwIiBubT0iYm9keS5odG1sIiBwPSJjOlx1c2Vyc1xuajM0MTdcYXBwZGF0YVxyb2FtaW5nXDA5ZDg0OWI2LTMyZDMtNGE0MC04NWVlLTZiODRiYTI5ZTM1Ylxtc2dzXG1zZy1mNTU2M2FmNS0zYWY1LTExZWYtOWQ1MC03NDNhZjQ2Yjg3OGVcYW1lLXRlc3RcZjU1NjNhZjctM2FmNS0xMWVmLTlkNTAtNzQzYWY0NmI4NzhlYm9keS5odG1sIiBzej0iMTY4MzIiIHQ9IjEzMzY0Njc0OTUzOTMzNzkzOCIgaD0ieUp2cnltT0NGaThRU1F6VUtJMjlBZ3FHMVpNPSIgaWQ9IiIgYmw9IjAiIGJvPSIxIi8+PC9tZXRhPg== x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BN8PR04MB6289:EE_|SA6PR04MB9566:EE_ x-ms-office365-filtering-correlation-id: b584d6fe-e4e1-4866-554b-08dc9d19dae8 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|376014|366016|38070700018; x-microsoft-antispam-message-info: =?utf-8?B?MHpKaVZIOG5LVzh1cWFSV0NvYVM0b3BoSGFCeDlMWUl1UDRHNFhaaDZwRkxq?= =?utf-8?B?YmNRMUF1TkRNeDlOUDZsd2RtRmEybHYxTGxGWitQcDJLWGI0T09qWm9qbzdt?= =?utf-8?B?YVd2dDNkWFVibFVCK1Rsb2ZaRVZ5WTk0KzlSVWFjY2lUNTZPYmEyYURXYmlK?= =?utf-8?B?d2dLbU84M21nRFhxVURHdC8vVVFYdzVBeGs4Ry83cFVhSTNjQ2xXUXl3TkRh?= =?utf-8?B?TkJTUFQ4b2I3R290NDczdDdjczAvaGZleDQxMm0yQWE3UFRhSnFRbGhXU3lt?= =?utf-8?B?bCtKU09IWGhPazB6V1M5MGdLeDNLb0dCdmxMcnVIYTcyNnJqeERYcTB0QUxE?= =?utf-8?B?dHowQmc4S1ZuWTByR0xsQ0todlAzZzNVelkxQndIV1BISGQxNGZUYmxBdFVO?= =?utf-8?B?VjU5aWJTU2dIZVFPN3hnU05pd21KenVqYnU2OFJyK3FTTmhZYU81RW9SdWNy?= =?utf-8?B?d0VFUTVFNDBmc0lFRHd1WVVhSGlsdW1EU3lFclRuNTFPWXBaVkdZY3JIdDFD?= =?utf-8?B?YTBiK2gxZXRnQkRKR1NjYXZWeCtLVU5tZHZEZ3lIaVpWTkFEWVBob0U4eDZ5?= =?utf-8?B?VHIxZU1YdDR6cHVad2ZPeTNEQnpEOGdRRFZzWWpqVXhPbWp1VUNOSmx0ZFN3?= =?utf-8?B?c25aZmxDcm1tNEtXZktrTEs4L3dVWkhMeW5BRUV4VjZWYW9aeW1ScjQ4eXRT?= =?utf-8?B?d3Z5MEJSVWVFajd6ek5iV0FEL1FLREZEOWVqRHBQdlBuUllhNGsvQXQ4OXVI?= =?utf-8?B?b29yZ28vclBPdGJ4RzJVY0J4dmEwbGJoL2Uza2Z3ejQzY3VvUEZlOHpCZDU5?= =?utf-8?B?SHF1UHEyeWhwQlB2YUtPSCs5MXl0cm5kVWZDN3o4UUJDWWUwRzZxRmErY3NZ?= =?utf-8?B?ZWZtQ1JyMXJxYXNVWU1kREw5TnEzRVN0M0N5T1NJZFAxMVNTS1JJbEJoL2Z5?= =?utf-8?B?ZTdlWmphUWxkZDFVT0RWdHY0RmltdC9uVFU2ai85RG5COS9xTEZlaGdpZC9W?= =?utf-8?B?WTl4alMvR3NTaXo2NTExMkpMQjlGWHlXbGJRdkNtYWx2aklEaERNUHRVS2VU?= =?utf-8?B?U3BkRURhTTZETEkxQXQrOTlYMDVqMkhmVW52YWkzSEllRDVyalg3R3Y0L0l0?= =?utf-8?B?VDhVajJJdTBDWTVxemc0Z2NOVjFWQkVTc3BxMVV5QXNObmVybGxsVkdwYnZ1?= =?utf-8?B?Tk9uK1p4dDZ5ekNZM3NwbXB2eHpUY0hLZ0w1QSswb3hrQ0F6Nk00NmlIRFBM?= =?utf-8?B?Snk1Q2NWUVUzTHhoT042MFI1NjRpZXlNUWpmR0RuQmU0QVFBbzZmUmZQTUtQ?= =?utf-8?B?OGxGZG10d3pXZU5PY0FKSE9BNm9jNmtOYnBPSjRteWRxUG1zNldUa2pDdjFX?= =?utf-8?B?SHJLYjJyRjZWSzcxdGVOVC9qaDZGQ2dCRW1pb0NHNFhDT05kMk95TkgzVG03?= =?utf-8?B?OFRDaXlNYU5mKzlhUEJaQnptVE1sZmZLZzhoYWZvTnJoRXVUaFVjWGE5R0l5?= =?utf-8?B?c3NVdHZrUDFJRS9ML1BKWHBMM0tadW10UklTQ0tXZEkrVTUyTDVMTlVzM1Fi?= =?utf-8?B?TGp4ZXhZeTFxenhWV1VULzBteVJMYUt2TkswYll4N214bTBQMFVxT1N0aFg5?= =?utf-8?B?dW93bm0yR2VXUWE0WFUyMjQ2dXlmalhRVytqbEJuRzVVWHFFMmVPT0RRN2tu?= =?utf-8?B?NjF3NzJNeElVN1hoN044b2NWeVBmK1dsVS9sQ0tMQ2hMcFEyVGFaT3dhSExB?= =?utf-8?B?cjRlS0FGYjIwcnp1aThYeVJNVEUyUm5jbEpOVHFrQkxvSVhSamZ2YnZnN1FD?= =?utf-8?Q?LWwYvRwNDv8y1aJUuW4yOSZW6h78WgU+NVeKM=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BN8PR04MB6289.namprd04.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(376014)(366016)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?utf-8?B?MXE3eGJxakVsV2t3QmwyRGx0WGNqVmRkRndJTzJ2L0l4SXhDQU5SbmRYZWNG?= =?utf-8?B?MmRUVVUydHdxd3hPcUZwWFY3ZVdNZjc1dXlkbTZSNVlNUFhOTm5NZzYzWDhV?= =?utf-8?B?TnQ0UGoxRWpkem5kd1dmM05rZDVKSTV2aFNBeUpWYWVrWHlEK3daZ2hqcDkv?= =?utf-8?B?Vi9xTzhGUEZtalZWUEdCU2hBQlZ5amZZTFZ1SXVscFFLSHZ4UWV3UTFhR1Ja?= =?utf-8?B?WjlmOFBXaDJoRmZLbC9FblNZaXdVd2xaOFhLd1UzeUViN1l2MzIveEVYOFBO?= =?utf-8?B?U004NlpCT1kwWFhxTUlnVjBZRGp1TmpvcDRUa0EvSDdNSk10M2N3M05keExs?= =?utf-8?B?MnZzaVlGOGpobEllK3JERzJYbUN1M1NhNUJEZFA1elpoSEVub1E1WmJoMkVj?= =?utf-8?B?eU9VSjZWS2ZYYXFjTlpaa2xMWU1uY1N5TFR0UGtwdHNoeFp0SnAzODNsU3FS?= =?utf-8?B?cGVnUUZ6SHJzN0E2bUZsNHlxUG02Ri9zSkFnM0E3WXh5NzJiSkUxN0svUU9m?= =?utf-8?B?bFU1b3ByMkd5Q2tYclh6K1lYalVnTmtzSEphcEJCV2NTUGRjT0NZZkgvY1FG?= =?utf-8?B?eTUybHRBK2JrQ1dkU1puYStRZVd6bkhDQ01EbDM2MXNITWs3L0E0S0xRL3ZM?= =?utf-8?B?NDdPbDJiNk05cGVxOWJxai9VdUg3VU42cTExLzNvcDVSTUlFcE9TY05nYlZi?= =?utf-8?B?L2NTQ05TekhqWmNod3F1eG9wTm9LcmJ3WFNLTnRBYXYxVG8wYmhzT0NQMWJZ?= =?utf-8?B?K2Zib01GNTlQam5EenhQWlJRSEwwSnNUT0tMU1B6VGVhK0VNanhnYnZaaWNt?= =?utf-8?B?YmdYdE00bzVROGF5TXJRRFNSa29ZS1dneWdmd255dnM4cjVJMzdMM2JVTzkw?= =?utf-8?B?c25ZdXU0cnBKT0h5cHBlSTVtdmZzQkN6ak9ObEphRzBuamt1S2VtOVZjTEU1?= =?utf-8?B?U1JYYldudUdmL29rSVhCVHNSVTA3NHNQNnJOcXRNL0d2SjIrWldjbFpjanFQ?= =?utf-8?B?MzdtelRPcFI2WHN4NEgreEZJS1JjU2xkSmVQM0ZlVGcxVDhuUnNreHZFVzhG?= =?utf-8?B?Qy9RMmo2WCtsYmNWN3lOSUd5WWR5bFRPbnIyS3c5cXo4SWUrZzh6eGRKZmpl?= =?utf-8?B?TW9aSnkrL01BMXczK1RxRVM1R2IwQW5IQTUxdGo0U0hzMEY3R0dxUFhKRW96?= =?utf-8?B?NVpCMkpJN2hyekNIdlpTWFRMZDhwc3V0S1Y0QzZnbWxnOE9LRGVqYnVOSjRJ?= =?utf-8?B?cGRsQ1U1ZjNNT2dSakcvMHFSdkpTSnBkVmNON1kyaFpEaWhaSDJFbnhhQ2Z5?= =?utf-8?B?bHJnYlB1Z0FJd1BtMjNsK3c0bEFWdjYzM3lJL2E2T0xHdFh0UzM2L1IvUUd3?= =?utf-8?B?V2dLM3VjT2JURFV2UXRtNUNkUkRMc05ZRkhUR2xaZ3hWNW5NZGsxS3ppbGtC?= =?utf-8?B?QUhMYnJaMlNLRzg5WDNtS2h1dU8yaVdodGlqZU84MmFqeUdxVFNrLy81SWpB?= =?utf-8?B?ejFJTXJGby93TWRPVGNYRldvUXVYWjJRVTR3WW1RblB4cHJkaVZTUW1HMnpK?= =?utf-8?B?NVFheXIycUVtNkhDK2dPcStpYzZpc1h0Y3I0LytaSG9oQm05MnViMytvc1Qx?= =?utf-8?B?SlhhVzA4SGtuaWJRTHpIdFRqeXhkM2N0RlcvY0JoVkRrTFprT1lJRmpURnJ6?= =?utf-8?B?NHE0RXc4NVJHd005TGRxamN4MktvTXRFRDFpdGdRTi9BblZGb3FFQWI1dDRK?= =?utf-8?B?TDVHR0JyOFNFeitBQm1HRXBHek1JVU8yUVQ3Y0hXb01ScHNKM3F3L0daQkYx?= =?utf-8?B?VTJUS1hSSzUram1zcU91MklPb2p3dmg0cjZBaEVoalAxQ3Z5THhGaWZ6QVNX?= =?utf-8?B?TytlMjlWL1N3cnlzRTNKQzhRNUZZaHlwRmN6b3gzNy9uU0JQclFvTUcybmd3?= =?utf-8?B?VlhISTQ1dEl4ajVEWTZPdkxtQzdLcFg3Si95ZWd5QjdEZ2dncXlHeDRxdVFY?= =?utf-8?B?aW84YzdOUHZBbFVONzZ5S1hIOTRQa1RvNEJBd0VnTVc1ZVJKbGt3ZVZkVER4?= =?utf-8?B?ckhMOTRuejVuTFdVWUwrV05lZUlSaVFVbm5WMnNFNUYxdzFNS0JnblNQaE4r?= =?utf-8?Q?QsAuXvgp3AKIFeXE0LuO4pFeI?= Content-Type: multipart/alternative; boundary="_000_BN8PR04MB6289DE86BD158D8785B302C5D0DF2BN8PR04MB6289namp_" 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: b584d6fe-e4e1-4866-554b-08dc9d19dae8 X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Jul 2024 17:42:35.1955 (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: tuPUS9aemi0vkD+X7F/Tkg46PKJuxfWzZhOFI8z3u/XqQ8LL96zEdnn8TzjQPKj7cHwpO3ZQOmJMAr7ouet04kQA8qQJPRH5z445IjMBVoo= X-MS-Exchange-Transport-CrossTenantHeadersStamped: SA6PR04MB9566 X-Proofpoint-GUID: yFWrpcRwGZ2uUcfSai3AOm46YT6lys5b X-Proofpoint-ORIG-GUID: yFWrpcRwGZ2uUcfSai3AOm46YT6lys5b 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_13,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=999 spamscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2406140001 definitions=main-2407050129 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BN8PR04MB6289DE86BD158D8785B302C5D0DF2BN8PR04MB6289namp_ Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I was checking pg_roles.acl_default to see if my role-level ALTER DEFAULT P= RIVILEGES had been effective. But I see the same content both before and af= ter the ALTEr. You mention that this needs to be done in each database. Is there a databas= e-level version of pg_roles.acl_default that I should be checking instead? Thanks, Mike Tefft From: Tom Lane Sent: Friday, July 5, 2024 10:51 AM To: Tefft, Michael J Cc: pgsql-general@lists.postgresql.org Subject: Re: Removing the default grant of EXECUTE on functions/procedures = to PUBLIC "Tefft, Michael J" writes: > I am trying to remove the default grant of EXECUTE on = all functions/procedures to PUBLIC. >> From my reading, there is no straigh= tforward way to do this. For example, "Tefft, Michael J" > writes: > I am trying to remove the default grant of EXECUTE on all functions/proce= dures 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 appl= ies 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 privi= leges for every existing role (which I expected), and ensure that default p= rivileges are altered for every new role that is created going forward. > Have I analyzed this correctly? You'll also need to repeat the ALTERs in each database of your installation. regards, tom lane --_000_BN8PR04MB6289DE86BD158D8785B302C5D0DF2BN8PR04MB6289namp_ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

I was checking pg_r= oles.acl_default to see if my role-level ALTER DEFAULT PRIVILEGES had been = effective. But I see the same content both before and after the ALTEr.=

 

You mention that th= is needs to be done in each database. Is there a database-level version of = pg_roles.acl_default that I should be checking instead?

 

Thanks,<= /span>

Mike Tefft

 

From: Tom Lane <tgl@sss.pgh.pa.us= >
Sent: Friday, July 5, 2024 10:51 AM
To: Tefft, Michael J <Michael.J.Tefft@snapon.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Removing the default grant of EXECUTE on functions/proc= edures to PUBLIC

 

"Tefft, Michael J" <Michael.=E2=80=8AJ.=E2=80=8A= Tefft@=E2=80=8Asnapon.=E2=80=8Acom> writes: > I am trying to remove the default grant of EXECUTE on all fun= ctions/procedures to PUBLIC. >> From my reading, there is no straight= forward way to do this. For example, 

"Tefft, Michael J" <Michael.J.Tefft@snapon.com> w=
rites:
> I am trying to remove the default grant of EXECUTE on all function=
s/procedures to PUBLIC.
>> From my reading, there is no straightforward way to do this. F=
or example,
> ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;<=
o:p>
> Does not apply this across the entire cluster (or database) but on=
ly 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 defaul=
t privileges for every existing role (which I expected), and ensure that de=
fault privileges are altered for every new role that is created going forwa=
rd.
 
> Have I analyzed this correctly?
 
You'll also need to repeat the ALTERs in each database of your
installation.
 
            =
;            &n=
bsp;            =
;  regards, tom lane
--_000_BN8PR04MB6289DE86BD158D8785B302C5D0DF2BN8PR04MB6289namp_--