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 1sPntx-006OO9-N9 for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 18:43:21 +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 1sPntu-00Cx0C-Ju for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 18:43:19 +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 1sPntu-00Cwzw-5a for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 18:43:18 +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 1sPntr-000b6C-DW for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 18:43:17 +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 465E14l8012877; Fri, 5 Jul 2024 13:43:12 -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=VPxmuGnMwIp1157g+78FjGynFAX 5MAIfQodTukOmk1c=; b=uvVhlsnW3Uep5eDCfKhpD+fBpm0DG2b1JAaqmuuw7RL GKFow86BjdDFKn5XbgYtOU4ipYrf/Wt3qPsOa0JUuJm0ukkvhzlm3OF62b6RoBoB KPwhHNLXiZC80eDfvtKZN2Bki294A678NfU1kQlaaZx5paMc7l7y7IL/Lvnexl7W QOEKW1iX+OZXNSbyz4d6QQyTEMulIbdQJCsm4HWgs6Odx+qb9dt9jZ91YgnzMlAd mXJhLoypYy6mS5PlyNgpGwAmPOlqTg1LsbVz6sptoUecO2NLcjfyazyNXp5ghUAu pySaP+oafqQvkumGX9yvIHrvBidkzxi9PV2aSa3R3Pw== Received: from nam12-bn8-obe.outbound.protection.outlook.com (mail-bn8nam12lp2171.outbound.protection.outlook.com [104.47.55.171]) by mx0b-004aa801.pphosted.com (PPS) with ESMTPS id 40326h37ms-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Fri, 05 Jul 2024 13:43:12 -0500 (CDT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=Lup4NgsauRFTLeJ9MLaN5nG6s3ikNycYo8vwWM8dORVSqu6xfCg6BWsYFK/CFSBizjbNP0ioixufZTALHPTaknJGQOFqfy5r05VQ5aerAtcbPyEMNFWiThw7L7KCvmXa4HoNhvzGI9pL7XduwIPO1+I9Vf+b7+DYKx2h5mUo+H6Pcj0XqYJQEr4XLEltzZy/jGidU/nhiIQnpc0oGfCJrSOz2z4L6dL76oPjLAUavXGL+Gi2fVtrFcUJIrS398OF+Ok1QcibI7G1+JXeUM5w26/C8jDcOtfsP2Mf9fbZ2jP9tP2WRyHnrVdtuy9lIq3BJ48gvnAXmDD7bcSDUdLcsg== 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=ChcLfC6Uc3zi7QouOM+hy762uJP2CcCYKwRAi8bouDg=; b=ktA1cuDF/xj65NLlC1rgSEqsfLzHKsc6f46WIt3XdSqJ0yHYBkuBzI9L/LLpPblzojHe7H8JUeLu9J7HhJ9UBbNYbbcCJ5iqMDafmC1CzLcz9/BEU15IQi3rdLsFTOEb2dkFWRPvjhIsHHooZUyzg4XO7RuxNSL8YNf7VVlo1tZKKqg9F2F32pEhogopfB077W3i32xAMNxY+dCqMgaJJ6XL0aCmH/H57+6yRKtyQUt5OctO6hL1dz2p/nUPxpQhgpyUXEPl9KsSashaaGbQX2GihGgGkl6MwnUWYZWiRTZcFokW/W2BGOrxoBeoqwMzdv01+W7ulBfTuoAb4lcGZA== 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 BL0PR04MB6577.namprd04.prod.outlook.com (2603:10b6:208:1c4::11) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7741.30; Fri, 5 Jul 2024 18:42:54 +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 18:42:54 +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+ZIhRy266wuZu91e0gAAhgWAAAXde1AAAYINAAAAQfuQ Date: Fri, 5 Jul 2024 18:42:54 +0000 Message-ID: References: <201663.1720191061@sss.pgh.pa.us> <326041.1720203728@sss.pgh.pa.us> In-Reply-To: <326041.1720203728@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+PGF0IGFpPSIwIiBubT0iYm9keS5odG1sIiBwPSJjOlx1c2Vyc1xuajM0MTdcYXBwZGF0YVxyb2FtaW5nXDA5ZDg0OWI2LTMyZDMtNGE0MC04NWVlLTZiODRiYTI5ZTM1Ylxtc2dzXG1zZy02MjY2YTNiZC0zYWZlLTExZWYtOWQ1MC03NDNhZjQ2Yjg3OGVcYW1lLXRlc3RcNjI2NmEzYmYtM2FmZS0xMWVmLTlkNTAtNzQzYWY0NmI4NzhlYm9keS5odG1sIiBzej0iMjExNjIiIHQ9IjEzMzY0Njc4NTcyODc5NzcwMiIgaD0iWktQR0FseWlUMWM1VVZVTU1DWnVOVVg4RnhvPSIgaWQ9IiIgYmw9IjAiIGJvPSIxIi8+PC9tZXRhPg== x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BN8PR04MB6289:EE_|BL0PR04MB6577:EE_ x-ms-office365-filtering-correlation-id: e4a4192b-193d-4579-9a2b-08dc9d2247f4 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|1800799024|366016|38070700018; x-microsoft-antispam-message-info: =?utf-8?B?b01jRDRYblN2d2ZidjRZVFZCNEFPeXBITXJXTjV4QWVHYVdCZytEZGpwVlky?= =?utf-8?B?cmZwK25NWXVaMEFTSnpvdDBzV1JUNjFIMS8rck9LcDNKOU9WOWk5UDUwOHkz?= =?utf-8?B?ZEhZS2RUNXgxZ1VEU2p2Nm8rZnViYnRCSG5iWFoxSDRkNzVDWC9TTHRyRjhy?= =?utf-8?B?UjdZNTR4aVEwRDB4MWZ5U1ZrYjYxRnR3dFk4VDd1R3VCMUFDb2JmRDJnb3R3?= =?utf-8?B?QTd2TmVHNy9uZWRXTlMwVFZ1YnNId0JpNk9pR2tLNXMxcEc5MkpZclNDM25Y?= =?utf-8?B?elNCZnhOdGpydGNWMnViY05OQ0xWeHgvc0YzZ2hVUU04SmJ0L0JhRk5IczdW?= =?utf-8?B?aGVoTHJsY3M0YmVoWkJkUGtNZFg1SXFnd1FtYnFLU0lRWkFSTWVJUVFlM044?= =?utf-8?B?bEE4SzY3cjJYSSs4OXczVWxrdlN4WjBYMDRYMW9GZlNkNFkvMjlDbFhNbDcx?= =?utf-8?B?cTEyZnNWMnloUlVqdlJmQUs3ZEJVU1hMUEQyMTVkd1NzVVNPVjgzT3A0YnJM?= =?utf-8?B?bmx2MGoxUmhuZ2RFZXQrU25hTi8rc1hJcnFTZG5IUXoyTGVGdnZRYlQ0ZEx2?= =?utf-8?B?SVFTNEJTdUVpMll2aHJRQWE3b0pjdVk0ZEJTdTYxRHVxUDh4dEszTFlSYUJM?= =?utf-8?B?UC9NTlkrNE83a29aMk9LTHprUzZBNVhjMldRK1VnYko5M3Rsa2ZIVEZPMkRz?= =?utf-8?B?cENVL0E5Z04yWnoyVUR3QVc4a21XRk1OS1ZkYVVudXJIWUIxY0tDbTNlOTlp?= =?utf-8?B?ZXZ6YnlRMjJzYkJpY1lWeVNEMDlHYkpmUGpaVFVjay84ZUdBMHNPVHpXeVYr?= =?utf-8?B?TVdkN1pkNURkaXhqNzJWaXlwb0JoaUlIek02RlVJeEdNanFwdzhkNXRtL2RV?= =?utf-8?B?Z0tud1NwWHRlaWp0Q291bFFDZS9BNndNZGhIVEo3ZTJXWFROanpRRU5ObUJm?= =?utf-8?B?NFF0UzlkTDhZdXl6Q2xOOUJmK2s2Z21veWk0bGs1dFMrTzFRQmVJc21OeXlW?= =?utf-8?B?bld1SksrWW50S3dBT0hqT3hrRjN4clo1VVVkY2JBZExWNFlRTHJ3b2hLMmlW?= =?utf-8?B?RmZLRVQrWGtQRnFRRnEvd0RsNEVEV2YwZVhkcjZYNkRFa2o3MU9qeVVCZlN5?= =?utf-8?B?VGFsSjQwa1NqdVZlaG0vTDBCcS9samljK1JiMTZFcmIrVEhQS1B4cnNwR0dn?= =?utf-8?B?RzkrQWVJbk9ka3Foc25wU0tHQ0haYldDNkhEQU9WVkh2TUhtVEdHZnYrUEhw?= =?utf-8?B?eEhMcVF1Q2dVbVFJRUYyTkJkeFZZREFZOEwwRDFpU1BRV2IzODg2N2x1RUYy?= =?utf-8?B?ZDRoYWZGU052MEZYL2ZCME1BaUMzVmNGY0lLT0t3K21BOTQvRkJ1K0tmYlIr?= =?utf-8?B?RlFiQlVPU3l4eTVBS1JBNkRURU1Ybjc4a0E5ZXRkbGNBcTBpWE5tUEhNdnRW?= =?utf-8?B?UGFaYjhuY0ZoakhKdHpjOUIrSS9yNHB6dzlBdEVPWTU0Y0hkQUU4cGxNbzBp?= =?utf-8?B?c043ZjJjditCUlQ2WHJQbTZaQlNwdkpQT2FUZDVVRVhhcmMreFhERnF5NFAx?= =?utf-8?B?VHVnWW4rdFpITDJucTh2bS85eGRKaTRVU0VNTmFUQTN4OVpKOG8wcXVPOEVS?= =?utf-8?B?cEI4U0JmRFNtZlp3cGIvRmt6enFGcnZzNzZ3dkFYYnROWnowVS9FQXJiRk5h?= =?utf-8?B?elZLNmRpVHRwaTVDQldJNkJIWnh2THFMTGExbWpQeTRBT3cwUGJkM2EzNUYz?= =?utf-8?B?OFFjRmNYMElvTktnekxXa3dHcldqcGZGT3liUDZ6WFZXcmRLU3VwejJBeHZX?= =?utf-8?Q?AROs62K1tQSWARFVxQSOb9/q+/uZaZYgKTI70=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)(376014)(1800799024)(366016)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?utf-8?B?am9DTko3Y1N2WE5wZTRRKytzU0lreGZ0QWcvUHFKbysxTEltZm1CUzB6ZVll?= =?utf-8?B?QTNpelR2cE0zSFM0cFYxdDcwZi9RVmQvOVlsZHkwcElYa25xVjN2VHNKMEVx?= =?utf-8?B?MkU0QlkwVmtDNjVWTm1iM1JPN3JBWHdGUTh1d1JGNDRuaWw1SEEybE1CalIz?= =?utf-8?B?cW9jUVFaakl2NG1qajRsNUEweXJ6ZUtaanpoRi9aeU9hdTc2VUhvT0gyOU9C?= =?utf-8?B?VjJZL2VSZnhjYlVzNEVuOXExZDJkKzZoMm5YZDVsNGY3dUJoZVNVSGJqdnpX?= =?utf-8?B?VWJFSk9LbWNmQURhWGVhTWhXRFBNbSt6cWhVSTcxS0NNTDBUTDJyOXVWaTdy?= =?utf-8?B?dFRmTTJrUFBUbEgrQmtKK1NURFhNcFBkc3lNN0gwWU9sSnRuaVhYd2Z5OXp3?= =?utf-8?B?V1hyUkVYcGN1NFFLSk5GRWtIYnRkWlFhanB0OExnSkIzaHI2MklFSHJsbVQw?= =?utf-8?B?Ykhwd3NXRm9SRTE3V0RNR2taVlFXOCtzd2N6eXplSVh3Z3hTWnNjQ0htS1BJ?= =?utf-8?B?MS8yZ05NQ0RraWZnVkVpdi9OVlNvcUkvMDltd2Y5VFE3YWVuckRnMUFNNVFI?= =?utf-8?B?ODF0SVBPR202cU5BNTVXOS8xMTA5ZDMxcG9lRitIOXg3UmtPaStYeW51RXBC?= =?utf-8?B?ajczYlBKSGpsM2MxTGR3NjRVcm5YSk5VdmF0bjBvMFhLejJrNURCN2RuSWpo?= =?utf-8?B?Yi9BYXF6MkZsOFd4TlJaMjQyYVZSUVRBT3JqdW9OUk1QbVJVSkRxWjZ1b2hK?= =?utf-8?B?QnVzNUgyaEV5MWNFYzY4SG5qZGc0RXVzdDkyZWlDQkJoV2d3enRrblc2bHV3?= =?utf-8?B?NnM2bk12eGdpNFRwSktXaHB5ODcxZFlBVG1PWk45UnZmZ2dkN0MwWWM5Vy9x?= =?utf-8?B?WEQrU1ZvRTNFbE10QU80YWw5Wmd1V1VRSUUwNW5FKyt1QUkwYndaOStKbUs4?= =?utf-8?B?SVhoWmJEUTF0bVMzRXJVNFAvUU4rYS9VTlRlRFF4bkNkeW1nbUFFUzNxUnZn?= =?utf-8?B?V2V5WUxkRmVKeE13MVE1Y21xVG0vbW0vaVBSczZBR05hODF5dWc5RHd5SWZJ?= =?utf-8?B?YTFFMHNNeUJDVFh2NmJZb2dyREVpZmFQZmxTK0M5TlNncFN0Zk9JL3pFdnhv?= =?utf-8?B?Ky9SOW9xR3ZXbm54OW1QODNtdmo4WUZtRFNXNzJaWE5oazIxdDdWSCtTQTVv?= =?utf-8?B?VnBmU0V5ekR3aXFKbk1SaWljR0pDQTlxdndsRTdpM0h2LzUxTVgxUzkwSEFE?= =?utf-8?B?UU9lcDNTU1JPdE93Q1ByQ1B3b3RJcmZHYXJ1Tm1KMmNoSjVMUWpFYlo4cjBy?= =?utf-8?B?MDl6OUtFR090cGJsN2pOY1RLS05aa0F0S2pSNklkaGRiRXVGNjZmNjBXUzla?= =?utf-8?B?aW1QbVdzQlVvdFdEVGh6am1UTlRDQ3ZTQ25ZMHo2VGlVcWNnOGVrRFE1WDNB?= =?utf-8?B?SENHWHRUam91ZlBLcXJLT0JCZVh4VXdJbGVxUzRkZEdld2kveDRLNGovUC9E?= =?utf-8?B?TkE4SnFoQTdETjk4dnFvRzVGWWlYTmp5TnhoZ1FhNjNQYTFHMUhsZG9HYmc2?= =?utf-8?B?MFF2bWlYUGZaVzFENVFDZmRHOWxsdnpjbWxYdFJjQzFQbExTdER1WVNmVDZE?= =?utf-8?B?SFVPblZPMERuVlBjbjI4UVJhNE80aFkzb3o0RldKUUlYWlhlYzM1cGdCOEJ1?= =?utf-8?B?S21oMHZ4aFdoQ2tOYkVnOTlMRExDMzJUWVBjeXErbndlU2NPRkE5UlhlZmR2?= =?utf-8?B?NnFqanpLb25SanhUREpBcEY3ZGVwb2czU1FHOTVkK0wvVVVEbmZieW1lZ2wx?= =?utf-8?B?cysya1VUeHVFTll6SlZOcmoxRmU0WFFnU0xZWkpTUlhHRGNSR1UwUk0zZS84?= =?utf-8?B?ZHl2Nkhxb1NKMmFVZENwUWdUbVlaL3JCSzFkTmFwSTIxckFhVVVYbSt5eHVr?= =?utf-8?B?eFl2SVYyVWJ6eCtLczJHNVRQam43YVBobjFVZUJFWnY1VnZUWEwwY0tHZlhm?= =?utf-8?B?bzk5Um1Udk43cXpOMUhKd2p1bGhjcTdPNnpFY09IMEtQMHJlc0hLZ1VPQWti?= =?utf-8?B?ZWJyWFZ0RzhhT2w4ckE4UW91UTk4V1dsNjVrZjJBZjZ5eTJTbFNtOGNSQjI4?= =?utf-8?Q?ojMB4qDuHwdDHQ7+VUEDOtP77?= Content-Type: multipart/alternative; boundary="_000_BN8PR04MB6289726F36244ED95C4FD3F3D0DF2BN8PR04MB6289namp_" 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: e4a4192b-193d-4579-9a2b-08dc9d2247f4 X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Jul 2024 18:42:54.0997 (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: qlbeQUA7DWcmgxNyZWFkuqILAvnUs55QDxFy+PQDinLKobls0zSJdPALF5vhjK9T+UXxycJ846OLOKJUvnDeJ5dWWl0umaiSOxV9Nho8U10= X-MS-Exchange-Transport-CrossTenantHeadersStamped: BL0PR04MB6577 X-Proofpoint-GUID: jKPJqCyFKtkpPSBh1rgD9DlBg3Z6eXh9 X-Proofpoint-ORIG-GUID: jKPJqCyFKtkpPSBh1rgD9DlBg3Z6eXh9 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_14,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=1015 mlxlogscore=999 spamscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2406140001 definitions=main-2407050136 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BN8PR04MB6289726F36244ED95C4FD3F3D0DF2BN8PR04MB6289namp_ Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I apologize, that was sloppy. I was using the acldefault() function with pg_roles, like this: =3D> select rolname, acldefault('f',oid) from pg_roles where rolname like = 'mjt%' order by 1; rolname | acldefault -----------+-------------------------------------- mjt_test1 | {=3DX/mjt_test1,mjt_test1=3DX/mjt_test1} mjt_test2 | {=3DX/mjt_test2,mjt_test2=3DX/mjt_test2} (2 rows) I had issued alter default privileges for role mjt_test1 revoke execute on functions fro= m public; but had not done a similar ALTER for mjt_test2. And so I was surprised that= they both showed a default =3DX/rolename. Examining \ddp and its underlying quuery, I see that view column pg_defaul= t_acl gets a new row with defaclacl populated after the ALTER DEFAULT PRIVI= LEGES. Thanks very much for your guidance, I am on track now. Mike Tefft From: Tom Lane Sent: Friday, July 5, 2024 2:22 PM 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 was checking pg_roles.=E2=80=8Aacl_default to see if= my role-level ALTER DEFAULT PRIVILEGES had been effective. But I see the s= ame content both before and after the ALTEr. Er, what? "Tefft, Michael J" > writes: > I was checking pg_roles.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. Er, what? There's no column named acl_default in pg_roles, nor any other standard PG view. psql's "\ddp" command is the most usual way to examine current defaults: regression=3D# create user joe; CREATE ROLE regression=3D# ALTER DEFAULT PRIVILEGES FOR USER joe REVOKE EXECUTE ON FUNC= TIONS FROM public; ALTER DEFAULT PRIVILEGES regression=3D# \ddp Default access privileges Owner | Schema | Type | Access privileges -------+--------+----------+------------------- joe | | function | joe=3DX/joe (1 row) regards, tom lane --_000_BN8PR04MB6289726F36244ED95C4FD3F3D0DF2BN8PR04MB6289namp_ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

I apologize, that w= as sloppy.

I was using the acl= default() function with pg_roles, like this:

=3D> select roln= ame,  acldefault('f',oid) from pg_roles where rolname like 'mjt%' orde= r by 1;

  rolname = ; |            =   acldefault

-----------+-------= -------------------------------

mjt_test1 | {=3DX/m= jt_test1,mjt_test1=3DX/mjt_test1}

mjt_test2 | {=3DX/m= jt_test2,mjt_test2=3DX/mjt_test2}

(2 rows)=

 

I had issued <= /o:p>

alter default privi= leges for role mjt_test1 revoke execute on functions from public;

but had not done a = similar ALTER for mjt_test2. And so I was surprised that they both showed a= default =3DX/rolename.

 

Examining  \dd= p and its underlying quuery, I see that view column pg_default_acl gets a n= ew row with defaclacl populated after the ALTER DEFAULT PRIVILEGES.

 

Thanks very much fo= r your guidance, I am on track now.

 

Mike Tefft

 

From: Tom Lane <tgl@sss.pgh.pa.us= >
Sent: Friday, July 5, 2024 2:22 PM
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 was checking pg_roles.=E2=80=8Aacl_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. Er, what? = 

"Tefft, Michael J" <Michael.J.Tefft@snapon.com> w=
rites:
> I was checking pg_roles.acl_default to see if my role-level ALTER =
DEFAULT PRIVILEGES had been effective. But I see the same content both befo=
re and after the ALTEr.
 
Er, what?  There's no column named acl_default in pg_roles, nor an=
y
other standard PG view.
 
psql's "\ddp" command is the most usual way to examine curren=
t
defaults:
 
regression=3D# create user joe;
CREATE ROLE
regression=3D# ALTER DEFAULT PRIVILEGES FOR USER joe REVOKE EXECUTE ON =
FUNCTIONS FROM public;
ALTER DEFAULT PRIVILEGES
regression=3D# \ddp
           Default ac=
cess privileges
 Owner | Schema |   Type   | Access privileges 
-------+--------+----------+-------------------
 joe   |        | function=
 | joe=3DX/joe
(1 row)
 
            =
;            &n=
bsp;            =
;  regards, tom lane
--_000_BN8PR04MB6289726F36244ED95C4FD3F3D0DF2BN8PR04MB6289namp_--