Charm relation hooks fail permanently if a user executes DROP SCHEMA public CASCADE

[nsakkos]

Steps to reproduce

1. Deploy the postgresql charm (e.g., channel 16/stable) and relate it to an application.
2. Connect to the resulting application database using the credentials created by the relation.
3. Execute DROP SCHEMA public CASCADE;
4. Remove the Juju relation with the application.
5. Re-integrate the application with the database (or relate a new application).
6. Observe that the relation hangs indefinitely, with the application waiting for a database connection string.

Expected behavior

If possible, the charm's internal mechanics should be resilient to standard database cleanup commands run by application users. A user dropping their application schemas (like the default public schema) should not delete the charm's internal role-management functions or break its ability to manage Juju relations.

Actual behavior

The internal function set_up_predefined_catalog_roles is deleted because it resides in the public schema. When the charm attempts to establish the new relation, it fails to execute the required role-setup function and crashes the hook. The application is left hanging indefinitely waiting for connection credentials.

Possible fixes

Would either of these be possible to implement?

• Move set_up_predefined_catalog_roles and other charm-critical functions out of the public schema into a dedicated, protected schema (e.g., charmed_internal) or
• Update relation hooks to verify the existence of set_up_predefined_catalog_roles and automatically copy it back from template1 if missing.

Versions

Operating system:

Juju CLI: 3.6.23-genericlinux-amd64

Juju agent: 3.6.12

Charm revision: 16/stable rev 1047

Log output

Juju debug log:

unit-postgresql-5: 02:51:24 ERROR unit.postgresql/5.juju-log database-peers:35: single_kernel_postgresql.utils.postgresql:Failed to create database: function set_up_predefined_catalog_roles() does not exist
LINE 1: SELECT set_up_predefined_catalog_roles();
...
ValueError: Cannot change extra-user-roles after relation has already been created

more extensive logs https://pastebin.canonical.com/p/rRbRBZnpNM/

Additional context

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-10341.

This message was autogenerated

[7annaba3l]

Hello,

Dropping the public schema is generally unsafe in a production environment. Public schema contains several objects that are created by plugins and extensions to work correctly. Dropping them will have all sort of unexpected behavior.

So on your side, I think a much wiser approach would be to export the public schema content and apply, incrementally, any change you might consider missing. If you are in need for help with your migration (assuming this is your goal) then please schedule a meeting to help you with that.

On our side, we will consider:

• preventing relation users from dropping public schema
• moving some of our internally defined functions elsewhere to be more resilient to these "standard database cleanup" routines.

Best regards

[marceloneppel]

Created both https://warthogs.atlassian.net/browse/DPE-10566 and https://warthogs.atlassian.net/browse/DPE-10567 for PG 18 to avoid backward incompatibilities.