PostgreSQL

Overview

By using PostgreSQL, you can benefit from features in PostgreSQL infrastructure such as backup and restore.

For Artifactory to run with PostgreSQL you must create a dedicated PostgreSQL database instance and then configure Artifactory to use it as described in the following sections.

Before You Continue

Before proceeding with the steps below, please make sure you have read and followed the steps described in Configuring the Database.

Enabling PostgreSQL connectivity from remote servers

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

Add the following line to <postgres_mount>/data/pg_hba.conf.
```
host [artifactory_db_name] [artifactory_user] [cidr]    md5
```
Example
```
 host  artifactory       artifactory     123.456.78.90/32 md5
```
[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='*'
You can also use a specific IP address for the PostgreSQL server to listen.
Restart PostgreSQL after adding the changes above.

Page Contents

Creating the Artifactory PostgreSQL Database

Supported PostgreSQL Versions

Artifactory supports the following PostgreSQL versions: 9.5 and 9.6 (note that these versions will be EOL in 2021), 10.x, 11.x, 12.x, and 13.x.

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

Creating an Artifactory User and Database

CREATE USER artifactory WITH PASSWORD 'password';
CREATE DATABASE artifactory WITH OWNER=artifactory ENCODING='UTF8';
GRANT ALL PRIVILEGES ON DATABASE artifactory TO artifactory;

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

Artifactory Privileges

We recommend providing Artifactory with full privileges on the database.

Configuring Artifactory to use PostgreSQL

Download the JDBC driver corresponding to your PostgreSQL version from the PostgreSQL JDBC Driver Download site and copy the downloaded jar file into $JFROG_HOME/artifactory/var/bootstrap/artifactory/tomcat/lib directory
Permissions
```
Make sure your driver has read permissions for all users. 
```
chmod +r <your driver>

Adjust the database connection details in the system.yaml configuration file.

For example

shared:
  database:
    type: postgresql
    driver: org.postgresql.Driver
    url: jdbc:postgresql://<your db url, for example: localhost:5432>/artifactory
    username: artifactory
    password: password

Storing BLOBs inside PostgreSQL is not recommended

The above recommended configuration keeps all artifact information in PostgreSQL while storing the artifact binary data on the file system (under $JFROG_HOME/artifactory/var/data/artifactory/filestore).

While it is possible, to store BLOBs inside PostgreSQL we do not recommended it. This is important because the PostgreSQL driver doesn't support streaming BLOBs with unknown length to the database. Therefore, Artifactory will temporarily save deployed files to the filesystem and only then save the BLOB to the database.

Enabling TLS Encryption

To enable Transport Layer Security (TLS) encryption for PostgreSQL, set the sslmode property to verify-full in the JDBC connector URL.

For example, in the $JFROG_HOME/artifactory/var/etc/system.yaml file:

shared:
  database:
    ...
    url:jdbc:postgresql://mypostgress.mydomain.com:5432/artifactory?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-  
    full&sslrootcert=/tmp/server.crt
...

If you are using old certificates or have an AWS RDS instance that was created before July 2020, you will not have Subject Alternative Name (SAN) enabled. To resolve this issue, you will need to generate a new certificate with SAN.

Content

Space Tools

PostgreSQL

Overview

Creating the Artifactory PostgreSQL Database

Configuring Artifactory to use PostgreSQL

Enabling TLS Encryption