Re: Problem with ssl and psql in Postgresql 13
Hi. At Thu, 17 Dec 2020 07:21:08 +, Gustavsson Mikael wrote in > But not from psql 13: > $ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver -W > Password: > psql: error: FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user > "kalle", database "postgres", SSL off > FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database > "postgres", SSL off It looks like the option "ssl" (in postgresql.conf) is turned off on the v13 server. You will find lines like the following in the server log in that case. LOG: hostssl record cannot match because SSL is disabled HINT: Set ssl = on in postgresql.conf. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
SV: Problem with ssl and psql in Postgresql 13
Hi, SSL is on. grep ssl /etc/postgresql/13/postgresql.conf ssl = 'on' ssl_cert_file = '/etc/pki/private/xxx_incl_key.pem' ssl_key_file = '/etc/pki/private/xxx.key' Clarification, its the same postgresql13 server. I just connect with different psql-clients. KR Från: Kyotaro Horiguchi Skickat: den 17 december 2020 09:29:38 Till: Gustavsson Mikael Kopia: pgsql-gene...@postgresql.org; Svensson Peter Ämne: Re: Problem with ssl and psql in Postgresql 13 Hi. At Thu, 17 Dec 2020 07:21:08 +, Gustavsson Mikael wrote in > But not from psql 13: > $ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver -W > Password: > psql: error: FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user > "kalle", database "postgres", SSL off > FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database > "postgres", SSL off It looks like the option "ssl" (in postgresql.conf) is turned off on the v13 server. You will find lines like the following in the server log in that case. LOG: hostssl record cannot match because SSL is disabled HINT: Set ssl = on in postgresql.conf. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Re: SV: Problem with ssl and psql in Postgresql 13
Gustavsson Mikael writes: > Clarification, its the same postgresql13 server. I just connect with > different psql-clients. Perhaps turning on log_connections on the server would offer some insight. It sort of looks like the v13 client is trying to connect with SSL, failing for some unknown reason, and falling back to non-SSL which also fails. regards, tom lane
Raise exception without using plpgsql?
Hi,
Is there a way to raise an exception with a message,
without having to add your own plpgsql helper-function?
Currently this is what I have:
CREATE OR REPLACE FUNCTION raise(message text, debug json, dummy_return_value
anyelement)
RETURNS anyelement
LANGUAGE plpgsql
AS $$
BEGIN
RAISE '% %', message, debug;
END;
$$;
The dummy value allows using the function in a context where a value of the
given type is expected.
Here is a mockup example on the coding pattern where I typically would use this
raise() helper-function:
SELECT
CASE
WHEN foo = 0 THEN f0(bar)::baz
WHEN foo = 1 THEN f1(bar)::baz
WHEN foo = 2 THEN f2(bar)::baz
ELSE raise('Not implemented',json_build_object(
'foo',foo,
'bar',bar
),NULL::baz)
END
FROM ...
The idea is to throw an exception when a case is not handled, instead of just
letting the CASE produce a NULL value.
The dummy_return_value anyelement with the same type as the other CASEs is
necessary, otherwise, if trying to just return anyelement without the
dummy_return_value you would get error:
DETAIL: A result of type anyelement requires at least one input of type
anyelement, anyarray, anynonarray, anyenum, or anyrange.
Is there any idiomatic way of achieving the same, without having to rely on a
plpgsql function in this way?
Best regards,
Joel
SV: SV: Problem with ssl and psql in Postgresql 13
Hi, log_connections is on. The ERR message is correct, we do not have an entry for SSL off. The question is why psql(13) is trying to connect without ssl? 2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: connection received: host=nnn.nn.n.nnn port=40112 2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off KR Mikael Gustavsson, SMHI Från: Tom Lane Skickat: den 17 december 2020 15:20:01 Till: Gustavsson Mikael Kopia: Kyotaro Horiguchi; pgsql-gene...@postgresql.org; Svensson Peter Ämne: Re: SV: Problem with ssl and psql in Postgresql 13 Gustavsson Mikael writes: > Clarification, its the same postgresql13 server. I just connect with > different psql-clients. Perhaps turning on log_connections on the server would offer some insight. It sort of looks like the v13 client is trying to connect with SSL, failing for some unknown reason, and falling back to non-SSL which also fails. regards, tom lane
Re: SV: Problem with ssl and psql in Postgresql 13
On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael wrote: > > > Hi, > > log_connections is on. The ERR message is correct, we do not have an entry > for SSL off. > The question is why psql(13) is trying to connect without ssl? > > 2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 > pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: > connection received: host=nnn.nn.n.nnn port=40112 > 2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle > pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf > entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off By default psql/libpq will fall back to a clear text connection if the ssl encrypted one failed. Specify sslmode=require (or preferably higher) in the connection string or set PGSSLMODE=require on the client to disable that behavior. If you do that, my guess is you will see a direct connection failure instead of that error? PostgreSQL 13 did change the default value for minimum tls version to 1.2. But that seems unlikely to be the problem since you get TLS 1.3 when you use the old version... I assume you're running both the 11 and the 13 client on the same host? -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: Raise exception without using plpgsql?
On Thursday, December 17, 2020, Joel Jacobson wrote: > Hi, > > Is there a way to raise an exception with a message, > without having to add your own plpgsql helper-function? > Not that i’ve seen. There is no SQL way to do so, and I’ve not seen a core c-language function that provides that feature. David J.
SV: SV: Problem with ssl and psql in Postgresql 13
Hi Magnus, The clients 11 and 13 is on the same host. Hmm, I get the same error if I set sslmode=require. $ /usr/pgsql-13/bin/psql -d postgres --set=sslmode=require -Ukalle -hserver -W Password: psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off KR Mikael Gustavsson, SMHI Från: Magnus Hagander Skickat: den 17 december 2020 15:52:55 Till: Gustavsson Mikael Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; Svensson Peter Ämne: Re: SV: Problem with ssl and psql in Postgresql 13 On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael wrote: > > > Hi, > > log_connections is on. The ERR message is correct, we do not have an entry > for SSL off. > The question is why psql(13) is trying to connect without ssl? > > 2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 > pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: > connection received: host=nnn.nn.n.nnn port=40112 > 2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle > pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf > entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off By default psql/libpq will fall back to a clear text connection if the ssl encrypted one failed. Specify sslmode=require (or preferably higher) in the connection string or set PGSSLMODE=require on the client to disable that behavior. If you do that, my guess is you will see a direct connection failure instead of that error? PostgreSQL 13 did change the default value for minimum tls version to 1.2. But that seems unlikely to be the problem since you get TLS 1.3 when you use the old version... I assume you're running both the 11 and the 13 client on the same host? -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: SV: Problem with ssl and psql in Postgresql 13
That's setting a variable, not a parameter. You need something like psql "dbname=postgres user=kalle host=server sslmode=require" Or PGSSLMODE=require /usr/bin/pgsql-same-as-you-had-before //Magnus On Thu, Dec 17, 2020 at 5:00 PM Gustavsson Mikael wrote: > > Hi Magnus, > > > The clients 11 and 13 is on the same host. > > > Hmm, I get the same error if I set sslmode=require. > > > $ /usr/pgsql-13/bin/psql -d postgres --set=sslmode=require -Ukalle -hserver -W > Password: > psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user > "kalle", database "postgres", SSL off > FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database > "postgres", SSL off > > KR Mikael Gustavsson, SMHI > > > > Från: Magnus Hagander > Skickat: den 17 december 2020 15:52:55 > Till: Gustavsson Mikael > Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; Svensson > Peter > Ämne: Re: SV: Problem with ssl and psql in Postgresql 13 > > On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael > wrote: > > > > > > Hi, > > > > log_connections is on. The ERR message is correct, we do not have an entry > > for SSL off. > > The question is why psql(13) is trying to connect without ssl? > > > > 2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 > > pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: > > connection received: host=nnn.nn.n.nnn port=40112 > > 2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 > > pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no > > pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database > > "postgres", SSL off > > > By default psql/libpq will fall back to a clear text connection if the > ssl encrypted one failed. Specify sslmode=require (or preferably > higher) in the connection string or set PGSSLMODE=require on the > client to disable that behavior. If you do that, my guess is you will > see a direct connection failure instead of that error? > > PostgreSQL 13 did change the default value for minimum tls version to > 1.2. But that seems unlikely to be the problem since you get TLS 1.3 > when you use the old version... > > I assume you're running both the 11 and the 13 client on the same host? -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
SV: SV: Problem with ssl and psql in Postgresql 13
Sorry, my bad. But i get the same result. $ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require" psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off /Mikael Från: Magnus Hagander Skickat: den 17 december 2020 17:03:18 Till: Gustavsson Mikael Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; Svensson Peter Ämne: Re: SV: Problem with ssl and psql in Postgresql 13 That's setting a variable, not a parameter. You need something like psql "dbname=postgres user=kalle host=server sslmode=require" Or PGSSLMODE=require /usr/bin/pgsql-same-as-you-had-before //Magnus On Thu, Dec 17, 2020 at 5:00 PM Gustavsson Mikael wrote: > > Hi Magnus, > > > The clients 11 and 13 is on the same host. > > > Hmm, I get the same error if I set sslmode=require. > > > $ /usr/pgsql-13/bin/psql -d postgres --set=sslmode=require -Ukalle -hserver -W > Password: > psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user > "kalle", database "postgres", SSL off > FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database > "postgres", SSL off > > KR Mikael Gustavsson, SMHI > > > > Från: Magnus Hagander > Skickat: den 17 december 2020 15:52:55 > Till: Gustavsson Mikael > Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; Svensson > Peter > Ämne: Re: SV: Problem with ssl and psql in Postgresql 13 > > On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael > wrote: > > > > > > Hi, > > > > log_connections is on. The ERR message is correct, we do not have an entry > > for SSL off. > > The question is why psql(13) is trying to connect without ssl? > > > > 2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 > > pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: > > connection received: host=nnn.nn.n.nnn port=40112 > > 2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 > > pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no > > pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database > > "postgres", SSL off > > > By default psql/libpq will fall back to a clear text connection if the > ssl encrypted one failed. Specify sslmode=require (or preferably > higher) in the connection string or set PGSSLMODE=require on the > client to disable that behavior. If you do that, my guess is you will > see a direct connection failure instead of that error? > > PostgreSQL 13 did change the default value for minimum tls version to > 1.2. But that seems unlikely to be the problem since you get TLS 1.3 > when you use the old version... > > I assume you're running both the 11 and the 13 client on the same host? -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: SV: SV: Problem with ssl and psql in Postgresql 13
Gustavsson Mikael writes: > $ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server > sslmode=require" > psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user > "kalle", database "postgres", SSL off > FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database > "postgres", SSL off It'd be useful to verify that that version of psql+libpq is actually built with ssl support. Try ldd /usr/pgsql-13/bin/psql and then repeat "ldd" on whichever libpq.so is mentioned in the output. regards, tom lane
SV: SV: SV: Problem with ssl and psql in Postgresql 13
Here is the result. ldd /usr/pgsql-13/bin/psql linux-vdso.so.1 (0x7ffd714d5000) libpq.so.5 => /usr/pgsql-13/lib/libpq.so.5 (0x7f2d1700a000) libpthread.so.0 => /lib64/libpthread.so.0 (0x7f2d16dea000) libreadline.so.7 => /lib64/libreadline.so.7 (0x7f2d16b9b000) libm.so.6 => /lib64/libm.so.6 (0x7f2d16819000) libc.so.6 => /lib64/libc.so.6 (0x7f2d16456000) libssl.so.1.1 => /lib64/libssl.so.1.1 (0x7f2d161c2000) libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x7f2d15cdc000) libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x7f2d15a87000) libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x7f2d1583) /lib64/ld-linux-x86-64.so.2 (0x7f2d1725b000) libtinfo.so.6 => /lib64/libtinfo.so.6 (0x7f2d15603000) libz.so.1 => /lib64/libz.so.1 (0x7f2d153ec000) libdl.so.2 => /lib64/libdl.so.2 (0x7f2d151e8000) libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f2d14eff000) libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7f2d14ce8000) libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f2d14ae4000) libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x7f2d148d3000) libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7f2d146cf000) libresolv.so.2 => /lib64/libresolv.so.2 (0x7f2d144b8000) liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7f2d142a8000) libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f2d1408a000) libselinux.so.1 => /lib64/libselinux.so.1 (0x7f2d13e6) libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f2d13c37000) libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x7f2d139b3000) ldd /usr/pgsql-13/lib/libpq.so.5 linux-vdso.so.1 (0x7fff51f79000) libssl.so.1.1 => /lib64/libssl.so.1.1 (0x7f88432d1000) libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x7f8842deb000) libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x7f8842b96000) libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x7f884293f000) libpthread.so.0 => /lib64/libpthread.so.0 (0x7f884271f000) libc.so.6 => /lib64/libc.so.6 (0x7f884235c000) libz.so.1 => /lib64/libz.so.1 (0x7f8842145000) libdl.so.2 => /lib64/libdl.so.2 (0x7f8841f41000) libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f8841c58000) libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7f8841a41000) libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f884183d000) libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x7f884162c000) libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7f8841428000) libresolv.so.2 => /lib64/libresolv.so.2 (0x7f8841211000) liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7f8841001000) libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f8840de3000) /lib64/ld-linux-x86-64.so.2 (0x7f88437b6000) libselinux.so.1 => /lib64/libselinux.so.1 (0x7f8840bb9000) libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f884099) libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x7f884070c000) /Mikael Från: Tom Lane Skickat: den 17 december 2020 17:25:31 Till: Gustavsson Mikael Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; Svensson Peter Ämne: Re: SV: SV: Problem with ssl and psql in Postgresql 13 Gustavsson Mikael writes: > $ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server > sslmode=require" > psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user > "kalle", database "postgres", SSL off > FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database > "postgres", SSL off It'd be useful to verify that that version of psql+libpq is actually built with ssl support. Try ldd /usr/pgsql-13/bin/psql and then repeat "ldd" on whichever libpq.so is mentioned in the output. regards, tom lane
Re: Raise exception without using plpgsql?
On Thu, Dec 17, 2020 at 03:32:09PM +0100, Joel Jacobson wrote: > Hi, > Is there a way to raise an exception with a message, > without having to add your own plpgsql helper-function? > Currently this is what I have: Well, you can: DO $$ begin raise notice 'zz'; END; $$; It's cheating though, as it still needs plpgsql. Best regards, depesz
Re: Raise exception without using plpgsql?
čt 17. 12. 2020 v 17:45 odesílatel hubert depesz lubaczewski < dep...@depesz.com> napsal: > On Thu, Dec 17, 2020 at 03:32:09PM +0100, Joel Jacobson wrote: > > Hi, > > Is there a way to raise an exception with a message, > > without having to add your own plpgsql helper-function? > > Currently this is what I have: > > Well, you can: > DO $$ begin raise notice 'zz'; END; $$; > It's cheating though, as it still needs plpgsql. > > Best regards, > Some years there was discussion about this possibility https://www.postgresql-archive.org/How-to-raise-error-from-PostgreSql-SQL-statement-if-some-condition-is-met-td5719585.html There was another thread, but I cannot to find it Regards Pavel > depesz > > > >
Unexpected result count from update statement on partitioned table
Hello, Our application uses a queue-like table to assign tasks to users and this has worked well for us for a few years. Now we are in the process of adding some restrictions to which tasks a user can work on and that is based on an attribute of each task that does not change for the task's lifespan. Users may have access to work on one or more or types of tasks. To improve query time when finding the set of tasks that we assign, we are introducing partitioning into our task queue table. When assigning tasks, we issue an update statement to mark the tasks as reserved using a subquery that orders the tasks by age. With the introduction of partitioning, we are seeing that the update statement affects more rows than expected. An example query is: --- update task_parent set reserved = true from ( select id from task_parent where reserved = false and task_type = 1 or task_type = 2 order by task_timestamp limit 50 for update skip locked) as sub where sub.id = task_parent.id returning task_parent.id --- In the statement above, we have a subquery to limit the number of tasks to 50 yet the update statement sometimes returns more than 50 records. I have narrowed this down to a small, reproducible example shown below. The first time I run the update statement I get ~65 records, then typically ~53 the next few runs, and then it starts consistently giving me 50 records after that. Then if I bump the limit to 100 I will get more than 100 initially and after several executions it starts to settle into always giving the expected 100. Below is the full setup that can be used to reproduce what I'm seeing. It was initially observed on PostgreSQL 11.8 but I can also reproduce it on 13.0. --- create table task_parent ( id bigint not null, task_type smallint not null, reserved boolean not null, task_timestamp timestamp not null ) partition by list (task_type); create table task_child_1 partition of task_parent for values in (1); create table task_child_2 partition of task_parent for values in (2); insert into task_parent select generate_series(1, 50), case when random() < 0.5 then 1 else 2 end, false, now() - (random() * '1 day'::interval); create index task_parent_task_time_idx on task_parent (task_timestamp); update task_parent set reserved = true from ( select id from task_parent where reserved = false and task_type = 1 or task_type = 2 order by task_timestamp limit 50 for update skip locked) as sub where sub.id = task_parent.id returning task_parent.id; --- A couple of interesting observations: 1) If I remove the order by clause I always get the expected number of results 2) If I rewrite the query to use a CTE for the task IDs instead of a subquery then I always get the expected number of results At its surface, this seems like it could be a bug but maybe there is something about this usage pattern that is known/expected to cause this behavior. So that's the question - is this a bug that should be reported to pgsql-bugs, or is this expected and if so, why? Craig
