Re: insufficient privilege with pg_read_all_stats granted

[Pavel Luzanov]

On 20.08.2024 23:50, Costa Alexoglou wrote:
I run PostgreSQL v15.8 (docker official image), and there is an issue 
when reading pg_stat_staments table with a result of query most of the 
times having `` value.


I have created the user that I use to fetch the data with the 
following way:

```
CREATE USER abcd WITH NOSUPERUSER NOCREATEROLE NOINHERIT LOGIN;

GRANT pg_read_all_stats, pg_stat_scan_tables, pg_read_all_settings to 
abcd;


I think the problem is in the NOINHERIT attribute for the abcd role.
abcd does not inherit the privileges gained from being included in other roles.

In v15, to see the text of SQL commands in pg_stat_statements, you can either 
explicitly
switch from abcd role to the pg_read_all_stats role (SET ROLE pg_read_all_stats)
or set the INHERIT attribute for abcd role (alter role abcd inherit).

In v16, you can explicitly specify how to get privileges in the GRANT command:

grant pg_read_all_stats to abcd with inherit true, set false;




I also tried with PostgreSQL v14.13, and this was not the case, it was 
working fine as expected.
Then I tried v16.4 and v17beta3, and I faced the privilege> issue, so I guess something changed v15 onwards?


But I don't understand why it worked in v14.
Probablysomethinghas changed,  butIcouldn't quicklyfindwhatexactly.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: insufficient privilege with pg_read_all_stats granted

[Pavel Luzanov]

On 21.08.2024 10:50, Pavel Luzanov wrote:

But I don't understand why it worked in v14.
Probablysomethinghas changed,  butIcouldn't quicklyfindwhatexactly.


Ifoundit.
https://github.com/postgres/postgres/commit/6198420a

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

security invoker review need full select (all columns) to do DML?

[jian he]

hi.

the following setup is extract from src/test/regress/sql/updatable_views.sql
you can search keywords: "-- ordinary view on top of security invoker
view permissions"


CREATE USER regress_view_user1;
CREATE USER regress_view_user2;
CREATE USER regress_view_user3;
drop table if exists base_tbl cascade;
-- ordinary view on top of security invoker view permissions
CREATE TABLE base_tbl(a int, b text, c float);
INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);

SET SESSION AUTHORIZATION regress_view_user1;
CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
ALTER VIEW rw_view1 SET (security_invoker = true);
SELECT * FROM rw_view1;  -- not allowed
UPDATE rw_view1 SET aa=aa;  -- not allowed
MERGE INTO rw_view1 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON t.aa = v.a
  WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed

SET SESSION AUTHORIZATION regress_view_user2;
CREATE VIEW rw_view2 AS SELECT cc AS ccc, aa AS aaa, bb AS bbb FROM rw_view1;
GRANT SELECT, UPDATE ON rw_view2 TO regress_view_user3;
SELECT * FROM rw_view2;  -- not allowed
UPDATE rw_view2 SET aaa=aaa;  -- not allowed
MERGE INTO rw_view2 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON
t.aaa = v.a
  WHEN NOT MATCHED THEN INSERT VALUES (v.c, v.a, v.b); -- not allowed

RESET SESSION AUTHORIZATION;
GRANT SELECT(a,b) ON base_tbl TO regress_view_user1;
GRANT UPDATE (a, b) ON base_tbl TO regress_view_user1;

SET SESSION AUTHORIZATION regress_view_user1;
SELECT a, b FROM base_tbl; -- ok
SELECT aa, bb FROM rw_view1; -- ok
UPDATE rw_view1 SET aa=11, bb=22; --ok
UPDATE rw_view1 SET aa=11, bb=22 returning aa,bb; --not ok.

Should the last query be allowed?
The (Updatable Views section in create_view.sgml) didn't explain it or
I didn't fully understand it.

pgbackrest restore with a checkpoint and timestamp after the checkpoint

[KK CHN]

List,


Query:
Can I  perform a  pgbackrest restore with  the last backup diff or incr
+  further transactions in the WAL  replayed to restore the  transactions
that happened after the last  pgbackrest backup checkpoint   ?


Scenario:

I am trying to perform this and unable to get a solution.

I have 20th Aug 2024 A Differential backup as follows..


*   diff backup: 20240820-152602F_20240820-160402D*
* timestamp start/stop: 2024-08-20 16:04:02+05:30 / 2024-08-20
16:04:05+05:30*


1. Today (21st Aug 2024)  I've performed a  table drop  as follows and
noted the time stamps in BOLD highlighted


edb=# \dt
List of relations
 Schema |   Name   | Type  |Owner
+--+---+--
 public | foo  | table | enterprisedb
 public | important_table  | table | enterprisedb
 public | important_table2 | table | enterprisedb
 public | important_table4 | table | enterprisedb
(4 rows)

edb=# select now();
   now
--
* 21-AUG-24 13:58:31.611403 +05:30  // Before  table drop *
(1 row)

*edb=# drop table important_table4;*
DROP TABLE
edb=# \dt
List of relations
 Schema |   Name   | Type  |Owner
+--+---+--
 public | foo  | table | enterprisedb
 public | important_table  | table | enterprisedb
 public | important_table2 | table | enterprisedb
(3 rows)

edb=# select now();
   now
--
 *21-AUG-24 13:58:58.379552 +05:30  //after table drop*
(1 row)

edb=#


2.  Issue  as follows ...

When I do a  restore with the above differential backup and time stamp of
recovery  upto 21-AUG-24 13:58:*48.611403*+05:30"  it recovers the database
and I am able to see the dropped table  important_table4 recovered.


  Query:   IF  THIS IS NOT the expected result  which I want..
I want the restored  db without the deleted table 



So I am  recording a time stamp   after the table drop as seen above.

But when I give the time stamp anything greater than   21-AUG-24
13:58:48.611403+05:30"
(Eg : time stamp  13:58:49.611403+05:30)with an  expectation  that the
restored db server must show the dropped state ( important_table4   not to
present there ) .

The  edb restart always fails after  pgbackrest restore with any value
higher than  timestamp  13:58:48.611403 Why ??

 As per my understanding any restore referring to a checkpoint ( the
differential backup taken as  listed above)  and a time stamp of todays
after dropping the table important_table4 must  replay the WAL files after
the differential backup taken dated as seen above and upto the timestamp
(todays)after dropping the importatn_table4. C*orrect me If  I am wrong
here  ?*

I am *expecting* to see   theedb=# \dt
without the dropped table  " important_table4 " ( if the WAL replayed
upto the timestamp as I specified, Is this possible ? ) .   But this never
gets me a successful restart  of the edb server  ?



Here the output   :


[root@uaterssdrservice01 bin]# sudo -u enterprisedb pgbackrest
--stanza=Demo --delta --set=*20240820-152602F_20240820-160402D*
 --target-timeline=current --type=time  --target="21-AUG-24
13:58:49.611403+05:30"
--target-action=promote restore


2024-08-21 14:34:17.116 P00   INFO: restore command begin 2.52.1: --delta
--exec-id=252857-6013404c --log-level-console=info --log-level-file=debug
--pg1-path=/var/lib/edb/as16/data --pg-version-force=16
--repo1-host=10.10.20.7 --repo1-host-user=postgres
--set=20240820-152602F_20240820-160402D --spool-path=/var/spool/pgbackrest
--stanza=Repo --target="21-AUG-24 13:58:49.611403+05:30"
--target-action=promote --target-timeline=current --type=time
2024-08-21 14:34:17.469 P00   INFO: repo1: restore backup set
20240820-152602F_20240820-160402D, recovery will start at 2024-08-20
16:04:02
2024-08-21 14:34:17.470 P00   INFO: remove invalid files/links/paths from
'/var/lib/edb/as16/data'
2024-08-21 14:34:18.274 P00   INFO: write updated
/var/lib/edb/as16/data/postgresql.auto.conf
2024-08-21 14:34:18.277 P00   INFO: restore global/pg_control (performed
last to ensure aborted restores cannot be started)
2024-08-21 14:34:18.277 P00   INFO: restore size = 89.8MB, file total = 2588
2024-08-21 14:34:18.278 P00   INFO:* restore command end: completed
successfully* (1164ms)

But  Issue is  as follows.

[root@uaterssdrservice01 bin]# systemctl  start  edb-as-16.service  (*No
Errors in console*)
[root@uaterssdrservice01 bin]# sudo -u enterprisedb psql edb
psql: error: connection to server on socket "/tmp/.s.PGSQL.5444" failed: No
such file or directory
Is the server running locally and accepting connections on that
socket?
[root@uaterssdrservice01 bin]#


Why the server restart always fails   on restore with this  time stamp (
greater than   21-AUG-24 13:58:49.611403+05:30 )   ??

Or I have to understand: Never can we  restore   ä  db server after the
last checkpoint ,  a

Re: security invoker review need full select (all columns) to do DML?

[Dean Rasheed]

On Wed, 21 Aug 2024 at 10:08, jian he  wrote:
>
> the following setup is extract from src/test/regress/sql/updatable_views.sql
> you can search keywords: "-- ordinary view on top of security invoker
> view permissions"
>
> CREATE TABLE base_tbl(a int, b text, c float);
> INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
>
> SET SESSION AUTHORIZATION regress_view_user1;
> CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
> ALTER VIEW rw_view1 SET (security_invoker = true);
>
> RESET SESSION AUTHORIZATION;
> GRANT SELECT(a,b) ON base_tbl TO regress_view_user1;

In updatable_views.sql that GRANT is actually

GRANT SELECT ON base_tbl TO regress_view_user1;

Without that, the view is effectively unusable by regress_view_user1
because it selects from column c of base_tbl, and regress_view_user1
lacks permissions on that column.

This is consistent with simple subqueries:

select a, b from (select a,b from base_tbl); -- ok
 a |   b
---+---
 1 | Row 1
(1 row)

select a, b from (select a,b,c from base_tbl); -- not allowed
ERROR:  permission denied for table base_tbl

The user must have select permissions on all columns selected by the
subquery/view, because we don't go through the outer query to check
which columns are actually referred to. Now maybe we could, but I
suspect that would be quite a lot of effort because you'd need to be
sure that the column wasn't referred to anywhere in either the outer
query or the subquery itself (e.g., in WHERE clauses, etc.).

Regards,
Dean

Re: security invoker review need full select (all columns) to do DML?

[Tom Lane]

Dean Rasheed  writes:
> The user must have select permissions on all columns selected by the
> subquery/view, because we don't go through the outer query to check
> which columns are actually referred to. Now maybe we could, but I
> suspect that would be quite a lot of effort because you'd need to be
> sure that the column wasn't referred to anywhere in either the outer
> query or the subquery itself (e.g., in WHERE clauses, etc.).

I'd argue that we should check that permission regardless, and are
probably required to by the SQL spec.  You don't normally get to
escape permission checks when bits of the query are optimized away.
(This is why permission checks are done on the range table not the
plan tree.)

regards, tom lane

Query tuning question

[Dirschel, Steve]

Aurora Postgres version 13.7.

Table definition:

acquisition_channel_db=> \d acquisition_channel.acquired_object
Table 
"acquisition_channel.acquired_object"
 Column |   Type   | Collation 
| Nullable | Default
+--+---+--+-
acquired_object_uuid   | character varying(36)|   | 
not null |
acquired_object_name   | text |   | 
not null |
acquired_object_size   | bigint   |   | 
 |
acquisition_run_record_id  | bigint   |   | 
 |
correlation_id | character varying(36)|   | 
 |
acquired_datetime  | timestamp with time zone |   | 
 |
checksum   | character varying(128)   |   | 
 |
acquisition_method_id  | bigint   |   | 
 |
ol_version | integer  |   | 
not null |
created_datetime   | timestamp with time zone |   | 
not null |
updated_datetime   | timestamp with time zone |   | 
 |
status | character varying(50)|   | 
 |
parent_acquired_object_uuid| character varying(36)|   | 
 |
extracted_from_object_path | text |   | 
 |
resource_group_id  | bigint   |   | 
 |
s3_gcs_bucket  | character varying(100)   |   | 
 |
s3_key | character varying(500)   |   | 
 |
metadata_s3_gcs_bucket | character varying(100)   |   | 
 |
metadata_s3_key| character varying(500)   |   | 
 |
routing_result_acquisition_channel_id  | bigint   |   | 
 |
acquired_object_type   | character varying(100)   |   | 
 |
created_by_id  | integer  |   | 
 |
updated_by_id  | integer  |   | 
 |
metadata   | jsonb|   | 
 |
acquired_object_id | bigint   |   | 
not null | nextval('acquisition_channel.acquired_object_id_seq'::regclass)
routed_to_acquisition_channel_datetime | timestamp with time zone |   | 
 |
routed_to_acquisition_channel_id   | bigint   |   | 
 |
rendition_guid | character varying(36)|   | 
 |
revision_guid  | character varying(36)|   | 
 |
channel_availability_status| character varying(100)   |   | 
 |
mime_type  | character varying(100)   |   | 
 |
encryption_public_key_hash | text |   | 
 |
acquired_metadata  | jsonb|   | 
 |
original_acquired_object_name  | text |   | 
 |
acquired_family_uuid   | character varying(36)|   | 
 |
last_broadcast_datetime| timestamp with time zone |   | 
 |
original_checksum  | character varying(128)   |   | 
 |
Indexes:
"acquired_object_pkey" PRIMARY KEY, btree (acquired_object_uuid)
"acquired_family_uuid_idx" btree (acquired_family_uuid)
"acquired_object_acq_method_id_acq_dt_acquired_object_uuid" btree 
(acquisition_method_id, acquired_datetime DESC NULLS LAST, acquired_object_uuid)
"acquired_object_acquired_items_initial_ui_page" btree 
(acquisition_method_id, acquired_datetime DESC NULLS LAST, 
acquired_object_uuid) WHERE status::text <> 'TEST'::text AND 
parent_acquired_object_uuid IS NULL
"acquired_object_acquired_object_id_unq" UNIQUE, btree (acquired_object_id)
"acquired_object_acquired_object_name" btree (lower(acquired_object_name))
"acquired_object_acquisition_method_id" btree (acquisition_method_id)
"acquired_object_acquisition_run_record_id" btree 
(acquisition_run_record_id)
"acquired_object_checksum" btree (checksum)
"acquired_object_correlation_id" btree (correlation_id)
"acquired_object_idx2" btree (parent_acquired_object_uuid, 
extracted_from_object_path)
"a

Npgsql.dll

[Andy Hartman]

I'm trying to get the latest version so I can use it in my Powershell
script. I think I have 4.51 but having issues connection to server from
script.

I get this error:

Exception calling "Open" with "0" argument(s): "Authentication method not
supported (Received: 10)"

Re: Npgsql.dll

[Daniel Gustafsson]

> On 21 Aug 2024, at 18:58, Andy Hartman  wrote:
> 
> I'm trying to get the latest version so I can use it in my Powershell script. 
> I think I have 4.51 but having issues connection to server from script.
> 
> I get this error:
> 
> Exception calling "Open" with "0" argument(s): "Authentication method not 
> supported (Received: 10)"

Thats unfortunately a known regression in the latest minor releases, and will
be fixed in the upcoming releases.  Using a recent version of npgsql and
ideally TLS1.3 is a workaround.


--
Daniel Gustafsson

unable to upgrade postgres extensions

[plsqlvids01 plsqlvids01]

AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per
https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x
am
trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the
same error, how to upgrade them?

postgres=> select version();

version-
 PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

postgres=> select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable |
extversion | extconfig |
extcondition---+-+--+--+++---+---
 14501 | plpgsql |   10 |   11 | f  | 1.0
  |   |
 16410 | pg_cron |   10 |   11 | f  | 1.6
  | {16425,16423,16447,16445} | {"","","",""}
 16413 | pgaudit |   10 |16412 | t  | 1.4.3
  |   |
(3 rows)
postgres=> select * from pg_available_extensions where name in
('pgaudit','pg_cron');
  name   | default_version | installed_version |
comment-+-+---+-
 pgaudit | 16.0| 1.4.3 | provides auditing functionality
 pg_cron | 1.6 | 1.6   | Job scheduler for PostgreSQL
(2 rows)

postgres=> ALTER EXTENSION pgaudit update to "16.0";
ERROR:  extension "pgaudit" has no update path from version "1.4.3" to
version "16.0"
postgres=> ALTER EXTENSION pgaudit update to "16.0.0";
ERROR:  extension "pgaudit" has no update path from version "1.4.3" to
version "16.0.0"
postgres=> ALTER EXTENSION pgaudit update to "16.0.1";
ERROR:  extension "pgaudit" has no update path from version "1.4.3" to
version "16.0.1"

postgres=> ALTER EXTENSION pg_cron update to "1.6.1";
ERROR:  extension "pg_cron" has no update path from version "1.6" to
version "1.6.1"

Re: insufficient privilege with pg_read_all_stats granted

[Costa Alexoglou]

> I think the problem is in the NOINHERIT attribute for the abcd role.

Indeed that is the issue, thanks for helping find this out

>

Re: unable to upgrade postgres extensions

[Adrian Klaver]

On 8/21/24 12:14, plsqlvids01 plsqlvids01 wrote:
AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per 
https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x  am trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the same error, how to upgrade them?


|postgres=> select version(); version 
- PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit (1 row) postgres=> select * from pg_extension; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ---+-+--+--+++---+--- 14501 | plpgsql | 10 | 11 | f | 1.0 | | 16410 | pg_cron | 10 | 11 | f | 1.6 | {16425,16423,16447,16445} | {"","","",""} 16413 | pgaudit | 10 | 16412 | t | 1.4.3 | | (3 rows) postgres=> select * from pg_available_extensions where name in ('pgaudit','pg_cron'); name | default_version | installed_version | comment -+-+---+- pgaudit | 16.0 | 1.4.3 | provides auditing functionality pg_cron | 1.6 | 1.6 | Job scheduler for PostgreSQL (2 rows) postgres=> ALTER EXTENSION pgaudit update to "16.0"; ERROR: extension "pgaudit" has no update path from version "1.4.3" to version "16.0" postgres=> ALTER EXTENSION pgaudit update to "16.0.0"; ERROR: extension "pgaudit" has no update path from version "1.4.3" to version "16.0.0" postgres=> ALTER EXTENSION pgaudit update to "16.0.1"; ERROR: extension "pgaudit" has no update path from version "1.4.3" to version "16.0.1" postgres=> ALTER EXTENSION pg_cron update to "1.6.1"; 


Here:

https://github.com/pgaudit/pgaudit/tree/REL_16_STABLE

I don't see any provision for doing an update from one version to another.


ERROR: extension "pg_cron" has no update path from version "1.6" to version 
"1.6.1"|


Which is true:

name   | default_version | installed_version |
pg_cron | 1.6 | 1.6

You can't update as it does not exist.

How did you do the upgrade from 12.7 to 16.1?

FYI, per here:

https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-release-calendar.html

RDS is up to Postgres 16.4, which is the latest minor release and what 
you should be using.



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

Re: unable to upgrade postgres extensions

[Alvaro Herrera]

On 2024-Aug-21, plsqlvids01 plsqlvids01 wrote:

> AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per
> https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x
> am
> trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the
> same error, how to upgrade them?

> postgres=> select * from pg_available_extensions where name in
> ('pgaudit','pg_cron');
>   name   | default_version | installed_version |
> comment-+-+---+-
>  pgaudit | 16.0| 1.4.3 | provides auditing 
> functionality
>  pg_cron | 1.6 | 1.6   | Job scheduler for PostgreSQL
> 
> postgres=> ALTER EXTENSION pgaudit update to "16.0";
> ERROR:  extension "pgaudit" has no update path from version "1.4.3" to
> version "16.0"

That sounds like an RDS problem, so you should be talking to Amazon
support.  But maybe see in "select * from
pg_available_extension_versions" if the versions their docco claims are
available, actually are.  Also, the fact that a version is available
does not automatically mean that an upgrade path exists; you may need to
do the upgrade in multiple jumps, for instance for pgAudit go from 1.4.3
to 1.6.2 first, then to 1.7.0 finally to 16.0.

-- 
Álvaro Herrera PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"How strange it is to find the words "Perl" and "saner" in such close
proximity, with no apparent sense of irony. I doubt that Larry himself
could have managed it." (ncm, http://lwn.net/Articles/174769/)

Re: unable to upgrade postgres extensions

[Tom Lane]

plsqlvids01 plsqlvids01  writes:
> AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per
> https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x
> am
> trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the
> same error, how to upgrade them?

You seem to be assuming that PG extensions have versioning matching
the core server.  There's no requirement for that, and it doesn't
look like these extensions attempt to keep those numbers in sync.

The underlying compiled modules (.so files) do need to be updated
for each major server version.  But if they load and work then
that's happened.  The "extension version" is just an arbitrary
identifier for the version of the extension's SQL-level declarations,
which frequently don't need to change for a server update.

regards, tom lane