pg RLS suggestions needed

[Laura Smith]

I've got a table with an RLS policy on it:

Policies:    POLICY "app_users_policy"
      USING ((app_id = CURRENT_USER))

Is there a way I'm not aware of (e.g. via Pl/PGSQL) that would allow 
"migration" of data from one RLS owner to another ?

At the moment, the only option I can think of is for the external app to login 
as one user, cache the data and then login as the other user and re-save it.

Basically the background is that I want to give users the ability to 
self-service migrate their existing app profile from "old" to "new" (the app 
being differentiated via RLS user on app_id)

Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause

[Евгений Плискин]

This is not a bug report but rather a suggestion.

Suppose database contains an index on a boolean column like this:
  create table1(id int, amount float, best_record boolean);
  create index index1 on table1(best_record) where best_record is true;

And suppose we issue a query like this:
  select * from table1 where best_record = true;

The planner does not try to use an index for this query because an index 
condition (bflag is true) does not literally match the query (bflag = true). 
But is that reasonable?
The index does not cover records where "best_record" is FALSE or NULL.
And similarly the query is not interested in any records where "best_record" is 
FALSE or NULL.
So why not use this index for this query?
Thank you.

Fwd: Postgresql/Postgis: Trigger for historization/versioning

[celati Laurent]

Good evening,
I work with Postgresql 13, Postgis (and Qgis 3.22.)
My need is to set up within my Postgis database (used for maps production),
triggers for automation of data historization/versioning.
The idea: For instance within a table 'BOREHOLE' (geometry: points),
different updates of the table are made over time.

The version change occurs in the event that:
• new objects are added or deleted in the table (INSERT / DELETE)
• objects are updated by new versions of objects canceling and replacing
existing objects (UPDATE).
Sometimes, these UPDATE only concern the geometry column (change of
location). Sometimes other fields/attributes (type of borehole, technical
referent, name of the campaign for the borehole, start date of
construction, name of the municipality where the borehole is located, etc.).

=> These INSERT/DELETE/UPDATE must have the effect, within the "archive"
schema (schema dedicated to the storage of historical/versioned
tables/objects), the creation of a new table "BOREHOLE_V1", " BOREHOLE
 _V2", "  BOREHOLE  _V3",etc.

*Requirements:*
• The objective is NOT to perform a version upgrade at each modification
(UPDATE) in the table, but to identify structuring phases.
In other words, the idea is that the administrator can decide, trigger when
he deems relevant, the trigger/history-versioning process.

• "Table versioning" way is recommended": the versioning tables (ARCHIVE
schema) will be made up of modified elements AND also unmodified objects
from the "BOREHOLE" table.
This choice was done in particular to facilitate the restoration of
versioned objects at the level of archived qgis projects.
*Advantage*: possibility of referring to a specific phase of the Instant T
project.
*Disadvantage*: duplication of objects even unmodified within the archive
table.

The administrator could activate a historization action when it seems
relevant to him. For example at the end of an Qgis editing session.

Could someone guide me, direct me to the methods offered by
PostgreSQL/Postgis (or even Qgis) likely to satisfy my needs?
A big thank-you. ;-) Hoping to have been sufficiently clear.

What ist the standard setting of FETCH_COUNT?

[Tiaswin]

What is the standard default setting for fetch_count and where can I
find the current setting?

Thanks.

Re: What ist the standard setting of FETCH_COUNT?

[Pavel Stehule]

Hi

po 19. 9. 2022 v 11:10 odesílatel Tiaswin  napsal:

> What is the standard default setting for fetch_count and where can I
> find the current setting?
>

[pavel@localhost pspg-master]$ psql
Assertions: on
psql (16devel)
Type "help" for help.

(2022-09-19 11:11:47) postgres=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
ERROR = 'false'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HIDE_TOAST_COMPRESSION = 'off'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '0'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '(%`date +"%Y-%m-%d %H:%M:%S"`) %/%=# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
QUIET = 'off'
ROW_COUNT = '0'
SERVER_VERSION_NAME = '16devel'
SERVER_VERSION_NUM = '16'
SHOW_ALL_RESULTS = 'on'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
SQLSTATE = '0'
USER = 'pavel'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (GCC)
12.2.1 20220819 (Red Hat 12.2.1-2), 64-bit'
VERSION_NAME = '16devel'
VERSION_NUM = '16'
debug_assertions = 'on'

Regards

Pavel


>
> Thanks.
>
>
>

Re: Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause

[Laurenz Albe]

On Sun, 2022-09-18 at 18:24 +0300, Евгений Плискин wrote:
> This is not a bug report but rather a suggestion.
> 
> Suppose database contains an index on a boolean column like this:
>   create table1(id int, amount float, best_record boolean);
>   create index index1 on table1(best_record) where best_record is true;
> 
> And suppose we issue a query like this:
>   select * from table1 where best_record = true;
> 
> The planner does not try to use an index for this query because an index 
> condition (bflag is true) does not literally match the query (bflag = true). 
> But is that reasonable?
> The index does not cover records where "best_record" is FALSE or NULL.
> And similarly the query is not interested in any records where "best_record" 
> is FALSE or NULL.
> So why not use this index for this query?

Because the conditions are different:

SELECT NULL = TRUE, NULL IS TRUE;
 ?column? │ ?column? 
══╪══
  │ f
(1 row)

The first result is NULL.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Adrian Klaver]

On 9/18/22 21:20, Bryn Llewellyn wrote:



Thanks. I can't, even now, formulate a search that finds this—unless I 
know that it's a kind of array constructor. (I'll concede, with the 
benefit of hindsight, that I should have thought of that.) I hope that 
I'll be able to learn to navigate the PG docs better over time.


The way I found it:

1) Use documentation search box.

2) Enter array(

3) It was in the fifth item PostgreSQL: Documentation: 14: 4.2. Value 
Expressions. The first four dealt with functions or the index and I knew 
they where not relevant.




Why is the "array()" constructor not found in "pg_proc"? After all, 
section 4.2.12 refers to "array_agg()" as a constructor. And that *is* 
found in "pg_proc".



The only place I see array_agg in 4.2.x is 4.2.7. Aggregate Expressions.

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause

[Tom Lane]

Laurenz Albe  writes:
> On Sun, 2022-09-18 at 18:24 +0300, Евгений Плискин wrote:
>> So why not use this index for this query?

> Because the conditions are different:

> SELECT NULL = TRUE, NULL IS TRUE;
>  ?column? │ ?column? 
> ══╪══
>   │ f
> (1 row)

> The first result is NULL.

Nonetheless, indxpath.c knows it can transform between "bool = true"
and "bool IS TRUE" for the purpose of making an index search
qualification, so it seems a bit odd that we fail to do the equivalent
transformation when attempting to prove an index predicate.

It'd be possible to improve this by adding some proof rules to
predicate_implied_by_simple_clause: I think both "x => x IS TRUE"
and the converse would be valid per the proof requirements, and
if you wanted to gild the lily it'd likely be possible to handle
some related cases like "x => x IS NOT FALSE".

Whether it's worth the cycles isn't too clear to me, but we could
argue about that if somebody submitted a patch.

regards, tom lane

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
> b...@yugabyte.com wrote:
> 
>> Thanks. I can't, even now, formulate a search that finds this—unless I know 
>> that it's a kind of array constructor. (I'll concede, with the benefit of 
>> hindsight, that I should have thought of that.) I hope that I'll be able to 
>> learn to navigate the PG docs better over time.
> 
> The way I found it:
> 
> 1) Use documentation search box.
> 
> 2) Enter array(
> 
> 3) It was in the fifth item PostgreSQL: Documentation: 14: 4.2. Value 
> Expressions. The first four dealt with functions or the index and I knew they 
> where not relevant.

I just repeated exactly those five search steps. I copied the first ten results 
at the end. Yes, the fifth hit is the link to where the account of the array() 
constructor is found. But you can't tell this from the hitlist itself. And 
searching in the page of the first twenty hits for "array(" finds nothing 
there. I do see that if you know the answer in advance, then you could realize 
that hit #5 is what you want. But the wording "a specific element of the array 
value can be extracted" didn't help me.

Never mind, I trust that I'll remember where to look now.



Result pages: 1 2 3 4 5 6 7 8 9 Next

1. PostgreSQL: Documentation: 14: 8.15. Arrays [8.21]
...Arrays Prev Up Chapter 8. Data Types Home Next 8.15. Arrays 8.15.1. 
Declaration of Array...
https://www.postgresql.org/docs/14/arrays.html

2. PostgreSQL: Documentation: 14: 9.19. Array Functions and Operators [5.04]
...Array Functions and Operators Prev Up Chapter 9. Functions and Operators 
Home Next 9.19. Array...
https://www.postgresql.org/docs/14/functions-array.html

3. PostgreSQL: Documentation: 14: 9.16. JSON Functions and Operators [4.25]
...array (array elements are indexed from zero, but negative integers count 
from the end). '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json...
https://www.postgresql.org/docs/14/functions-json.html

4. PostgreSQL: Documentation: 14: Index [2.63]
...array, Arrays accessing, Accessing Arrays constant, Array Value Input 
constructor, Array Constructors declaration, Declaration of Array...
https://www.postgresql.org/docs/14/bookindex.html

5. PostgreSQL: Documentation: 14: 4.2. Value Expressions [2.27]
...array type, then a specific element of the array value can be extracted by 
writing...
https://www.postgresql.org/docs/14/sql-expressions.html

6. PostgreSQL: Documentation: 14: 8.14. JSON Types [1.76]
...array on the right side is not considered contained within the -- array on 
the left...
https://www.postgresql.org/docs/14/datatype-json.html

7. PostgreSQL: Documentation: 14: 66.3. Extensibility [1.33]
...array of operators and comparison values */ ScanKey orderbys; /* array of 
ordering operators and comparison * values...
https://www.postgresql.org/docs/14/spgist-extensibility.html

8. PostgreSQL: Documentation: 14: F.18. intarray [1.33]
...arrays. Although they will accept input arrays of more dimensions, the data 
is treated as though...
https://www.postgresql.org/docs/14/intarray.html

9. PostgreSQL: Documentation: 14: CREATE TYPE [1.33]
...array type; for example, it will not be a candidate for the result type of 
ARRAY...
https://www.postgresql.org/docs/14/sql-createtype.html

10. PostgreSQL: Documentation: 14: 9.24. Row and Array Comparisons [1.15]
...Array Comparisons Prev Up Chapter 9. Functions and Operators Home Next 9.24. 
Row and Array...
https://www.postgresql.org/docs/14/functions-comparisons.html

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Why is the "array()" constructor not found in "pg_proc"?
> 
> Because it isn't a function. Yeah, it kind of looks like one, but its 
> argument is a subquery. If SQL had first-class functions and closures, maybe 
> ARRAY() could be implemented as an ordinary function. But I don't see any 
> plausible way to do that as things stand.
> 
> There are a bunch of other things that look like functions but aren't in 
> pg_proc, too :-(. Most of them are just catering to the SQL committee's weird 
> allergy to writing functions with plain function syntax. But ARRAY()'s 
> problem is semantic not syntactic.

There must be a significant difference between this:

  select 'dog'

and this:

  (select 'dog')

This works fine:

  select length( (select 'dog') )

But without the doubled parentheses, it causes a syntax error.

On the other hand, an extra pair of surrounding parentheses here

  select array( (values (17), (42)) )

while not necessary, *is* tolerated.

All this started because I had wrongly assumed that "pg_terminate_backend()" 
would have the same character as "array()" by not being subject to the 
"execute" privilege — just as is the case for all SQL built-ins in Oracle 
database, like "length()". I have a better mental model now.

Anyway, I know what to do in future. I'll simply look in pg_proc on a 
case-by-case basis.

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[David G. Johnston]

On Mon, Sep 19, 2022 at 9:44 AM Bryn Llewellyn  wrote:

>
> There must be a significant difference between this:
>
>   select 'dog'
>

That isn't anything in particular (it is a command by itself, it can be a
subquery in a FROM clause or the ARRAY() expression.

>
> and this:
>
>   (select 'dog')
>

That is a scalar subquery the produces exactly one row and one column that
can be substituted in anyplace a single value is required.


> This works fine:
>
>   select length( (select 'dog') )
>
> But without the doubled parentheses, it causes a syntax error.
>

Scalar subqueries are required to have surrounding parentheses.

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES

"A scalar subquery is an ordinary SELECT query in parentheses that returns
exactly one row with one column."


> On the other hand, an extra pair of surrounding parentheses here
>
>   select array( (values (17), (42)) )
>
> while not necessary, *is* tolerated.
>

An actual subquery works here so the parentheses are grouping in nature and
not an inherent part of the syntax.

David J.

Re: Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Tom Lane]

Bryn Llewellyn  writes:
> This works fine:
>   select length( (select 'dog') )
> But without the doubled parentheses, it causes a syntax error.

The parens are required for it to be a valid scalar subquery, as per
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES

The reason ARRAY() is different is exactly that its argument is
directly a subquery, not an expression that chances to be a
scalar subquery.

> On the other hand, an extra pair of surrounding parentheses here
>   select array( (values (17), (42)) )
> while not necessary, *is* tolerated.

You can pretty much always add *extra* parens in any expression
context.

regards, tom lane

I slipped up so that no existing role allows connection. Is rescue possible?

[Bryn Llewellyn]

*Summary*

Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to 
create a new one within my existing PG 14.5 software env?

*Detail*

This is a sandbox PostgreSQL 14.5 cluster on my MacBook and  it contains 
nothing of value. I was doing some empirical destructive tests with a view to 
clarifying my mental model. In the belief that a superuser is unstoppable, I 
had set all the options like "createdb" and "createrole" for the "postgres" 
role to their "no" mode. And I couldn't detect any problems. However, I'd left 
the "login" option in its "yes" mode.

The rationale here was informed by tests with superusers created (and then 
dropped) ad hoc. I found that setting "nologin" trumped the otherwise 
unstoppability of a superuser. This was a surprise.

This was also nice because I haven't yet seen a use case that needs more 
than one superuser in the whole cluster. Yet I'm stuck with a second superuser, 
in addition to "postgres", with the name of the macOS user, "Bllewell" (with 
init cap) that owns the installation. And it has to exist because it owns the 
"pg_catalog" schema (and its cousins) an every database. So I set "nologin" for 
"Bllewell".

I tried both "drop role postgres" and "drop database postgres". They both 
failed with errors to the effect that they are needed by the system. Then came 
the test whose outcome was to lock me out totally. At this point, "\du" without 
the "S" qualifier listed only "postgres" and "Bllewell". I did this:

alter user postgres with nosuperuser;

I expected an error—just as I'd got on attempting to drop the "postgres" role 
or the "postgres" database. But it quietly succeeded. And then I hit a wrong 
key and exited my "psql" session. Now I can't start a psql session. Trying with 
one of the two available roles gets me this:

role "Bllewell" is not permitted to log in

And trying with the other gets me this:

permission denied for database "postgres"… User does not have CONNECT 
privilege… permission denied for database "postgres"

Neither error is a lie. The first reflects my intention. And the second 
reflects the fact that, while "postgres" was a superuser, it didn't need an 
explicit "connect" privilege on any database.

My "hba" file says "trust"—and, before locking myself out, I was happily able 
to start sessions without a password challenge.

With Oracle Database, the roughly equivalent user, called "SYS", is what it is 
by virtue of its intrinsic immutable hard-coded identity. And a person who can 
authorize as the O/S user that owns the installation can always start a 
session. This is regarded as the last ditch rescue mechanism. But I'm already 
authorised as the O/S user that owns the PG installation. And I'm locked out.

Re: I slipped up so that no existing role allows connection. Is rescue possible?

[Tom Lane]

Bryn Llewellyn  writes:
> Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to 
> create a new one within my existing PG 14.5 software env?

Stop the cluster, start a single-user session ("postgres --single"),
re-grant superuser to the postgres user and/or whatever else you
wish you could take back, end that session, restart the cluster.

(You're not the first to mess up like this.)

regards, tom lane

Re: I slipped up so that no existing role allows connection. Is rescue possible?

[David G. Johnston]

On Monday, September 19, 2022, Bryn Llewellyn  wrote:

> **Summary**
>
> Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to
> create a new one within my existing PG 14.5 software env?
>
>
Running the postgres executable in single user mode should provide an
avenue I believe.

David J.

GIST combo index condition chosen for users queries is different from table owner's query

[Dennis White]

My project is using PostgreSQL 12.9 and has a table that gets millions of
positions of ships every day. It is partitioned by tier (the importance of
position) and sub-partitioned by time.
We also use RLS to restrict access to rows in the table.

Our problem is the query planner will not let user queries use the spatial
condition like it does for the table owner and as a result the queries
don't ever finish.
I created a minimal test case of the problem and have attached all of the
statements needed to recreate the problem

The key statements are as follows:

CREATE TABLE IF NOT EXISTS test.qtest_position (
position_id bigint NOT NULL,
tiersmallint,
toi timestamptz(0) NOT NULL,
track_idbigint,
security_tagvarchar(33),
posit   public.geometry(Point)
) PARTITION BY LIST (tier);

CREATE TABLE IF NOT EXISTS test.qtest_posit_t1 PARTITION OF
test.qtest_position
FOR VALUES IN (1) PARTITION BY RANGE (toi);

CREATE TABLE test.qtest_posit_t1_template ( LIKE test.qtest_posit_t1
INCLUDING ALL );
ALTER TABLE test.qtest_posit_t1_template ADD PRIMARY KEY (position_id);
CREATE INDEX ON test.qtest_posit_t1_template (track_id, toi);
CREATE INDEX ON test.qtest_posit_t1_template USING GIST (posit, toi)
include (security_tag);

SELECT partman.create_parent('test.qtest_posit_t1', 'toi', 'native', '1
days',
  p_premake := 20, p_start_partition := '2022-09-01', p_template_table :=
'test.qtest_posit_t1_template');

Here's a simple example of a user query and the resulting plan:

set role test_user;
SET
explain analyze SELECT
position_961.SECURITY_TAG,
position_961.TRACK_ID,
position_961.POSIT,
position_961.POSITION_ID,
position_961.TOI
FROM
test.qtest_position position_961
WHERE
(ST_Intersects( position_961.POSIT,
ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[
67.729806,
15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')
 , 4326)  )
)  AND
 position_961.TOI BETWEEN '2022-09-02T20:28:42.753Z'::TIMESTAMPTZ AND
'2022-09-03T20:28:42.753Z'::TIMESTAMPTZ
  and test.user_has_access(security_tag) = '1';



QUERY PLAN






--
 Append  (cost=0.42..495598323.78 rows=2 width=60) (actual
time=7246.524..31614.944 rows=699 loops=1)
   ->  Index Scan using
qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx on
qtest_posit_t1_p2022_09_02 position_961  (cost=0.42..72721949.89 rows=1
width=60) (actual time=7246.523..9560.695 rows=
121 loops=1)
 Index Cond: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with
time zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time
zone))
 Filter: ((test.user_has_access(security_tag) = '1'::text) AND
(test.user_has_access(security_tag) = '1'::text) AND st_intersects(posit,
'010320E610010005002C9B3924B5EE504091F3
FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
 Rows Removed by Filter: 2851335
   ->  Seq Scan on qtest_posit_t1_p2022_09_03 position_961_1
 (cost=0.00..422876373.88 rows=1 width=60) (actual time=84.624..22054.050
rows=578 loops=1)
 Filter: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with time
zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone)
AND (test.user_has_access(security_tag) = '1'::tex
t) AND (test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'010320E610010005002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9
F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
 Rows Removed by Filter: 16561241
 Planning Time: 0.369 ms
 Execution Time: 31615.459 ms
(10 rows)

-- run the same query as table owner
set role test_owner;
SET
explain analyze SELECT
position_961.SECURITY_TAG,
position_961.TRACK_ID,
position_961.POSIT,
position_961.POSITION_ID,
position_961.TOI
FROM
test.qtest_position position_961
WHERE
(ST_Intersects( position_961.POSIT,
ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[
67.729806,
15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')
 , 4326)  )
)  AND
 position_961.TOI BETWEEN '

Re: I slipped up so that no existing role allows connection. Is rescue possible?

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> b...@yugabyte.com writes:
>> 
>> Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to 
>> create a new one within my existing PG 14.5 software env?
> 
> Stop the cluster, start a single-user session ("postgres --single"), re-grant 
> superuser to the postgres user and/or whatever else you wish you could take 
> back, end that session, restart the cluster.

So nice to find this waiting for me when I got back to the keyboard after a 
late lunch. Thanks, Tom. And thanks to David, too who said much the same. I'll 
note how I spelled the magic—as a little memo for me:

postgres --single -D /usr/local/var/postgres postgres

The world that the "backend> " prompt opened up for me was rather basic. But 
even so, "alter user postgres with superuser" worked fine. (This was the only 
rescue that I needed.) And, after a normal restart, everything looks normal 
again now from the psql prompt.

This leads to some follow-up questions. But I'll start a new thread.

Re: GIST combo index condition chosen for users queries is different from table owner's query

[Tom Lane]

Dennis White  writes:
> Is there something I can do to allow users queries to use the index with a
> condition like that used for the table owner's query?

It looks like the problem in your badly-optimized query is that
there is not an indexable condition being extracted from the
ST_INTERSECTS() call.  In the well-optimized one, we've got

   ->  Index Scan using qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx...
 Index Cond: ((posit &&
'010320E610010005002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry)
 AND ...
 Filter: ((test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'010320E610010005002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))

I presume what's happening there is that st_intersects() has got a support
function that knows that "st_intersects(foo, bar)" implies "foo && bar"
and the latter can be used with an index on foo.

However, to do that in the presence of RLS we have to know that the
extracted condition would be leakproof.  I'm not sure that the geometry &&
operator is leakproof in the first place; and even if it is, we might not
consider this option unless st_intersects() is also marked leakproof,
which most likely it isn't.  You'd have to ask the PostGIS crew whether
either of those things would be safe to consider leakproof ... but I'm
betting they'll say that doing so would create an unreasonably large
bug surface.

By and large, the combination of RLS with complicated WHERE conditions
is just deadly for performance, because most of the time we won't be
able to use the WHERE conditions until after applying the RLS filter.
Do you really need to use RLS in this application?  If you're stuck
doing so, you could maybe ameliorate things by implementing the RLS
check functions in the fastest way you can, like writing C code
for them.

regards, tom lane

Re: I slipped up so that no existing role allows connection. Is rescue possible?

[Adrian Klaver]

On 9/19/22 16:15, Bryn Llewellyn wrote:

//

/t...@sss.pgh.pa.us  wrote:
/


So nice to find this waiting for me when I got back to the keyboard 
after a late lunch. Thanks, Tom. And thanks to David, too who said much 
the same. I'll note how I spelled the magic—as a little memo for me:


postgres --single -D /usr/local/var/postgres postgres

The world that the "backend> " prompt opened up for me was rather basic. 


Which is documented here:

https://www.postgresql.org/docs/current/app-postgres.html#APP-POSTGRES-SINGLE-USER


--
Adrian Klaver
adrian.kla...@aklaver.com

Re[4]: CVE-2022-2625

[misha1966 misha1966]

Thank you all! Everything worked out!

CVE-2022-2625 contains a lot more than it seems...
 
  
>Пятница, 16 сентября 2022, 0:19 +09:00 от Tom Lane :
> 
>=?UTF-8?B?bWlzaGExOTY2IG1pc2hhMTk2Ng==?= < mmisha1...@bk.ru > writes:
>> Is there a patch for 9.6 ?
>No; that's out of support too.
>
>You might find that adapting the v10 patch back to 9.6, and
>thence to 9.5, would be easier than trying to do it in one step.
>
>I'm a little bemused by your fixation on this particular CVE,
>though. As such things go, it's not a very big deal. It's only
>of interest if you are routinely installing new extensions, *and*
>those extensions' scripts contain insecure uses of CREATE OR
>REPLACE/CREATE IF NOT EXISTS, *and* you can't fix the extensions
>instead. I would not have thought an institution that's so
>frozen that it can't update to an in-support PG version would be
>doing a lot of new extension installations.
>
>In any case, the real thing you ought to be focusing on is whether
>you are running back-ported patches for any of the *other* CVE-worthy
>security bugs we've fixed since 9.5 went EOL. And how about the
>data-corrupting bugs? Most longtime PG developers think data
>corruption hazards are a good deal more important than a lot of
>the stuff we assign CVEs to. Almost every CVE we've ever issued is
>only relevant if you have hostile actors able to issue arbitrary SQL
>in your database, in which case you're in a world of trouble anyway.
>
>regards, tom lane
 

Re: I slipped up so that no existing role allows connection. Is rescue possible?

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>>> t...@sss.pgh.pa.us wrote:
>>> 
>>> ...
>> 
>> So nice to find this waiting for me when I got back to the keyboard after a 
>> late lunch. Thanks, Tom. And thanks to David, too who said much the same. 
>> I'll note how I spelled the magic—as a little memo for me:
>> 
>> postgres --single -D /usr/local/var/postgres postgres
>> 
>> The world that the "backend> " prompt opened up for me was rather basic. 
> 
> Which is documented here:
> 
> www.postgresql.org/docs/current/app-postgres.html#APP-POSTGRES-SINGLE-USER

Yes, after Tom's hint, a search for "single-user" took me to that page. But, 
beginner as I am, I didn't know that single-user mode was the thing that I 
needed. I need a remedial class. Something like "PostgreSQL for those whose 
mental model has been conditioned by decades of working with Oracle Database".