feat: update runtimes and add pgstac customization options

[vincentsarago]

This PR closes #88 and add customizations option for PgSTAC bootstrap to:

• enable/disable the context extension
• enable/disable the mosaic table index

⚠️ Checklist if your PR is changing anything else than documentation

• Posted the link to a successful manually triggered deployment workflow (successful including the resources destruction)

Merge request description

[vincentsarago]

this is taken from eoapi repo

[emileten]

deployment workflow https://github.com/developmentseed/eoapi-cdk/actions/runs/8045501396

[emileten]

Questions for my understanding

enable/disable the context extension
enable/disable the mosaic table index

What were we doing before, was it disabled by default?

[vincentsarago]

@emileten we were adding the mosaic index by default but not the context extension, maybe we can switch back to false 👍

[emileten]

Maybe we can switch back to false

I'll defer to you on this decision, I don't know much about these options. Doesn't sound like it hurts to have the context on by default ?

[emileten]

A live example of the bootstrapper logs

The workflow failed again, there's something we need to fix in the bootstrapper changes https://github.com/developmentseed/eoapi-cdk/actions/runs/8045602231/job/21971192880#step:9:2478

The lambda logs https://us-east-1.console.aws.amazon.com/cloudwatch/home?region=us-east-1#logsV2:log-groups/log-group/$252Faws$252Flambda$252Fpgstac60fe14-pgstacdblambda91737152-ELDP6T9rb8jI/log-events/2024$252F02$252F26$252F$255B$2524LATEST$255Df9a0fff7fa2042bb84e34c9220cb7071

Unable to bootstrap database with exception=must be member of role "pgstac_ingest" 

Also httpx failed responding with the error

send(..) failed executing httpx.put(..): can only concatenate str (not "int") to str 

[emileten]

@vincentsarago just checking, were you expecting the last commit to fix the bug ? or is there more to do

[vincentsarago]

I was hopping yes 😅 but couldn't see where I had to click to run the deployment workflow

[emileten]

ugh sorry, just started one. Will show you later

[emileten]

@vincentsarago looks like we still have the problem

[ERROR] InsufficientPrivilege: must be member of role "pgstac_ingest"
Traceback (most recent call last):
  File "/var/task/handler.py", line 280, in handler
    raise e
  File "/var/task/handler.py", line 243, in handler
    Migrate(pgdb).run_migration(params["pgstac_version"])
  File "/var/task/pypgstac/migrate.py", line 149, in run_migration
    cur.execute(migration_sql)
  File "/var/task/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)

I can take a look tomorrow as well.

FYI to run a deployment:

• click on the Actions tab
• On the left click on the Deployment workflow
• On the right click on Run Workflow, choose your branch and click on Run Workflow

[vincentsarago]

@emileten let's fall back to pgstac 0.7.10 to validate the changes and then we can debug why it doesn't work for pgstac 0.8.4

[emileten]

@vincentsarago like this 0d675e2?

[vincentsarago]

@emileten just pgstac, we can keep titiler-pgstac and tipg to the latest version

[emileten]

Ok seems to work https://github.com/developmentseed/eoapi-cdk/actions/runs/8204255860/job/22438560336#step:10:798.

Now we need to

debug why it doesn't work for pgstac 0.8.4

😄

[emileten]

there seems to be a 0.8.3 change that sounds like it could have triggered this https://github.com/stac-utils/pgstac/blob/e0b7de4c9c8130c55de9fe6eb0a87324cd705135/CHANGELOG.md?plain=1#L37 we can try with the previous version

[vincentsarago]

👁️ stac-utils/pgstac#239

[vincentsarago]

@emileten if I understand well

looking at #100 (comment) it seems the migration step fails with

[ERROR] InsufficientPrivilege: must be member of role "pgstac_ingest"

in

eoapi-cdk/lib/database/bootstrapper_runtime/handler.py

Line 243 in 4425253

Migrate(pgdb).run_migration(params["pgstac_version"])

when we use

eoapi-cdk/lib/database/bootstrapper_runtime/handler.py

Lines 231 to 232 in 4425253

	user=connection_params["username"],
	password=connection_params["password"],

username/password (must be postgres by default)

After the migration we then grant the username/password (pgstac_user by default)

eoapi-cdk/lib/database/bootstrapper_runtime/handler.py

Lines 133 to 135 in 4425253

	"GRANT pgstac_read TO {username};"
	"GRANT pgstac_ingest TO {username};"
	"GRANT pgstac_admin TO {username};"

so to me it seems that we should GRANT pgstac_ingest TO postgres; but to me it seems weird because pgstac_ingest role doesn't exist before the migration starts...

cc @bitner

[bitner]

@emileten @vincentsarago
The pypgstac migrate should be run as superuser (or a role with sufficient root-like privileges in the case of systems like RDS that do not allow true superusers -- this is typically the postgres role). pypgstac migrate on a fresh database will create the pgstac_read, pgstac_ingest, and pgstac_admin roles.

Once pgstac has been installed, you can assign these roles to other database login roles. Ideally, you would use least-access necessary roles for different tasks. Best practice would be to have three roles to interact with pgstac.

• An admin role that is used to manage migrations. Whatever role that is used to run pypgstac migrate will automatically be granted the pgstac_admin role.
• A loader role that is used with the transactions extension or when using pypgstac load. GRANT pgstac_ingest to dataloader;
• A user role that is used with the API for search or other read operations GRANT pgstac_read to apiuser;. Do note that this role is not truly read only as it will still be able to (indirectly via functions that run as the pgstac_admin role) update the searches and search_wheres table.

[emileten]

https://github.com/developmentseed/eoapi-cdk/actions/runs/8266556264/job/22614791059