Re: Can user specification of a column value be required when querying a view ?
On 2023-11-25 10:49:56 -0500, Ron Johnson wrote: > On Sat, Nov 25, 2023 at 4:49 AM Peter J. Holzer wrote: > On 2023-11-24 13:06:45 -0500, Ron Johnson wrote: > > On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer > 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. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can user specification of a column value be required when querying a view ?
On Sat, Nov 25, 2023 at 4:49 AM Peter J. Holzer wrote: > On 2023-11-24 13:06:45 -0500, Ron Johnson wrote: > > On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer > 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 ?
On 2023-11-24 13:06:45 -0500, Ron Johnson wrote: > On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer 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. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can user specification of a column value be required when querying a view ?
On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer 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 ?
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. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can user specification of a column value be required when querying a view ?
Or row level security. On Mon, Nov 20, 2023 at 9:25 PM Alan Hodgson wrote: > On Mon, 2023-11-20 at 13:44 -0800, Christophe Pettus wrote: > > > > 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... > "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 ?
On Mon, 2023-11-20 at 13:44 -0800, Christophe Pettus wrote: > > > > 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... > > "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 ?
OK, didn't think so, just checking. Thanks for verifying ! On Mon, Nov 20, 2023 at 4:45 PM Christophe Pettus wrote: > > > > 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... > > "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 ?
> 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... > "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 ?
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 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). > > 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 > >
