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 1txUUI-00AAIg-3C for pgsql-general@arkaria.postgresql.org; Wed, 26 Mar 2025 17:24:22 +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 1txUUG-007EdK-Dq for pgsql-general@arkaria.postgresql.org; Wed, 26 Mar 2025 17:24:20 +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 1txUUF-007EbF-Vw for pgsql-general@lists.postgresql.org; Wed, 26 Mar 2025 17:24:20 +0000 Received: from mout.gmx.net ([212.227.17.20]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1txUUD-001M8G-2R for pgsql-general@lists.postgresql.org; Wed, 26 Mar 2025 17:24:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1743009856; x=1743614656; i=karsten.hilbert@gmx.net; bh=7c1waKy9C2vXKcBp8+msACTJC921FmZSo9ktBscwrNI=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:Message-ID:References: MIME-Version:Content-Type:In-Reply-To:Content-Transfer-Encoding: cc:content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=IDL9q991QvT2fHUqWyxs9OBnIF7IfeiJriBOuUVCbgSW/3czdwyu+djOBzX8IADJ uYtZiANEdBNjh5OlVImIcq3aokb9pl+oJCUtwMzjlqNUpXVWyM04a9p6jEBkSuTcl C39jKC2MHAol4iW5Bi68iE7RjPgTUcUDpvTfU1nKlDlZvhIK5R0sXFZEfac2QMyXx eX0hnPAWwDRdDYx8kOEaYgYA2uGMDB16bCHKcyVKcM83vfUcE2vvFlWv61NfyvGOZ whJwEqXufflc6m+3EDUX+S9uXchiBOEHsIcJ6g9PjM6iTdLBX6L4qENXNNwyCwpTs Hxh0EljR7FXUTR41/Q== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([84.190.228.50]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MybGX-1tCefV1Jdd-011uad; Wed, 26 Mar 2025 18:24:16 +0100 Received: from ncq by hermes with local (Exim 4.96) (envelope-from ) id 1txUUA-0001En-2e; Wed, 26 Mar 2025 18:24:14 +0100 Date: Wed, 26 Mar 2025 18:24:14 +0100 From: Karsten Hilbert To: Tom Lane Cc: pgsql-general@lists.postgresql.org Subject: Re: Q on SELECT column list pushdown from view to table Message-ID: References: <1321213.1742943334@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1321213.1742943334@sss.pgh.pa.us> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:Kd8qlHDKoTfzDvBQIYOLFwqBXuGx2VCAi+D1J36ikLDip5xkz/B uHDNpwg/Mt4VdNatcqRV9MO1w+ucj82w4tAuOyc7uaKtZ95sp+mW+xhLlCgb8tTSyv0Jx9S q8/m0Yg9pBTOMt1rGcUUygfT2vVZafAJ49nL9gCk0af+uZO6UbEd9MypYg3UKnMrxMluUxP mE4UDZR6AjhyTb4OXT02g== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:a8LUyNan2ao=;+VtaBz15DJvEPrxaSDOUxkhAubS NLJ7MCQpEqh9yZJRvXk4DMWxfNmTqdtWJtapHvUQOc/O6DHNG8NAUHkbpjRca5GUa4D9/kV0i udiLvufDic1UL5S5Bh2nKUOdQrlv1e9wLWitpNrzZ6EXumTkXAxJ83kPgKRyZaC1T8CiJr05w FgIpOA2H/pZKMwmsVajdeqA4a56OPeKy40MXiBXa3TchHxTn0iKmLGlVrRvpinrWbTseuejpO e4LGI73UlGBzAyG+kFepSdpEMUt0TluQNpO427Hbg/+k1lQ/X/0wYeZ5OCCLkGjPmwUT5aP6E wn6K8cTN3DvCJm5qCs1Y11JNbkMzW0TW4GDOXMGGR2vtbQfrcJpAvlCIuwdRMm8X2vVv2ohTb maMiLKAa48KSAJGrFoDKgzhJCc56eCkzv5++UDkpNBRAI9FJPY1vFU3yC+p+IZKPBs85qh1Kv 7B+BqjDavpuZqvC1QaYcLD7cnW2g1B/7qO+aOr03AfgzL1Jj47HMdfxwl6cLuTrTKSSyDCUKy 6dELlWxkidyeptEZJ7r3Irt/b8///zdXd4hJYqrxUL87fpmnn2nIMv0vKQL3Jhq4XSyg/9VqR BB5/b6G8uG34xUSdIgoqGaWKrIYGhZeeLT2yzTNwO6pbeFquRT4ZvuFuFf8+uw9VSi/+7ewYI W7dlH9wGILcj5AVNHidaoHmRY97UgHbroV+CdCUjCSq6r5pSshgqu14NyK5PH2FlTZKQBKcAG vlebK/8WcU47nIgNKgGS0/jBMYi/GopYYf/TtIjWejJUUfanpQM5YUej2+BYXiLWQRiV0pblo kcWWmleUi/OgCd4+XfI/crkoiQR4Xa1lqPblQy6LxjEU2g4eOinwGaJ4wQvGN7wtACXBD89R+ vtvI26h8CV4EtHDEphshXZ6+tW/DhF2hPVolirwHMziga/fRSevF8KUaVB8888s6dxdaywjiV PQTPvx+t4AwqLn7+3/anj7kEaMrj5n3NjQyQ0FnMXmqlQYYHwY9iLL80e95hJ8bPDhHf19/Qj yg4vD5St4YDq4N+lQ7F6JaGHDsKM5mj3CqDnPZRn0PVH/cAM1G2j5ilva98lmqj9BqYm/MRuu XFZYCMlaTVEpQhiwuwNLubsqBcIqoQx7Fb+4cV1hCV2p6IEAM/7on108Ij0xAvug7c62jUZbS jZEUBh6CS7uJE64QnBzBXHt1diZmhQyomOJa/lOeTYBBjtDojMYIWdWGgL1n2alnJ4Jl6jjm8 pMzh5wumEpsesVyhnj8erwTZtZI/eYaAZ1+hFH/ZgJiKb+fDOFGJCuyr6QTNM113bR0pQnbDi UN1iIndktJx2fOnTkC6WNNDuaHD3yGfpO+u+NIR6Au8I8VLURXcXPtF8nEMNx881krCX+3oN7 biwWze61oI5FlWuP6w4F50yC0dKFZOLV6SrJLydKrSK0R3n1ryNVUTu3nIJbxi97mNTRZSlar MUFNUhg== Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am Tue, Mar 25, 2025 at 06:55:34PM -0400 schrieb Tom Lane: > Karsten Hilbert writes: > > I expected this: > > > set role "restricted-role"; > > -- this works: > > select public_col from t_partially_private; > > -- this fails: with "permission denied on table t_partially_private" > > select public_col from v_partially_private; > > > to work but selecting from the view fails. > > Works fine if you don't mess with the view's security_invoker > status. I know but doing so was kind of the point. The views are created by a "database owner" role having access to all tables. Therefore, roles using the views would normally gain access to tables they are otherwise not allowed to read. Hence setting security to invoker made a lot of sense at first sight ... Perhaps I am misunderstanding the intent of the feature. Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B