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 1r5EZ2-00CUYT-Cx for pgsql-general@arkaria.postgresql.org; Tue, 21 Nov 2023 00:24:28 +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 1r5EZ0-00BhhF-Kz for pgsql-general@arkaria.postgresql.org; Tue, 21 Nov 2023 00:24:26 +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 1r5EZ0-00Bhh7-2k for pgsql-general@lists.postgresql.org; Tue, 21 Nov 2023 00:24:26 +0000 Received: from cross.birch.relay.mailchannels.net ([23.83.209.46]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5EYu-007W1e-R4 for pgsql-general@lists.postgresql.org; Tue, 21 Nov 2023 00:24:25 +0000 X-Sender-Id: _forwarded-from|172.105.109.24 Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id 88AEB501A34 for ; Tue, 21 Nov 2023 00:24:17 +0000 (UTC) Received: from outbound1g.ore.mailhop.org (unknown [127.0.0.6]) (Authenticated sender: duocircle) by relay.mailchannels.net (Postfix) with ESMTPA id 3FE5C501631 for ; Tue, 21 Nov 2023 00:24:17 +0000 (UTC) ARC-Seal: i=2; s=arc-2022; d=mailchannels.net; t=1700526257; a=rsa-sha256; cv=pass; b=B84v1QFO/68VPWuleUl5x1iMJOfaYf9KWJSOqIjpK4rLD1EVERHQPUFJjc3SCnrY8Sn1Ag r3S3EL1ZhUwXibeK2TwJ+y5JkhUT3AqT6SSW2Tg3QrnMT64kNhyOgtAHR89KbKDzc83HA+ +bEAPwWu15wWau49fgXoGuU9OjloppVidhO3dPRwGm+ig/cpjjoVECEsG2be1Tqc6NaamN u6bYcOPWnK7jAiW38r46mCKN5Y9S2wUiFy3mD7KeGlR/EnnKhrnZsIfVBaTdkaj1tS08di 8AlUEJ7zk56/P4l0vK/iRfnyI9j77A7j/hxPDs00qf9WJ9SVOgDgHab3+0Vvhw== ARC-Message-Signature: i=2; a=rsa-sha256; c=relaxed/relaxed; d=mailchannels.net; s=arc-2022; t=1700526257; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references:dkim-signature; bh=sOskYKUaRqTmH0E66fzJgdWNvWicTPkT3BV38BUW+Zk=; b=m/iuv7EeGziGl+/2/rfeRhUAiw9Nj3YRESV9VZfss3lky1Vrox03XpYeTRU18GH7fwa/Yv UQiQQYrPXNgygdsF5PHmQ8Jnkk2tZXoqiHZrTjVXDE6oh2b40piXaDRp7lAFP0f2ijxYPm ccJj3Q1yG7TtLuH6wXVcCBwZhMvqrTQ4X2fTvZ+1aiu81tvXObjCb8sIplZuX1STRNqVJ8 oem2MS9+o/UeIWs60ztKFA27D3UWIhF6QEkyAuLuJ1FBD+BA6+lHPbgnFP2Zpe6WpFc++C G+G9UXym9bHCgUSzixJqTLUAcdipSxzKZy6gDOcuTJzEJ19rpDO/67mAWxFo3Q== ARC-Authentication-Results: i=2; rspamd-55bcb54c45-997q5; arc=pass ("outbound.mailhop.org:s=arc-outbound20181012:i=1"); auth=pass smtp.auth=duocircle smtp.mailfrom=ahodgson@lists.simkin.ca X-Sender-Id: _forwarded-from|172.105.109.24 X-MC-Relay: Forwarding X-MailChannels-SenderId: _forwarded-from|172.105.109.24 X-MailChannels-Auth-Id: duocircle X-Name-Snatch: 4ee0ed8200784695_1700526257368_923036540 X-MC-Loop-Signature: 1700526257368:2879239951 X-MC-Ingress-Time: 1700526257367 Received: from outbound1g.ore.mailhop.org (outbound1g.ore.mailhop.org [54.68.34.165]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384) by 100.122.31.6 (trex/6.9.2); Tue, 21 Nov 2023 00:24:17 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1700526257; cv=none; d=outbound.mailhop.org; s=arc-outbound20181012; b=Sb9Yi7ljjW5lIvSFWdwAKOlrnj+FF2jmvSIYFyyB7wXX0RsFj2/bxntLZAIUKN+6dzp6Y8JUW/Pe/ PD8wz5MZN/eZ3oYANl8vzrVU9oxiphZnrwSfRxbcn3QZggZ74S6OCk0jky9q4pirC9ZSxtgJ+s9t3n lSmPwTWYo9qjsDEZpLb7kLV2SCv4Jy8So952wbpm0CSV8pWMmxpUdTmMtGSk1J8Sd4b4c3sRAT3nTI 7Ys9gRCL6l9flg0q4+yQ0PhQxl2aBoSONQaDltNokJr0IbfszW6GN6Bqk2WejvqQ5KxIXOlyWFR/0q WIILiisQGOFsyYJ3d7JArnjWMUYWr3g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=outbound.mailhop.org; s=arc-outbound20181012; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:dkim-signature:dkim-signature:dkim-signature:from; bh=sOskYKUaRqTmH0E66fzJgdWNvWicTPkT3BV38BUW+Zk=; b=i97nW7x5UfGW7KbQrzNSBVmjsULY5DJjMamoHoM/bV2rK49AB7ku/B1JGdZuuTqbfujuKqxiRgm8x Ii0FU15yCPxuci+2IlfRF/y8TjXbqni9dImPym/xv/z28A5w/aAeD97Fjy6ZPHrA0Rs4UO3pPsPh7u 6UHpef/xRPEMeOitSc3ImEpLOhmgNLhMndZuKing27f8UcyUybWtafm0W6bWz0YMT1YdSOGAjsKsV4 DuCjh9lU/yn95xgPkAZLTxXy2pNS36SC4SNhFg7Ljp4kf+Y6DCw8SrKJ2+3+rFlrLFfDPnb38ykdv5 PFvVJ3mMeZcgejWtL/WS5oZ1Js/ziAw== ARC-Authentication-Results: i=1; outbound3.ore.mailhop.org; spf=pass smtp.mailfrom=lists.simkin.ca smtp.remote-ip=172.105.109.24; dkim=pass header.d=lists.simkin.ca header.s=athena header.a=rsa-sha256 header.b=JNjQ7mOo; dmarc=pass header.from=lists.simkin.ca; arc=none header.oldest-pass=0; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lists.simkin.ca; s=duo-1610662143981-d3c4c825; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:from; bh=sOskYKUaRqTmH0E66fzJgdWNvWicTPkT3BV38BUW+Zk=; b=SAzyg/q2iuz239zouZoHTOPwnI+aKpPIDVCmyPQtTFijAu8g85D+r3UJSKTPlnaPwNnIr+VaUaKyZ v6jZu8CcFGP45fMsLVhCgn5p//lEvREpOfiS2vyO58h1ce+xbkL+3bNZuvbu28stcngWMwsW7I5z3B j3V0my99usGfH1x8= DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outbound.mailhop.org; s=dkim-high; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:from; bh=sOskYKUaRqTmH0E66fzJgdWNvWicTPkT3BV38BUW+Zk=; b=MdlA8z05IdmtcV8eqAFaUTZot2M4yblJ8J0SQPHUKEP709f0c/CxVKBbmDnDNmc3b987XM5pMWM/R In/1hKqb18QiwNPOP/olGOHdBQow784jfRf6OdIhrKK8BbUVRbyYJptSDg82a4vJnIVUV0NiUmWXj8 a62wP9TS+sD176Ahb4CzM6A3pIbDh9zXMkxRp93+gz/mhMB8MP01uFlwyUdQxkDiiTwuVlByGld9uh DG9Da7XdzRef/+cncdq7VSiOrqt65zjezUOT0xhC8VggwFExb3DiMpjNACedeXLo8+5wsCjsYVZceB vSiGTnDL8Nyu8OlzCyw9IBeFSlVsCKQ== X-Originating-IP: 172.105.109.24 X-MHO-RoutePath: c2lta2lu X-MHO-User: 4c9434ad-8804-11ee-8626-d1581f149e9f X-Report-Abuse-To: https://support.duocircle.com/support/solutions/articles/5000540958-duocircle-standard-smtp-abuse-information X-Mail-Handler: DuoCircle Outbound SMTP Received: from athena.simkin.ca (athena.simkin.ca [172.105.109.24]) by outbound3.ore.mailhop.org (Halon) with ESMTPSA id 4c9434ad-8804-11ee-8626-d1581f149e9f; Tue, 21 Nov 2023 00:24:15 +0000 (UTC) Received: from skynet.simkin.ca (node-1w7jr9sr73xr35x3kkzhqfq6l.ipv6.telus.net [IPv6:2001:569:bce0:6400:82de:baed:4036:a04d]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (secp384r1) server-digest SHA384) (No client certificate requested) by athena.simkin.ca (Postfix) with ESMTPSA id 7A3D8C6B877F for ; Mon, 20 Nov 2023 16:24:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=lists.simkin.ca; s=athena; t=1700526249; bh=xnvaAsINh82eF+ZrUrd8h11KthtjE/TWe5A+qjnwDmU=; h=Subject:From:To:Date:From:To:Subject:Date:Reply-To; b=JNjQ7mOoipr3om2jHKZ6hfHoDo1/9ESum7OPKqyQ5y6PNxCCyyxN8IxN0g997Wh2t 22UVm0PcGwCQZLz1IPCMPDht1ZbszWuTZ8COp6v1XtrlgQJLv+O0Sd/Vkm6Xf5KT9O EBUI3cGhcC1NyuVvDjRGCsHQzN6e51FhnwQQ27Z9M7QWAmaxjVDHiq1q0owoczLtVs tbKepFF047CvOWpOECtVz6b5i636BN2qdPwiWfZUppetoTf2OavjVnADjfxr221bfH O/n11iOHCmRGceH4wN8DJZlIbVXmcGZnEutw/RBlK+R6vxe/S3wvIQKSR9rVC2UNI/ QWUCIu4TgqE4Q== Received: from skynet.simkin.ca (skynet.simkin.ca [IPv6:::1]) by skynet.simkin.ca (Postfix) with ESMTP id 6C08222D016A0 for ; Mon, 20 Nov 2023 16:24:12 -0800 (PST) Message-ID: <73acc1a178aa851f6f5a424d6874a157ca0db2f7.camel@lists.simkin.ca> Subject: Re: Can user specification of a column value be required when querying a view ? From: Alan Hodgson To: pgsql-general@lists.postgresql.org Date: Mon, 20 Nov 2023 16:24:12 -0800 In-Reply-To: <1E154B80-1BF2-478A-987A-F28F58A4D1A8@thebuild.com> References: <1E154B80-1BF2-478A-987A-F28F58A4D1A8@thebuild.com> Content-Type: multipart/alternative; boundary="=-w3mgxJqmuwNTgL+4TLLN" X-PGP-Key: fp="EF82 6F8D A3BA A096 FFA3 05F2 A7A0 40A5 34E5 AA0B"; id="0x34E5AA0B"; get= User-Agent: Evolution 3.48.4 MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-w3mgxJqmuwNTgL+4TLLN Content-Type: text/plain; charset="ISO-8859-15" Content-Transfer-Encoding: quoted-printable On Mon, 2023-11-20 at 13:44 -0800, Christophe Pettus wrote: >=20 >=20 > > On Nov 20, 2023, at 13:41, David Gauthier > > wrote: > > I want the users to be required to provide a value for ssn in the > > following query...=20 > > "select * from huge_view where ssn =3D '106-91-9930' " > > I never want them to query the view without specifying ssn. > > It has to do with resources and practicality. > >=20 > > Is there a way to do that ? >=20 > Not in a way that PostgreSQL itself will enforce.=A0 If you are > concerned about a query running wild and taking up resources, > setting statement_timeout for the user that will be running these > queries is the best way forward.=A0 A user that has general access to > PostgreSQL and can run arbitrary queries will be able to craft a > query that takes up a lot of system time and memory without too > much trouble. If it's really about SSN's it might be more about bulk access to PII than performance. A function is probably the right choice in either case. --=-w3mgxJqmuwNTgL+4TLLN Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable
On Mon, 2023-11-20 at 13:44 -0800, Christophe Pet= tus wrote:


On Nov 20, 2023, at 13:41, David Gauthier <= ;dfgpostgres@gmail.com> wro= te:
I want the users to be required to provide a value for ss= n in the following query...
"select * from huge_view where s= sn =3D '106-91-9930' "
I never want them to query the view wi= thout specifying ssn.
It has to do with resources and practic= ality.

Is there a way to do that ?

Not in a way that PostgreSQL itself will enf= orce.  If you are concerned about a query running wild and taking up r= esources, setting statement_timeout for the user that will be running these= queries is the best way forward.  A user that has general access to P= ostgreSQL and can run arbitrary queries will be able to craft a query that = takes up a lot of system time and memory without too much trouble.

If it's really about SSN's it might be mo= re about bulk access to PII than performance.

A fu= nction is probably the right choice in either case.
=
--=-w3mgxJqmuwNTgL+4TLLN--