Public schema options helm chart

PostgreSQL Percona Operator for PostgreSQL
1

Description:

After deploying Percona Operator for PostgreSQL I deployed a cluster via cr.yaml and added users and databases that I wanted to host.

Another application, Gitea is also being deployed via helm charts, and it needs to use one of the databases but it tries to alter public schema, which is denied:

2024/09/03 16:35:38 cmd/migrate.go:40:runMigrate() [F] Failed to initialize ORM engine: migrate: sync: pq: permission denied for schema public
Gitea migrate might fail due to database connection...This init-container will try again in a few seconds

I read most of the docs and searched online for answers. I tried different things for users.options but could not figure out how to make this work.

I don’t want the user to become a superuser, I don’t want that unless there is no other way. Perhaps after doing that once, I can revert to a regular user?

I am not sure what the implications are though. What happens if the next application wants to alter public schema as well?

Any help is appreciated. Thanks in advance.

Steps to Reproduce:

  users:
    - name: gitea
      databases:
        - gitea
#      options:

Gitea Helm values:

    database:
      DB_TYPE: postgres
      HOST: cluster1-pgbouncer.postgres-operator.svc:5432
      SSL_MODE: require
      NAME: gitea
      USER: gitea
      PASSWD: blablabla

Version:

2.4.1

Logs:

I listed part of the log in the description.

Expected Result:

I expected this to happen :slight_smile:

Actual Result:

What I would like to accomplish is that one way or another, Gitea can use the database I provisioned. I don’t know if its possible to provide users.options to allow the public schema to be altered, or if there is any other way to work around this issue?

Additional Information:

I am by no means a database or postgres adept. My expertise is in Linux and Kubernetes. Please go easy on me :smiley: .

2

I somewhat managed to fix it, manually.

I used the cr.yaml to add a superuser, and was able to use psql to manage the permissions of the user gitea on database gitea.

Like this:

GRANT ALL PRIVILEGES ON DATABASE gitea TO gitea;
GRANT CREATE ON DATABASE gitea TO gitea;
\c gitea
GRANT ALL ON SCHEMA public TO gitea;

I tried to find out what the default permissions are after creating a user and database with cr.yaml but the lack of knowledge prevented me from figuring it out using queries.

Anyways, after this the gitea helm chart deployed and seems to work!

Can someone help me out translate the above psql commands to users.options in the cr.yaml, so that new users and databases are created with the permissions I typically need for applications?

Kind regards.

3

@Fragment2 thanks for sharing.

I wrote the blog post on how I would do that (an automated workaround that can be used for declarative approach): Running Gitea with PostgreSQL on Kubernetes - DEV Community

At the same time, I agree that it is a problem. We will solve it in [K8SPG-634] - Percona JIRA

1 Like
4

You’re a hero!

Thank you its really great to see my input has led to something constructive.

5

Quick question about the solution you offer in your blog post:

Does this work for multiple databases:

data:
  init.sql: |
    \c gitea
    GRANT CREATE ON SCHEMA public TO "gitea";
    \c app2
    GRANT CREATE ON SCHEMA public TO "app2";
    \c app3
    GRANT CREATE ON SCHEMA public TO "app3";
6

@Fragment2 I don’t see why it would not.

It is just a SQL that is executed line by line.