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 1r6pIS-002nd0-75 for pgsql-general@arkaria.postgresql.org; Sat, 25 Nov 2023 09:49:56 +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 1r6pIO-001Ov7-FG for pgsql-general@arkaria.postgresql.org; Sat, 25 Nov 2023 09:49:52 +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 1r6pIO-001Os8-3g for pgsql-general@lists.postgresql.org; Sat, 25 Nov 2023 09:49:52 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6pIG-008RM5-6A for pgsql-general@lists.postgresql.org; Sat, 25 Nov 2023 09:49:51 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id F11E61193A; Sat, 25 Nov 2023 10:49:42 +0100 (CET) Date: Sat, 25 Nov 2023 10:49:42 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Can user specification of a column value be required when querying a view ? Message-ID: <20231125094942.p3vdynfg2slsn2ok@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <1E154B80-1BF2-478A-987A-F28F58A4D1A8@thebuild.com> <73acc1a178aa851f6f5a424d6874a157ca0db2f7.camel@lists.simkin.ca> <20231124180056.k4ea6or6clngrkaq@hjp.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="tfbjfvzzzcrreby2" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --tfbjfvzzzcrreby2 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2023-11-24 13:06:45 -0500, Ron Johnson wrote: > On Fri, Nov 24, 2023 at 1:01=E2=80=AFPM Peter J. Holzer wrote: > On 2023-11-20 22:03:06 -0500, Ron Johnson wrote: > > Or row level security. >=20 > Does that help here? AIUI row level security can be used to limit acc= ess > to specific rows (e.g. user alex can access info about ssn '106-91-99= 30' > but not '234-56-7890') but not how many rows can be accessed in a sin= gle > query. >=20 >=20 > I don't think OP indicated that ssn in a unique key.=C2=A0 No he didn't, but that's IMHO not relevant to the possibility of using row level security. If a row level security allows a user to select a row, that row can be selected by any query, including =C2=ABselect * from t= =C2=BB. I don't see a way to use RLS to ensure that a query can only return a sufficiently small subset of the total rows a user has access to. How would you do that? hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --tfbjfvzzzcrreby2 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmVhwzEACgkQ8g5IURL+ KF1Avg/+PFa8ABkKNXqKv22n1+r8kRkPx0X0iIQGpMiRTufZ/AUtb/LMTfPOtXBM EEJ23d82ScanHb8o1R2yVgn4eAGNQrkMKA3g8bG3ercYnUt5xZ1sv/nFvPgajJuD bUoqkGv2ZKQhEnW7ss+BH4Xnozvo0ksNOu4XoadnXqtd/xA3jzAdVXGar2yYIqdw D8O04ZDdN/Q6EkcbrnxBn+vOzcmE+LWrP0vwMtMSKW2RzNWcEcqo33m2vJnbYhjj SlCES1RT1SwlpT7MJJv3w3KLlw2zk5HFp916hA5bVTuG3YOnUIQpz3kU8mf4FEp2 hCjf/TikeKMh+3AuJD37D+keG7SZsHUB6Yvlq1rj1B0VBLftrVq1c1nMzj8CZxpq vfvjhdwLbpJhc+ap4ZbSc5DJt9Ff14OSzYaOk5nPfbnpxe9mbDNfgeeueBCQFZJP 2SM8+58UzDamn/KGfYalZ3fccFFwc9nytaLJj4I+rNHELuLW1btI6BxmBs7H32DM Lh/x9o68/d8rcAY9XCrNwI88BtbYCYc0oiUJg4gvwWHIGKPl9geonLAbxZcATbMq thBcNQQ7OO4iOz8EWegG6yPcIJxlhvZavDiISR0Xkv2Hxa+kQQO9IZx9tgeeZVBe OAstDpDBMwuc02HOghFOtyJOuooNiR0uAuzsxs9RyWar6a0zOWE= =B7xu -----END PGP SIGNATURE----- --tfbjfvzzzcrreby2--