Missing permissions on second related application

[deusebio]

When two client applications connect to postgresql via the relations, sometimes the second application does not have permissions to access the database.

Steps to reproduce

The issue has happened sometimes when deploying the data warehouse solution, where there are two applications related to postgresql:

1. One application is kyuubi (in the example relation-17) which is in the same model as postgresql (called metastore)
2. One application is spark-streaming (in the example relation-40) which is NOT in the same model

Inspection of the tables that need to be accessed by both shows that only one relation has access, e.g.

oot@metastore-0:/# psql --username="operator" -h 127.0.0.1 --dbname hivemetastore
Password for user operator: 
psql (14.15 (Ubuntu 14.15-0ubuntu0.22.04.1))
Type "help" for help.

hivemetastore=# \dt
 ...
 public | DATABASE_PARAMS           | table | relation_id_17
 public | DBS                       | table | relation_id_17
 ...
hivemetastore=# \l
 hivemetastore | operator | UTF8     | C       | C.UTF-8 | operator=CTc/operator      +
               |          |          |         |         | relation_id_17=CTc/operator+
               |          |          |         |         | admin=CTc/operator         +
               |          |          |         |         | backup=CTc/operator        +
               |          |          |         |         | replication=CTc/operator   +
               |          |          |         |         | rewind=CTc/operator        +
               |          |          |         |         | monitoring=CTc/operator    +
               |          |          |         |         | relation_id_40=CTc/operator
 postgres      | operator | UTF8     | C       | C.UTF-8 | operator=CTc/operator      +
               |          |          |         |         | backup=CTc/operator        +
               |          |          |         |         | replication=CTc/operator   +
               |          |          |         |         | rewind=CTc/operator        +
               |          |          |         |         | monitoring=CTc/operator    +
               |          |          |         |         | admin=c/operator
 template0     | operator | UTF8     | C       | C.UTF-8 | =c/operator                +
               |          |          |         |         | operator=CTc/operator
 template1     | operator | UTF8     | C       | C.UTF-8 | =c/operator                +
               |          |          |         |         | operator=CTc/operator

hivemetastore=# SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='DBS';
 relation_id_17 | INSERT
 relation_id_17 | SELECT
 relation_id_17 | UPDATE
 relation_id_17 | DELETE
 relation_id_17 | TRUNCATE
 relation_id_17 | REFERENCES
 relation_id_17 | TRIGGER

The logs in the metastore charm shows some exceptions:

unit-metastore-0: 15:39:30 INFO unit.metastore/0.juju-log database:40: Updating Patroni config file
unit-metastore-0: 15:39:43 ERROR unit.metastore/0.juju-log database:40: Uncaught exception while in charm code:
Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-metastore-0/charm/src/charm.py", line 2252, in <module>
    main(PostgresqlOperatorCharm, use_juju_for_storage=True)
  File "/var/lib/juju/agents/unit-metastore-0/charm/venv/lib/python3.10/site-packages/ops/__init__.py", line 343, in __call__
    return _main.main(charm_class=charm_class, use_juju_for_storage=use_juju_for_storage)
  File "/var/lib/juju/agents/unit-metastore-0/charm/venv/lib/python3.10/site-packages/ops/_main.py", line 543, in main
    manager.run()
  File "/var/lib/juju/agents/unit-metastore-0/charm/venv/lib/python3.10/site-packages/ops/_main.py", line 529, in run
    self._emit()
  File "/var/lib/juju/agents/unit-metastore-0/charm/venv/lib/python3.10/site-packages/ops/_main.py", line 515, in _emit
    self.framework.reemit()
  File "/var/lib/juju/agents/unit-metastore-0/charm/venv/lib/python3.10/site-packages/ops/framework.py", line 867, in reemit
    self._reemit()
  File "/var/lib/juju/agents/unit-metastore-0/charm/venv/lib/python3.10/site-packages/ops/framework.py", line 947, in _reemit
    custom_handler(event)
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
    return callable(*args, **kwargs)  # type: ignore
  File "/var/lib/juju/agents/unit-metastore-0/charm/src/charm.py", line 654, in _on_config_changed
    self.update_config()
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
    return callable(*args, **kwargs)  # type: ignore
  File "/var/lib/juju/agents/unit-metastore-0/charm/src/charm.py", line 1905, in update_config
    self._handle_postgresql_restart_need()
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
    return callable(*args, **kwargs)  # type: ignore
  File "/var/lib/juju/agents/unit-metastore-0/charm/src/charm.py", line 1967, in _handle_postgresql_restart_need
    self.postgresql_client_relation.update_tls_flag("True" if self.is_tls_enabled else "False")
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
    return callable(*args, **kwargs)  # type: ignore
  File "/var/lib/juju/agents/unit-metastore-0/charm/src/relations/postgresql_provider.py", line 224, in update_tls_flag
    self.database_provides.set_tls(relation.id, tls)
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/data_platform_libs/v0/data_interfaces.py", line 1683, in set_tls
    self.update_relation_data(relation_id, {"tls": tls})
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/data_platform_libs/v0/data_interfaces.py", line 496, in wrapper
    return f(self, *args, **kwargs)
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/data_platform_libs/v0/data_interfaces.py", line 1407, in update_relation_data
    return self._update_relation_data(relation, data)
  File "/var/lib/juju/agents/unit-metastore-0/charm/lib/charms/data_platform_libs/v0/data_interfaces.py", line 1632, in _update_relation_data
    raise PrematureDataAccessError(
charms.data_platform_libs.v0.data_interfaces.PrematureDataAccessError: Premature access to relation data, update is forbidden before the connection is initialized.
unit-metastore-0: 15:39:43 ERROR juju.worker.uniter.operation hook "database-relation-created" (via hook dispatching script: dispatch) failed: exit status 1

When the relation 40 (cross-model) is removed and recreated, the permission are correctly updated, e.g.

hivemetastore=# SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='VERSION';
 relation_id_17 | INSERT
 relation_id_17 | SELECT
 relation_id_17 | UPDATE
 relation_id_17 | DELETE
 relation_id_17 | TRUNCATE
 relation_id_17 | REFERENCES
 relation_id_17 | TRIGGER
 relation_id_42 | INSERT
 relation_id_42 | SELECT
 relation_id_42 | UPDATE
 relation_id_42 | DELETE
 relation_id_42 | TRUNCATE
 relation_id_42 | REFERENCES
 relation_id_42 | TRIGGER

Expected behaviour

The permissions should be updated already at the beginning, without the need of removing and re-adding the relations.

Versions

Operating system: 22.04

Juju CLI: 3.6.9 (because of some regression in Juju we can't upgrade to 3.6.12 yet)

Juju agent: 3.6.9

Charm revision: 495

microk8s: 1.32

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

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

This message was autogenerated

[dragomirp]

Should be fixed in #933.

[taurus-forever]

The new 14/candidate r717 has been promoted, please give it a try.

[deusebio]

Thank you folks! Much appreciated the prompt response! I'll give it a try and report back if any problem arise. We can probably just keep the issue open for a few days, and then close it if it does not come up again with the new 14/candidate. Being intermittent makes is a bit hard to reproduce deterministically, but I'll just report back here if I find any issue going forward.

Thanks!