Postgres: permission denied for schema public

[JonasHiltl]

I'm using the solidblocks-rds-postgresql docker image in a docker-compose setup with two databases. The setup works well and the databases and users both get created. But when I connect to the database: keycloak with user: keycloak and password: password and try to create a table, e.g.

CREATE TABLE test (

);

I get the error

ERROR:  permission denied for schema public

Here is my docker compose to reproduce:

  rds:
    image: ghcr.io/pellepelster/solidblocks-rds-postgresql:15-v0.2.5
    container_name: rds
    volumes:
      - ./data:/storage/data
      - ./backup:/storage/backup
    ports:
      - 5432:5432
    environment:
      DB_INSTANCE_NAME: ocl
      DB_BACKUP_LOCAL: 1
      DB_ADMIN_PASSWORD: password
      DB_DATABASE_db1: keycloak
      DB_USERNAME_db1: keycloak
      DB_PASSWORD_db1: password
      DB_DATABASE_db2: app
      DB_USERNAME_db2: app_user
      DB_PASSWORD_db2: password

I saw in the repo that this statements is executed to give permissions in the public schema:

psql_execute "${database}" "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"${username}\""

but found out that the statement only gives permissions for already existing tables. So the user doesn't have permissions to create new tables in the public schema. This stackoverflow answer explains how to give permissions for future tables.

I assume this behaviour isn't expected and the above statement should be adjusted to also grant default privileges for future tables.

[pellepelster]

Hi,

that's an interesting case, some debugging revealed that the majority of the integration tests used PostgreSQL version 14. After adding some tests for version 15 your problem immediately was reproducible in the test suite. I added the missing permissions to the schema which fixed the issue (see https://github.com/pellepelster/solidblocks/commit/c3ac63e57a096ad54431f0b4159a314c481bc253).

I am still investigating the consequences of adjusting the default privileges for the schema and try to figure out how to ensure a good test coverage for all versions without multiplying the test times.

In the meantime you should be able to use ghcr.io/pellepelster/solidblocks-rds-postgresql:15-snapshot-rc to test the fix for your setup.

[JonasHiltl]

Thanks for the fast response, I tested the image ghcr.io/pellepelster/solidblocks-rds-postgresql:15-snapshot-rc and can confirm that it works now.

[pellepelster]

fix is released as v0.2.6