Re: PostgreSQL : error hint for LATERAL join
Hello,
Your point of view is interesting. May I discuss it ?
One answered to me on my question about " bug (ou not) in CTE Common Table
Expressions or Subqueries in the FROM Clause " :
" Its doesn’t have to seem logical to you, but this is how it is defined to
work and thus the observed behavior is not a bug. "
I thought about it and OK, even if regretted that the PostgreSQL language
sometimes ( rarely ) doesn't really make sense for me (given my logic).
So, are standards the rule or not ?
For me, one of the two following things should be true : either the hint (in
case of a lateral error) is incomplete or the possibility of " cross join
lateral " should be removed.
Of course, the idea of CROSS join doesn't make think about LATERAL idea.
But, is there a difference between a CROSS join and a INNER join with the " ON
TRUE " clause ?
Note : an inner join is a cross join with a clause ON (logical condition) and
OK, of course, an INNER join has not always " ON TRUE " clause, but it can.
I think you are right to write " Because the lateral takes precedence ".
LATERAL " takes precedence " over CROSS in the same way as INNER JOIN ... and
INNER JOIN ON TRUE
With LATERAL, joins are no more independant relations, neither CROSS or INNER.
SELECT *
FROM (
VALUES ('a'),('b')
) t (c1)
JOIN LATERAL (
VALUES ('b', c1), ('c',c1 || '*')
) u(d1) ON true
;
c1 | d1 | column2
++-
a | b | a
a | c | a*
b | b | b
b | c | b*
(4 lignes)
Regards
De: "David G. Johnston"
À: "PALAYRET Jacques"
Cc: "pgsql-general"
Envoyé: Vendredi 8 Avril 2022 15:36:34
Objet: Re: PostgreSQL : error hint for LATERAL join
On Fri, Apr 8, 2022 at 1:29 AM PALAYRET Jacques < [
mailto:jacques.palay...@meteo.fr | jacques.palay...@meteo.fr ] > wrote:
For a LATERAL join, I think the hint (in case of error) is incomplete :
" DÉTAIL : The combining JOIN type must be INNER or LEFT for a LATERAL
reference. "
to be replaced by :
" DÉTAIL : The combining JOIN type must be CROSS, INNER or LEFT for a LATERAL
reference. "
Note : it depends on what is needed
Regardless of whether the syntax works or not, the conceptual idea that a
lateral is also somehow a cross join is just wrong. A cross join, by
definition, takes two independent relations and performs a cartesian product
between them. A lateral join, by definition, takes a row from the left side of
the join, evaluates the right side using one or more columns from that rows,
then produces an output row for each row produced by the right side (copying
the left) - the inner/outer marker indicating what to do when the right side
produces zero rows.
If you use a non-trivial demonstration query (i.e., one that doesn't try to
multiply 1x1) this becomes more clear:
postgres=# SELECT *
FROM (
VALUES ('a'),('b')
) t (c1)
CROSS JOIN LATERAL (
VALUES ('b', c1), ('c',c1 || '*')
) u(d1)
;
c1 | d1 | column2
++-
a | b | a
a | c | a*
b | b | b
b | c | b*
(4 rows)
The presence of the cross join is misleading (if anything the error message is
sound advice and the behavior shown is wrong, but likely standard's mandated).
If it were a true cross join the relation u produced 4 unique rows and the
relation t produced 2, thus the output should have 8 rows. It only has four.
Because the lateral takes precedence here and only matches a subset of the
right-side output rows with the left side.
David J.
LDAP - Postgres server running on windows
I have a client running a PostgreSQL 14 environment on windows. I have found documentation on enabling LDAP auth for pgAdmin 4 and attempted to edit my config.py file, but have not been successful. Does anyone have a walk-through or any advice for enabling LDAP authentication for Postgres running on a windows box? If not, I will look at migrating to a Linux server where I can utilize the LDAPUtil and a whole lot more documentation exists. Thanks in advance. Joshua Price
Declarative partitioning and partition pruning/check
Hi, I have tried to read about Oracle's spatial partitioning feature ( https://www.oracle.com/technetwork/database/enterprise-edition/spatial-twp-partitioningbp-10gr2-05-134277.pdf) and wondered if something like this is possible with PostgreSQL (with PostGIS): The first part, getting the rows into the "right" partition isn't especially interesting: Reduce every geometry to a point, and use the x and y coordinates separately in a range partition. This is possible with PostgreSQL as it is a normal range partition on double. The second part is more interesting. Whenever the spatial index is (implicitly or directly) used in a query, the partition pruning step (during execution) checks the spatial index's root bounding box to determine if the partition can be skipped. Is this possible to achieve in PostgreSQL? There is already a function in PostGIS to get the spatial index root bounding box (_postgis_index_extent(tbl regclass, col text)), but I think the real issue is that the actual SQL query might not even call the index directly (SELECT * FROM a WHERE ST_Intersects(mygeom, a.geom) - the ST_Intersects function uses the index internally). Best Regards, Mats Taraldsvik
DBlink extension and behavior of dblink_get_connections()
Hi, I am using dblink extension and getting connection name using dblink_get_connections() function if it do not return specified name then establishing a new connection and if it return then using same connection. But there is one weird behaviour, if someone killed session then dblink_get_connections() function still returning connection name in that session and my dblink.exec error out. Regards, Pankaj Gupta This e-mail message and any files transmitted with it may contain confidential and proprietary information and are intended solely for the use of the individual or entity to which they are addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this e-mail in error please notify the sender by reply email and destroy all copies of the original message. Thank you for your cooperation.
Re: PostgreSQL : error hint for LATERAL join
PALAYRET Jacques writes: > For me, one of the two following things should be true : either the hint (in > case of a lateral error) is incomplete or the possibility of " cross join > lateral " should be removed. The reason the hint is worded the way it is is a practical one: the other possibilities are not drop-in syntactic replacements. Given CREATE TABLE t (x float8); all five of these are syntactically legal (with or without the implied LATERAL): SELECT * FROM t, sin(x); SELECT * FROM t CROSS JOIN sin(x); SELECT * FROM t JOIN sin(x) ON true; SELECT * FROM t INNER JOIN sin(x) ON true; SELECT * FROM t LEFT JOIN sin(x) ON true; The only way to get to the error in question is to write something like SELECT * FROM t FULL JOIN sin(x) ON true; If that's what you have, then changing FULL to INNER or LEFT will produce a syntactically legal query; changing it to CROSS will not unless you make additional changes (which'd be hard if the ON condition were nontrivial). Also, if we were trying to be completionist, we'd have to somehow work in the comma and implicit-INNER syntaxes, which would surely make the hint a confusing mess. Another way to look at it is that there are only two allowed semantic behaviors here: INNER and LEFT joins. The fact that there's more than one way to spell an inner join is a historical accident. regards, tom lane
Re: PostgreSQL : error hint for LATERAL join
On Mon, Apr 11, 2022 at 7:20 AM Tom Lane wrote: > PALAYRET Jacques writes: > > For me, one of the two following things should be true : either the hint > (in case of a lateral error) is incomplete or the possibility of " cross > join lateral " should be removed. > > The reason the hint is worded the way it is is a practical one: the other possibilities are not drop-in syntactic replacements. > > [...] > > SELECT * FROM t, sin(x); > SELECT * FROM t CROSS JOIN sin(x); > SELECT * FROM t JOIN sin(x) ON true; > SELECT * FROM t INNER JOIN sin(x) ON true; > SELECT * FROM t LEFT JOIN sin(x) ON true; > > > Another way to look at it is that there are only two allowed > semantic behaviors here: INNER and LEFT joins. The fact that > there's more than one way to spell an inner join is a historical > accident. > > Agreed on both points, and a hat-tip for the former. I did get somewhat annoyed previously that I had to write "on true" (I considered that a code smell) but decided it was a better option than both "t, six(x)" and "t cross join sin(x)" as I dislike the implicit format even more and the semantic mis-match with the cross join was unappealing as well. There really is no other option for a LEFT JOIN here so just the consistency with an INNER JOIN has now made writing "on true", at least for a lateral join, make sense to me. David J.
How easy is it to lose permissions in 'public' schema?
I'm using postgres 14 in a database recently brought over from postgres
10. (I did not do the bringing over.)
In my set-up, I partition datasets by schema, create a role per schema
and part of that is this explicit permission granting (from superuser):
p\g
grant all on all tables in schema base, bulk, sgstemplate to
sgstemplate\p\g
--where sgstemplate is sedded to the real deal by the installer
there is no explicit 'public' in that set-up and here-to-fore the new
grantee has been able to see into public stuff just fine, in particular
to public functions which rely on getting grantee's version of tables
(replicated in sister schema).
I've just bumped into this.
barnard=> select public.genome_threshold_mono('a'::text,'b'::text);
ERROR: permission denied for schema public
LINE 1: select public.genome_threshold_mono('a'::text,'b'::text);
I know I haven't intentionally removed 'public' from grantee's purview
and short of the code block above not actually getting run, any guesses
as to how access to 'public' got removed from grantee?
I've run those grants specifically naming public and all is well. Do I
need to add that to the installer script?
Re: How easy is it to lose permissions in 'public' schema?
On 4/11/22 16:10, Rob Sargent wrote:
I'm using postgres 14 in a database recently brought over from postgres
10. (I did not do the bringing over.)
In my set-up, I partition datasets by schema, create a role per schema
and part of that is this explicit permission granting (from superuser):
p\g
grant all on all tables in schema base, bulk, sgstemplate to
sgstemplate\p\g
--where sgstemplate is sedded to the real deal by the installer
there is no explicit 'public' in that set-up and here-to-fore the new
grantee has been able to see into public stuff just fine, in particular
to public functions which rely on getting grantee's version of tables
(replicated in sister schema).
I've just bumped into this.
barnard=> select public.genome_threshold_mono('a'::text,'b'::text);
ERROR: permission denied for schema public
LINE 1: select public.genome_threshold_mono('a'::text,'b'::text);
I know I haven't intentionally removed 'public' from grantee's purview
and short of the code block above not actually getting run, any guesses
as to how access to 'public' got removed from grantee?
I'm going to say someone read this:
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
And did something along the line of this:
"
Next Steps: How Can I Protect My Databases?
Do not allow users to create new objects in the public schema
As a superuser, run the following command in all of your databases:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Running REVOKE CREATE ON SCHEMA public FROM PUBLIC; prevents all
non-superusers from creating objects in the public schema. This setting
will protect a PostgreSQL database from the problem described in
CVE-2018-1058.
Once this command is run, certain operations could fail within your
database. For example, a non-superuser will not be able to create tables
or functions anymore with the public schema, which may affect how a user
manages application schema migrations.
Note that the REVOKE command is more powerful than running DROP SCHEMA
public; as pg_dump does not preserve the public schema removal.
After running this command, you should strongly consider auditing your
public schema to see if any users have created functions that have names
similar to ones in the pg_catalog. From the command-line tool (e.g.
psql), you can see a list of functions available in the public schema by
running:
\df public.*
To see a full list of functions defined In the pg_catalog schema, please
run:
\df pg_catalog.*
"
Probably should take a look at what permissions the functions in public
have?
I've run those grants specifically naming public and all is well. Do I
need to add that to the installer script?
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: How easy is it to lose permissions in 'public' schema?
Adrian Klaver writes:
> On 4/11/22 16:10, Rob Sargent wrote:
>> I've just bumped into this.
>>
>> barnard=> select public.genome_threshold_mono('a'::text,'b'::text);
>> ERROR: permission denied for schema public
>> LINE 1: select public.genome_threshold_mono('a'::text,'b'::text);
>>
>> I know I haven't intentionally removed 'public' from grantee's purview
>> and short of the code block above not actually getting run, any guesses
>> as to how access to 'public' got removed from grantee?
> I'm going to say someone read this:
> https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
> And did something along the line of this:
> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Note that that only recommends removing CREATE, though, not USAGE
which is what Rob seems to be lacking.
regards, tom lane
Re: How easy is it to lose permissions in 'public' schema?
On 4/11/22 17:34, Tom Lane wrote:
Adrian Klaver writes:
On 4/11/22 16:10, Rob Sargent wrote:
I've just bumped into this.
barnard=> select public.genome_threshold_mono('a'::text,'b'::text);
ERROR: permission denied for schema public
LINE 1: select public.genome_threshold_mono('a'::text,'b'::text);
I know I haven't intentionally removed 'public' from grantee's purview
and short of the code block above not actually getting run, any guesses
as to how access to 'public' got removed from grantee?
I'm going to say someone read this:
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
And did something along the line of this:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Note that that only recommends removing CREATE, though, not USAGE
which is what Rob seems to be lacking.
Yeah that is why I threw in the 'And did something along the line of
this' and the 'Probably should take a look at what permissions the
functions in public have?'. I'm guessing someone saw the release notes
for 10.3(https://www.postgresql.org/docs/10/release-10-3.html) and the
comments on the mailing list and got proactive.
regards, tom lane
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: How easy is it to lose permissions in 'public' schema?
> On Apr 11, 2022, at 6:51 PM, Adrian Klaver wrote:
>
> On 4/11/22 17:34, Tom Lane wrote:
>> Adrian Klaver writes:
On 4/11/22 16:10, Rob Sargent wrote:
> I've just bumped into this.
>
> barnard=> select public.genome_threshold_mono('a'::text,'b'::text);
> ERROR: permission denied for schema public
> LINE 1: select public.genome_threshold_mono('a'::text,'b'::text);
>
> I know I haven't intentionally removed 'public' from grantee's purview
> and short of the code block above not actually getting run, any guesses
> as to how access to 'public' got removed from grantee?
>>> I'm going to say someone read this:
>>> https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
>>> And did something along the line of this:
>>> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
>> Note that that only recommends removing CREATE, though, not USAGE
>> which is what Rob seems to be lacking.
>
> Yeah that is why I threw in the 'And did something along the line of this'
> and the 'Probably should take a look at what permissions the functions in
> public have?'. I'm guessing someone saw the release notes for
> 10.3(https://www.postgresql.org/docs/10/release-10-3.html) and the comments
> on the mailing list and got proactive.
>
>>regards, tom lane
>
Gentlemen,thank you.
Something similar to as described is a definite possibility during the
‘bringing over’. Same time one of the brought over dbs was imported twice
without constraints etc. I love being looked after.
Cheers,
rjs
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
