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 1sog1j-00CyLN-MJ for pgsql-general@arkaria.postgresql.org; Thu, 12 Sep 2024 09:22:13 +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 1sog1j-002Z5H-Cc for pgsql-general@arkaria.postgresql.org; Thu, 12 Sep 2024 09:22:11 +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 1sobl6-00FGQy-3e for pgsql-general@lists.postgresql.org; Thu, 12 Sep 2024 04:48:44 +0000 Received: from mail-me3aus01on2160.outbound.protection.outlook.com ([40.107.108.160] helo=AUS01-ME3-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sobkx-000l2B-9B for pgsql-general@lists.postgresql.org; Thu, 12 Sep 2024 04:48:42 +0000 ARC-Seal: i=2; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=pass; b=ieqUBJ4PfZaPm1iHlmCTq6OvxnbfN++WPS5DlPXx68i04pvSjUZoTbA2F5fV8Gy/uiZ6/oXs+dXrLfNbCPs979W/C6eo2tzOZbL8IkgREIRfzvFm8r71lItLCSOU6gNK0XZD8MgBXRXDpaUZ6NIOdBGR1HahTdWqhFhtCVuSNHqFct+bxm5p1gZUxUOtsvirgVnRdiiG3U2SofqJOrC+zwZoYdjH5fYD3T5lrV0G4xi7dd58rTPEnKaSey2IntEus+G635yqDcboypXy/6Q8+50vyTUGFhfJahYfB94tHAAUKuwV7scG6Mp5PGBpf1u2VocKfaaM6ds4kKtizsB6Bw== ARC-Message-Signature: i=2; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; 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=ENBqHTCM0GhJePPPVd4UkdWDeUjyPAhgYrJiRoWP4GQ=; b=IqkZWS10Asx2GX2PucMvsESCuj4K6Xbaanr0z0ICLVPkSbjhMiH2QQIyZslwrcbYjlqe0vq0CMXA5ZeSkH+ezK7VN0fYvG7+AyXtvwpgqDEVIc5/ivQexq1uxesutjeVbFCDBHMq5WrqS1Uyv0ctgb0Fmk9GCpWKU65PMJ+EqLXf11TGDfrKGAuzOQSB7IZPV6rO9PQS2TjpuvpQbYj19YyfwqinMA+Da/ZaeJ9SHpzHSl8Qx5zwXwN7ISP5LT8tEmjTzQiUaf3D4+Yp5FA0kaE/impGzRKjjHKDrtgol+G77obdxQc0XUOQI4qDmDKjLbii72xCLMD0lLcPwsKeLg== ARC-Authentication-Results: i=2; mx.microsoft.com 1; spf=pass (sender ip is 104.47.71.168) smtp.rcpttodomain=lists.postgresql.org smtp.mailfrom=niwa.co.nz; dmarc=pass (p=none sp=none pct=100) action=none header.from=niwa.co.nz; dkim=pass (signature was verified) header.d=niwa.co.nz; arc=pass (0 oda=1 ltdi=1 spf=[1,1,smtp.mailfrom=niwa.co.nz] dkim=[1,1,header.d=niwa.co.nz] dmarc=[1,1,header.from=niwa.co.nz]) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=niwa.co.nz; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=ENBqHTCM0GhJePPPVd4UkdWDeUjyPAhgYrJiRoWP4GQ=; b=Uk/MGlw9qMY0bTZTlZprhWgMG7bsDZdq/gjM77AqKuvo1Mp/auvi2bXdv4Ic459jTDpir8eyqgsd92RXgiNlmGt/YuTxzqyIaGhGQ4V1nm3XFsX6bgm/Ln+OCr9LXnu68xNCFL4b/qj3XqCg4fP4ABkCndP6PzjkvLmyX/5dv1c= Received: from SY6PR01CA0148.ausprd01.prod.outlook.com (2603:10c6:10:1ba::12) by ME0P300MB0425.AUSP300.PROD.OUTLOOK.COM (2603:10c6:220:229::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7962.18; Thu, 12 Sep 2024 04:48:27 +0000 Received: from SY3PEPF0000A726.ausprd01.prod.outlook.com (2603:10c6:10:1ba:cafe::3b) by SY6PR01CA0148.outlook.office365.com (2603:10c6:10:1ba::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7939.26 via Frontend Transport; Thu, 12 Sep 2024 04:48:27 +0000 X-MS-Exchange-Authentication-Results: spf=pass (sender IP is 104.47.71.168) smtp.mailfrom=niwa.co.nz; dkim=pass (signature was verified) header.d=niwa.co.nz;dmarc=pass action=none header.from=niwa.co.nz; Received-SPF: Pass (protection.outlook.com: domain of niwa.co.nz designates 104.47.71.168 as permitted sender) receiver=protection.outlook.com; client-ip=104.47.71.168; helo=AUS01-SY4-obe.outbound.protection.outlook.com; pr=C Received: from hamlmail.dmz.niwa.co.nz (103.229.249.34) by SY3PEPF0000A726.mail.protection.outlook.com (10.167.241.22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7918.13 via Frontend Transport; Thu, 12 Sep 2024 04:48:27 +0000 Received: from AUS01-SY4-obe.outbound.protection.outlook.com (mail-sy4aus01lp2168.outbound.protection.outlook.com [104.47.71.168]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "mail.protection.outlook.com", Issuer "DigiCert Cloud Services CA-1" (not verified)) by hamlmail.dmz.niwa.co.nz (Postfix) with ESMTPS id AD5FF60E660; Thu, 12 Sep 2024 16:48:26 +1200 (NZST) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=t0a6MmmRZvvkSVEvnJkEGPtRd4fqJWzQwFE295PcwwWmKWfL64dl2P8Eokxjd+Z4q7Zu1tPPk4chJpVYFjyoB14d620PY+VghUdzCYT+xO/hgT96gBgNfSvbr+DSRK6p74YBeYKSTS4yOWLvlZtQ4j8pWcsnm5evhcW3UT1Y11s3hNmuMedqxjXfJp5CwwxeC2HMiWV//TKbw0Yx6r9/ezJlDwUx9ymVL7KkScJJcrR0wDu2UQ0GpHKiS8HX7tKpt8OKIC3oZUqOMpI4Dd43vyd8gOja3pq/jg41evWejbsPUFjhZOKiooNxiNQHdhhh/bGQzxnoTDNiHakqwmzTuw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; 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=aFgJwgnwCdMR2eqGHyJMkZtwRsb3mNKl0THD7k4lhc4=; b=Iit+WxbLD3wCHZ9ztvPswXyUQ+K5GQZX/pBeQWo44PVJYzLjuDK/qqG2ip1Z2FrwFLi0KunMcLfVNdSosCgtkxQ4ClT96B3cIJ0W+YXTR/FiKPMqUusL09V2StwcEV2jmHpHsHinrkgUOd8HN1V+9am4s9E3AnDNeJCmWWkoLxnkO0+SY/0/HqaOE9jBCkk14Wr1F2tXg1Sk2nmNukxNwQpf2vLtASpVtLTsuza6M+k0B//GLU/g4ZJeckuYSBzOFFOfXKXfasNbFpJyfnuCIBUJS7ByYFMtMn950cvUBQ6re0ReDy3nRlLbfor+FKgnamwtVM70cDi4NcW0NE2oUw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=niwa.co.nz; dmarc=pass action=none header.from=niwa.co.nz; dkim=pass header.d=niwa.co.nz; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=niwa.co.nz; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=aFgJwgnwCdMR2eqGHyJMkZtwRsb3mNKl0THD7k4lhc4=; b=n1JEWhIT8r8eitaY1CQTJrccAs2jwRrLArw4YE5yDUtwSklDQqSVJ09pAagubLrb5At8jdJ0SJdrFmVrThcwwclf/at0/9BbEunrlr6hYuDD+ZC1hViy5Q9ZyIfhuahkRb0vovz/hxzR0N7yhO3Ld+em/e6nfoBC0VnDMzhEk8k= Received: from SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM (2603:10c6:10:28c::10) by SY3PPF9AB546352.AUSP300.PROD.OUTLOOK.COM (2603:10c6:18::4a4) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7962.17; Thu, 12 Sep 2024 04:48:24 +0000 Received: from SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM ([fe80::da5c:b8f6:3849:775c]) by SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM ([fe80::da5c:b8f6:3849:775c%3]) with mapi id 15.20.7962.016; Thu, 12 Sep 2024 04:48:23 +0000 From: Brent Wood To: Andreas Joseph Krogh CC: "pgsql-general@lists.postgresql.org" Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC Thread-Topic: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC Thread-Index: AQHbBMWnf9hAXuNPkUGaVRlrp4U9tLJTkTOAgAABt8M= Date: Thu, 12 Sep 2024 04:48:23 +0000 Message-ID: References: <3952715.1726115805@sss.pgh.pa.us> In-Reply-To: <3952715.1726115805@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: Authentication-Results-Original: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=niwa.co.nz; x-ms-traffictypediagnostic: SY7P300MB0761:EE_|SY3PPF9AB546352:EE_|SY3PEPF0000A726:EE_|ME0P300MB0425:EE_ X-MS-Office365-Filtering-Correlation-Id: c36775ad-86f3-4bf0-78eb-08dcd2e62481 x-niwa-seemail: Out X-MS-Exchange-SenderADCheck: 1 X-MS-Exchange-AntiSpam-Relay: 0 X-Microsoft-Antispam-Untrusted: BCL:0;ARA:13230040|366016|69100299015|1800799024|376014|38070700018; X-Microsoft-Antispam-Message-Info-Original: =?us-ascii?Q?0SAcO36IACpjov/FTYhuHvfWM/hIaqwsIqyyBA6dIq6vhlusvg5FnoAciwZl?= =?us-ascii?Q?FS9gOnbgwYEs6/K8hMukVvN6NKgHSpPF4WSJMqo0scupGwzw5qOoShe5uy5T?= =?us-ascii?Q?WQX4lB5MhqY2mYISCBGQy+JC8sa2GoASjK7Tv3dMWtZ8yUUKlRqZavf4Yar4?= =?us-ascii?Q?Sj+R0ZqjDE944d+PBIVVj/GpoRGQsFcViMTgYZOlLK5HygNNCGnzWeWKejxw?= =?us-ascii?Q?FtAH2m/uW9oAylWRxsuzVRzL/Tp/aAoNlGmLufAP0UuS/26LsBXquu8TM62B?= =?us-ascii?Q?TIVu1S+nUonLXwQMj3l+Ech1xVL4OhTZ7EivaqfsvIQTn6MLsYynZQIi109y?= =?us-ascii?Q?u5T9b5/J2E64xV3Lzvii/3kJq/pNkG6l17ZE6PIR+Xq6432sq9naxwQpzmxS?= =?us-ascii?Q?kDUbTPM+0XC9EH1X4vXLlie8imdoBgK9zbxgFkAP4/8yafPW7/nM1nhz//xM?= =?us-ascii?Q?1RiT1LpUjP5vDqMJgoDLX2QNDynAuxqh37YXT025aXQfzGSj0orAXTjZkznY?= =?us-ascii?Q?iAK7XocD9axcCf9yUt0P1ORUAlucOcxPlw8cxO1duIYHuIFwZKffJ53wikdM?= =?us-ascii?Q?OcJi9oICBTkGBFBte0tUWwNsjnl6CEuEDwWUomwDH6GmfgPzUkwC/kMOdh7m?= =?us-ascii?Q?YpW3zDJ/y/m4XL8+lVvlUJ2nMXkAe4kvuVK+Z3gDD9YKTvSt3yyfBfaC/66Y?= =?us-ascii?Q?4TiJHdzBqwiombGOkODr+WyArmB5dXtnsirRj5DVLrYTtGKTMDEy1eovbUAG?= =?us-ascii?Q?nYYd/6egXZjp79dh3a52sN2Fa7eyOV5J0heXADr0yM4YPWdrfCHPppSdV1u2?= =?us-ascii?Q?0C1PdU7HpmrTxzl5LP3zjxuU/iekcGvR4mS4Uf/d/S9NSBMnsfpEWKzB3VNj?= =?us-ascii?Q?9vcrGPpHIbttDCUpfHaTHF+94EdtPVbAVOoUh0CAnTwtwU29/voS17hw+hNP?= =?us-ascii?Q?4fWGN/dMY8GXziEqTyajAHft2v05tTPYyWzQ6693ABE9RkWnDvrQJgm5q47c?= =?us-ascii?Q?xvtU/1VnuWW6jZNJBWWJlH8yz7HpD8HNzSjZstPDzZAyu7uM5UP4P2zi+nBp?= =?us-ascii?Q?D0ZZx6w2OyAvZzNeXbWtuyYgjsKzjLEEkDZE4TDutF/tQCq4KElNRN8UA4nT?= =?us-ascii?Q?jLnEtt5FNPx/Zl7+5fCzUMSLlNalIBtoARddWSwqkpns+6wWdA8Jd+gLovQG?= =?us-ascii?Q?AsqLXObUiI6ai3oixWV1HNRoJsOUUzPpK4hGJk5eTLd9KJ2ez6yoQtDGsh9g?= =?us-ascii?Q?B/H/oChNq42CzWCwU7Tjr06tyyqDmfz22sovIk3EMJ1STnTfUH4vSF3isDw9?= =?us-ascii?Q?det8VjCCPFeKBiqQUf0WWFjLgCwN2d6CZMsATf3r3T1/N7Flz8ndojumxiv9?= =?us-ascii?Q?CCVJDUcSO+lD7rddXne6RevNLUPsk/hduN+6I4sLbSZtO/UQSw=3D=3D?= X-Forefront-Antispam-Report-Untrusted: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(366016)(69100299015)(1800799024)(376014)(38070700018);DIR:OUT;SFP:1102; Content-Type: multipart/alternative; boundary="_000_SY7P300MB07611F2D0067FA7A417DCB29A1642SY7P300MB0761AUSP_" MIME-Version: 1.0 X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY3PPF9AB546352 X-EOPAttributedMessage: 0 X-MS-Exchange-SkipListedInternetSender: ip=[104.47.71.168];domain=AUS01-SY4-obe.outbound.protection.outlook.com X-MS-Exchange-ExternalOriginalInternetSender: ip=[104.47.71.168];domain=AUS01-SY4-obe.outbound.protection.outlook.com X-MS-Exchange-Transport-CrossTenantHeadersStripped: SY3PEPF0000A726.ausprd01.prod.outlook.com X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id-Prvs: 7561cf11-eb01-433a-8a38-08dcd2e62249 X-Microsoft-Antispam: BCL:0;ARA:13230040|82310400026|69100299015|36860700013|1800799024|1032899013|35042699022|376014; X-Microsoft-Antispam-Message-Info: lnmLD0fv/TPGUyEdex/96cQORrKX2lRVg15O51/1+o35wKJYCj5IttkooiH2RLTknBTJyzzDjNEPhMwj0ld/Rxk9JiN6t1y+RxMFPkb2WVoQGagJdqODcK0znBjSJXIBGAMvrjQy/nvYFKuJfIKAffYNtEojf54196MrS7w1otTZ5JnlGDC4bDwwofv+72lXmNlvrteItO8yiVvYxYQ2zg9SnHvmDCgErYU35/RqNn3l+gLirB49HGgtSpFqe7r1nrLxN/0KZAN+1EKXOVcot7G7mCwjnpF51BryhqJaXZp6eP6daq8lwJqlsc5o2BXRtS3aj8Sx0AsScrdjrO6cxEhzP+Uu8pLGYCqMxnTCTNeryLHZ/ISrz72fLQ5dieHvdxAkyVvT7P6VzwzmmmqDCK9EtAIcLVnWBBZ7cSp6kkQGUCVikZmnBusJDdsJ7+2xEuCNqKo4ITZUNNDw6d7G0gHq+B0sxANadpEziBeGk8d60LjNWHH9+4bXfC0/N95xqL/ZxD0bYVG0W5s8bElo+91LN1tT13akTAkwWJUBl4gg380PERxoTLCVLDGEJPB9h9bj1LVnRzeFtlZgnEuGX2jZQheqt4UnKVwlfNOAjxghpNrGI8Fh82995Ga6SsKAB3U0iVYq1GYlEJxi2c1H8uMSgG16OZrgxFTKRLC3rUWQYBiCjLT/1hNIAlpQqw263xG2z+J6tUxQm9nEGewl11LO0k/vltIBeZ5suwVXu2ZvPQsjhOcpHZn+eT7oavAl9/+3QYbLsYkEHh2KE2JH2q34Fbe5TJlcsETs8lnPZB+LbNzeoyHUFXGLecKoCPK8w53SAQBFhYn7jKHKXXK5kQ== X-Forefront-Antispam-Report: CIP:103.229.249.34;CTRY:AU;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AUS01-SY4-obe.outbound.protection.outlook.com;PTR:mail-sy4aus01lp2168.outbound.protection.outlook.com;CAT:NONE;SFS:(13230040)(82310400026)(69100299015)(36860700013)(1800799024)(1032899013)(35042699022)(376014);DIR:OUT;SFP:1102; X-OriginatorOrg: niwa.co.nz X-MS-Exchange-CrossTenant-OriginalArrivalTime: 12 Sep 2024 04:48:27.5293 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: c36775ad-86f3-4bf0-78eb-08dcd2e62481 X-MS-Exchange-CrossTenant-Id: 41caed73-6a0c-468a-ba49-9ff6aafd1c77 X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=41caed73-6a0c-468a-ba49-9ff6aafd1c77;Ip=[103.229.249.34];Helo=[hamlmail.dmz.niwa.co.nz] X-MS-Exchange-CrossTenant-AuthSource: SY3PEPF0000A726.ausprd01.prod.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem X-MS-Exchange-Transport-CrossTenantHeadersStamped: ME0P300MB0425 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SY7P300MB07611F2D0067FA7A417DCB29A1642SY7P300MB0761AUSP_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Could you use FDW's in another completely separate db for them to access so= they have no direct access to the source data (or database), only the link= ed tables which have no local data, other users, etc, present at all? Which is sort of what was suggested: "Put some kind of restrictive app in f= ront of the database." This other db could be that app? Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 ________________________________ From: Tom Lane Sent: Thursday, September 12, 2024 16:36 To: Andreas Joseph Krogh Cc: pgsql-general@lists.postgresql.org Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FR= OM PUBLIC Andreas Joseph Krogh writes: > Motivation: I have PowerBI users, with a separate =91reporting=92-role, a= ccessing > a database and I want to prevent them from listing all tables, users, dat= abases > and view-definitions (to not see the underlying query). Postgres is not designed to support this requirement. > I'm evaluating this: > REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE SELE= CT ON > ALL TABLES IN SCHEMA information_schema FROM PUBLIC; > Will this affect =93normal behaviour=94, ie. prevent the planner, or othe= r > internal mechanisms, from working properly for sessions logged in with th= e > =91reporting=92-role? Probably 95% of that stuff will still work. By the same token, there are plenty of information-leaking code pathways that will still leak. For instance, your restricted user will have no trouble discovering the OIDs and names of all extant tables, using something like do $$ begin for tid in 1..1000000 loop if tid::regclass::text !=3D tid::text then raise notice 'tid % is %', tid, tid::regclass; end if; end loop; end $$; Functions such as pg_describe_object still work fine, too. Experimenting with psql, a lot of stuff is broken as expected: busted=3D> \d mytable ERROR: permission denied for table pg_class but some things still work: busted=3D> \sf sin CREATE OR REPLACE FUNCTION pg_catalog.sin(double precision) RETURNS double precision LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$dsin$function$ This is pretty much the other side of the same coin. The reason you can still parse and plan a query is that it does not occur to large parts of the backend that there should be any reason to refuse to read a system catalog. That carries over to these operations as well. This recent thread might be enlightening: https://www.postgresql.org/message-id/flat/18604-04d64b68e981ced6%40postgre= sql.org If you have a requirement like this, I think the only safe way to meet it is to not give those users direct SQL access. Put some kind of restrictive app in front of the database. regards, tom lane [https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 National Institute of Water & Atmospheric Research Ltd (NIWA) 301 Evans Bay Parade Hataitai Wellington New Zealand Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitt= er Instagram YouTube To ensure compliance with legal requirements and to maintain cyber security= standards, NIWA's IT systems are subject to ongoing monitoring, activity l= ogging and auditing. This monitoring and auditing service may be provided b= y third parties. Such third parties can access information transmitted to, = processed by and stored on NIWA's IT systems. Note: This email is intended solely for the use of the addressee and may co= ntain information that is confidential or subject to legal professional pri= vilege. If you receive this email in error please immediately notify the se= nder and delete the email. --_000_SY7P300MB07611F2D0067FA7A417DCB29A1642SY7P300MB0761AUSP_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Could you use FDW's in another completely separate db for them to access so= they have no direct access to the source data (or database), only the link= ed tables which have no local data, other users, etc,  present at all?=

Which is sort of what was suggested: "Put some kind of restrictive app= in front of the database." This other db could be that app?

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529


Andreas Joseph Krogh <andreas@visena.com= > writes:
> Motivation: I have PowerBI users, with a separate =91reporting=92-role= , accessing
> a database and I want to prevent them from listing all tables, users, = databases
> and view-definitions (to not see the underlying query).

Postgres is not designed to support this requirement.

> I'm evaluating this:
> REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE S= ELECT ON
> ALL TABLES IN SCHEMA information_schema FROM PUBLIC;
> Will this affect =93normal behaviour=94, ie. prevent the planner, or o= ther
> internal mechanisms, from working properly for sessions logged in with= the
> =91reporting=92-role?

Probably 95% of that stuff will still work.  By the same token, there<= br> are plenty of information-leaking code pathways that will still leak.
For instance, your restricted user will have no trouble discovering
the OIDs and names of all extant tables, using something like

do $$ begin
for tid in 1..1000000 loop
  if tid::regclass::text !=3D tid::text then
    raise notice 'tid % is %', tid, tid::regclass;
  end if; end loop;
end $$;

Functions such as pg_describe_object still work fine, too.

Experimenting with psql, a lot of stuff is broken as expected:

busted=3D> \d mytable
ERROR:  permission denied for table pg_class

but some things still work:

busted=3D> \sf sin
CREATE OR REPLACE FUNCTION pg_catalog.sin(double precision)
 RETURNS double precision
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$dsin$function$

This is pretty much the other side of the same coin.
The reason you can still parse and plan a query is that
it does not occur to large parts of the backend that there
should be any reason to refuse to read a system catalog.
That carries over to these operations as well.

This recent thread might be enlightening:

https://aus01.safelinks.protectio= n.outlook.com/?url=3Dhttps%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2= F18604-04d64b68e981ced6%2540postgresql.org&data=3D05%7C02%7CBrent.Wood%= 40niwa.co.nz%7Cd471a43339634ba57f4208dcd2e48c6a%7C41caed736a0c468aba499ff6a= afd1c77%7C0%7C0%7C638617126279440102%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLj= AwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata= =3DQbk8QFtdRcaWAzgvEHEA0kKTVIu4umFtqfNcYjCzJj4%3D&reserved=3D0

If you have a requirement like this, I think the only safe
way to meet it is to not give those users direct SQL access.
Put some kind of restrictive app in front of the database.

            &nb= sp;           regards, to= m lane


Brent Wood
Principal Technician - GIS and Spatial Data Managem= ent
Programme Leader - Environmental Information Delive= ry
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz Facebook LinkedIn= Twitter Instagram YouTube
To ensure compliance with legal requirement= s and to maintain cyber security standards, NIWA's IT systems are subject t= o ongoing monitoring, activity logging and auditing. This monitoring and au= diting service may be provided by third parties. Such third parties can access information transmitted to, process= ed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may co= ntain information that is confidential or subject to legal professional pri= vilege. If you receive this email in error please immediately notify the se= nder and delete the email.
--_000_SY7P300MB07611F2D0067FA7A417DCB29A1642SY7P300MB0761AUSP_--