Aw: Re: I slipped up so that no existing role allows connection. Is rescue possible?
> 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". I think it's normal to not know the Ins and Outs of a ... new software. And that's the point why that class should be an easy one: Drop the idea that PG works like Oracle 101. Then, read the manual, back to cover. Yes, one will forget most of what's written there. However, a coarse structure of a new mental model will form. Karsten
PCI-DSS Requirements
Hi Team, Anyone on PCI-DSS requirements for PostgreSQL DB, need help for some of the points. Regards, Inzamam Shafiq DBA
Re: GIST combo index condition chosen for users queries is different from table owner's query
Thanks. As soon as I read your reply I recalled the leakproof issue from a discussion with a former colleague years ago. At the time, I was new to Postgresql and I realize now I should have remembered that. Disabling the RLS indeed resulted in the superior plan for the test_user. The harder part will be baking the function call used for RLS into all query predicates rather than relying on RLS to do it for us. I also recall that we got around the leakproof problem in postgres 10.2 by somehow just declaring st_intersects() to be leakproof but that would probably not work in an AWS RDS deployment. I will research the leakproof issue more and see what options we may have in dealing with this problem. Perhaps sometime in the future RLS won't break such queries but I understand that is probably not an easy task. Thanks for replying and helping me on my way. Dennis On Mon, Sep 19, 2022 at 7:28 PM Tom Lane wrote: > 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: PCI-DSS Requirements
On 9/20/22 04:27, Inzamam Shafiq wrote: Hi Team, Anyone on PCI-DSS requirements for PostgreSQL DB, need help for some of the points. Can you be more specific? (Typically. the auditors or the "audit pre-check" team will ask for a bunch of details on how your instance is configured.) The usual questions I get are: - What password hash algorithm is used? - How frequently to passwords expire? - Is SSL used when communicating with applications? -- Angular momentum makes the world go 'round.
tcp settings
I'm wondering if there's a disconnect between my servers postgresql.conf and the system tcp settings? Are the config names supposed to match a file in /proc/sys/net/ipv4? In postgres 14 install's postgresql.conf on centos 7: # - TCP settings - # see "man tcp" for details #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds; # 0 selects the system default #client_connection_check_interval = 0 # time between checks for client # disconnection while running queries; # 0 for never and in the /proc/sys/net/ipv4 I see no "keepalives" plural. tcp_abort_on_overflow tcp_fastopen_key tcp_min_snd_mss tcp_slow_start_after_idle tcp_adv_win_scale tcp_fin_timeout tcp_min_tso_segs tcp_stdurg tcp_allowed_congestion_control tcp_frto tcp_moderate_rcvbuf tcp_synack_retries tcp_app_win tcp_invalid_ratelimit tcp_mtu_probing tcp_syncookies tcp_autocorking tcp_keepalive_intvl(75) tcp_no_metrics_save tcp_syn_retries tcp_available_congestion_control tcp_keepalive_probes(9) tcp_notsent_lowat tcp_thin_dupack tcp_base_mss tcp_keepalive_time(7200) tcp_orphan_retries tcp_thin_linear_timeouts tcp_challenge_ack_limit tcp_limit_output_bytes tcp_reordering tcp_timestamps tcp_congestion_control tcp_low_latency tcp_retrans_collapse tcp_tso_win_divisor tcp_dsack tcp_max_orphans tcp_retries1 tcp_tw_recycle tcp_early_retrans tcp_max_ssthresh tcp_retries2 tcp_tw_reuse tcp_ecn tcp_max_syn_backlog tcp_rfc1337 tcp_window_scaling tcp_fack tcp_max_tw_buckets tcp_rmem tcp_wmem tcp_fastopen tcp_mem tcp_sack tcp_workaround_signed_windows My issue is that psql is timing out with "SSL SYSCALL error: Connection timed out". The TCP defaults for keepalive look fine to me but not what I'm experiencing. I have PGCONNECT_TIMEOUT=0 in my env for psql.
Experience with Dell SRM/SRDF?
Has anyone used SRM/SRDF to replicate a Linux VM running PostgreSQL? If so, did you have any problems when "flipping" to the DR replica? I don't think PostgreSQL will care or notice, as long as data/base and data/pg_xlog are on the same replicated LUN, but wanted to ask for others' experience. -- Angular momentum makes the world go 'round.
Re: I slipped up so that no existing role allows connection. Is rescue possible?
On 9/19/22 18:15, Tom Lane wrote:
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
Tom, your knowledge is vast and your advice is extremely useful. Have
you ever considered creating a dedicated page for beginners? Something
like AskTom.postgresql.org would probably be appropriate.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
Re: I slipped up so that no existing role allows connection. Is rescue possible?
.
.
.
And Tom’s English is excellent!
On Tue, Sep 20, 2022, 18:29 Mladen Gogala wrote:
> On 9/19/22 18:15, Tom Lane wrote:
>
> 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
>
>
>
> Tom, your knowledge is vast and your advice is extremely useful. Have you
> ever considered creating a dedicated page for beginners? Something like
> AskTom.postgresql.org would probably be appropriate.
>
> Regards
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>
Re: tcp settings
Rob Sargent writes: > I'm wondering if there's a disconnect between my servers postgresql.conf > and the system tcp settings? Are the config names supposed to match a > file in /proc/sys/net/ipv4? Probably not --- we certainly didn't pick them with the intention of matching any specific system's naming of the options. On my Linux box it looks like the related files are named /proc/sys/net/ipv4/tcp_keepalive_XXX But in any case, if this is your problem: > My issue is that psql is timing out with "SSL SYSCALL error: Connection > timed out". then keepalives aren't necessarily the solution anyway. When is this failure occurring ... is it while trying to establish the database connection in the first place? Or does it only happen if you've left the psql session sit idle for a long while? regards, tom lane
Re: tcp settings
>> My issue is that psql is timing out with "SSL SYSCALL error: Connection >> timed out". > > then keepalives aren't necessarily the solution anyway. When is > this failure occurring ... is it while trying to establish the > database connection in the first place? Or does it only happen > if you've left the psql session sit idle for a long while? > >regards, tom lane Sitting idle for not even a long while. Under half hour I feel would do it. But I don’t think it times out while I’m “SQLing”
Re: I slipped up so that no existing role allows connection. Is rescue possible?
> On Sep 20, 2022, at 4:54 PM, Theodore M Rolle, Jr. wrote: > > > . > . > . > And Tom’s English is excellent! >> That’s what this is! With the bonus of AK,DJ and the gang - and you Mladen. (Maybe not as searchable as one might like but that makes one pay attention. )
Re: tcp settings
Rob Sargent writes: >> then keepalives aren't necessarily the solution anyway. When is >> this failure occurring ... is it while trying to establish the >> database connection in the first place? Or does it only happen >> if you've left the psql session sit idle for a long while? > Sitting idle for not even a long while. Under half hour I feel would do it. > But I don’t think it times out while I’m “SQLing” OK, that does sound like something that reducing the keepalive interval could help with. The traditional keepalive timeout is a couple of hours, at least on my Linux box: $ cat /proc/sys/net/ipv4/tcp_keepalive_time 7200 but it sounds like there's something between you and the database server that will forget connections a lot quicker than that. regards, tom lane
