Thread: BUG #5775: DBLINK Connection String Truncation
The following bug has been logged online: Bug reference: 5775 Logged by: David Quinn-Jacobs Email address: dqj@ecornell.com PostgreSQL version: 9.0.1 Operating system: Cent OS 5 Description: DBLINK Connection String Truncation Details: Causes connection failure. To re-create: CREATE TABLE Local ( foo TEXT ); CREATE TABLE Remote ( bar TEXT ); CREATE VIEW DblinkBugView AS SELECT * FROM Local L LEFT OUTER JOIN dblink( 'hostname=123.456.789.012 dbname=a_longish_database_name user=my_user_name password=my_password', 'SELECT bar FROM Remote' ) AS R ( bar TEXT ) ON (L.foo=R.bar); The sample case can have both tables located on the same server to simplify. The error message is not just generated. The string is actually truncated, as demonstrated in the psql command result: mydb=# SELECT * FROM DblinkBugView; NOTICE: identifier "host=123.456.789.012 dbname=a_longish_database_name user=my_user_name password=my_password" will be truncated to "host=123.456.789.012 dbname=a_longish_database_name user=my_user_name p" ERROR: could not establish connection DETAIL: FATAL: no pg_hba.conf entry for host "123.456.789.012", user "my_user_name", database "a_longish_database_name", SSL off Note: I replaced the actual connection strings with equivalents, so the position of truncation differed slightly. The password was cut off, so the connection failed. The message is a bit incongruous with the actual problem. Note also that the code works fine in 8.4, but not 9.0.1. Thank you! -dqj
"David Quinn-Jacobs" <dqj@ecornell.com> writes: > Causes connection failure. To re-create: > CREATE TABLE Local ( foo TEXT ); > CREATE TABLE Remote ( bar TEXT ); > CREATE VIEW DblinkBugView AS > SELECT * FROM Local L > LEFT OUTER JOIN dblink( 'hostname=123.456.789.012 > dbname=a_longish_database_name user=my_user_name password=my_password', > 'SELECT bar FROM Remote' ) AS R ( bar TEXT ) ON (L.foo=R.bar); > The sample case can have both tables located on the same server to simplify. > The error message is not just generated. The string is actually truncated, > as demonstrated in the psql command result: > mydb=# SELECT * FROM DblinkBugView; > NOTICE: identifier "host=123.456.789.012 dbname=a_longish_database_name > user=my_user_name password=my_password" will be truncated to > "host=123.456.789.012 dbname=a_longish_database_name user=my_user_name p" > ERROR: could not establish connection > DETAIL: FATAL: no pg_hba.conf entry for host "123.456.789.012", user > "my_user_name", database "a_longish_database_name", SSL off Well, it works for me, as nearly as I can replicate this test case. I get the bogus NOTICE but the connection goes through anyway: contrib_regression=# SELECT * FROM DblinkBugView; NOTICE: identifier "host=127.0.0.1 dbname=contrib_regression user=postgres" will be truncated to "host=127.0.0.1 dbname=contrib_regression user=po" foo | bar -----+----- (0 rows) Please notice that the connection error you are getting is *not* about a bad password. It looks like you've neglected to provide a pg_hba.conf entry that will allow dblink connections. regards, tom lane