Failed to detect the target database version when loading collection and items

I am trying to create/migrate and utilize a PostreSQL in a Google Cloud Platform. I want to load my STAC items and collections into the database. However, I am confronted with error when loading the collections. Below is the traceback error. It says it failed to detect the target database version.

I am using the pypgstac v0.9.0. Besides the error, I think there are also other errors when migrating such as permission denied to set role "pgstac_ingest". I thought it was similar to this issue: https://github.com/stac-utils/pgstac/issues/239. But I think its different.

pypgstac-loader      | DEBUG:pypgstac.db:PG VERSION: 14.12.
pypgstac-loader      | INFO:pypgstac.migrate:Migrating PgSTAC on PostgreSQL Version 14.12
pypgstac-loader      | DEBUG:pypgstac.db:PgSTAC is not installed.
pypgstac-loader      | INFO:pypgstac.migrate:No pgstac version set, installing 0.9.1 from scratch.
pypgstac-loader      | DEBUG:pypgstac.migrate:Running migration file /usr/local/lib/python3.11/site-packages/pypgstac/migrations/pgstac.0.9.1.sql.
pypgstac-loader      | DEBUG:smart_open.smart_open_lib:{'uri': '/usr/local/lib/python3.11/site-packages/pypgstac/migrations/pgstac.0.9.1.sql', 'mode': 'r', 'buffering': -1, 'encoding': None, 'errors': None, 'newline': None, 'closefd': True, 'opener': None, 'compression': 'infer_from_extension', 'transport_params': None}

pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_admin" already exists, skipping
pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_read" already exists, skipping
pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_ingest" already exists, skipping
pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_ingest" is already a member of role "pgstac_read"
pypgstac-loader      | Traceback (most recent call last):
pypgstac-loader      |   File "/usr/local/bin/pypgstac", line 8, in <module>
pypgstac-loader      |     sys.exit(cli())
pypgstac-loader      |              ^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 125, in cli
pypgstac-loader      |     fire.Fire(PgstacCLI)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 141, in Fire
pypgstac-loader      |     component_trace = _Fire(component, args, parsed_flag_args, context, name)
pypgstac-loader      |                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 466, in _Fire
pypgstac-loader      |     component, remaining_args = _CallAndUpdateTrace(
pypgstac-loader      |                                 ^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
pypgstac-loader      |     component = fn(*varargs, **kwargs)
pypgstac-loader      |                 ^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 61, in migrate
pypgstac-loader      |     return migrator.run_migration(toversion=toversion)
pypgstac-loader      |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/migrate.py", line 149, in run_migration
pypgstac-loader      |     cur.execute(migration_sql)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
pypgstac-loader      |     raise ex.with_traceback(None)
pypgstac-loader      | psycopg.errors.InsufficientPrivilege: permission denied to set role "pgstac_ingest"
pypgstac-loader      | Traceback (most recent call last):
pypgstac-loader      |   File "/usr/local/bin/pypgstac", line 8, in <module>
pypgstac-loader      |     sys.exit(cli())
pypgstac-loader      |              ^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 125, in cli
pypgstac-loader      |     fire.Fire(PgstacCLI)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 141, in Fire
pypgstac-loader      |     component_trace = _Fire(component, args, parsed_flag_args, context, name)
pypgstac-loader      |                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 466, in _Fire
pypgstac-loader      |     component, remaining_args = _CallAndUpdateTrace(
pypgstac-loader      |                                 ^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
pypgstac-loader      |     component = fn(*varargs, **kwargs)
pypgstac-loader      |                 ^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 74, in load
pypgstac-loader      |     loader.load_collections(file, method)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/load.py", line 200, in load_collections
pypgstac-loader      |     self.check_version()
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/load.py", line 160, in check_version
pypgstac-loader      |     raise Exception("Failed to detect the target database version.")
pypgstac-loader      | Exception: Failed to detect the target database version.

Start with pypgstac/load.py check_version() and migrate.py run_migration(), then reproduce the migration and collection load against PostgreSQL 14.12 on Google Cloud using the reported pypgstac v0.9.0 setup. Trace the permission failure for pgstac_ingest and the version check; done means migration succeeds and collections load without the reported detection error.