postgres: permission denied for schema public when using custom user

[groundhog2k]

Fixes #1201

[groundhog2k]

@giannoug I created a PR. Is it what you need?

[groundhog2k]

If it's ok for you then I would like to merge it.

[giannoug]

If it's ok for you then I would like to merge it.

LGTM, hope it works 🤞

[groundhog2k]

Anyway you are free to always add .sh or .sql scripts to the section customScripts:or use a configMap in extraScripts: to modify permissions, create users, databases etc. during postgres startup. So this PR is theoretically not necessary. :-)

[giannoug]

That was my first take on this, but I believe these scripts run as the "new" user so they don't have the permissions to change the ownership...

[groundhog2k]

This values.yaml does exactly what you want:

## Database configuration
settings:
  ## The default postgres superuser (default when nothing is set: "postgres")
  superuser:

  ## The superuser password
  superuserPassword: "admin"

  # publicSchemaAccess: true

## Optional user database which is created during first startup with user and password
userDatabase:
  ## Name of the user database
  name: "mydb"

  ## Database user with full access rights
  user: "mydbuser"

  ## Password of the database user
  password: "mydbpasswd"

## Storage parameters
storage:
  ## Alternative set requestedSize to define a size for a dynmaically created PVC
  requestedSize: 128Mi

## Optional custom scripts that can be defined inline and will be mounted as files in /docker-entrypoint-initdb.d
customScripts:
  02-grant-public.sh: |
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
      GRANT ALL ON SCHEMA public TO mydbuser;
    EOSQL

[groundhog2k]

Final question: Do we need the PR or will the example of values.yaml from previous comment solve your problem (and maybe other requirements)? I would prefer to not implement special cases and keep that to the consumer/user of the chart to customize things to their needs via additional scripts during deployment.

[giannoug]

I'll check later today and report back. I was using .sql scripts and couldn't figure it out. Maybe with a .sh script with the postgres user will work. I totally agree with you, special cases should be avoided.

[giannoug]

I followed your recommendation and I see that the script is executed:

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/01-init-userdb.sh
CREATE ROLE
GRANT

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/02-grant-public.sh
GRANT

Still though my application is unable to create tables in the public schema. This is not an issue with the chart though so I'm closing the original bug. Thanks for your time and effort!

[groundhog2k]

You're welcome. I also checked that the permissions were created with psql.