Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely
On Fri, Oct 07, 2022 at 09:35:49AM -0400, Bruce Momjian wrote: > On Fri, Oct 7, 2022 at 08:05:36AM +, Erki Eessaar wrote: > > I confirmed, that setting search_path is indeed sometimes needed in case of > > SECURITY DEFINER routines that have SQL-standard bodies. See an example at > > the > > end of the letter. > > > > I suggest the following paragraph to the documentation: > > > > Starting from PostgreSQL 14 SQL-standard bodies can be used in SQL-language > > functions. This form tracks dependencies between the function and objects > > used > > in the function body. However, there is still a possibility that such > > function > > calls other code that reacts to search path. Thus, as a best practice, > > SECURITY > > DEFINER functions with SQL-standard bodies should also override search_path. > > I think this gets back to what Noah said about this section not needing > to explain all the details but rather give general guidance. I am not > sure adding the reasons for _why_ you should use search path for > SQL-standard bodies is really adding anything. Noah, is that accurate? Yes, that's my thinking. It's hard to make objective decisions about how deeply to cover each topic in the documentation. I'm content with the present state of this particular section, though.
Re: Comparison Predicates - example - documentation seems contradictory?
you're 100% right. Got it now. The documentation is correct and does not need to be changed. chrs. From: Alvaro Herrera Sent: Friday, 7 October 2022 7:49 PM To: [email protected] ; [email protected] Subject: Re: Comparison Predicates - example - documentation seems contradictory? On 2022-Oct-06, PG Doc comments form wrote: > See the heading: "Table 9.2. Comparison Predicates" > > "2 BETWEEN 3 AND 1 → f" # ok this is false. > > But let's read the example immediately below (the 'not between' example) > > 2 NOT BETWEEN 1 AND 3 → f # what? This is also false. how can the negation > also be the same value? I would expect it to be true? Actually, 2 *is* between 1 and 3. So if you ask if it's NOT between, that's false. The other one is false because the boundaries are reversed, and BETWEEN does not put them in the proper order before comparing. BETWEEN SYMMETRIC does that, as explained in the line below. -- Álvaro Herrera 48°01'N 7°57'E — https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.enterprisedb.com%2F&data=05%7C01%7C%7Ced05496dced34d16154408daa84d6526%7C84df9e7fe9f640afb435%7C1%7C0%7C638007347613366454%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=f5aDRnJa9jZX%2BmfPUAxgsBXSJASWt52JWjWb5WS0DCY%3D&reserved=0
