Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Automatically create per-user schemas

Description

It is important to acknowledge that user management in the Percona Operator for PostgreSQL does not really deliver its end of the bargain. Users are created, but you can't start using them right away, as they don't have permissions to use public schema. This breaks a lot of declarative flows.

Now users have to come up with workarounds, like creating an init script with a config map:

 

CrunhcyData operator resolves this with this:

We should follow a similiar approach.

Environment

None

Activity

Julio Pasinatto February 3, 2025 at 9:37 PM

Verified. Using the same example as below with:

results in:


without specifying autoCreateUserSchema or with:

results in:

Slava Sarzhan December 20, 2024 at 1:06 PM

It was done. The user can control it via new option in CR:

by default, it is true.

Fernando Laudares Carmagos November 3, 2024 at 4:02 PM

When creating a user and database at the same time, we could be creating the user first and then the database, making it be owned by the user:

This would allow the user full write access to it.

Fernando Laudares Carmagos November 3, 2024 at 3:55 PM
Edited

Having users and databases automatically created has limited utility if the new user doesn’t have automatic write access to the whole database.
A very simple example:

The user can connect:

But it cannot write to the public schema:

As it stands now, the user is able to create a new schema and write to it:

Sergey Pronin October 12, 2024 at 7:24 PM

We got similar feedback from :

  1. When the pg operator creates the default environment, please extend ownership of database "cluster1" to the already created  role "cluster1". That way it can at least create and populate tables. As it stands now, the ROLE cannot do anything because as of pg 15 the permission to do anything in schema public is revoked.

Done

Details

Assignee

Reporter

Labels

Needs QA

Yes

Needs Doc

Yes

Story Points

Sprint

Fix versions

Priority

Smart Checklist

Created September 4, 2024 at 8:43 AM
Updated March 17, 2025 at 5:49 PM
Resolved March 17, 2025 at 10:59 AM