Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qj1WP-008ypc-0G for pgsql-general@arkaria.postgresql.org; Wed, 20 Sep 2023 18:01:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qj1WN-001i6a-Gw for pgsql-general@arkaria.postgresql.org; Wed, 20 Sep 2023 18:01:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qj1WN-001i6F-1d for pgsql-general@lists.postgresql.org; Wed, 20 Sep 2023 18:01:55 +0000 Received: from sonic313-21.consmr.mail.ir2.yahoo.com ([77.238.179.188]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qj1WK-005fMw-0n for pgsql-general@postgresql.org; Wed, 20 Sep 2023 18:01:53 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1695232910; bh=zak5yZurUE6nsovk7VF2NNdIxBQGu1pBjWJ/9q2bEM4=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=FTxYIX0GHXTpGXcSf4cF8LLKfQXOsAdp6VcxnR5arTLeXNc5Zkyf3WYr+LXVoqeOk9WGfZKbqVx+e/ZWnIP7HE9EBGFfTORJtan7faSr+uYCOT+Wla89L7OHdUSt3mWkLPr01Ckb71TbelLBZny7zogM3QtWFettnqmuSnEZVTzNSpq9CDvdEghTB+fjO5f7X2OS2WqmzGZnEj+9EtSzg7Egjz1c2lME6CoBfGRqKmbJNRftS+w0osC04ktTff6ehw0lLi/TUSb0uqiRCZ6BJVLHXLek5OP9FstVacxkmziR3EWqMEjq0Jm+d7JZmaDUsGesQlYYaA8lNZq0iIGwBA== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1695232910; bh=VT8u91V06kefeSgoMFHNDlLSMttX6r7Id3whRNSwyo8=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=G2SHLQY+RIsEjSSIW+rwupy5WRyf/jV3R6cwTTKVWah91hkK6R7jV/qT0yFPTp2rWH6q7U46zj8gKGGzoalIOz4QB63DyvqOaHqNHX7DrVcMpB03w9PtcFRsjNLhCEa24HJSpbL9cV0amMsLyyNzHQ0Y8hB8rlNv7KK6MYeqzxnqWa2d1W8ZDgnAvDZUbrrtzSOdugLYD2c+JJnmodyM3kVAGx7zacw7NbGf1TewgobhSQQRXRCF2FZWHzic/dmyEXPUwc4gS1p4d/A/Q+eBXMJNjEwrCUHiyHyBbqMSNM7FSs9M8/N3YYaPkH42LPcYMxnA/VAGrup1e85v6FCVMg== X-YMail-OSG: iycqgMMVM1n54lXgHAD8Ja622BeBMROxqe39O4idi7.6d2Di49pQJZ6LccX2s74 nOzQ1HUM8XZ_j7qLAksHAQ.2DO8ZBexvI8kNteDiQxM3YNLZVWVx1zCdiGK19Dj_yZi.zfLerQa_ SF_zNrtXY_nGQOKS7E1RScxWxK7FFV_BCyDty4L6E_7F9JfuwOIex8Hr2YiLFU2zc3poI6oNMr6k w_Z15XUhzpVCLJ56uOygkO8_r9xk2rKkePZKCwqzy5srShU6pE8ZlaxB2XfAPXt_ziZdrI6VmvGq EF28xvtq_OIpe_seGwDpTicYfQAEewzvlhQrshi7hyiAAorxHYwJ0Yf6WRwlBbBTV1MeGuknO0nV V1sF8XvVIoswYbsrfSA553HvMXpG7bnUBcH5Y35cia7a6Ls6nOwuYZbgRHJjn4BlumGcHRUC5Tb0 vZjtjWNTGqPocOOeCVj1yYWfNxeLLTdl74jjq8DLSD4rLzTwcxltEh4YMxmXFo6kdUeMv88FbXVn i60a2oWGD1qJo7BMj6inBhQuO0WVcSqPHUwp1TcJ6FL54jFIoBl4d.2UyqT0MbIdJSvsUAptm0HR Nhd664olLzhKh5vkEeUxz9BMRoe63Qj7WjsduqZ_yG.DsLAZSdpexjuKNSZNWjRKYwQkOD3FqdMt khDu_cgdmFv60H0HPfVgDBnNasEcN_Yta52UZ__VGDSUiLBvu1J0FewHlzh01lv6Dck8f8HXtGeD YkQdT0TPXRIcKRdMEN.yQ7sEFBcVsV7DWK6.ZM3WEouXB5lGefyzty.e_3iEa2aVHZqbw80c3lCz c8gHDcw_tPpNHfJ_spr3D.cFmfNCnXNkfOGIQcDgZ1.m7ERsdfb7n9tMe0SnJQlW_rQGcMS28z59 vNs36GhiF1f9wpOFl9_CimSvkIOx_WliNcVZc8ws4UznwrhJF8QpxX7RqzappHgXpusXtDgNIc25 lGmVeGwDbajYSVyqsaEvY4b6vecsRc7QD9lkj0BpQaRpHEnEjFiUpYTEFOKYxRyecoHCtoQi.0yS r5B1jaorg6keGH14G8LzmUPzGSwJ1SPw2MAtm8ykmEXORH3DKicQOJxvhgLEdjfW3hdpeShzgrOY B3lgj65mhSdPMox0UaXeBj2rXFSEWi2Psvz_xADpTOnZ0YT5a1nzQEuaZoHR2exQdAKLB0YKBfHE wDnZXw.g4ZLTgsBhJdTQ9vjJUXtKQlfkuhS2WxwNIznrEkso4fkG76ZhGrN0jnhlK4trnp1_YTZW zQcmxzNRrsJuP1aTLM_HkwCPynmnP4kzoqSx8BkP3_R8WpuE55gwuGvgBenEaI4Zdz38AYQJpBhj tUOp2k2HLnTorsyBZ7l7GH43oIkspaFz5J8UyUXc9SSL.0_awu3B8MGovNfYEaGwODDF1pYs74HW MfuqzS8_TR8cHzbV5DNOr5VMR3P980JBEwQRSOiH4CMcm2x6S6czFOzm2Ghyw_Cgm6Yn2zmYdqCX aPFB8z12INI6PbKzcQM2gKxGMPd30HrHD6EAyoBSvTTH89pMACHOgb_6EtW_tfEYOVWJ6Gh95i2B RKDPnR8TOmM1xK9j9yyT.8JaeF4kBeEhtQr0g26v1WX_PiOmfff1w5rH4Fk46U6S6tCvZjvxKICF xiNtFLUhaEVNf7dWiMZJddhT1OaLg.bcTXoFDMDBfjqcCnhjQSdHOoARrsrPjI6b8ejt4slKK8jM DPCvbifS4Wnze95ZjsoHLh_W7gLkvkz0ffOPieWu.IAf4WHWox6SMyjW.8ZRexLBfDapzrgpien9 o8Ix9L9gKIgE_PHbAtsgqqjHDl1SO_W8bkNTExCD2_.9KIgwMtAm8YW4LUdvQQ8e2YSTG6tnxIaW YK0KgvQMEKOmNOfV9jFjf_y69cCMubXNJK_1u2QNWsAmBaEr.QYeW03WrQrTh6nu1f29PoJDkvHQ 8DQak1GuJuVtozjLK6epyZ9K60fv647tgy1oMc6oL0y4tcIGHS0DIOPClYmfx73x6W.sajY7l85S ZPJo1CrA3c6yPvus4NT3u.a3Skl4G2SF7oQyKbkDY2J2lvd4TFXea1rcaBmXirZNRTEDN12Z_dqf dDTyuT9_0pdKkH7DFCd0viWnYRPiemRtmkkf_507_gpQK8ZxGLjwuMwNIRtNwABrc5fJPC0QVeTf xC9aFcT3Z0hD75f7kDMNPbyLh5CvHINeWdzS7KpylOxCkcr85a23lv1h1az782mw- X-Sonic-MF: X-Sonic-ID: 84dc04b9-db71-4a41-a050-237a66c0f2ac Received: from sonic.gate.mail.ne1.yahoo.com by sonic313.consmr.mail.ir2.yahoo.com with HTTP; Wed, 20 Sep 2023 18:01:50 +0000 Date: Wed, 20 Sep 2023 18:01:45 +0000 (UTC) From: Harry Green To: Adrian Klaver Cc: pgsql-general Message-ID: <1656079583.6535601.1695232905186@mail.yahoo.com> In-Reply-To: <10104aac-4b99-666f-60d8-4564d2067182@aklaver.com> References: <65A32E9A-A82B-4968-9CE9-09C21AAE6CC0@arizona.edu> <103d7895-72a6-98ce-87b3-f23307408244@aklaver.com> <50A7BD11-0BFE-44A3-9F6E-31351CDB9569@arizona.edu> <10104aac-4b99-666f-60d8-4564d2067182@aklaver.com> Subject: Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf' MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6535600_1193642606.1695232905182" X-Mailer: WebService/1.1.21797 YMailNorrin Content-Length: 10875 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_6535600_1193642606.1695232905182 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks for your=C2=A0 answer,I have done a complete pg_dump and complete r= estore, and tried all forms of complete pg_dumps and restores (i.e. the dif= ferent 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 f= unction 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.=C2=A0 The pg_restore is actually correctly recreating the function using the 'cre= ate function...' command, but it is doing so on line 95, ahead of the 'crea= te table ... accounts ...' which appears on line 510. The trouble is that i= n the function itself, we refer to the accounts table, and if I am not mist= aken, 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 th= e create table instruction.=C2=A0 CREATE FUNCTION public.check_account_from_bill_items(id integer) RETURNS bo= olean=C2=A0 =C2=A0 LANGUAGE plpgsql=C2=A0 =C2=A0 AS $$DECLAREexp varchar; BEGINselect into exp account from bill_items where account in (select name = from accounts where type=3D'Expense' or type=3D'Asset' or type=3D'Stock') a= nd bill_items.id=3Did;if exp is not null thenreturn true;elsereturn false;e= nd if; END;$$; ------------------------------------------- CREATE TABLE public.accounts (=C2=A0 =C2=A0 name character varying NOT NULL= ,=C2=A0 =C2=A0 type character varying NOT NULL,=C2=A0 =C2=A0 vat real,=C2= =A0 =C2=A0 account_name character varying,=C2=A0 =C2=A0 account_number char= acter varying,=C2=A0 =C2=A0 sorting_code character varying,=C2=A0 =C2=A0 se= curity_code character varying,=C2=A0 =C2=A0 website character varying,=C2= =A0 =C2=A0 email character varying,=C2=A0 =C2=A0 username character varying= ,=C2=A0 =C2=A0 password character varying,=C2=A0 =C2=A0 institution charact= er varying,=C2=A0 =C2=A0 address text,=C2=A0 =C2=A0 telephone character var= ying,=C2=A0 =C2=A0 description text,=C2=A0 =C2=A0 security_no_for_telephone= character varying,=C2=A0 =C2=A0 id integer NOT NULL,=C2=A0 =C2=A0 subtype = character varying,=C2=A0 =C2=A0 jnbp numeric(3,2) DEFAULT 0.5,=C2=A0 =C2=A0= currency character(3) DEFAULT 'GBP'::bpchar,=C2=A0 =C2=A0 foreign_currency= character(3),=C2=A0 =C2=A0 stopped_using date,=C2=A0 =C2=A0 vat_purchase b= oolean DEFAULT false,=C2=A0 =C2=A0 vat_income boolean DEFAULT false,=C2=A0 = =C2=A0 balance_sheet_type character varying); ALTER TABLE public.accounts=C2=A0 Looking at the documentation for Create Function, I'm not entirely sure whe= ther there is anything in there which is not supposed to be there. At any r= ate, 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.=C2=A0We 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 sen= d you any parts that might help.=C2=A0 Many thanks for you help - any further suggestions would be great! On Monday, 18 September 2023 at 23:22:10 BST, Adrian Klaver wrote: =20 =20 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= =20 > connect twice, the first failed because of a typo in the password, the=20 > second because it tried to connect without ssl. I am not sure why it=20 > tried twice. >=20 >> --=20 >> Adrian Klaver >> adrian.klaver@aklaver.com >> >=20 > --=20 > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology=C2=A0Group >=20 > Institutions do not=C2=A0have opinions, merely=C2=A0customs >=20 >=20 --=20 Adrian Klaver adrian.klaver@aklaver.com =20 ------=_Part_6535600_1193642606.1695232905182 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thanks for your&nb= sp; 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 f= ormats). There is no partial or tables only or data only dumping or restori= ng. 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 s= erves correctly happens to begin by 'check...' because that happens to be t= he most descriptive word for what it does. 

T= he pg_restore is actually correctly recreating the function using the 'crea= te function...' command, but it is doing so on line 95, ahead of the 'creat= e 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 mista= ken, 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 att= ach the text of the entire create function instruction, and also of the cre= ate table instruction. 

CREATE FUNCTION publi= c.check_account_from_bill_items(id integer) RETURNS boolean
 = ;   LANGUAGE plpgsql
    AS $$
DECLARE
exp varchar;

BEGIN
select into = exp account from bill_items where account in (select name from accounts whe= re type=3D'Expense' or type=3D'Asset' or type=3D'Stock') and bill_items.id= =3Did;
if exp is not null then
return true;
e= lse
return false;
end if;

END;=
$$;

-----------------------------------= --------

CREATE TABLE public.accounts (
=     name character varying NOT NULL,
    type= character varying NOT NULL,
    vat real,
&n= bsp;   account_name character varying,
    account= _number character varying,
    sorting_code character v= arying,
    security_code character varying,
=     website character varying,
    email char= acter varying,
    username character varying,
    password character varying,
    institu= tion character varying,
    address text,
&nb= sp;   telephone character varying,
    description= text,
    security_no_for_telephone character varying,=
    id integer NOT NULL,
    subty= pe 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 fa= lse,
    vat_income boolean DEFAULT false,
&n= bsp;   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 supp= osed to be there. At any rate, the system does not seem to allow the creati= on of functions that are 'illegal' for the most part, and I cannot see why = anything in here WOULD be illegal.
 
We are workin= g with version 10.23, and I cannot send you the entire output of the pg_dum= p 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!



<= /div> =20
=20
On Monday, 18 September 2023 at 23:22:10 BST, Adrian Kl= aver <adrian.klaver@aklaver.com> wrote:


On 9/18/23 14:46, Johnson, Bruce E - = (bjohnson) wrote:

Repl= y 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 th= e password, the
> second because it tried to = connect without ssl. I am not sure why it
> t= ried twice.
>

<= /div>
>> --
>> Adria= n Klaver
>>
= >
> --
> Bru= ce Johnson
> University of Arizona
> College of Pharmacy
> Info= rmation Technology Group
>
> Institutions do not have opinions, merely custo= ms
>
>

--
= Adrian Klaver



------=_Part_6535600_1193642606.1695232905182--