`pgstac_ingest` role does not allow access to the `POST /collections` API endpoint

[jtherrmann]

According to https://stac-utils.github.io/pgstac/pgstac/#pgstac-users:

The pgstac_ingest role has read/write priviliges on all tables and should be used for data ingest or if using the transactions extension with stac-fastapi-pgstac.

However, when running the STAC API with the Transactions extension enabled, connecting to the database as the pgstac_ingest role, an attempt to create a collection via the POST /collections endpoint fails with:

asyncpg.exceptions.InsufficientPrivilegeError: must be owner of table partitions

The owner of the partitions table is pgstac_admin, as expected. So it would appear that the pgstac_ingest role does not actually allow access to all of the Transactions endpoints?

[jtherrmann]

Related to https://github.com/stac-utils/pgstac/issues/146

[duckontheweb]

I haven't verified this, but I'm guess this is being raised by this line that alters the partitions table. That table is owned by pgstac_admin and according to the Postgres ALTER TABLE docs:

You must own the table to use ALTER TABLE.

We worked around this temporarily by granting the pgstac_admin role to the application role we use for transactions, but that obviously isn't ideal...