PostgreSQL for JFrog Catalog

Using PostgreSQL as the database for JFrog Catalog provides robust backup and restore capabilities and aligns with other JFrog products that use PostgreSQL. JFrog Catalog requires its own dedicated logical PostgreSQL database. This database can reside on the same PostgreSQL instance or VM as Xray, but Catalog must use a separate logical database from Xray.

Like other JFrog products such as 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 are using a cloud provider, leveraging a cloud-managed PostgreSQL database is recommended.

PostgreSQL Requirements

Catalog supports the following PostgreSQL versions:

• 16.x (from Catalog version 1.6.1)
• 15.x (from version 3.78.9, when Catalog was part of Xray)

Minimum system requirements for the Catalog database:

• CPU: 1 vCPU
• RAM: 2 GB
• Storage: 100 GB

If you are using a cloud provider, a managed PostgreSQL service is recommended. We also recommend implementing disk space monitoring, as storage requirements may increase over time.

📘

Database Topology

Catalog requires its own dedicated logical database. You can host the Catalog database on the same PostgreSQL instance, including the same VM or managed database service used by Xray, provided that Catalog and Xray use separate logical databases.

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;

After verifying that the script is correct, run it to create the database and proceed with configuration.

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.

1. Add the following line to <postgres_mount>/data/pg_hba.conf

   properties

   host [catalog_db_name] [catalog_user] [cidr]    md5

   Example

   properties

   host  catalog       catalog     123.456.78.90/32 md5

   📘

   Note

   [cidr] is the single host or network segment you want to give access to. 2. 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. 3. Restart PostgreSQL after adding the above changes.

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

1. Stop the Catalog service.

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

   YAML

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

3. Start the Catalog service.

Configure Catalog to Use PostgreSQL (Helm Deployments)

We recommend managing 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.

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

   YAML

   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

   Bash

   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

   YAML

   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"

2. Perform a Helm upgrade/install.