Can PAF be used to provide zero downtime while primary and backup servers are being patched?
Our IT dept needs to install a patch on both primary and backup servers for our Postgres Automatic Failover configured DB (version 9.6 on linux). From the standpoint of the DB users, can a strategy be implemented such that they see zero downtime during this process as the 2 servers are taken down in sequence for their patch, switching the role of primary and backup as this happens ?
privileges oddity
when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", it triggers this error: 2020-08-06 17:27:27.664 UTC [15914]: [3] user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: permission denied for schema zoewang at character 15 --- YET --- risk_oltp_prod=# \dn+ zoewang List of schemas Name | Owner | Access privileges| Description -+--++- zoewang | srv_risk | srv_risk=UC/srv_risk +| | | akanzler=UC/srv_risk +| | | srv_risk_ro=U/srv_risk | (1 row) HUH? (And the user also has all privs on all the tables in the schema...) -- Scott Ribe scott_r...@elevated-dev.com https://www.linkedin.com/in/scottribe/
Re: privileges oddity
Scott Ribe writes: > when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", > it triggers this error: > 2020-08-06 17:27:27.664 UTC [15914]: [3] > user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: > permission denied for schema zoewang at character 15 Gonna need more context. The session-level user seems to have the right privileges, but maybe something is happening inside a security-definer function that doesn't have privileges? regards, tom lane
Re: privileges oddity
On 8/6/20 11:11 AM, Scott Ribe wrote: when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", it triggers this error: 2020-08-06 17:27:27.664 UTC [15914]: [3] user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: permission denied for schema zoewang at character 15 --- YET --- risk_oltp_prod=# \dn+ zoewang List of schemas Name | Owner | Access privileges| Description -+--++- zoewang | srv_risk | srv_risk=UC/srv_risk +| | | akanzler=UC/srv_risk +| | | srv_risk_ro=U/srv_risk | (1 row) HUH? (And the user also has all privs on all the tables in the schema...) Schema for the table? -- Scott Ribe scott_r...@elevated-dev.com https://www.linkedin.com/in/scottribe/ -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 6, 2020, at 12:22 PM, Adrian Klaver wrote: > > Schema for the table? Nothing relevant: Column| Type | Collation | Nullable | Default -+---+---+--+- curve_name | character varying(30) | | | curve_type | character varying(15) | | | tenor_name | character varying(10) | | | tenor_date | date | | | value_date | date | | | curve_value | numeric | | |
Re: privileges oddity
On 8/6/20 11:35 AM, Scott Ribe wrote: On Aug 6, 2020, at 12:22 PM, Adrian Klaver wrote: Schema for the table? Nothing relevant: Column| Type | Collation | Nullable | Default -+---+---+--+- curve_name | character varying(30) | | | curve_type | character varying(15) | | | tenor_name | character varying(10) | | | tenor_date | date | | | value_date | date | | | curve_value | numeric | | | No triggers or FOREIGN KEYS? -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
On Aug 6, 2020, at 12:22 PM, Tom Lane wrote: > > Gonna need more context. The session-level user seems to have the > right privileges, but maybe something is happening inside a > security-definer function that doesn't have privileges? The only security definer function in the db is a simple pg_shadow lookup used by pgbouncer. Hmm, I should check both direct to PG and through PG bouncer--even though he is getting connected as the correct user, per PG's error in the log.
Re: privileges oddity
Scott Ribe writes: > On Aug 6, 2020, at 12:22 PM, Tom Lane wrote: >> Gonna need more context. The session-level user seems to have the >> right privileges, but maybe something is happening inside a >> security-definer function that doesn't have privileges? > The only security definer function in the db is a simple pg_shadow lookup > used by pgbouncer. Hmph. Any chance of getting a stack trace from the point of the error? Also, which PG version is this? regards, tom lane
Re: privileges oddity
> On Aug 6, 2020, at 12:36 PM, Adrian Klaver wrote: > > No triggers or FOREIGN KEYS? No. No keys or indexes either--that was the entire table def.
Re: privileges oddity
On 8/6/20 11:39 AM, Scott Ribe wrote: On Aug 6, 2020, at 12:36 PM, Adrian Klaver wrote: No triggers or FOREIGN KEYS? No. No keys or indexes either--that was the entire table def. echo "Hmph" -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 6, 2020, at 12:38 PM, Tom Lane wrote: > > Hmph. Any chance of getting a stack trace from the point of the error? possibly > Also, which PG version is this? 12.3 It is probably relevant that we cleaned up roles & privs yesterday, lots of REVOKE & GRANT, and some DROP ROLE. I started out thinking I'd made a mistake with that, but now I'm starting to wonder if there's a bug I hit in some of that which fubar'd something in system catalog... Maybe also relevant that the original creator & owner of the schema (zoewang) was dropped after schema owner was changed srv_risk. (Likewise, changed ownership of tables in the schema...)
Re: privileges oddity
Greetings, * Scott Ribe (scott_r...@elevated-dev.com) wrote: > when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", > it triggers this error: > > 2020-08-06 17:27:27.664 UTC [15914]: [3] > user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: > permission denied for schema zoewang at character 15 > > --- YET --- > > risk_oltp_prod=# \dn+ zoewang > List of schemas > Name | Owner | Access privileges| Description > -+--++- > zoewang | srv_risk | srv_risk=UC/srv_risk +| > | | akanzler=UC/srv_risk +| > | | srv_risk_ro=U/srv_risk | > (1 row) > > HUH? (And the user also has all privs on all the tables in the schema...) Are you 110% sure that you're actually connecting to the same instance in both cases (I'd say database too, but hopefully psql isn't lying to you about that on your prompt, but maybe double-check anyway...). Have you re-tried from the app (maybe someone fixed it in the meantime)? Thanks, Stephen signature.asc Description: PGP signature
Re: privileges oddity
> On Aug 6, 2020, at 12:53 PM, Stephen Frost wrote: > > Are you 110% sure that you're actually connecting to the same instance > in both cases (I'd say database too, but hopefully psql isn't lying to > you about that on your prompt, but maybe double-check anyway...). yes--double checked > Have you re-tried from the app (maybe someone fixed it in the > meantime)? still seeing it from psql \
Re: privileges oddity
FYI, REVOKE ALL ON SCHEMA... followed by GRANT ALL ON SCHEMA... did not change anything
Standard practices for granting privileges
Hello, I have experience with Microsoft SQL Server and trying to migrate to PostGreSQL. Wanted to reach out to community experts to guide me define or follow best practices when granting privileges to the database users. We have a bunch of stored procedures which will be executed by the application users. In SQL Server, we have granted only EXECUTE privilege to the application user. But in PostGreSQL, I discovered that application users need to be granted privileges on tables as well which are referred inside the stored procedures. On reading PostGreSql documentation, I found that we can use SECURITY DEFINER so that application users get privileges of the object owner at run time and execute the procedure without having privileges on underlying tables. But using the SECURITY DEFINER option conflicts with transaction control statements. Hence the only option left is to grant privileges to application users on the underlying tables. Which seems to me a risky option (especially as compared to SQL Server where only EXECUTE permission was sufficient). Can anyone please guide me what are the best security practices to follow under such circumstances? Thanks in advance. Regards, Lokesh Vij
Re: Can PAF be used to provide zero downtime while primary and backup servers are being patched?
On Thu, Aug 6, 2020 at 10:01:21AM -0400, David Gauthier wrote: > Our IT dept needs to install a patch on both primary and backup servers for > our > Postgres Automatic Failover configured DB (version 9.6 on linux). From the > standpoint of the DB users, can a strategy be implemented such that they see > zero downtime during this process as the 2 servers are taken down in sequence > for their patch, switching the role of primary and backup as this happens ? Assuming this is a minor relase upgrade, you can do it by just bouncing the servers. You can do the switch-over/switch-back too, but you have to manage session migration. I wrote a blog about it: https://momjian.us/main/blogs/pgblog/2018.html#October_1_2018 -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Advancing the archiver position safely
I've encountered a rather unusual situation (PostgreSQL 9.6). On a particular server, for reasons I've not fully diagnosed, the archiver thinks that the current WAL segment to be archived is 00023B680062. This is unfortunate, because the oldest WAL segment that actually exists on disk is 00023F110004, so the archive script is failing repeatedly because of the missing segment. The system is not actually missing important (for recovery) WAL segments, at least: Latest checkpoint's REDO WAL file:000241760029 I'd like to "catch up" the archiver such that it is operating on files that actually exist; besides setting archive_command to '/bin/true' and letting it chew through the old ones, is there a way of safely advancing the position of the archiver? -- -- Christophe Pettus x...@thebuild.com
Re: Advancing the archiver position safely
Christophe Pettus writes: > I've encountered a rather unusual situation (PostgreSQL 9.6). On a > particular server, for reasons I've not fully diagnosed, the archiver > thinks that the current WAL segment to be archived is > 00023B680062. This is unfortunate, because the oldest WAL > segment that actually exists on disk is 00023F110004, so > the archive script is failing repeatedly because of the missing > segment. > > The system is not actually missing important (for recovery) WAL segments, at > least: > > Latest checkpoint's REDO WAL file:000241760029 > > I'd like to "catch up" the archiver such that it is operating on files > that actually exist; besides setting archive_command to '/bin/true' > and letting it chew through the old ones, is there a way of safely > advancing the position of the archiver? Take a look at the contents of your pg_xlog/archive_status directory where you will likely find a .ready file corresponding to the missing segment and perhaps others. Deleting the .ready file should allow the archiver to get past the missing file. Make certain you're *not* mucking with the WAL files themselves. > -- > -- Christophe Pettus >x...@thebuild.com > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
Re: Advancing the archiver position safely
> On Aug 6, 2020, at 18:45, Jerry Sievers wrote: > Deleting the .ready file should allow the archiver to get past the > missing file. Ah, excellent, yes. -- -- Christophe Pettus x...@thebuild.com
