PostgreSQL for JFrog Catalog

Using PostgreSQL as the database for JFrog Catalog offers benefits such as robust backup and restore capabilities. It's also recommended to use an external database for JFrog products.

Furthermore, aligning with other JFrog products like Xray, Catalog, exclusively supports PostgreSQL.

Catalog supports the following versions of PostgreSQL:

16.x (from version 1.6.1)
15.x (from version 3.78.9 - as the Catalog was part of Xray)

If you're using a cloud provider, leveraging a cloud-managed PostgreSQL database is recommended.

Create the JFrog Catalog PostgreSQL Database

Use the commands below to create a Catalog user and database with appropriate permissions. Modify the relevant values to match your specific environment:

Creating a Catalog User and Database

CREATE USER catalog WITH PASSWORD 'password';
CREATE DATABASE catalogdb WITH OWNER=catalog ENCODING='UTF8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template=template0;;
GRANT ALL PRIVILEGES ON DATABASE catalogdb TO catalog;

Once you have verified that the script is correct, you need to run it to create the database and proceed with configuring the database.

Catalog Privileges

We recommend providing the Catalog with full privileges on the database.

Enable PostgreSQL Connectivity from Remote Servers

The following is an example of enabling PostgreSQL connectivity from remote servers. Consult your security team for your organization's best practices.

Add the following line to <postgres_mount>/data/pg_hba.conf
```
host [catalog_db_name] [catalog_user] [cidr]    md5
```
Example
```
host  catalog       catalog     123.456.78.90/32 md5
```
Note: [cidr] is the single host or network segment you want to give access to.
Add the following line to <postgres_mount>/data/postgresql.conf listen_addresses='*'
Note: You can also use a specific IP address for the PostgreSQL server to listen.
Restart PostgreSQL after adding the above changes.

Configure Catalog to Use PostgreSQL (Non-Helm Deployments: RPM, Deb, Linux Archive, Docker Compose)

Stop the Catalog service.

Edit the database connection details in the system.yaml configuration file as follows:

shared:
  database:
    type: postgresql
    driver: pgx
    url: "postgres://<DB URL>" #for example: localhost:5432>/catalogdb?sslmode=disable
    username: xray
    password: password

Start the Catalog service.

Configure Catalog to Use PostgreSQL (Helm Deployments)

It is highly recommended to manage the Catalog using Xray's values.yaml. The following example provides a snippet of the Xray values.yaml to configure an external database for Catalog.

Modify the database connection details in the Xray values.yaml configuration file as follows.

global:
  jfrogUrl: http://<artifactory-url>
  joinKeySecretName: my-joinkey-secret
  masterKeySecretName: my-masterkey-secret
xray:
  replicaCount: 1
rabbitmq:
  enabled: true
  replicaCount: 1
  auth:
    username: guest
    password: "Password@123"
postgresql:
  enabled: false
database:
  url: "postgres://xray-postgresql:5432/xraydb?sslmode=disable"
  user: xray
  password: xray
  actualUsername: xray
# This will install catalog chart as a dependency chart in xray.
catalog:
  enabled: true
  createCatalogDb:
    enabled: false
  database:
    type: "postgresql"
    driver: "pgx"  
    url: "postgres://catalog-postgresql:5432/catalogdb?sslmode=disable"
    user: catalog
    password: catalog

Alternatively, you can also pass database credentials as a Kubernetes secret. E.g., Create the Secret

kubectl create secret generic catalog-database-creds 
--from-literal=db-url='postgres://<DB URL> #for example:
localhost:5432/catalogdb?sslmode=disable' --from-literal=db-user='admin_user' 
--from-literal=db-password='password' -n xray

Update the Catalog section in Xray's values.yaml as below

global:
  jfrogUrl: http://<artifactory-url>
  joinKeySecretName: my-joinkey-secret
  masterKeySecretName: my-masterkey-secret
xray:
  replicaCount: 1
rabbitmq:
  enabled: true
  replicaCount: 1
  auth:
    username: guest
    password: "Password@123"
postgresql:
  enabled: false
database:
  url: "postgres://xray-postgresql:5432/xraydb?sslmode=disable"
  user: xray
  password: xray
  actualUsername: xray
# This will install catalog chart as a dependency chart in xray.
catalog:
  enabled: true
  createCatalogDb:
    enabled: false 
  database:
    type: "postgresql"
    driver: "pgx"
    secrets:
      user:
        name: "catalog-database-creds"
        key: "db-user"
      password:
        name: "catalog-database-creds"
        key: "db-password"
      url:
        name: "catalog-database-creds"
        key: "db-url"

Perform a Helm upgrade/install.