From: Alan Hodgson <ahodgson@lists.simkin.ca>
To: pgsql-general@lists.postgresql.org
Subject: Re: Can user specification of a column value be required when querying a view ?
Date: Mon, 20 Nov 2023 16:24:12 -0800
Message-ID: <73acc1a178aa851f6f5a424d6874a157ca0db2f7.camel@lists.simkin.ca> (raw)
In-Reply-To: <1E154B80-1BF2-478A-987A-F28F58A4D1A8@thebuild.com>
References: <CAEs=6DmkV_hd+c9OVK4yCuedsrjO0_ZD6tnDRw5OdCC=eMh2vg@mail.gmail.com>
<1E154B80-1BF2-478A-987A-F28F58A4D1A8@thebuild.com>
On Mon, 2023-11-20 at 13:44 -0800, Christophe Pettus wrote:
>
>
> > On Nov 20, 2023, at 13:41, David Gauthier <dfgpostgres@gmail.com>
> > wrote:
> > I want the users to be required to provide a value for ssn in the
> > following query...
> > "select * from huge_view where ssn = '106-91-9930' "
> > I never want them to query the view without specifying ssn.
> > It has to do with resources and practicality.
> >
> > Is there a way to do that ?
>
> Not in a way that PostgreSQL itself will enforce. 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. 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ahodgson@lists.simkin.ca
Subject: Re: Can user specification of a column value be required when querying a view ?
In-Reply-To: <73acc1a178aa851f6f5a424d6874a157ca0db2f7.camel@lists.simkin.ca>
References: <CAEs=6DmkV_hd+c9OVK4yCuedsrjO0_ZD6tnDRw5OdCC=eMh2vg@mail.gmail.com> <1E154B80-1BF2-478A-987A-F28F58A4D1A8@thebuild.com> <73acc1a178aa851f6f5a424d6874a157ca0db2f7.camel@lists.simkin.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox