PostgresSQL Provider eats Cloud SQL socket information

[webb-ben]

Description
When sqlalchemy.create_engine parses the connection information for a Cloud SQL provider, the port is incorrectly identified with [this regex].(https://github.com/sqlalchemy/sqlalchemy/blob/fc57bafbae9d67b7ce95e26c939ca957c366b0f7/lib/sqlalchemy/engine/url.py#L843-L862)

Steps to Reproduce
Deploy a Google Cloud Run container configured for a Cloud SQL PostGIS database.

Expected behavior
Able to deploy pygeoapi as a serverless container around a Google Cloud SQL database.

Screenshots/Tracebacks
Given:

        providers: 
            - type: feature
              name: PostgreSQL
              data:
                  host: /cloudsql/geoconnex-us:us-central1:reference-features
                  dbname: reference
                  user: ${POSTGRES_USER}
                  password: ${POSTGRES_PASSWORD}
              id_field: huc2
              table: hu02
              uri_field: uri
              geom_field: geom

Traceback (most recent call last):
  File "/usr/local/bin/pygeoapi", line 33, in <module>
    sys.exit(load_entry_point('pygeoapi', 'console_scripts', 'pygeoapi')())
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1134, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1059, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1665, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1665, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1401, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 767, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/pygeoapi/pygeoapi/openapi.py", line 1302, in generate
    content = generate_openapi_document(config_file, format_)
  File "/pygeoapi/pygeoapi/openapi.py", line 1277, in generate_openapi_document
    content = yaml.safe_dump(get_oas(s), default_flow_style=False)
  File "/pygeoapi/pygeoapi/openapi.py", line 1234, in get_oas
    return get_oas_30(cfg)
  File "/pygeoapi/pygeoapi/openapi.py", line 495, in get_oas_30
    p = load_plugin('provider', get_provider_by_type(
  File "/pygeoapi/pygeoapi/plugin.py", line 111, in load_plugin
    plugin = class_(plugin_def)
  File "/pygeoapi/pygeoapi/provider/postgresql.py", line 101, in __init__
    self._engine, self.table_model = self._get_engine_and_table_model()
  File "/pygeoapi/pygeoapi/provider/postgresql.py", line 267, in _get_engine_and_table_model
    table_model = self._reflect_table_model(engine)
  File "/pygeoapi/pygeoapi/provider/postgresql.py", line 287, in _reflect_table_model
    raise ProviderConnectionError(msg)
pygeoapi.provider.base.ProviderConnectionError: Could not connect to postgresql+psycopg2://***:***@/cloudsql/geoconnex-us:us-central1:reference-features:5432/reference (password hidden).

and given

        providers: 
            - type: feature
              name: PostgreSQL
              data:
                  host: geoconnex-us:us-central1:reference-features
                  dbname: reference
                  user: ${POSTGRES_USER}
                  password: ${POSTGRES_PASSWORD}
              id_field: huc2
              table: hu02
              uri_field: uri
              geom_field: geom

File "/usr/local/bin/pygeoapi", line 33, in <module>
    sys.exit(load_entry_point('pygeoapi', 'console_scripts', 'pygeoapi')())
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1134, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1059, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1665, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1665, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1401, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 767, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/pygeoapi/pygeoapi/openapi.py", line 1302, in generate
    content = generate_openapi_document(config_file, format_)
  File "/pygeoapi/pygeoapi/openapi.py", line 1277, in generate_openapi_document
    content = yaml.safe_dump(get_oas(s), default_flow_style=False)
  File "/pygeoapi/pygeoapi/openapi.py", line 1234, in get_oas
    return get_oas_30(cfg)
  File "/pygeoapi/pygeoapi/openapi.py", line 495, in get_oas_30
    p = load_plugin('provider', get_provider_by_type(
  File "/pygeoapi/pygeoapi/plugin.py", line 111, in load_plugin
    plugin = class_(plugin_def)
  File "/pygeoapi/pygeoapi/provider/postgresql.py", line 101, in __init__
    self._engine, self.table_model = self._get_engine_and_table_model()
  File "/pygeoapi/pygeoapi/provider/postgresql.py", line 254, in _get_engine_and_table_model
    engine = create_engine(
  File "<string>", line 2, in create_engine
  ValueError: invalid literal for int() with base 10: 'us-central1:reference-features:5432'

Environment

• OS: Google Cloud Run, Google Cloud SQL
• Python version: 3.10
• pygeoapi version: latest

Additional context
I believe this worked prior to the SQL Alchemy implementation because the pygeoapi deployment of river runner runs in CloudSQL.