When attempting to create a connection between a Postgres flex-server and a webapp using a Managed Identity an error occurs

justbert commented 9 months ago

Describe the bug

When attempting to get information about the DB, the db show command fails causing the webapp connection create command to fail.

Suspect the issue is in the db show since when a DB name that exists is provided, a invalid value for parameter databaseName is returned but when a DB that doesn't exist is passed, a db not found error is returned.

Related command

`az webapp connection create`
`az postgres flexible-server db show`

Errors

The behavior of this command has been altered by the following extension: serviceconnector-passwordless
Command execution failed, command is: az postgres flexible-server db show --server-name <SERVER> --database-name postgres -g <RG> --subscription <SUB>-o json, error message is:
 ERROR: (InvalidParameterValue) Invalid value given for parameter databaseName. Specify a valid parameter value.
Code: InvalidParameterValue
Message: Invalid value given for parameter databaseName. Specify a valid parameter value.

Issue script & Debug output

cli.knack.cli: Command arguments: ['webapp', 'connection', 'create', 'postgres-flexible', '--connection', '<DB>', '--source-id', '/subscriptions/<SUB>/resourceGroups/<RG>/providers/Microsoft.Web/sites/<WEBAPP>', '--target-id', '/subscriptions/<SUB>/resourceGroups/<RG>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<SERVER>/databases/postgres', '--client-type', 'none', '--user-identity', 'client-id=<ID>', 'subs-id=<SUB>', '--debug']
cli.knack.cli: __init__ debug log:
Enable color in terminal.
cli.knack.cli: Event: Cli.PreExecute []
cli.knack.cli: Event: CommandParser.OnGlobalArgumentsCreate [<function CLILogging.on_global_arguments at 0x7fa0128500e0>, <function OutputProducer.on_global_arguments at 0x7fa0127fa200>, <function CLIQuery.on_global_arguments at 0x7fa01282fce0>]
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableCreate []
cli.azure.cli.core: Modules found from index for 'webapp': ['azure.cli.command_modules.appservice', 'azure.cli.command_modules.serviceconnector', 'azext_serviceconnector_passwordless']
cli.azure.cli.core: Loading command modules:
cli.azure.cli.core: Name                  Load Time    Groups  Commands
cli.azure.cli.core: appservice                0.135        73       260
cli.azure.cli.core: serviceconnector          0.047        16       244
cli.azure.cli.core: Total (2)                 0.183        89       504
cli.azure.cli.core: These extensions are not installed and will be skipped: ['azext_ai_examples', 'azext_next']
cli.azure.cli.core: Loading extensions:
cli.azure.cli.core: Name                  Load Time    Groups  Commands  Directory
cli.azure.cli.core: serviceconnector-passwordless      0.018        11        16  /home/<USER>/.azure/cliextensions/serviceconnector-passwordless
cli.azure.cli.core: Total (1)                 0.018        11        16
cli.azure.cli.core: Loaded 87 groups, 504 commands.
cli.azure.cli.core: Found a match in the command table.
cli.azure.cli.core: Raw command  : webapp connection create postgres-flexible
cli.azure.cli.core: Command table: webapp connection create postgres
cli.azure.cli.core: remaining    :                                   flexible
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableTruncate [<function AzCliLogging.init_command_file_logging at 0x7fa011719940>]
cli.azure.cli.core.azlogging: metadata file logging enabled - writing logs to '/home/<USER>/.azure/commands/2024-02-08.10-06-17.webapp_connection_create_postgres-flexible.3800.log'.
az_command_data_logger: command args: webapp connection create postgres-flexible --connection {} --source-id {} --target-id {} --client-type {} --user-identity {} {} --debug
cli.knack.cli: Event: CommandInvoker.OnPreArgumentLoad [<function register_global_subscription_argument.<locals>.add_subscription_parameter at 0x7fa01172bec0>]
cli.knack.cli: Event: CommandInvoker.OnPostArgumentLoad []
cli.knack.cli: Event: CommandInvoker.OnPostCommandTableCreate [<function register_ids_argument.<locals>.add_ids_arguments at 0x7fa011789c60>, <function register_cache_arguments.<locals>.add_cache_arguments at 0x7fa011789da0>]
cli.knack.cli: Event: CommandInvoker.OnCommandTableLoaded []
cli.knack.cli: Event: CommandInvoker.OnPreParseArgs []
cli.knack.cli: Event: CommandInvoker.OnPostParseArgs [<function OutputProducer.handle_output_argument at 0x7fa0127fa2a0>, <function CLIQuery.handle_query_parameter at 0x7fa01282fd80>, <function register_ids_argument.<locals>.parse_ids_arguments at 0x7fa011789d00>]
az_command_data_logger: extension name: serviceconnector-passwordless
az_command_data_logger: extension version: 1.0.1
cli.azure.cli.core.commands: The behavior of this command has been altered by the following extension: serviceconnector-passwordless
cli.azure.cli.core.commands.client_factory: Getting management service client client_type=ServiceLinkerManagementClient
cli.azure.cli.core.auth.persistence: build_persistence: location='/home/<USER>/.azure/msal_token_cache.json', encrypt=False
cli.azure.cli.core.auth.binary_cache: load: /home/<USER>/.azure/msal_http_cache.bin
urllib3.util.retry: Converted retries value: 1 -> Retry(total=1, connect=None, read=None, redirect=None, status=None)
msal.authority: openid_config = {'token_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/token', 'token_endpoint_auth_methods_supported': ['client_secret_post', 'private_key_jwt', 'client_secret_basic'], 'jwks_uri': 'https://login.microsoftonline.com/<TENANT>/discovery/v2.0/keys', 'response_modes_supported': ['query', 'fragment', 'form_post'], 'subject_types_supported': ['pairwise'], 'id_token_signing_alg_values_supported': ['RS256'], 'response_types_supported': ['code', 'id_token', 'code id_token', 'id_token token'], 'scopes_supported': ['openid', 'profile', 'email', 'offline_access'], 'issuer': 'https://login.microsoftonline.com/<TENANT>/v2.0', 'request_uri_parameter_supported': False, 'userinfo_endpoint': 'https://graph.microsoft.com/oidc/userinfo', 'authorization_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/authorize', 'device_authorization_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/devicecode', 'http_logout_supported': True, 'frontchannel_logout_supported': True, 'end_session_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/logout', 'claims_supported': ['sub', 'iss', 'cloud_instance_name', 'cloud_instance_host_name', 'cloud_graph_host_name', 'msgraph_host', 'aud', 'exp', 'iat', 'auth_time', 'acr', 'nonce', 'preferred_username', 'name', 'tid', 'ver', 'at_hash', 'c_hash', 'email'], 'kerberos_endpoint': 'https://login.microsoftonline.com/<TENANT>/kerberos', 'tenant_region_scope': 'NA', 'cloud_instance_name': 'microsoftonline.com', 'cloud_graph_host_name': 'graph.windows.net', 'msgraph_host': 'graph.microsoft.com', 'rbac_url': 'https://pas.windows.net'}
msal.application: Broker enabled? False
urllib3.util.retry: Converted retries value: 1 -> Retry(total=1, connect=None, read=None, redirect=None, status=None)
msal.authority: openid_config = {'token_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/token', 'token_endpoint_auth_methods_supported': ['client_secret_post', 'private_key_jwt', 'client_secret_basic'], 'jwks_uri': 'https://login.microsoftonline.com/<TENANT>/discovery/v2.0/keys', 'response_modes_supported': ['query', 'fragment', 'form_post'], 'subject_types_supported': ['pairwise'], 'id_token_signing_alg_values_supported': ['RS256'], 'response_types_supported': ['code', 'id_token', 'code id_token', 'id_token token'], 'scopes_supported': ['openid', 'profile', 'email', 'offline_access'], 'issuer': 'https://login.microsoftonline.com/<TENANT>/v2.0', 'request_uri_parameter_supported': False, 'userinfo_endpoint': 'https://graph.microsoft.com/oidc/userinfo', 'authorization_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/authorize', 'device_authorization_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/devicecode', 'http_logout_supported': True, 'frontchannel_logout_supported': True, 'end_session_endpoint': 'https://login.microsoftonline.com/<TENANT>/oauth2/v2.0/logout', 'claims_supported': ['sub', 'iss', 'cloud_instance_name', 'cloud_instance_host_name', 'cloud_graph_host_name', 'msgraph_host', 'aud', 'exp', 'iat', 'auth_time', 'acr', 'nonce', 'preferred_username', 'name', 'tid', 'ver', 'at_hash', 'c_hash', 'email'], 'kerberos_endpoint': 'https://login.microsoftonline.com/<TENANT>/kerberos', 'tenant_region_scope': 'NA', 'cloud_instance_name': 'microsoftonline.com', 'cloud_graph_host_name': 'graph.windows.net', 'msgraph_host': 'graph.microsoft.com', 'rbac_url': 'https://pas.windows.net'}
msal.application: Broker enabled? False
cli.azure.cli.core.auth.msal_authentication: UserCredential.get_token: scopes=('https://management.core.windows.net//.default',), claims=None, kwargs={}
msal.application: Cache hit an AT
msal.telemetry: Generate or reuse correlation_id: 6c1357c9-c2b9-4d9d-b0c8-321d9ba88913
cli.azure.cli.command_modules.serviceconnector._utils: CompletedProcess(args='az account show -o json', returncode=0, stdout=b'{\n  "environmentName": "AzureCloud",\n  "homeTenantId": "<TENANT>",\n  "id": "<ID>",\n  "isDefault": true,\n  "managedByTenants": [\n    {\n      "tenantId": "<TENANT>"\n    }\n  ],\n  "name": "<TENANT NAME>I",\n  "state": "Enabled",\n  "tenantId": "<TENANT>",\n  "user": {\n    "name": "<USER>",\n    "type": "user"\n  }\n}\n', stderr=b'')
cli.azure.cli.command_modules.serviceconnector._utils: CompletedProcess(args='az account show -o json', returncode=0, stdout=b'{\n  "environmentName": "AzureCloud",\n  "homeTenantId": "<TENANT>",\n  "id": "<ID>",\n  "isDefault": true,\n  "managedByTenants": [\n    {\n      "tenantId": "<TENANT>"\n    }\n  ],\n  "name": "<TENANT NAME>I",\n  "state": "Enabled",\n  "tenantId": "<TENANT>",\n  "user": {\n    "name": "<USER>",\n    "type": "user"\n  }\n}\n', stderr=b'')
cli.azure.cli.command_modules.serviceconnector._utils: CompletedProcess(args='az postgres flexible-server db show --server-name <SERVER> --database-name postgres -g <RG> --subscription <SUB> -o json', returncode=1, stdout=b'', stderr=b'ERROR: (InvalidParameterValue) Invalid value given for parameter databaseName. Specify a valid parameter value.\nCode: InvalidParameterValue\nMessage: Invalid value given for parameter databaseName. Specify a valid parameter value.\n')
cli.azure.cli.core.azclierror: Traceback (most recent call last):
  File "/opt/az/lib/python3.11/site-packages/knack/cli.py", line 233, in invoke
    cmd_result = self.invocation.execute(args)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/azure/cli/core/commands/__init__.py", line 664, in execute
    raise ex
  File "/opt/az/lib/python3.11/site-packages/azure/cli/core/commands/__init__.py", line 729, in _run_jobs_serially
    results.append(self._run_job(expanded_arg, cmd_copy))
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/azure/cli/core/commands/__init__.py", line 698, in _run_job
    result = cmd_copy(params)
             ^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/azure/cli/core/commands/__init__.py", line 334, in __call__
    return self.handler(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/azure/cli/core/commands/command_operation.py", line 121, in handler
    return op(**command_args)
           ^^^^^^^^^^^^^^^^^^
  File "/home/<USER>/.azure/cliextensions/serviceconnector-passwordless/azext_serviceconnector_passwordless/custom.py", line 31, in connection_create_ext
    return connection_create_func(cmd, client, connection_name, client_type,
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/azure/cli/command_modules/serviceconnector/custom.py", line 477, in connection_create_func
    new_auth_info = enable_mi_for_db_linker(
                    ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/<USER>/.azure/cliextensions/serviceconnector-passwordless/azext_serviceconnector_passwordless/_credential_free.py", line 67, in enable_mi_for_db_linker
    target_handler.check_db_existence()
  File "/home/<USER>/.azure/cliextensions/serviceconnector-passwordless/azext_serviceconnector_passwordless/_credential_free.py", line 654, in check_db_existence
    raise e
  File "/home/<USER>/.azure/cliextensions/serviceconnector-passwordless/azext_serviceconnector_passwordless/_credential_free.py", line 644, in check_db_existence
    db_info = run_cli_cmd(
              ^^^^^^^^^^^^
  File "/home/<USER>/.azure/cliextensions/serviceconnector-passwordless/azext_serviceconnector_passwordless/_utils.py", line 46, in run_cli_cmd
    raise e
  File "/home/<USER>/.azure/cliextensions/serviceconnector-passwordless/azext_serviceconnector_passwordless/_utils.py", line 36, in run_cli_cmd
    return run_cli_cmd_base(cmd + ' -o json', retry, interval, should_retry_func)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/azure/cli/command_modules/serviceconnector/_utils.py", line 91, in run_cli_cmd
    raise CLIInternalError('Command execution failed, command is: '
azure.cli.core.azclierror.CLIInternalError: Command execution failed, command is: az postgres flexible-server db show --server-name <SERVER> --database-name postgres -g <RG> --subscription <SUB> -o json, error message is:
 ERROR: (InvalidParameterValue) Invalid value given for parameter databaseName. Specify a valid parameter value.
Code: InvalidParameterValue
Message: Invalid value given for parameter databaseName. Specify a valid parameter value.

cli.azure.cli.core.azclierror: Command execution failed, command is: az postgres flexible-server db show --server-name <SERVER> --database-name postgres -g <RG> --subscription <SUB> -o json, error message is:
 ERROR: (InvalidParameterValue) Invalid value given for parameter databaseName. Specify a valid parameter value.
Code: InvalidParameterValue
Message: Invalid value given for parameter databaseName. Specify a valid parameter value.

az_command_data_logger: Command execution failed, command is: az postgres flexible-server db show --server-name <SERVER> --database-name postgres -g <RG> --subscription <SUB> -o json, error message is:
 ERROR: (InvalidParameterValue) Invalid value given for parameter databaseName. Specify a valid parameter value.
Code: InvalidParameterValue
Message: Invalid value given for parameter databaseName. Specify a valid parameter value.

cli.knack.cli: Event: Cli.PostExecute [<function AzCliLogging.deinit_cmd_metadata_logging at 0x7fa011719bc0>]
az_command_data_logger: exit code: 1
cli.__main__: Command ran in 2.562 seconds (init: 0.174, invoke: 2.388)
cli.azure.cli.core.decorators: Suppress exception:
Traceback (most recent call last):
  File "/opt/az/lib/python3.11/site-packages/azure/cli/__main__.py", line 62, in <module>
    raise ex
  File "/opt/az/lib/python3.11/site-packages/azure/cli/__main__.py", line 55, in <module>
    sys.exit(exit_code)
SystemExit: 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/az/lib/python3.11/site-packages/azure/cli/core/decorators.py", line 79, in _wrapped_func
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/azure/cli/core/telemetry.py", line 532, in _get_secrets_warning_config
    show_secrets_warning = _get_config().getboolean('clients', 'show_secrets_warning', fallback=None)
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/az/lib/python3.11/site-packages/knack/config.py", line 147, in getboolean
    raise ValueError('Not a boolean: {}'.format(val))
ValueError: Not a boolean: None

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 12827 in cache
telemetry.main: Begin creating telemetry upload process.
telemetry.process: Creating upload process: "/opt/az/bin/python3 /opt/az/lib/python3.11/site-packages/azure/cli/telemetry/__init__.py /home/<USER>/.azure"
telemetry.process: Return from creating process
telemetry.main: Finish creating telemetry upload process.

Expected behavior

The DB info is returned and the connection is created.

Environment Summary

❯ az version
{
  "azure-cli": "2.57.0",
  "azure-cli-core": "2.57.0",
  "azure-cli-telemetry": "1.1.0",
  "extensions": {
    "serviceconnector-passwordless": "1.0.1"
  }
}

Additional context

No response

yonzhan commented 9 months ago

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

houk-ms commented 9 months ago

@xfz11 please take a look

xfz11 commented 9 months ago

Hi @yonzhan, could you help assign the issue to postgresql team? Briefly, when user runs a command az postgres flexible-server db show --server-name <SERVER> --database-name postgres and the database is postgres, then the command throws error:

Code: InvalidParameterValue
Message: Invalid value given for parameter databaseName. Specify a valid parameter value.

The error seems confused and block the following process depending on the command.

evelyn-ys commented 9 months ago

@nasc17 as the last contributor for az postgres flexible-server

xfz11 commented 9 months ago

@justbert For a workaround, you can create a new database in postgresql server. The postgres is a special db and it may cause some problems.

justbert commented 9 months ago

@justbert For a workaround, you can create a new database in postgresql server. The postgres is a special system db and it may cause some problems.

What do you mean? postgres is just the default database which can be used, according to the docs:

The postgres database is a default database meant for use by users, utilities and third party applications...

https://www.postgresql.org/docs/current/app-initdb.html

xfz11 commented 9 months ago

What do you mean? postgres is just the default database which can be used, according to the docs:

The postgres database is a default database meant for use by users, utilities and third party applications...

https://www.postgresql.org/docs/current/app-initdb.html

Yes, it is. But Azure Postgresql flexible server handle it specially. If you really need to use postgres database, let's wait for Postgresql team @nasc17 for more update.

justbert commented 9 months ago

What do you mean? postgres is just the default database which can be used, according to the docs:

The postgres database is a default database meant for use by users, utilities and third party applications...

https://www.postgresql.org/docs/current/app-initdb.html

Yes, it is. But Azure Postgresql flexible server handle it specially. If you really need to use postgres database, let's wait for Postgresql team @nasc17 for more update.

I've tried finding this special case in the documentation but cannot find it. Can you point me to the Azure documentation which describes this?

nasc17 commented 9 months ago

@justbert For a workaround, you can create a new database in postgresql server. The postgres is a special db and it may cause some problems.

Thank you @xfz11 for providing this workaround. As of now we are looking into determining the reason as to why the 'postgres' database would require being handled differently.

xfz11 commented 9 months ago

@justbert For a workaround, you can create a new database in postgresql server. The postgres is a special db and it may cause some problems.

Thank you @xfz11 for providing this workaround. As of now we are looking into determining the reason as to why the 'postgres' database would require being handled differently.

@nasc17 Sorry if I cause any misunderstanding, I don't mean to let 'postgres' to be handled specially. I just want to say, if user get a database postgres with the command az postgres flexible-server db show it throws exception, and if user create a new db and get it, the command will not. And we ask for az postgres flexible-server db show -d postgres can also return data successfully and not throw exception.

Azure / azure-cli