Can user specification of a column value be required when querying a view ?
Hi:
I have a view that I want to require user specification for a specific
column before the query starts (if that makes sense).
Example
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 ?
Thanks
Maybe you could create a function that has a required parameter, so rather
than 'select * from huge_view where .." they do 'select * from
function(some_ssn) [where...]' ?
That function would then query the view using the supplied ssn.
Just a thought.
Steve
On Tue, Nov 21, 2023 at 8:41 AM David Gauthier <dfgpostgres@gmail.com>
wrote:
Show quoted text
Hi:
I have a view that I want to require user specification for a specific
column before the query starts (if that makes sense).Example
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 ?
Thanks
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.
OK, didn't think so, just checking. Thanks for verifying !
On Mon, Nov 20, 2023 at 4:45 PM Christophe Pettus <xof@thebuild.com> wrote:
Show quoted text
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 thefollowing 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.
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.
Or row level security.
On Mon, Nov 20, 2023 at 9:25 PM Alan Hodgson <ahodgson@lists.simkin.ca>
wrote:
Show quoted text
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.
On 2023-11-20 22:03:06 -0500, Ron Johnson wrote:
Or row level security.
Does that help here? AIUI row level security can be used to limit access
to specific rows (e.g. user alex can access info about ssn '106-91-9930'
but not '234-56-7890') but not how many rows can be accessed in a single
query.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2023-11-20 22:03:06 -0500, Ron Johnson wrote:
Or row level security.
Does that help here? AIUI row level security can be used to limit access
to specific rows (e.g. user alex can access info about ssn '106-91-9930'
but not '234-56-7890') but not how many rows can be accessed in a single
query.
I don't think OP indicated that ssn in a unique key.
On 2023-11-24 13:06:45 -0500, Ron Johnson wrote:
On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2023-11-20 22:03:06 -0500, Ron Johnson wrote:Or row level security.
Does that help here? AIUI row level security can be used to limit access
to specific rows (e.g. user alex can access info about ssn '106-91-9930'
but not '234-56-7890') but not how many rows can be accessed in a single
query.I don't think OP indicated that ssn in a unique key.
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 «select * from t».
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
On Sat, Nov 25, 2023 at 4:49 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2023-11-24 13:06:45 -0500, Ron Johnson wrote:
On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer <hjp-pgsql@hjp.at>
wrote:
On 2023-11-20 22:03:06 -0500, Ron Johnson wrote:
Or row level security.
Does that help here? AIUI row level security can be used to limit
access
to specific rows (e.g. user alex can access info about ssn
'106-91-9930'
but not '234-56-7890') but not how many rows can be accessed in a
single
query.
I don't think OP indicated that ssn in a unique key.
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 «select * from t».
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?
It's an alternative to functions for restricting the client to only his
data.
On 2023-11-25 10:49:56 -0500, Ron Johnson wrote:
On Sat, Nov 25, 2023 at 4:49 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2023-11-24 13:06:45 -0500, Ron Johnson wrote:On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2023-11-20 22:03:06 -0500, Ron Johnson wrote:
> Or row level security.Does that help here?
[...]
It's an alternative to functions for restricting the client to only his data.
Which isn't the problem here. So RLS doesn't help.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"