Error when installing pgstac > v0.8.1

[charalamm]

I have a fresh postgres and I am trying to install pgstac via pygstac.

I installed pypgstac[psycopg]==v0.8.4 and run pypgstac migrate --debug but I get

DEBUG:pypgstac.db:PG VERSION: 15.4.
INFO:pypgstac.migrate:Migrating PGStac on PostgreSQL Version 15.4
DEBUG:pypgstac.db:PGStac is not installed.
INFO:pypgstac.migrate:No pgstac version set, installing 0.8.4 from scratch.
DEBUG:pypgstac.migrate:Running migration file /home/.../.venv/lib/python3.11/site-packages/pypgstac/migrations/pgstac.0.8.4.sql.
DEBUG:smart_open.smart_open_lib:{'uri': '/home/.../.venv/lib/python3.11/site-packages/pypgstac/migrations/pgstac.0.8.4.sql', 'mode': 'r', 'buffering': -1, 'encoding': None, 'errors': None, 'newline': None, 'closefd': True, 'opener': None, 'compression': 'infer_from_extension', 'transport_params': None}
Traceback (most recent call last):
  File "/home/.../.venv/bin/pypgstac", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/home/.../.venv/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 125, in cli
    fire.Fire(PgstacCLI)
  File "/home/.../.venv/lib/python3.11/site-packages/fire/core.py", line 141, in Fire
    component_trace = _Fire(component, args, parsed_flag_args, context, name)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../.venv/lib/python3.11/site-packages/fire/core.py", line 466, in _Fire
    component, remaining_args = _CallAndUpdateTrace(
                                ^^^^^^^^^^^^^^^^^^^^
  File "/home/.../.venv/lib/python3.11/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
    component = fn(*varargs, **kwargs)
                ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../.venv/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 61, in migrate
    return migrator.run_migration(toversion=toversion)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../.venv/lib/python3.11/site-packages/pypgstac/migrate.py", line 149, in run_migration
    cur.execute(migration_sql)
  File "/home/.../.venv/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.InsufficientPrivilege: must be member of role "pgstac_ingest"

I started reducing the version and finally pgstac got installed with pypgstac==v0.8.1.

Then I installed again 0.8.4 and run pypgstac migrate --debug but I get the same error.
Finally I run in the db GRANT pgstac_ingest TO user; and then pypgstac migrate with 0.8.4 and it worked, however I am wondering if the manual command can cause any problems

[bitner]

@charalamm with that debugging information, I'm not able to tell what is going on here as the psycopg trace does not include the SQL that was resulting in the error.

The tests are definitely creating a fresh database and are able to run the base migration, so I'm not sure what could be different on your system. Could you try running the base migration manually on a fresh database without pgstac installed? (On a fresh install, this is all that pypgstac migrate is doing behind the scenes)

psql -f /home/.../.venv/lib/python3.11/site-packages/pypgstac/migrations/pgstac.0.8.4.sql

[bitner]

@charalamm I think that I have tracked this down as an issue that is with RDS that does not show up on regular Postgres relative to how RDS sets up the rds_superuser. I'm still trying to track this down specifically to figure out a fix.