Обсуждение: 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
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
Re: Can user specification of a column value be required when querying a view ?
От
Steve Baldwin
Дата:
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:
Hi:I have a view that I want to require user specification for a specific column before the query starts (if that makes sense).
ExampleI 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
Re: Can user specification of a column value be required when querying a view ?
От
Christophe Pettus
Дата:
> 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 thathas general access to PostgreSQL and can run arbitrary queries will be able to craft a query that takes up a lot of systemtime and memory without too much trouble.
Re: Can user specification of a column value be required when querying a view ?
От
David Gauthier
Дата:
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:
> 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.
Re: Can user specification of a column value be required when querying a view ?
От
Alan Hodgson
Дата:
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.
Re: Can user specification of a column value be required when querying a view ?
От
Ron Johnson
Дата:
Or row level security.
On Mon, Nov 20, 2023 at 9:25 PM Alan Hodgson <ahodgson@lists.simkin.ca> wrote:
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.
Re: Can user specification of a column value be required when querying a view ?
От
"Peter J. Holzer"
Дата:
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!"
Вложения
Re: Can user specification of a column value be required when querying a view ?
От
Ron Johnson
Дата:
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.
Re: Can user specification of a column value be required when querying a view ?
От
"Peter J. Holzer"
Дата:
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!"
Вложения
Re: Can user specification of a column value be required when querying a view ?
От
Ron Johnson
Дата:
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.
Re: Can user specification of a column value be required when querying a view ?
От
"Peter J. Holzer"
Дата:
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!"