PostgreSQL Prerequisites

SMARTPTT EXPRESS ONLY

The database management system (DBMS) is supported only in SmartPTT Express. For DBMS supported in SmartPTT Enterprise and SmartPTT PLUS, see MSSQL 

Considerations

• Consider version support policy.
• Consider known cybersecurity threats.
• Consider other aspects important for your organization.

Download

You can download PostgreSQL installation package from the following sources:

Specific versionLatest

$Version        = 18          # replace with another version of your choice
$DownloadFolder = "C:\Folder" # must be existing directory

winget download -e --id PostgreSQL.PostgreSQL.$Version -d $DownloadFolder 

PowerShell

$DownloadFolder = "C:\Folder" # must be existing directory

winget download -e --id PostgreSQL.PostgreSQL. -d $DownloadFolder 

Installation

• On the SmartPTT Express server host, install at least PostgreSQL command line tools.
• Install/upgrade other PostgreSQL components as required in your organization.

For reference, follow the dedicated guidelines .

Setup

• Change the PostgreSQL listen addresses 

  WindowsUbuntu

  listen_addresses = '*'
  

  listen_addresses = '*'
  

• User creation:

  • Create user with database creation privileges 

    CREATE Statement

    CREATE ROLE "database-creator" WITH
        LOGIN
        NOSUPERUSER
        CREATEDB
        NOCREATEROLE
        NOINHERIT
        NOREPLICATION
        NOBYPASSRLS
        CONNECTION LIMIT -1
        PASSWORD 'your-password';
    

  • Create two more users for the future schema assignment .

    CREATE Statement

    CREATE USER "user-account" WITH
        LOGIN
        NOSUPERUSER
        INHERIT
        NOCREATEDB
        NOCREATEROLE
        NOREPLICATION
        NOBYPASSRLS
        PASSWORD 'your-password';
    

• Create database in PostgreSQL 

  CREATE Statement

  CREATE DATABASE "smartptt-express"
      WITH
      OWNER = "smartptt-express-database-owner"
      ENCODING = 'UTF8'
      LC_COLLATE = 'English_United States.utf8'
      LC_CTYPE = 'English_United States.utf8'
      LOCALE_PROVIDER = 'libc'
      TABLESPACE = pg_default
      CONNECTION LIMIT = -1
      IS_TEMPLATE = False;
  
  --GRANT TEMPORARY, CONNECT ON DATABASE "smartptt-express" TO PUBLIC;
  
  --GRANT CONNECT ON DATABASE "smartptt-express" TO "smartptt-express-client";
  
  GRANT ALL ON DATABASE "smartptt-express" TO "smartptt-express-database-owner";
  

• Assign database permissions to created users 

  CREATE Statement

  CREATE SCHEMA IF NOT EXISTS "smartptt-express"
      AUTHORIZATION "database-owner";
  
  GRANT USAGE ON SCHEMA "smartptt-express" TO "high-privileged-user";
  
  ALTER DEFAULT PRIVILEGES FOR ROLE "database-owner"
    IN SCHEMA "smartptt-express"
  GRANT DELETE, INSERT, MAINTAIN, REFERENCES, TRIGGER, TRUNCATE, UPDATE
    ON TABLES TO "high-privileged-user";
  
  
  GRANT USAGE ON SCHEMA "smartptt-express" TO "low-privileged-user";
  
  ALTER DEFAULT PRIVILEGES FOR ROLE "database-owner"
    IN SCHEMA "smartptt-express"
  GRANT SELECT ON TABLES TO "low-privileged-user";
  

• Reload PostgreSQL configuration  and/or restart the PostgreSQL .

• PostgreSQL accessibility test.

  pg_isready

  pg_isready -h <IP_OR_HOSTNAME> -p <TCP_PORT>
  

Networking

Quality

GENERAL NETWORK QUALITY REQUIREMENTS

• Packet loss is 0 to 2.5%.
• Two-way delay is 0 to 90 msec.
• Jitter is 0 to 90 msec.

Firewall

On the DBMS host, unlock the TCP traffic for the PostgreSQL port. Default is 5432.

On the server host, unlock the TCP traffic for the following ports:

5432

TCP port responsible for the PostgreSQL server connection for authentication, data reading and writing, database interaction.

See Also

• PostgreSQL
• PostgreSQL Downloads
• PostgreSQL: Security Information
• PostgreSQL Tutorial at W3Schools