Обсуждение: Connection not allowed because of an error 'Not in pg_hba.conf'
Connection not allowed because of an error 'Not in pg_hba.conf'
От
"Johnson, Bruce E - (bjohnson)"
Дата:
I am doing some testing trying to migrate some websites from using Oracle to Postgres. (Using Perl DBI and DBD::Pg as the connecting mechanism)
(Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky Linux using the supplied PostgreSQL DBI and DBD::Pg packages)
The error I am getting on the client is:
password authentication failed for user "trav"
connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), port 5432 failed: FATAL: no pg_hba.conf entry for host "10.128.206.109", user "trav", database "webdata", no encryption
But I do have an entry that should allow it:
#Internal server mgmt range
hostssl all all 10.128.206.0/23 password
From the manual (pg 704, 21.1. The pg_hba.conf File):
"An IP address range is specified using standard numeric notation for the range's starting address, then a slash (/) and a CIDR mask length. The mask length indicates the number of high-order bits of the client IP address that must match. Bits to the right of this should be zero in the given IP address. There must not be any white space between the IP address, the /, and the CIDR mask length.
Typical examples of an IPv4 address range specified this way are 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 10.6.0.0/16 for a larger one. "
10.128.206.109 is definitely in that range.
The test script DOES work with my desktop running the same software, but I have it set in pg_hba.conf as just my systems ip:
hostssl webdata trav nnn.nnn.nnn.nnn/32 password
(Ip address redacted because it is externally accessible)
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
On 9/18/23 12:45, Johnson, Bruce E - (bjohnson) wrote: > I am doing some testing trying to migrate some websites from using > Oracle to Postgres. (Using Perl DBI and DBD::Pg as the connecting > mechanism) > > (Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky > Linux using the supplied PostgreSQL DBI and DBD::Pg packages) > > The error I am getting on the client is: > > password authentication failed for user "trav" > > connection to server at "dhbpostgres.pharmacy.arizona.edu" > (10.128.206.109), port 5432 failed: FATAL: no pg_hba.conf entry for > host "10.128.206.109", user "trav", database "webdata", no encryption > > > But I do have an entry that should allow it: > > #Internal server mgmt range > > hostsslallall10.128.206.0/23 password I guessing you want that to be: 10.128.206.109/32 or 10.128.206.0/24 > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs > > -- Adrian Klaver adrian.klaver@aklaver.com
"Johnson, Bruce E - (bjohnson)" <Johnson@pharmacy.arizona.edu> writes:
> The error I am getting on the client is:
> password authentication failed for user "trav"
> connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), port 5432 failed: FATAL: no pg_hba.conf
entryfor host "10.128.206.109", user "trav", database "webdata", no encryption
> But I do have an entry that should allow it:
> #Internal server mgmt range
> hostssl all all 10.128.206.0/23 password
I think what you are seeing here is two separate connection attempts.
libpq will try an SSL connection, and that one is seemingly failing
with a bad password. Then it'll try a non-SSL connection, and that
one is getting rejected by the server because "hostssl" doesn't
permit it, leading to your second message (which clearly shows
that that connection wasn't ssl-encrypted).
You could adjust your connection parameters on the client side to
prevent the useless non-SSL connection attempt. But of course the
real question is how come the password authentication failed in
the first attempt. Looking into the server's log might yield
a clue.
regards, tom lane
On Sep 18, 2023, at 11:45 AM, Johnson, Bruce E - (bjohnson) <Johnson@pharmacy.arizona.edu> wrote:I am doing some testing trying to migrate some websites from using Oracle to Postgres. (Using Perl DBI and DBD::Pg as the connecting mechanism)(Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky Linux using the supplied PostgreSQL DBI and DBD::Pg packages)The error I am getting on the client is:password authentication failed for user "trav"connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), port 5432 failed: FATAL: no pg_hba.conf entry for host "10.128.206.109", user "trav", database "webdata", no encryptionBut I do have an entry that should allow it:#Internal server mgmt rangehostssl all all 10.128.206.0/23 password
I might be missing something obvious, but your error says “no encryption”, while the pg_hba entry is “hostssl” indicating it will match encrypted connections only, so it doesn’t match.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145
cell: 907-328-9145
From the manual (pg 704, 21.1. The pg_hba.conf File):"An IP address range is specified using standard numeric notation for the range's starting address, then a slash (/) and a CIDR mask length. The mask length indicates the number of high-order bits of the client IP address that must match. Bits to the right of this should be zero in the given IP address. There must not be any white space between the IP address, the /, and the CIDR mask length.
Typical examples of an IPv4 address range specified this way are 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 10.6.0.0/16 for a larger one. "
10.128.206.109 is definitely in that range.The test script DOES work with my desktop running the same software, but I have it set in pg_hba.conf as just my systems ip:hostssl webdata trav nnn.nnn.nnn.nnn/32 password(Ip address redacted because it is externally accessible)--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
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.klaver@aklaver.com >> > > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs > > -- Adrian Klaver adrian.klaver@aklaver.com
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 $$
DECLARE
exp varchar;
BEGIN
select 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 then
return true;
else
return 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 <adrian.klaver@aklaver.com> 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
>>
>
> --
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
>
> Institutions do not have opinions, merely customs
>
>
--
Adrian Klaver
Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf'
От
"David G. Johnston"
Дата:
On Wed, Sep 20, 2023 at 2:06 PM Harry Green <harrygreen91@yahoo.com> 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.
Dear All,
I'd like to thank you for your excellent input on this problem. We have now resolved this issue and I thought I would mention how. The topic of the function being used as a check constraint had come up and I had rejected this because it was the name given to a function which does some checking. Since the function was used elsewhere I did not think it was itself used as a check constraint, but in fact it was (on a different table than the one that comes up in the error message). So you were right to highlight that fact. I should note, however, that it seems to me this is a bug in postgresql, if not in the narrower sense than in the wider: the issue is that nowhere in the process of creating a very simple function that references some table and then employed as a check constraint on a different table is the user warned or stopped from doing so. In many cases, doing this saves time over creating triggers or alternative mechanisms, so the user is naturally drawn to employing this technique. Yet when the pg_dump is attempted the process fails. Furthermore, it is not entirely clear why pg_dump cannot add the check constraints after all the tables are created, just as it does with triggers. This is why it is worth considering or treating this as a bug that may have a solution - either by modifying pg_restore (or text equivalent process) or by preventing the user from employing certain types of functions as check constraints.
The problem was resolved not by altering the functions in any way, but by creating triggers that employed them through wrap-up functions which used the NEW.column_name mechanism in the usual way on inserts and updates.
Thank you once again for your valuable feedback.
On Wednesday, 20 September 2023 at 22:16:32 BST, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Sep 20, 2023 at 2:06 PM Harry Green <harrygreen91@yahoo.com> 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.
On 10/3/23 02:59, Harry Green wrote: > > Dear All, > I'd like to thank you for your excellent input on this problem. We have > now resolved this issue and I thought I would mention how. The topic of > the function being used as a check constraint had come up and I had > rejected this because it was the name given to a function which does > some checking. Since the function was used elsewhere I did not think it > was itself used as a check constraint, but in fact it was (on a > different table than the one that comes up in the error message). So you > were right to highlight that fact. I should note, however, that it seems > to me this is a bug in postgresql, if not in the narrower sense than in > the wider: the issue is that nowhere in the process of creating a very > simple function that references some table and then employed as a check > constraint on a different table is the user warned or stopped from doing > so. In many cases, doing this saves time over creating triggers or > alternative mechanisms, so the user is naturally drawn to employing this > technique. Yet when the pg_dump is attempted the process fails. > Furthermore, it is not entirely clear why pg_dump cannot add the check > constraints after all the tables are created, just as it does with > triggers. This is why it is worth considering or treating this as a bug > that may have a solution - either by modifying pg_restore (or text > equivalent process) or by preventing the user from employing certain > types of functions as check constraints. 1) Hijacking another thread is not a good idea. 2) Per my first post on this issue: https://www.postgresql.org/docs/current/sql-createtable.html "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column." It is pretty plain you cannot refer to columns outside the table the CHECK is on. Trying to cheat by hiding that in a function, well you see what happens then. 3) The function bodies are opaque to the server. This means there will be no dependency tracking for the dump/restore. > > The problem was resolved not by altering the functions in any way, but > by creating triggers that employed them through wrap-up functions which > used the NEW.column_name mechanism in the usual way on inserts and updates. > > Thank you once again for your valuable feedback. > > > On Wednesday, 20 September 2023 at 22:16:32 BST, David G. Johnston > <david.g.johnston@gmail.com> wrote: > > > On Wed, Sep 20, 2023 at 2:06 PM Harry Green <harrygreen91@yahoo.com > <mailto:harrygreen91@yahoo.com>> 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. > -- Adrian Klaver adrian.klaver@aklaver.com