Charmed PostgreSQL VM Tutorial > 5. Integrate with other applications

Integrate with other applications

Integrations, known as “relations” in Juju 2.9, are the easiest way to create a user for PostgreSQL in Charmed PostgreSQL VM.

Integrations automatically create a username, password, and database for the desired user/application. As mentioned earlier in 2. Deploy PostgreSQL , it is a better practice to connect to PostgreSQL via a specific user rather than the admin user.

In this section, you will integrate your Charmed PostgreSQL to another charmed application.

Summary

• Deploy data-integrator
• Integrate with PostgreSQL
• Access the related database
• Remove the user

Deploy data-integrator

In this tutorial, we will relate to the Data Integrator charm. This is a bare-bones charm that allows for central management of database users. It automatically provides credentials and endpoints that are needed to connect with a charmed database application.

To deploy data-integrator, run

juju deploy data-integrator --config database-name=test-database

Example output:

Located charm "data-integrator" in charm-hub, revision 11
Deploying "data-integrator" from charm-hub charm "data-integrator", revision 11 in channel stable on jammy

Running juju status will show you data-integrator in a blocked state. This state is expected due to not-yet established relation (integration) between applications.

Model     Controller  Cloud/Region         Version  SLA          Timestamp
tutorial  overlord    localhost/localhost  3.1.7   unsupported  10:22:13+01:00

App              Version  Status   Scale  Charm            Channel    Rev  Exposed  Message
data-integrator           blocked      1  data-integrator  stable      11  no       Please relate the data-integrator with the desired product
postgresql                active       2  postgresql       14/stable  281  no       

Unit                Workload  Agent  Machine  Public address  Ports  Message
data-integrator/0*  blocked   idle   3        10.89.49.179           Please relate the data-integrator with the desired product
postgresql/0*       active    idle   0        10.89.49.129           
postgresql/1        active    idle   1        10.89.49.197           

Machine  State    Address       Inst id        Series  AZ  Message
0        started  10.89.49.129  juju-a8a31d-0  jammy       Running
1        started  10.89.49.197  juju-a8a31d-1  jammy       Running
3        started  10.89.49.179  juju-a8a31d-3  jammy       Running

Integrate with PostgreSQL

Now that the data-integrator charm has been set up, we can relate it to PostgreSQL. This will automatically create a username, password, and database for data-integrator.

Relate the two applications with:

juju integrate data-integrator postgresql

Wait for juju status --watch 1s to show all applications/units as active:

Model     Controller  Cloud/Region         Version  SLA          Timestamp
tutorial  overlord    localhost/localhost  3.1.7    unsupported  10:22:31+01:00

App              Version  Status  Scale  Charm            Channel    Rev  Exposed  Message
data-integrator           active      1  data-integrator  stable      11  no       
postgresql                active      2  postgresql       14/stable  281  no       

Unit                Workload  Agent  Machine  Public address  Ports  Message
data-integrator/0*  active    idle   3        10.89.49.179           
postgresql/0*       active    idle   0        10.89.49.129           Primary
postgresql/1        active    idle   1        10.89.49.197           

Machine  State    Address       Inst id        Series  AZ  Message
0        started  10.89.49.129  juju-a8a31d-0  jammy       Running
1        started  10.89.49.197  juju-a8a31d-1  jammy       Running
3        started  10.89.49.179  juju-a8a31d-3  jammy       Running

To retrieve the username, password and database name, run the command

juju run data-integrator/leader get-credentials

Example output:

unit-data-integrator-0:
  UnitId: data-integrator/0
  id: "20"
  results:
    ok: "True"
    postgresql:
      database: test-database
      endpoints: 10.89.49.129:5432
      password: 136bvw0s7FjJ6mxZ
      read-only-endpoints: 10.89.49.197:5432
      username: relation-3
      version: "14.7"
  status: completed
  timing:
    completed: 2023-03-20 09:22:50 +0000 UTC
    enqueued: 2023-03-20 09:22:46 +0000 UTC
    started: 2023-03-20 09:22:50 +0000 UTC

Note that your hostnames, usernames, and passwords will likely be different.

Access the related database

Use endpoints, username, password from above to connect newly created database test-database on the PostgreSQL server:

> psql --host=10.89.49.129 --username=relation-3 --password test-database
...
test-database=> \l
...
 test-database | operator | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/operator             +
               |          |          |         |         | operator=CTc/operator    +
               |          |          |         |         | "relation-3"=CTc/operator
...

The newly created database test-database is also available on all other PostgreSQL cluster members:

> psql --host=10.89.49.197 --username=relation-3 --password --list
...
 test-database | operator | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/operator             +
               |          |          |         |         | operator=CTc/operator    +
               |          |          |         |         | "relation-3"=CTc/operator
...

When you relate two applications, Charmed PostgreSQL automatically sets up a new user and database for you. Note the database name we specified when we first deployed the data-integrator charm: --config database-name=test-database.

Remove the user

Removing the integration automatically removes the user that was created when the integration was created. Enter the following to remove the integration:

juju remove-relation postgresql data-integrator

Now try again to connect to the same PostgreSQL you just used in the previous section:

> psql --host=10.89.49.129 --username=relation-3 --password --list

This will output an error message like the one shown below:

psql: error: connection to server at "10.89.49.129", port 5432 failed: FATAL:  password authentication failed for user "relation-3"

This is expected, since this user no longer exists after removing the integration.

Data remains on the server at this stage.

To create a user again, integrate the applications again:

juju integrate data-integrator postgresql

Re-integrating generates a new user and password:

juju run data-integrator/leader get-credentials

You can then connect to the database with these new credentials. From here you will see all of your data is still present in the database.

Next step: 6. Enable TLS