Re: Database selection
On Thu, 2023-09-21 at 02:41 +0530, veem v wrote: > However, One of the things regarding the transaction management, which I see > in > most postgres blogs is 'mvcc'(multiversion concurrency control) being an issue > at times in a heavy concurrent system, in postgres as its maintaining exact > copies of all the old versions of the rows(if someone still reading those) and > at times cleaning these(vacuuming) becomes a pain. If your workload consists of updating rows over and over at high frequency, that is particularly hard for PostgreSQL, and you might want to consider alternatives. There are ways to mitigate the impact (HOT update), but that requires some design and planning effort. > Does AWS aurora postgres depend on the same vacuuming technology for > maintaining > the transactions? > Does Aurora Mysql opt for a similar strategy for transaction management? or > any > different/better ones? Nobody except Amazon can tell. It is closed source. Yours, Laurenz Albe
Re: Changed functionality from 14.3 to 15.3
On 2023-09-20 17:53 -0400, Michael Corey wrote: > To make matters even more strange. I checked the permissions of > rds_superuser in 15 and 14 > > For 14 > GRANT pg_monitor, pg_signal_backend, rds_password, rds_replication TO > rds_superuser WITH ADMIN OPTION; > > For 15 > GRANT pg_checkpoint, pg_monitor, *pg_read_all_data*, pg_signal_backend, > *pg_write_all_data*, rds_password, rds_replication TO rds_superuser WITH > ADMIN OPTION; > > AWS added these permissions, but based on what they do you would think this > would allow the SELECTs in 15. Yes it would if sten_schema would inherit from rds_superuser. But it cannot inherit privileges from rds_superuser (indrect membership through object_creator) because object_creator was created with NOINHERIT. And INHERIT applies to direct memberships only. -- Erik
Re: Changed functionality from 14.3 to 15.3
On 2023-09-20 15:19 -0400, Michael Corey wrote: > Just to be clear in your last response are you saying on your 14.3 you are > getting the > ERROR: permission denied for table ref_media_code ? Yes: db14=> select version(); version - PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row) db14=> select current_user; current_user -- sten_schema (1 row) db14=> show search_path; search_path - "$user", ref_schema, public (1 row) db14=> select * from ref_media_code; ERROR: permission denied for table ref_media_code db14=> select * from sten_media_codes_view ; ERROR: permission denied for table ref_media_code > If this is true then it seems to be something in our setup. My guess is the missing GRANT SELECT ON ref_schema.ref_media_code TO sten_schema unless that privilege should be inherited from some other role. I think there are two possible situations if I don't mix up anything: 1. The setup script is flawed and tests should fail on both 14 and 15 because of it, but something is foul on your 14 which results in a false positive (granted privileges on 14). 2. The setup script is ok and tests should pass but something is foul on your 15 which results in a false negative (missing privileges on 15). But besides that, tests depending on existing state (something that is not part of each test setup) gives me the heebie-jeebies. I worked on a project were this was the case: Oracle databases for devs, test, and QA copied from a bunch of blessed databases. And somehow those copies were incomplete sometimes, e.g. missing constraints or indexes. > This database may have been upgraded from 13.x to 14. The sten_schema > has INHERIT when I create, but that does not mean INHERIT from > ref_schema, correct? No, unless sten_schema is also member of ref_schema which is not the case per your script. Both roles are member of object_creator though. > All the items I have created just once I have not removed or recreated any > of these for my test. The problem is impacting my real actual schemas and > was discovered after we did the upgrade to 15. I decided then to restore > the original 14 server and made two copies. I kept one as 14 and upgraded > the other to 15. Lastly, I created the test case. Can you create a 15 server from scratch and test it or do tests rely on existing data? You could dump and restore db14 from the original 14 into the new 15. pg_dump covers privileges but not roles or memberships. So you may be able to get rid of whatever may be wrong with your current 15. But then again, I don't have an explanation why the upgrade 14 -> 15 would change privileges or roles. The release notes for 15 list several changes regarding roles and privileges but I don't see how they apply here. First item of E.5.3.1.6. Privileges [1] could be relevant: "Allow table accesses done by a view to optionally be controlled by privileges of the view's caller. Previously, view accesses were always treated as being done by the view's owner. That's still the default." But view sten_media_codes_view is not defined with security_invoker=true and sten_schema is current_user and owner. [1] https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.5.3.8 -- Erik
Re: Changed functionality from 14.3 to 15.3
On 2023-09-20 14:24 -0700, David G. Johnston wrote: > On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold wrote: > > > Has your 14.3 some left-over state from previous test runs? I assume > > the server is not re-created for each test run. I was wondering if the > > roles may still exist and with additional memberships. But then again > > the script just uses CREATE ROLE. So the roles definitely do not exist > > beforehand. But what are the actual memberships of sten_schema? > > Because it must inherit SELECT on ref_media_code on 14.3. It can't be > > from object_creator because that role also gets newly created. > > > > Your description also suggests that maybe the v14 instance has altered > default privileges setup that maybe the v15 doesn't have. Not possible for the roles created in the setup script because the grantee must exist when defining default privileges. Also \dp shows only those privileges granted in the setup script. More questions that need answers: * How are the databases created? * Does the template database contribute anything, e.g. event triggers? * Any other setup scripts involved? -- Erik
Re: Changed functionality from 14.3 to 15.3
On Wed, Sep 20, 2023 at 2:48 PM Michael Corey wrote: > How can I check the default privileges? > \ddp https://www.postgresql.org/docs/current/catalog-pg-default-acl.html David J.
Re: Changed functionality from 14.3 to 15.3
Erik, To make matters even more strange. I checked the permissions of rds_superuser in 15 and 14 For 14 GRANT pg_monitor, pg_signal_backend, rds_password, rds_replication TO rds_superuser WITH ADMIN OPTION; For 15 GRANT pg_checkpoint, pg_monitor, *pg_read_all_data*, pg_signal_backend, *pg_write_all_data*, rds_password, rds_replication TO rds_superuser WITH ADMIN OPTION; AWS added these permissions, but based on what they do you would think this would allow the SELECTs in 15. On Wed, Sep 20, 2023 at 4:40 PM Erik Wienhold wrote: > On 2023-09-20 15:19 -0400, Michael Corey wrote: > > Just to be clear in your last response are you saying on your 14.3 you > are > > getting the > > ERROR: permission denied for table ref_media_code ? > > Yes: > > db14=> select version(); > > version > > - > PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, > compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit > (1 row) > > db14=> select current_user; > current_user > -- > sten_schema > (1 row) > > db14=> show search_path; > search_path > - > "$user", ref_schema, public > (1 row) > > db14=> select * from ref_media_code; > ERROR: permission denied for table ref_media_code > db14=> select * from sten_media_codes_view ; > ERROR: permission denied for table ref_media_code > > > If this is true then it seems to be something in our setup. > > My guess is the missing > > GRANT SELECT ON ref_schema.ref_media_code TO sten_schema > > unless that privilege should be inherited from some other role. > > I think there are two possible situations if I don't mix up anything: > > 1. The setup script is flawed and tests should fail on both 14 and 15 >because of it, but something is foul on your 14 which results in a >false positive (granted privileges on 14). > > 2. The setup script is ok and tests should pass but something is foul >on your 15 which results in a false negative (missing privileges on >15). > > But besides that, tests depending on existing state (something that is > not part of each test setup) gives me the heebie-jeebies. I worked on > a project were this was the case: Oracle databases for devs, test, and > QA copied from a bunch of blessed databases. And somehow those copies > were incomplete sometimes, e.g. missing constraints or indexes. > > > This database may have been upgraded from 13.x to 14. The sten_schema > > has INHERIT when I create, but that does not mean INHERIT from > > ref_schema, correct? > > No, unless sten_schema is also member of ref_schema which is not the > case per your script. Both roles are member of object_creator though. > > > All the items I have created just once I have not removed or recreated > any > > of these for my test. The problem is impacting my real actual schemas > and > > was discovered after we did the upgrade to 15. I decided then to restore > > the original 14 server and made two copies. I kept one as 14 and upgraded > > the other to 15. Lastly, I created the test case. > > Can you create a 15 server from scratch and test it or do tests rely on > existing data? You could dump and restore db14 from the original 14 > into the new 15. pg_dump covers privileges but not roles or memberships. > So you may be able to get rid of whatever may be wrong with your current > 15. > > But then again, I don't have an explanation why the upgrade 14 -> 15 > would change privileges or roles. The release notes for 15 list several > changes regarding roles and privileges but I don't see how they apply > here. > > First item of E.5.3.1.6. Privileges [1] could be relevant: > > "Allow table accesses done by a view to optionally be controlled by > privileges of the view's caller. Previously, view accesses were always > treated as being done by the view's owner. That's still the default." > > But view sten_media_codes_view is not defined with security_invoker=true > and sten_schema is current_user and owner. > > [1] https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.5.3.8 > > -- > Erik > -- Michael Corey
Re: Changed functionality from 14.3 to 15.3
David, How can I check the default privileges? On Wed, Sep 20, 2023 at 5:24 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold wrote: > >> Has your 14.3 some left-over state from previous test runs? I assume >> the server is not re-created for each test run. I was wondering if the >> roles may still exist and with additional memberships. But then again >> the script just uses CREATE ROLE. So the roles definitely do not exist >> beforehand. But what are the actual memberships of sten_schema? >> Because it must inherit SELECT on ref_media_code on 14.3. It can't be >> from object_creator because that role also gets newly created. >> > > Your description also suggests that maybe the v14 instance has altered > default privileges setup that maybe the v15 doesn't have. > > David J. > > -- Michael Corey
Re: Changed functionality from 14.3 to 15.3
On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold wrote: > Has your 14.3 some left-over state from previous test runs? I assume > the server is not re-created for each test run. I was wondering if the > roles may still exist and with additional memberships. But then again > the script just uses CREATE ROLE. So the roles definitely do not exist > beforehand. But what are the actual memberships of sten_schema? > Because it must inherit SELECT on ref_media_code on 14.3. It can't be > from object_creator because that role also gets newly created. > Your description also suggests that maybe the v14 instance has altered default privileges setup that maybe the v15 doesn't have. David J.
Re: Database selection
> On Sep 20, 2023, at 14:11, veem v wrote: > > Does AWS aurora postgres depend on the same vacuuming technology for > maintaining the transactions? Yes. Aurora has replaced the PostgreSQL storage engine, but the MVCC part is largely the same. The issues with vacuuming are largely overstated, though, and alternative system for concurrency control have their own issues. > Does Aurora Mysql opt for a similar strategy for transaction management? or > any different/better ones? That's probably best addressed to a MySQL-oriented list, or directly to Amazon. It should be noted that Aurora is a commercial product, and much of it is closed-source. User communities will have somewhat limited ability to offer advice about it.
Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf'
On Wed, Sep 20, 2023 at 2:06 PM Harry Green wrote: > I attach the text of the entire create function instruction, and also of > the create table instruction. > > The restore is not going to execute functions on its own and you've only shown two create statements. Somewhere else in your dump file the function check_account_from_bill_items must be referenced in order for it to be called. You need to show that. It is not the function creation that is going to be illegal, it will be, like Adrian said, something like using a volatile function in a check constraint that is going to be illegal. We are working with version 10.23, and I cannot send you the entire output > of the pg_dump file because it is 3.3 GB, but am happy to send you any > parts that might help. > > I doubt a schema-only dump is going to be that large...but you are right that you should be trying harder to isolate this down to a reproducible test case and thus be able to provide more information without it being too much. David J.
Re: Database selection
Thank you. Yes feature wise postgres seems rich as I read multiple blogs. And right now, I can't think of any other reason for opting mysql though as opposed to postgres. However, One of the things regarding the transaction management, which I see in most postgres blogs is 'mvcc'(multiversion concurrency control) being an issue at times in a heavy concurrent system, in postgres as its maintaining exact copies of all the old versions of the rows(if someone still reading those) and at times cleaning these(vacuuming) becomes a pain. Does AWS aurora postgres depend on the same vacuuming technology for maintaining the transactions? Does Aurora Mysql opt for a similar strategy for transaction management? or any different/better ones? On Thu, 21 Sept 2023 at 02:09, Laurenz Albe wrote: > On Thu, 2023-09-21 at 01:17 +0530, veem v wrote: > > I see multiple docs on the internet, stating the difference between > Mysql and postgres. > > But I want to understand the real views of the experienced folks here. > > > > While we are on AWS and planning to opt for one of the relational > databases out of > > mysql and postgres for our application(It is a Hybrid type, mostly > dealing with > > batch processing but also supporting OLTP type UI screens etc. and ACID > transactions preferred). > > > > What all things/parameters should we keep in mind here for opting a > database? > > or in other words how to evaluate the suitable database for our > application? > > You are asking the wrong people. We here on the PostgreSQL mailing list > will tell > you that you shouldn't touch MySQL except with a long stick, and for > almost all > use cases PostgreSQL is preferable. The people on MySQL mailing lists or > forums > might tell an entirely different story. > > I would say something about license and free software, but if you plan to > lock > yourself into the cage of a cloud hosted database, that probably doesn't > matter much. > > Yours, > Laurenz Albe >
Re: Database selection
On Thu, 2023-09-21 at 01:17 +0530, veem v wrote: > I see multiple docs on the internet, stating the difference between Mysql and > postgres. > But I want to understand the real views of the experienced folks here. > > While we are on AWS and planning to opt for one of the relational databases > out of > mysql and postgres for our application(It is a Hybrid type, mostly dealing > with > batch processing but also supporting OLTP type UI screens etc. and ACID > transactions preferred). > > What all things/parameters should we keep in mind here for opting a database? > or in other words how to evaluate the suitable database for our application? You are asking the wrong people. We here on the PostgreSQL mailing list will tell you that you shouldn't touch MySQL except with a long stick, and for almost all use cases PostgreSQL is preferable. The people on MySQL mailing lists or forums might tell an entirely different story. I would say something about license and free software, but if you plan to lock yourself into the cage of a cloud hosted database, that probably doesn't matter much. Yours, Laurenz Albe
Re: Changed functionality from 14.3 to 15.3
Michael Corey ezt írta (időpont: 2023. szept. 20., Sze, 20:48): > ... All of the DDL is just the setup for the test case. I ran those steps > in both databases to setup the exact same environment. The COMMIT is not > needed for the test out of habit I put it in my setup. The main issue is > in 14.3 I can run this select as user sten_schema, but in 15.3 I am unable > due to a permission issue. > Hi Michael, I couldn't reproduce the outcome you observed in PG14.3 using the "postgres:14.3" (debian) Docker image. My minimal docker test: docker pull postgres:14.3 docker run --name pg143tx -e POSTGRES_DB=db14 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=pw9 -d postgres:14.3 docker exec -ti pg143tx psql -d db14 -U postgres My log: psql (14.3 (Debian 14.3-1.pgdg110+1)) Type "help" for help. < copy paste your test code > CREATE ROLE ERROR: role "rds_superuser" does not exist CREATE ROLE CREATE ROLE CREATE ROLE CREATE ROLE CREATE ROLE ALTER ROLE ALTER ROLE GRANT ROLE CREATE ROLE ALTER ROLE ALTER ROLE GRANT ROLE CREATE SCHEMA ALTER SCHEMA GRANT GRANT GRANT GRANT CREATE TABLE ALTER TABLE GRANT GRANT GRANT INSERT 0 1 INSERT 0 1 INSERT 0 1 WARNING: there is no transaction in progress COMMIT CREATE SCHEMA ALTER SCHEMA GRANT GRANT GRANT GRANT CREATE VIEW ALTER TABLE GRANT GRANT db14=# \c db14 sten_schema You are now connected to database "db14" as user "sten_schema". db14=> select * from sten_media_codes_view ; ERROR: permission denied for table ref_media_code db14=> select * from ref_media_code ; ERROR: permission denied for table ref_media_code db14=> SELECT version(); version - PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row) Regards, Imre
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
On Wed, 2023-09-20 at 17:59 +, Harry Green wrote: > I have done a complete pg_dump and complete restore, and tried all forms of > complete > pg_dumps and restores (i.e. the different formats). There is no partial or > tables only > or data only dumping or restoring. Further, you refer to a 'check constraint' > but > this is just a function that we wrote with the 'create function...' command, > whose > name if memory serves correctly happens to begin by 'check...' because that > happens > to be the most descriptive word for what it does. > > The pg_restore is actually correctly recreating the function using the > 'create function...' > command, but it is doing so on line 95, ahead of the 'create table ... > accounts ...' > which appears on line 510. The trouble is that in the function itself, we > refer to > the accounts table, and if I am not mistaken, the whole point of pl/pgsql is > that > you can refer to tables, etc. so I don't think I have done anything 'illegal'. Ok, so my conjecture about check constraints was wrong. The fact remains that you have to restore the dump into an empty database. Then these errors should not occur. Yours, Laurenz Albe
Cannot link against libpq on macOS
Hello, I have been struggling with trying to use a current version of libpq to link against on macOS in Xcode. It seems that nothing works, nobody on the web eventually succeeded either as far as I found so far. I try to write a small sample client. I started with the public source download to built libpq itself, which did work. But I am lost as to what files to actually include in my project then. I dragged lib, include, libpq into my project, but then it the compiler complains about the header internal references, so I need to remove lib/ in front of any includes in files, or it doesn’t get found. It makes no difference if I add Linker flags or search paths. PLease, can someone help? Thanks, Alex
Database selection
Hello All, I see multiple docs on the internet, stating the difference between Mysql and postgres. But I want to understand the real views of the experienced folks here. While we are on AWS and planning to opt for one of the relational databases out of mysql and postgres for our application(It is a Hybrid type, mostly dealing with batch processing but also supporting OLTP type UI screens etc. and ACID transactions preferred). What all things/parameters should we keep in mind here for opting a database? or in other words how to evaluate the suitable database for our application? Regards Veem
Re: Changed functionality from 14.3 to 15.3
Erik, Just to be clear in your last response are you saying on your 14.3 you are getting the ERROR: permission denied for table ref_media_code ? If this is true then it seems to be something in our setup. This database may have been upgraded from 13.x to 14. The sten_schema has INHERIT when I create, but that does not mean INHERIT from ref_schema, correct? All the items I have created just once I have not removed or recreated any of these for my test. The problem is impacting my real actual schemas and was discovered after we did the upgrade to 15. I decided then to restore the original 14 server and made two copies. I kept one as 14 and upgraded the other to 15. Lastly, I created the test case. On Wed, Sep 20, 2023 at 3:07 PM Erik Wienhold wrote: > On 2023-09-20 13:17 -0400, Michael Corey wrote: > > PG 14 Server > > psql (14.2, server 14.3) > > You are now connected to database "db14" as user "postgres". > > db14=> \dn+ ref_schema|sten_schema > > List of schemas > > Name |Owner|Access privileges| > Description > > > -+-+-+- > > ref_schema | ref_schema | ref_schema=UC/ref_schema +| > > | | sten_schema=U/ref_schema +| > > | | ref_schema_read=U/ref_schema +| > > | | ref_schema_write=U/ref_schema | > > sten_schema | sten_schema | sten_schema=UC/sten_schema +| > > | | ref_schema=U/sten_schema +| > > | | sten_schema_read=U/sten_schema +| > > | | sten_schema_write=U/sten_schema | > > (2 rows) > > > > > > db14=> \dp ref_schema.ref_media_code > > Access privileges > >Schema | Name | Type | Access privileges| > Column privileges | Policies > > > ++---++---+-- > > ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| > | > > || | ref_schema_read=r/ref_schema +| > | > > || | sten_schema_write=r/ref_schema | > | > > (1 row) > > > > > > db14=> \dp sten_schema.sten_media_codes_view > > Access privileges > >Schema| Name | Type |Access privileges > | Column privileges | Policies > > > -+---+--+-+---+-- > > sten_schema | sten_media_codes_view | view | > sten_schema=arwdDxt/sten_schema+| | > > | | | > sten_schema_write=r/sten_schema | | > > (1 row) > > > > PG 15 server > > psql (14.2, server 15.3) > > You are now connected to database "db14" as user "postgres". > > db14=> \dn+ ref_schema|sten_schema > > List of schemas > > Name |Owner|Access privileges| > Description > > > -+-+-+- > > ref_schema | ref_schema | ref_schema=UC/ref_schema +| > > | | sten_schema=U/ref_schema +| > > | | ref_schema_read=U/ref_schema +| > > | | ref_schema_write=U/ref_schema | > > sten_schema | sten_schema | sten_schema=UC/sten_schema +| > > | | ref_schema=U/sten_schema +| > > | | sten_schema_read=U/sten_schema +| > > | | sten_schema_write=U/sten_schema | > > (2 rows) > > > > > > db14=> \dp ref_schema.ref_media_code > > Access privileges > >Schema | Name | Type | Access privileges| > Column privileges | Policies > > > ++---++---+-- > > ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| > | > > || | ref_schema_read=r/ref_schema +| > | > > || | sten_schema_write=r/ref_schema | > | > > (1 row) > > > > > > db14=> \dp sten_schema.sten_media_codes_view > > Access privileges > >Schema| Name | Type |Access privileges > | Column privileges | Policies > > > -+---+--+-+---+-- > > sten_schema | sten_media_codes_view | view | > sten_schema=arwdDxt/sten_schema+| | > > | |
Re: Changed functionality from 14.3 to 15.3
On 2023-09-20 13:17 -0400, Michael Corey wrote: > PG 14 Server > psql (14.2, server 14.3) > You are now connected to database "db14" as user "postgres". > db14=> \dn+ ref_schema|sten_schema > List of schemas > Name |Owner|Access privileges| Description > -+-+-+- > ref_schema | ref_schema | ref_schema=UC/ref_schema +| > | | sten_schema=U/ref_schema +| > | | ref_schema_read=U/ref_schema +| > | | ref_schema_write=U/ref_schema | > sten_schema | sten_schema | sten_schema=UC/sten_schema +| > | | ref_schema=U/sten_schema +| > | | sten_schema_read=U/sten_schema +| > | | sten_schema_write=U/sten_schema | > (2 rows) > > > db14=> \dp ref_schema.ref_media_code > Access privileges >Schema | Name | Type | Access privileges| > Column privileges | Policies > ++---++---+-- > ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| > | > || | ref_schema_read=r/ref_schema +| > | > || | sten_schema_write=r/ref_schema | > | > (1 row) > > > db14=> \dp sten_schema.sten_media_codes_view > Access privileges >Schema| Name | Type |Access privileges > | Column privileges | Policies > -+---+--+-+---+-- > sten_schema | sten_media_codes_view | view | > sten_schema=arwdDxt/sten_schema+| | > | | | sten_schema_write=r/sten_schema > | | > (1 row) > > PG 15 server > psql (14.2, server 15.3) > You are now connected to database "db14" as user "postgres". > db14=> \dn+ ref_schema|sten_schema > List of schemas > Name |Owner|Access privileges| Description > -+-+-+- > ref_schema | ref_schema | ref_schema=UC/ref_schema +| > | | sten_schema=U/ref_schema +| > | | ref_schema_read=U/ref_schema +| > | | ref_schema_write=U/ref_schema | > sten_schema | sten_schema | sten_schema=UC/sten_schema +| > | | ref_schema=U/sten_schema +| > | | sten_schema_read=U/sten_schema +| > | | sten_schema_write=U/sten_schema | > (2 rows) > > > db14=> \dp ref_schema.ref_media_code > Access privileges >Schema | Name | Type | Access privileges| > Column privileges | Policies > ++---++---+-- > ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| > | > || | ref_schema_read=r/ref_schema +| > | > || | sten_schema_write=r/ref_schema | > | > (1 row) > > > db14=> \dp sten_schema.sten_media_codes_view > Access privileges >Schema| Name | Type |Access privileges > | Column privileges | Policies > -+---+--+-+---+-- > sten_schema | sten_media_codes_view | view | > sten_schema=arwdDxt/sten_schema+| | > | | | sten_schema_write=r/sten_schema > | | > (1 row) Thanks. Those privileges are identical. But sten_schema has no SELECT privilege on table ref_media_code on either server. That's necessary when querying through view sten_media_codes_view. And there's also no GRANT for that in your script. I somehow missed that previously. > "And both databases start empty or at least in the exact same state?" > Yes, this is a test case, so I created two new databases one in 14.3 and > one in 15.3, did the setup as I provided, and ran the two SELECTs in both > databases and received different results. Now I had the time to run your script and I can reproduce the missing privileges on both 14.3 and 15.3. Has your 14.3 some left-over state from previous test runs? I assume the server is not re-created for each test run. I was wondering if the roles may still exist and with a
Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf'
Thanks for your answer,I have done a complete pg_dump and complete restore, and tried all forms of complete pg_dumps and restores (i.e. the different formats). There is no partial or tables only or data only dumping or restoring. Further, you refer to a 'check constraint' but this is just a function that we wrote with the 'create function...' command, whose name if memory serves correctly happens to begin by 'check...' because that happens to be the most descriptive word for what it does. The pg_restore is actually correctly recreating the function using the 'create function...' command, but it is doing so on line 95, ahead of the 'create table ... accounts ...' which appears on line 510. The trouble is that in the function itself, we refer to the accounts table, and if I am not mistaken, the whole point of pl/pgsql is that you can refer to tables, etc. so I don't think I have done anything 'illegal'. I attach the text of the entire create function instruction, and also of the create table instruction. CREATE FUNCTION public.check_account_from_bill_items(id integer) RETURNS boolean LANGUAGE plpgsql AS $$DECLAREexp varchar; BEGINselect into exp account from bill_items where account in (select name from accounts where type='Expense' or type='Asset' or type='Stock') and bill_items.id=id;if exp is not null thenreturn true;elsereturn false;end if; END;$$; --- CREATE TABLE public.accounts ( name character varying NOT NULL, type character varying NOT NULL, vat real, account_name character varying, account_number character varying, sorting_code character varying, security_code character varying, website character varying, email character varying, username character varying, password character varying, institution character varying, address text, telephone character varying, description text, security_no_for_telephone character varying, id integer NOT NULL, subtype character varying, jnbp numeric(3,2) DEFAULT 0.5, currency character(3) DEFAULT 'GBP'::bpchar, foreign_currency character(3), stopped_using date, vat_purchase boolean DEFAULT false, vat_income boolean DEFAULT false, balance_sheet_type character varying); ALTER TABLE public.accounts Looking at the documentation for Create Function, I'm not entirely sure whether there is anything in there which is not supposed to be there. At any rate, the system does not seem to allow the creation of functions that are 'illegal' for the most part, and I cannot see why anything in here WOULD be illegal. We are working with version 10.23, and I cannot send you the entire output of the pg_dump file because it is 3.3 GB, but am happy to send you any parts that might help. Many thanks for you help - any further suggestions would be great! On Monday, 18 September 2023 at 23:22:10 BST, Adrian Klaver wrote: On 9/18/23 14:46, Johnson, Bruce E - (bjohnson) wrote: Reply to list also. Ccing list to return your answer there. > It turned out that for some reason the perl DBD::Pg module was trying to > connect twice, the first failed because of a typo in the password, the > second because it tried to connect without ssl. I am not sure why it > tried twice. > >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs > > -- Adrian Klaver adrian.kla...@aklaver.com
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
Thank you Laurenz for your answer,I have done a complete pg_dump and complete restore, and tried all forms of complete pg_dumps and restores (i.e. the different formats). There is no partial or tables only or data only dumping or restoring. Further, you refer to a 'check constraint' but this is just a function that we wrote with the 'create function...' command, whose name if memory serves correctly happens to begin by 'check...' because that happens to be the most descriptive word for what it does. The pg_restore is actually correctly recreating the function using the 'create function...' command, but it is doing so on line 95, ahead of the 'create table ... accounts ...' which appears on line 510. The trouble is that in the function itself, we refer to the accounts table, and if I am not mistaken, the whole point of pl/pgsql is that you can refer to tables, etc. so I don't think I have done anything 'illegal'. I attach the text of the entire create function instruction, and also of the create table instruction. CREATE FUNCTION public.check_account_from_bill_items(id integer) RETURNS boolean LANGUAGE plpgsql AS $$DECLAREexp varchar; BEGINselect into exp account from bill_items where account in (select name from accounts where type='Expense' or type='Asset' or type='Stock') and bill_items.id=id;if exp is not null thenreturn true;elsereturn false;end if; END;$$; --- CREATE TABLE public.accounts ( name character varying NOT NULL, type character varying NOT NULL, vat real, account_name character varying, account_number character varying, sorting_code character varying, security_code character varying, website character varying, email character varying, username character varying, password character varying, institution character varying, address text, telephone character varying, description text, security_no_for_telephone character varying, id integer NOT NULL, subtype character varying, jnbp numeric(3,2) DEFAULT 0.5, currency character(3) DEFAULT 'GBP'::bpchar, foreign_currency character(3), stopped_using date, vat_purchase boolean DEFAULT false, vat_income boolean DEFAULT false, balance_sheet_type character varying); ALTER TABLE public.accounts Looking at the documentation for Create Function, I'm not entirely sure whether there is anything in there which is not supposed to be there. At any rate, the system does not seem to allow the creation of functions that are 'illegal' for the most part, and I cannot see why anything in here WOULD be illegal. We are working with version 10.23, and I cannot send you the entire output of the pg_dump file because it is 3.3 GB, but am happy to send you any parts that might help. Many thanks for you help - any further suggestions would be great! On Monday, 18 September 2023 at 20:29:19 BST, Laurenz Albe wrote: On Mon, 2023-09-18 at 15:08 +, Harry Green wrote: > I am having trouble restoring a database backed up with pg_dump/pg_dump_all. > The error messages I get are below and appear to suggest that certain > sql-language or > pl/pgsql-language functions which include an sql statement referencing a > table are > trying to be executed before the table which they reference has been created. > I am surprised that pg_dump could get the order wrong, but that is what is > happening. > > Looking at the first of the error messages, the pl/pgsql function is created > as > create function public.check_account_from_bill_items(character...) on line > 95, > but the instruction to create the accounts table '... create table > public.accounts ... ' > appears on line 510. > > Consequently, the restore does not work because the relations are created in > the > wrong order. Any ideas how I can solve it? > > Please see the error message below: > > psql:20230913_1300.sql: ERROR: relation "accounts" does not exist > LINE 1: select name from accounts where (type='Expense' or ... > ^ > QUERY: select name from accounts where (type='Expense' or > type='Asset' or type='Stock') and name=account > CONTEXT: PL/pgSQL function public.check_account_from_bill_items(character > varying) line 6 at SQL statement > COPY bill_items, line 1: "4096 Website 0 11.6094 \N \N 0 > 11.6094 5852 1 \N" > > psql:20230913_1300.sql:201718: ERROR: relation "ebooks" does not exist > LINE 1: select count(*) from ebooks where isbn13=$1 > ^ > QUERY: select count(*) from ebooks where isbn13=$1 > CONTEXT: SQL function "ebook_records_with_isbn13" during inlining > COPY book_information, line 1: "Title abcd \N \N 1---1 > 111-1---1 11.11 111.11 1 \N ..." > > [...] > > psql:20230913_1300.sql:20459125: ERROR: insert or update on table > "book_praise" violates foreign key constraint "book_praise_title_fkey" > D
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
On 9/20/23 10:59 AM, Harry Green wrote: Thank you Laurenz for your answer, I have done a complete pg_dump and complete restore, and tried all forms of complete pg_dumps and restores (i.e. the different formats). There is no partial or tables only or data only dumping or restoring. Further, you refer to a 'check constraint' but this is just a function that we wrote with the 'create function...' command, whose name if memory serves correctly happens to begin by 'check...' because that happens to be the most descriptive word for what it does. The pg_restore is actually correctly recreating the function using the 'create function...' command, but it is doing so on line 95, ahead of the 'create table ... accounts ...' which appears on line 510. The trouble is that in the function itself, we refer to the accounts table, and if I am not mistaken, the whole point of pl/pgsql is that you can refer to tables, etc. so I don't think I have done anything 'illegal'. The issue is not that a table name is being used in a function, it is that the function is being used before the table is created. My suspicion still is that it is being used as a CHECK function. I would suggest taking a schema only dump of the database e.g: pg_dump -d -s -f db_name_schema.sql This will dump only the schema(object) definitions. Then search that file for check_account_from_bill_items. I'm betting it is used somewhere before the accounts table is created.
Re: Changed functionality from 14.3 to 15.3
PG 14 Server psql (14.2, server 14.3) You are now connected to database "db14" as user "postgres". db14=> \dn+ ref_schema|sten_schema List of schemas Name |Owner|Access privileges| Description -+-+-+- ref_schema | ref_schema | ref_schema=UC/ref_schema +| | | sten_schema=U/ref_schema +| | | ref_schema_read=U/ref_schema +| | | ref_schema_write=U/ref_schema | sten_schema | sten_schema | sten_schema=UC/sten_schema +| | | ref_schema=U/sten_schema +| | | sten_schema_read=U/sten_schema +| | | sten_schema_write=U/sten_schema | (2 rows) db14=> \dp ref_schema.ref_media_code Access privileges Schema | Name | Type | Access privileges| Column privileges | Policies ++---++---+-- ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| | || | ref_schema_read=r/ref_schema +| | || | sten_schema_write=r/ref_schema | | (1 row) db14=> \dp sten_schema.sten_media_codes_view Access privileges Schema| Name | Type |Access privileges | Column privileges | Policies -+---+--+-+---+-- sten_schema | sten_media_codes_view | view | sten_schema=arwdDxt/sten_schema+| | | | | sten_schema_write=r/sten_schema | | (1 row) PG 15 server psql (14.2, server 15.3) You are now connected to database "db14" as user "postgres". db14=> \dn+ ref_schema|sten_schema List of schemas Name |Owner|Access privileges| Description -+-+-+- ref_schema | ref_schema | ref_schema=UC/ref_schema +| | | sten_schema=U/ref_schema +| | | ref_schema_read=U/ref_schema +| | | ref_schema_write=U/ref_schema | sten_schema | sten_schema | sten_schema=UC/sten_schema +| | | ref_schema=U/sten_schema +| | | sten_schema_read=U/sten_schema +| | | sten_schema_write=U/sten_schema | (2 rows) db14=> \dp ref_schema.ref_media_code Access privileges Schema | Name | Type | Access privileges| Column privileges | Policies ++---++---+-- ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| | || | ref_schema_read=r/ref_schema +| | || | sten_schema_write=r/ref_schema | | (1 row) db14=> \dp sten_schema.sten_media_codes_view Access privileges Schema| Name | Type |Access privileges | Column privileges | Policies -+---+--+-+---+-- sten_schema | sten_media_codes_view | view | sten_schema=arwdDxt/sten_schema+| | | | | sten_schema_write=r/sten_schema | | (1 row) "And both databases start empty or at least in the exact same state?" Yes, this is a test case, so I created two new databases one in 14.3 and one in 15.3, did the setup as I provided, and ran the two SELECTs in both databases and received different results. On Wed, Sep 20, 2023 at 12:33 PM Erik Wienhold wrote: > On 2023-09-20 09:15 -0400, Michael Corey wrote: > > Thanks for responding. All of the DDL is just the setup for the test > > case. I ran those steps in both databases to setup the exact same > > environment. > > And both databases start empty or at least in the exact same state? > > > The COMMIT is not needed for the test out of habit I put it > > in my setup. The main issue is in 14.3 I can run this select as user > > sten_schema, but in 15.3 I am unable due to a permission issue. > > I don't know why those privileges could be missing if that script > completes. Please provide the privileges like I asked to see what's > actually granted on both databases: > > > On Tue, Sep 19, 2023 at 8:17 PM Erik Wienhold wrote: > > > > > > Have you checked that the permis
Re: Changed functionality from 14.3 to 15.3
On 2023-09-20 09:15 -0400, Michael Corey wrote: > Thanks for responding. All of the DDL is just the setup for the test > case. I ran those steps in both databases to setup the exact same > environment. And both databases start empty or at least in the exact same state? > The COMMIT is not needed for the test out of habit I put it > in my setup. The main issue is in 14.3 I can run this select as user > sten_schema, but in 15.3 I am unable due to a permission issue. I don't know why those privileges could be missing if that script completes. Please provide the privileges like I asked to see what's actually granted on both databases: > On Tue, Sep 19, 2023 at 8:17 PM Erik Wienhold wrote: > > > > Have you checked that the permissions are actually the same on both > > databases after running that script? > > > > \dn+ ref_schema|sten_schema > > \dp ref_schema.ref_media_code > > \dp sten_schema.sten_media_codes_view -- Erik
Re: Calculating Days/Time(Are Loops Neccessary?)
On 2023-Sep-19, Anthony Apollis wrote:
> I have inherited this code, problem is it is over code, i believe. The
> package is gonna run once a month and this code run is a loop. How can this
> loop be running and checking data up until last day, if it only run once a
> month?
I didn't stop to understand your problem fully, but if you need the set
of calendar days in one month, you can use the generate_series()
function around some arithmetic that gives you the start of the month
plus when it ends, something like this:
with onedate(theday) as (values (date '2023-02-03'))
select g::date
from onedate,
generate_series(date_trunc('month', onedate.theday),
date_trunc('month', onedate.theday) + interval '1 month' - interval '1
day',
'1 day') g ;
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"El sabio habla porque tiene algo que decir;
el tonto, porque tiene que decir algo" (Platon).
Re: Calculating Days/Time(Are Loops Neccessary?)
On 9/19/23 10:09, Anthony Apollis wrote: I have a table that needs to give me all data up until yesterday. This All data since, beginning of month, beginning of year, beginning of time? will be part of an SSIS package that runs monthly, a day before the last day. code is: You need to provide a simplified version of what you are trying to achieve. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Changed functionality from 14.3 to 15.3
Erik,
Thanks for responding. All of the DDL is just the setup for the test
case. I ran those steps in both databases to setup the exact same
environment. The COMMIT is not needed for the test out of habit I put it
in my setup. The main issue is in 14.3 I can run this select as user
sten_schema, but in 15.3 I am unable due to a permission issue.
On Tue, Sep 19, 2023 at 8:17 PM Erik Wienhold wrote:
> On 2023-09-19 15:09 -0400, Michael Corey wrote:
> > We are experiencing different functionality once we upgraded from
> Postgres
> > 14.3 to Postgres 15.3.
> >
> > Below is a test case that we created which shows a schema user who has a
> > VIEW that accesses a table in another schema. In 14.3 the schema user is
> > able to create the VIEW against the other schema's table and successfully
> > SELECT data from that VIEW as well as directly from the other schema's
> > table.
> >
> > In 15.3 the same setup does allow for the VIEW to be created however, the
> > schema user is unable to SELECT data using the VIEW or directly from the
> > user's table.
> >
> > Is anyone aware of changes that would cause this functionality to stop
> > working?
> >
> > --
> > -- Super Roles
> > CREATE ROLE object_creator NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB
> > NOCREATEROLE NOREPLICATION ;
> > GRANT rds_superuser TO object_creator;
> >
> > --
> > -- Common Roles
> >
> > CREATE ROLE ref_schema_read ;
> > CREATE ROLE ref_schema_write ;
> >
> > CREATE ROLE sten_schema_read ;
> > CREATE ROLE sten_schema_write ;
> >
> > --
> > -- User = sten_schema
> >
> > CREATE ROLE sten_schema ;
> > ALTER ROLE sten_schema WITH LOGIN INHERIT ;
> > ALTER ROLE sten_schema IN DATABASE db14 SET search_path TO "$user",
> > ref_schema, public;
> > GRANT object_creator TO sten_schema ;
> >
> > --
> > -- User = ref_schema
> >
> > CREATE ROLE ref_schema ;
> > ALTER ROLE ref_schema WITH LOGIN INHERIT ;
> > ALTER ROLE ref_schema IN DATABASE db14 SET search_path TO "$user",
> > sten_schema, public;
> > GRANT object_creator TO ref_schema ;
> >
> > -- Schema = ref_schema
> > -- Permissions on schema are:
> >
> > CREATE SCHEMA IF NOT EXISTS ref_schema ;
> > ALTER SCHEMA ref_schema OWNER TO ref_schema;
> >
> > GRANT ALL ON SCHEMA ref_schema TO ref_schema;
> > GRANT USAGE ON SCHEMA ref_schema TO sten_schema;
> > GRANT USAGE ON SCHEMA ref_schema TO ref_schema_read;
> > GRANT USAGE ON SCHEMA ref_schema TO ref_schema_write;
> >
> > --
> > -- Table
> >
> > CREATE TABLE IF NOT EXISTS ref_schema.ref_media_code
> > (
> > media_code character varying(10) COLLATE pg_catalog."default" NOT
> NULL
> > ) ;
> >
> > ALTER TABLE IF EXISTS ref_schema.ref_media_code OWNER to ref_schema;
> >
> > GRANT ALL ON TABLE ref_schema.ref_media_code TO ref_schema;
> > GRANT SELECT ON TABLE ref_schema.ref_media_code TO ref_schema_read;
> > GRANT SELECT ON TABLE ref_schema.ref_media_code TO sten_schema_write;
> >
> > insert into ref_schema.ref_media_code values ('CODE1') ;
> > insert into ref_schema.ref_media_code values ('CODE2') ;
> > insert into ref_schema.ref_media_code values ('CODE3') ;
> > commit ;
>
> This COMMIT statement is suspicious because I don't see an explicit
> transaction start. Does the script run with autocommit=off and does it
> even finish without error? Everything after this transaction may have
> been rolled back.
>
> If sten_schema.sten_media_codes_view already exists (after all, you're
> using IF NOT EXISTS) and the rest of the script fails (without
> autocommit) you'll end up with the original view and schema and whatever
> permissions they had to begin with.
>
> > -- Schema = sten_schema
> > -- Permissions on schema are:
> >
> > CREATE SCHEMA IF NOT EXISTS sten_schema ;
> > ALTER SCHEMA sten_schema OWNER TO sten_schema;
> >
> > GRANT ALL ON SCHEMA sten_schema TO sten_schema;
> > GRANT USAGE ON SCHEMA sten_schema TO ref_schema;
> > GRANT USAGE ON SCHEMA sten_schema TO sten_schema_read;
> > GRANT USAGE ON SCHEMA sten_schema TO sten_schema_write;
> >
> > CREATE OR REPLACE VIEW sten_schema.sten_media_codes_view
> > AS
> > SELECT mc.media_code
> >FROM ref_schema.ref_media_code mc;
> >
> > ALTER TABLE sten_schema.sten_media_codes_view OWNER TO sten_schema;
> >
> > GRANT ALL ON TABLE sten_schema.sten_media_codes_view TO sten_schema;
> > GRANT SELECT ON TABLE sten_schema.sten_media_codes_view TO
> > sten_schema_write;
> >
> > ***
> >
> > --
> > -- Postgres 14.3 TEST
> > --
> > postgres=> \c db14 sten_schema
> > Password for user sten_schema:
> > psql (14.2, server 14.3)
> > You are now connected to database "db14" as user "sten_schema".
> >
> > db14=> select * from sten_media_codes_view ;
> > media_code
> >
> > CODE1
> > CODE2
> > CODE3
> > (3 rows)
> >
> > db14=> select * from ref_media_code ;
> > media_code
> >
> > CODE1
> > CODE2
> > CODE3
> > (3 rows)
> >
> >
> >
> > --
> > -- Postgres 15.3 TEST
> > --
> >
Re: could not open file "base/XX/XX": Interrupted system call
On Wed, 2023-09-20 at 10:49 +0200, Torsten Krah wrote: > I am running the postgres docker image with that version: > > 2023-09-20 10:36:32.478 CEST [1] LOG: starting PostgreSQL 13.12 (Debian > 13.12-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) > 12.2.0, 64-bit > 2023-09-20 10:36:32.564 CEST [1] LOG: listening on IPv4 address "0.0.0.0", > port 5432 > 2023-09-20 10:36:32.564 CEST [1] LOG: listening on IPv6 address "::", port > 5432 > > And this is happening for some queries: > > 2023-09-20 10:38:25.076 CEST [47] ERROR: could not open file > "base/16386/17328": Interrupted system call > 2023-09-20 10:38:41.897 CEST [49] ERROR: could not open file > "base/16386/68359": Interrupted system call Is that an NFS mount? What are the mount options? Yours, Laurenz Albe
Re: could not open file "base/XX/XX": Interrupted system call
Am Mittwoch, dem 20.09.2023 um 13:11 +0200 schrieb Laurenz Albe: > Is that an NFS mount? What are the mount options? > > Yours, > Laurenz Albe No mount at all. Just the docker container filesystem using the overlay2 storage driver, there is nothing externally mounted in that container, the data is already in the image (via docker COPY) from which the container gets created (and that data directory was created in the image build process via pg_restore from the dump file). The whole container / image just involves overlay2 fs which itself is sitting on ext4. Torsten
could not open file "base/XX/XX": Interrupted system call
Hi, I am running the postgres docker image with that version: 2023-09-20 10:36:32.478 CEST [1] LOG: starting PostgreSQL 13.12 (Debian 13.12-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit 2023-09-20 10:36:32.564 CEST [1] LOG: listening on IPv4 address "0.0.0.0", port 5432 2023-09-20 10:36:32.564 CEST [1] LOG: listening on IPv6 address "::", port 5432 And this is happening for some queries: 2023-09-20 10:38:25.076 CEST [47] ERROR: could not open file "base/16386/17328": Interrupted system call 2023-09-20 10:38:41.897 CEST [49] ERROR: could not open file "base/16386/68359": Interrupted system call I can enter the container and view the files above with e.g. strings $file | less and I can create a md5sum from that file without an error. What can I do about that EINTR on open, how to fix it? The host (6.1.53-060153-generic #202309130436 SMP PREEMPT_DYNAMIC) and the docker daemon (Version: 24.0.6) do have nothing suspicious in there dmesg / logs. Anyone an idea how to debug / progress with that one? Torsten
