az postgres flexible-server execute "no pg_hba.conf entry for host"

[karpikpl]

Describe the bug

I'm getting an error message when running

      az postgres flexible-server execute \
      --name $POSTGRES_NAME \
      --database-name postgres \
      --querytext "select * from pgaadauth_list_principals(false);" \
      --admin-user $CURRENT_USER \
      --admin-password $(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken)

Warning

Failed connection to 6oz3lgrp6b54q-postgresql. Check error and validate firewall and public access or virtual network settings.
connection to server at ".postgres.database.azure.com" (), port 5432 failed: FATAL: no pg_hba.conf entry for host "", user "", database "postgres", no encryption

But when I try to connect using psql - it works from same host.

docker run -it --rm postgres /bin/bash
psql "host=<NAME>.postgres.database.azure.com port=5432 dbname=postgres user=<MY-USER> password=<TOKEN> sslmode=require"

I'm using same user/host/token for this, but for some reason CLI gets blocked.

My IP from the error is added to firewall rules

Related command

az postgres flexible-server execute

Errors

Warning

Failed connection to 6oz3lgrp6b54q-postgresql. Check error and validate firewall and public access or virtual network settings.
connection to server at ".postgres.database.azure.com" (), port 5432 failed: FATAL: no pg_hba.conf entry for host "", user "", database "postgres", no encryption

Issue script & Debug output

az postgres flexible-server execute \
      --name <DB-NAME> \
      --database-name postgres \
      --querytext "select * from pgaadauth_list_principals(false);" \
      --admin-user "<MY-USER>" \
      --admin-password "<PASSWORD>"
cli.knack.cli: Command arguments: ['postgres', 'flexible-server', 'execute', '--name', '<DB-NAME>', '--database-name', 'postgres', '--querytext', 'select * from pgaadauth_list_principals(false);', '--admin-user', '<MY-USER>', '--admin-password', '<PASSWORD>', '--debug']
cli.knack.cli: __init__ debug log:
Enable color in terminal.
cli.knack.cli: Event: Cli.PreExecute []
e_logging at 0x000001B8C2342F20>]
cli.azure.cli.core.azlogging: metadata file logging enabled - writing logs to 'C:\Users\<MY-USER>\.azure\commands\2025-01-09.11-15-51.postgres_flexible-server_execute.16096.log'.
az_command_data_logger: command args: postgres flexible-server execute --name {} --database-name {} --querytext {} --admin-user {} --admin-password {} --debug
cli.knack.cli: Event: CommandInvoker.OnPreArgumentLoad [<function register_global_subscription_argument.<locals>.add_subscription_parameter at 0x000001B8C234F740>]
cli.knack.cli: Event: CommandInvoker.OnPostArgumentLoad []
cli.knack.cli: Event: CommandInvoker.OnPostCommandTableCreate [<function register_ids_argument.<locals>.add_ids_arguments at 0x000001B8C23A9620>, <function register_cache_arguments.<locals>.add_cache_arguments at 0x000001B8C23A9760>]
cli.knack.cli: Event: CommandInvoker.OnCommandTableLoaded []
cli.knack.cli: Event: CommandInvoker.OnPreParseArgs []
cli.knack.cli: Event: CommandInvoker.OnPostParseArgs [<function OutputProducer.handle_output_argument at 0x000001B8BF4060C0>, <function CLIQuery.handle_query_parameter at 0x000001B8BF433C40>, <function register_ids_argument.<locals>.parse_ids_arguments at 0x000001B8C23A96C0>]
az_command_data_logger: extension name: rdbms-connect
az_command_data_logger: extension version: 1.0.7
cli.azext_rdbms_connect.custom: Failed connection to <DB-NAME>. Check error and validate firewall and public access or virtual network settings.
cli.azure.cli.core.azclierror: Traceback (most recent call last):
  File "C:\Users\<MY-USER>\.azure\cliextensions\rdbms-connect\azext_rdbms_connect\custom.py", line 180, in _connect_execute_query
    connection = psycopg2.connect(**connection_kwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\<MY-USER>\.azure\cliextensions\rdbms-connect\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  password authentication failed for user "<MY-USER>"
connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  no pg_hba.conf entry for host "<MY-IP>", user "<MY-USER>", database "postgres", no encryption


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\knack/cli.py", line 233, in invoke
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/commands/__init__.py", line 664, in execute
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/commands/__init__.py", line 731, in _run_jobs_serially
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/commands/__init__.py", line 701, in _run_job
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/commands/__init__.py", line 334, in __call__
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/commands/command_operation.py", line 121, in handler
  File "C:\Users\<MY-USER>\.azure\cliextensions\rdbms-connect\azext_rdbms_connect\custom.py", line 70, in execute_flexible_server_postgres
    return connect_to_server_helper(server_type="postgres",
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\<MY-USER>\.azure\cliextensions\rdbms-connect\azext_rdbms_connect\custom.py", line 112, in connect_to_server_helper
    json_data = _connect_execute_query(server_type=server_type,
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\<MY-USER>\.azure\cliextensions\rdbms-connect\azext_rdbms_connect\custom.py", line 191, in _connect_execute_query
    raise AzureConnectionError(f"Unable to connect to flexible server: {e}")
azure.cli.core.azclierror.AzureConnectionError: Unable to connect to flexible server: connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  password authentication failed for user "<MY-USER>"
connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  no pg_hba.conf entry for host "<MY-IP>", user "<MY-USER>", database "postgres", no encryption


cli.azure.cli.core.azclierror: Unable to connect to flexible server: connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  password authentication failed for user "<MY-USER>"
connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  no pg_hba.conf entry for host "<MY-IP>", user "<MY-USER>", database "postgres", no encryption

az_command_data_logger: Unable to connect to flexible server: connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  password authentication failed for user "<MY-USER>"
connection to server at "<DB-NAME>.postgres.database.azure.com" (172.200.250.239), port 5432 failed: FATAL:  no pg_hba.conf entry for host "<MY-IP>", user "<MY-USER>", database "postgres", no encryption

cli.knack.cli: Event: Cli.PostExecute [<function AzCliLogging.deinit_cmd_metadata_logging at 0x000001B8C23431A0>]
az_command_data_logger: exit code: 1
cli.__main__: Command ran in 3.242 seconds (init: 0.435, invoke: 2.807)
telemetry.main: Begin splitting cli events and extra events, total events: 1
telemetry.client: Accumulated 0 events. Flush the clients.
telemetry.main: Finish splitting cli events and extra events, cli events: 1
telemetry.save: Save telemetry record of length 4182 in cache
telemetry.main: Begin creating telemetry upload process.
telemetry.process: Creating upload process: "C:\Program Files\Microsoft SDKs\Azure\CLI2\python.exe C:\Program Files\Microsoft SDKs\Azure\CLI2\Lib\site-packages\azure\cli\telemetry\__init__.pyc C:\Users\<MY-USER>\.azure"
telemetry.process: Return from creating process
telemetry.main: Finish creating telemetry upload process.

Expected behavior

since firewall rules are in place and I can connect to the DB, I expect the CLI command to succeed

Environment Summary

azure-cli 2.67.0

core 2.67.0
telemetry 1.1.0

Extensions:
aks-preview 13.0.0b2
azure-iot 0.25.0
bastion 1.3.1
containerapp 1.1.0b1
load 1.4.0
rdbms-connect 1.0.7
ssh 2.0.6

Dependencies:
msal 1.31.0
azure-mgmt-resource 23.1.1

Python location 'C:\Program Files\Microsoft SDKs\Azure\CLI2\python.exe'
Extensions directory 'C:\Users<USER>.azure\cliextensions'

Python (Windows) 3.12.7 (tags/v3.12.7:0b05ead, Oct 1 2024, 03:06:41) [MSC v.1941 64 bit (AMD64)]

Legal docs and information: aka.ms/AzureCliLegal

Your CLI is up-to-date.

Additional context

No response

[yonzhan]

Thank you for opening this issue, we will look into it.

[karpikpl]

it was a typo :(

[microsoft-github-policy-service]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @anuragdalmia, @ShilpiGautam, @ramaganesan-rg.