Database Configuration

Introduction

The ESP application doesn’t need any permissions outside of its own PostgreSQL database and it is recommended to create a single database for the application, as well as a dedicated role with matching name, and grant all permissions for that role to that database.

Currently, there isn’t a role separation between “read-only”, “read/write”, “read/write/DDL modification permissions” users at the application level. The same user is used with the l7-init (DDL migrations) as in normal application execution. These credentials are stored in the database config file /opt/l7esp/data/conf/database.json.

Prerequisites 64-bit Linux VM with the following minimum specs:

• Debian or RHEL based OS

• 8vCPUs

• 64GB of RAM

• 500GB of available storage

• Timezone configured for UTC/GMT

• PostgreSQL version 13.X+

Cloud

If you are hosting L7|ESP in the cloud, see the following guides:

• Amazon Aurora PostgreSQL

• Azure Database for PostgreSQL

Instructions

ESP DB creation

Most installations of PostgreSQL done via a package manager include a user called “postgres”. This user has full super admin privileges to the PostgreSQL instance installed on your system. Using the su command switch to the postgres user account then use the PostgreSQL interactive terminal (psql) to create the database, user role, and assign the permissions.

postgres=# CREATE DATABASE <esp_db_name>;
postgres=# CREATE USER <esp_db_user> WITH ENCRYPTED PASSWORD '<esp_db_password>';
postgres=# GRANT ALL PRIVILEGES ON DATABASE <esp_db_name> TO <esp_db_user>;

ESP DB configuration

The database configuration file at /opt/l7esp/data/conf/database.json should take the following format when using an external hosted PostgreSQL solution such as AWS RDS:

{
    "host": "<postgres_server_url_or_ip>",
    "port": "<postgres_server_port>",
    "user": "<esp_db_user>",
    "pass": "<esp_db_password>",
    "name": "<esp_db_name>",
    "schema": "<esp_db_schema>",
    "multitenant": false,
    "start_service": false,
    "l7-esp.concierge": {
        "pool_size": 3
},
    "l7-esp.notifications": {
        "pool_size": 3
    },
    "l7-esp.hub": {
        "pool_size": 3
    },
    "l7-esp.http": {
        "pool_size": 3
    }
}

Note

When using an external PGSQL server, it will either need the data migrated from the existing PGSQL server or the new PGSQL server will need to be initialized, to prepare it for use with ESP.

Preparing PostgreSQL for ESP

Before starting ESP with an external database, you need to modify the PostgreSQL configuration file to work with L7|ESP. You can find the location of the configuration file by running the following query in the PostgreSQL terminal: SHOW config_file;.

To optimize PostgreSQL for L7|ESP, refer to the System Tuning guide.