Problems using seperate postgres reader/writer hosts with pgstac

[duckontheweb]

Ported from stac-utils/stac-fastapi#370.

Original Issue:

I'm running into problems when using pgstac with separate reader and writer hosts (in my case an Aurora cluster). Search endpoints return an error similar to the following:

 cannot execute SELECT FOR UPDATE in a read-only transaction

The issue appears to be that the SQL functions defined by pgstac for searching perform operations that require write access (select for update, create temp table, etc). But, the search endpoints connect to the DB through the readpool
https://github.com/stac-utils/stac-fastapi/blob/5dc8e0eb63d9cc07663968ed5ed9505e753bf298/stac_fastapi/pgstac/stac_fastapi/pgstac/core.py#L119-L120

This is not a problem when a single host is used for both read and write, as in the docker compose yaml. https://github.com/stac-utils/stac-fastapi/blob/5dc8e0eb63d9cc07663968ed5ed9505e753bf298/docker-compose.yml#L47-L48

When POSTGRES_HOST_READER points to a host that is truly read-only, however, this becomes a problem.

[bkanuka]

confirmed here that search is not a read-only operation:
stac-utils/pgstac#191 (comment)

The problem may be here: https://github.com/stac-utils/stac-fastapi-pgstac/blob/main/stac_fastapi/pgstac/core.py#L163

Because search is a write operation, it should be using ...get_connection(request, "w")

[jbeezley]

Now that stac-utils/pgstac#215 is merged. Does that fix this issue? Is there anything that needs to be done to make use of it?

[mindflayer]

As far as I understand, stac-fastapi-pgstac still knows nothing about the readonly mode, right?

[jbeezley]

I was able to get it to work in RDS. You have to use one of the pre-release 3.0 versions of stac-fastapi-pgstac, then update the pgstac setting readonly to true.

[mindflayer]

I was able to get it to work in RDS. You have to use one of the pre-release 3.0 versions of stac-fastapi-pgstac, then update the pgstac setting readonly to true.

What version of pgSTAC did you have to use, @jbeezley ? I don't quite understand what is the right combination for make it happen.
Thanks in advance!

[jbeezley]

Looks like 3.0 of stac-fastapi-pgstac was just released. It will use pgstac 0.8.x. I'm using the docker images posted at https://github.com/stac-utils/stac-fastapi-pgstac/pkgs/container/stac-fastapi-pgstac and setting environment variables for the reader/writer endpoints from RDS.