0

I am trying to connect to a postgres database from my python script using the psycopg2 library. But I am getting the following error

import psycopg2

conn = psycopg2.connect(host = <hostname>, port = 5432, database = <db_name>, user = <user_name>, password = <password>)

psycopg2.OperationalError: FATAL:  no pg_hba.conf entry for host "<MY IP ADDRESS>", user <user_name>, database <db_name>, no encryption

However, my IP address has already been added to the whitelist and I am able to connect to the database from the same computer using PgAdmin and VS Code. So it doesn't appear to be a case of IP whitelisting

Any idea why this might be happening ?

P.S: The postgres database is setup in Azure cloud

sibiyes
  • 61
  • 7
  • Are you connecting to the same database as the same user with the same password? – Tim Roberts Jun 29 '23 at 17:51
  • Yes. Connecting to the same database with same username and password. – sibiyes Jun 29 '23 at 17:56
  • Have you tried `sslmode=prefer` to set up an SSL connection? – Tim Roberts Jun 29 '23 at 18:01
  • 1) Define what you mean by `whitelist`. 2) The error is specific `...no pg_hba.conf entry...` so are you sure the host you are connecting from is set up in the file? – Adrian Klaver Jun 29 '23 at 18:10
  • Was that the only error message you got, or was there perhaps an immediately prior error message you overlooked? – jjanes Jun 29 '23 at 21:51
  • check if your system IP address in within the range of ip address you added ```import psycopg2 src_conn_string = "SourceConnectionString" dst_conn_string = "DStConnectionString" try: src_conn = psycopg2.connect(src_conn_string) src_cursor = src_conn.cursor() print("Connected to source database.") try: dst_conn = psycopg2.connect(dst_conn_string) dst_cursor = dst_conn.cursor() print("Connected to destination database.")``` – Sampath Jun 30 '23 at 03:36
  • you check the address with [ipconfig](https://i.imgur.com/G1XEiKc.png) and in networking Or you can remove the all ip address added and the application again – Sampath Jun 30 '23 at 03:39
  • "IPv4 Address" under the network adapter you are connected to. The IP address listed there is your system's IP address. I have cross-checked it [Output](https://i.imgur.com/QbRU0Dv.png). hope it helps – Sampath Jun 30 '23 at 03:47
  • For more details refer this [MSDOC](https://learn.microsoft.com/en-us/azure/postgresql/single-server/connect-python) – Sampath Jul 01 '23 at 01:40

1 Answers1

0

enter image description here

enter image description here

enter image description here

  • Tried this code from this MSDOC:
import  psycopg2
host = "sampath.postgres.database.azure.com"
dbname = "postgres"
user = "sampath"
password = "*sam"
sslmode = "require"  
src_conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host,  user,  dbname,  password,  sslmode)
try:
src_conn = psycopg2.connect(src_conn_string)
src_cursor = src_conn.cursor()
print("Connected to the source database.")
src_cursor.close()
src_conn.close()
print("Disconnected from the source database.")
except  psycopg2.Error  as  src_error:
print("Error connecting to the source database:",  src_error)

Output with FireWall Rule:

enter image description here

Sampath
  • 810
  • 2
  • 2
  • 13