postgis: ensure_spindex() unconditionally updates user permissions #1959
Description
Running uv run datacube -v -E postgis system init --no-init-users on an empty database crashes because ensure_spindex() unconditionally performs GRANT select ON .. TO odc_user:
$ uv run datacube -v -E postgis system init --no-init-users
warning: `VIRTUAL_ENV=/env` does not match the project environment path `.venv` and will be ignored; use `--active` to target the active environment instead
2025-06-10 08:57:01,057 752 datacube INFO Running datacube command: /code/.venv/bin/datacube -v -E postgis system init --no-init-users
Initialising database...
2025-06-10 08:57:01,346 752 datacube.drivers.postgis._core INFO Creating schema.
2025-06-10 08:57:01,346 752 datacube.drivers.postgis._core INFO Creating types.
2025-06-10 08:57:01,347 752 datacube.drivers.postgis._core INFO Creating tables.
2025-06-10 08:57:01,347 752 datacube.drivers.postgis._core INFO Dataset indexes: {Index('ix_odc_dataset_updated', Column('updated', DateTime(timezone=True), table=<dataset>, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.functions.now at 0x7d2cf2ac3380; now>, for_update=False), comment='when last updated')), Index('ix_odc_dataset_added', Column('added', DateTime(timezone=True), table=<dataset>, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.functions.now at 0x7d2cf2ac3140; now>, for_update=False), comment='when added')), Index('ix_odc_dataset_archived', Column('archived', DateTime(timezone=True), table=<dataset>, comment='when archived, null if active')), Index('ix_ds_mdt_active', Column('metadata_type_ref', SmallInteger(), ForeignKey('odc.metadata_type.id'), table=<dataset>, nullable=False, comment='The metadata type - how to interpret the metadata')), Index('ix_ds_prod_active', Column('product_ref', SmallInteger(), ForeignKey('odc.product.id'), table=<dataset>, nullable=False, comment='The product this dataset belongs to'))}
2025-06-10 08:57:01,372 752 datacube.drivers.postgis._core INFO Creating triggers.
2025-06-10 08:57:01,375 752 alembic.runtime.migration INFO Context impl PostgresqlImpl.
2025-06-10 08:57:01,375 752 alembic.runtime.migration INFO Will assume transactional DDL.
2025-06-10 08:57:01,377 752 alembic.runtime.migration INFO Running stamp_revision -> d27eed82e1f6
2025-06-10 08:57:01,378 752 datacube.index.postgis.index INFO Adding default metadata types.
Traceback (most recent call last):
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1963, in _exec_single_context
self.dialect.do_execute(
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 943, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedObject: role "odc_user" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/code/.venv/bin/datacube", line 10, in <module>
sys.exit(cli())
^^^^^
File "/code/.venv/lib/python3.12/site-packages/click/core.py", line 1442, in __call__
return self.main(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/click/core.py", line 1363, in main
rv = self.invoke(ctx)
^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/click/core.py", line 1830, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/click/core.py", line 1830, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/click/core.py", line 1226, in invoke
return ctx.invoke(self.callback, **ctx.params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/click/core.py", line 794, in invoke
return callback(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/datacube/ui/click.py", line 266, in new_func
return f(cfg_env, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/datacube/ui/click.py", line 298, in with_index
return f(index, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/datacube/scripts/system.py", line 63, in database_init
was_created = index.init_db(
^^^^^^^^^^^^^^
File "/code/datacube/index/postgis/index.py", line 164, in init_db
self.create_spatial_index(CRS("EPSG:4326"))
File "/code/datacube/index/postgis/index.py", line 191, in create_spatial_index
sp_idx = self._db.create_spatial_index(crs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/datacube/drivers/postgis/_connections.py", line 240, in create_spatial_index
ensure_spindex(self._engine, spidx)
File "/code/datacube/drivers/postgis/_spatial.py", line 173, in ensure_spindex
c.execute(text(command))
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1415, in execute
return meth(
^^^^^
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 523, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1982, in _exec_single_context
self._handle_dbapi_exception(
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2351, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1963, in _exec_single_context
self.dialect.do_execute(
File "/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 943, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) role "odc_user" does not exist
[SQL: grant select on odc.spatial_4326 to odc_user;]