Posgres 13 `create_items` result in `UndefinedTableError` about pgstac

[thomafred]

A quick update: I have attempted to recreate the issue using the following docker-compose.yml

services:
  dev:
    container_name: pgstac-dev
    image: pgstac-dev
    build:
      context: .
      dockerfile: Dockerfile.dev
    platform: linux/amd64
    depends_on:
      - database
    volumes:
      - ./:/opt/src
    environment:
      - PGUSER=username
      - PGPASSWORD=password
      - PGHOST=database
      - PGDATABASE=postgis
  database:
    container_name: pgstac-db
    image: pgstac-db
    build:
      context: .
      dockerfile: Dockerfile
    platform: linux/amd64
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=postgis
      - PGUSER=username
      - PGPASSWORD=password
      - PGHOST=0.0.0.0
      - PGDATABASE=postgis
    ports:
      - "5439:5432"
    volumes:
      - pgstac-pgdata:/var/lib/postgresql/data
      - ./:/opt/src

  app-pgstac:
    container_name: stac-fastapi-pgstac
    image: MYTENANT.azurecr.io/app-stac-fastapi:latest
    platform: linux/amd64
    environment:
      - APP_HOST=0.0.0.0
      - APP_PORT=8081
      - RELOAD=false
      - ENVIRONMENT=local
      - POSTGRES_USER=username
      - POSTGRES_PASS=password
      - POSTGRES_DBNAME=postgis
      - POSTGRES_HOST_READER=database
      - POSTGRES_HOST_WRITER=database
      - POSTGRES_PORT=5432
      - WEB_CONCURRENCY=10
      - VSI_CACHE=TRUE
      - GDAL_HTTP_MERGE_CONSECUTIVE_RANGES=YES
      - GDAL_DISABLE_READDIR_ON_OPEN=EMPTY_DIR
      - DB_MIN_CONN_SIZE=1
      - DB_MAX_CONN_SIZE=1
    ports:
      - "8081:8081"
    depends_on:
      - database
volumes:
  pgstac-pgdata:

The STAC FastAPI image is built using the following dockerfile

FROM python:3.8-slim as base

RUN apt-get update
RUN apt-get install -y \
    build-essential \
    git

RUN pip install \
    uvicorn \
    stac-fastapi.api \
    stac-fastapi.types \
    stac-fastapi.extensions \
    stac-fastapi.pgstac

ARG APP_PORT=8081

ENV APP_HOST 0.0.0.0
ENV APP_PORT $APP_PORT
ENV RELOAD "false"
ENV ENVIRONMENT local
ENV POSTGRES_USER username
ENV POSTGRES_PASSWORD password
ENV POSTGRES_DBNAME stac
ENV POSTGRES_HOST_READER database
ENV POSTGRES_HOST_WRITER database
ENV POSTGRES_PORT 5432
ENV WEB_CONCURRENCY 10
ENV VSI_CACHE "TRUE"
ENV GDAL_HTTP_MERGE_CONSECUTIVE_RANGES "YES"
ENV GDAL_DISABLE_READDIR_ON_OPEN "EMPTY DIR"
ENV DB_MIN_CONN_SIZE 1
ENV DB_MAX_CONN_SIZE 1

EXPOSE $APP_PORT

CMD ["python", "-m", "stac_fastapi.pgstac.app"]

In fact, this did not recreate the issue, instead everything worked as it should. It therefore seems there may be an issue with how our database is configured. Our initial suspect is Partman.

Side note: The initial item JSON was missing geometry. This is invalid. I have updated the issue with a correction.

[thomafred]

@bitner - I see Partman has been removed as a requirement for pgstac. Is it possible that some sort of conflict is happening?

[thomafred]

Attempted to drop my database and recreate it. Used pypgstac migrate to initialize the new database and set the permissions for my service account. This did unfortunately not resolve the problem.

Still seems like a configuration issue, but since the Partman extension is not installed, I can only assume this is not the culprit.

[bitner]

You are correct partman is no longer being used. There are triggers built in to pgstac that manage the creation of partitions now.

@thomafred Can you run select * from pgstac.migrations to show what version you are using (and what versions you have migrated from)>

[thomafred]

@bitner - I dropped and recreated my database, so there is only a single migration:

version	datetime
0.3.6	2021-12-06 10:14:28.140742

[thomafred]

Giving our STAC API admin privileges solved to problem.

What privileges are necessary in order to do all of the API operations?

[bitner]

@thomafred PGStac does not do anything to control permissions on the database. That is up to the database administrator and depends on the role that you are using to connect to the database.

If a connection is made from the role that was used to create pgstac, they should have all permissions necessary. Otherwise those permissions will need to be granted to the user that is connecting.

To grant all read and write permissions to a user, you should be able to run:
GRANT USAGE ON SCHEMA pgstac to ;
GRANT ALL ON ALL TABLES IN schema pgstac TO ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgstac TO ;

You can be as fine grained as you want using the PostgreSQL role system (https://www.postgresql.org/docs/current/sql-grant.html) and if you use multiple database roles, could even use Row Level Security with pgstac.

[thomafred]

awesome, thank you!