Databases AWS

IvorySQL 5.6 on AWS User Guide

| Product: IvorySQL 5.6 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying IvorySQL 5.6 on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other PostgreSQL builds and extensions in the listing are described on the listing's guide hub.

IvorySQL is an Apache-2.0 licensed, Oracle-compatible distribution of PostgreSQL. IvorySQL 5.6 is built on PostgreSQL 18.6, the PostgreSQL release of 2026-08-13 that fixes the security issues found in 18.4 and earlier. One database cluster answers on two ports over the same data:

  • 5432 speaks native PostgreSQL, exactly like stock PostgreSQL 18.
  • 1521 speaks Oracle SQL: dual, sysdate, nvl(), the number, varchar2 and date types, and PL/iSQL packages with package bodies.

Oracle application code can keep its SQL on 1521 while your PostgreSQL tooling (backups, monitoring, reporting) uses 5432 against the same tables.

The image compiles IvorySQL 5.6 from the pinned IvorySQL_5.6 upstream release (IvorySQL publishes no Ubuntu packages) into /usr/local/ivorysql/ivorysql-5, and removes the compiler and every build-only package afterwards. Unattended security upgrades stay on for the operating system.

What is on the image:

  • IvorySQL 5.6 (PostgreSQL 18.6) as ivorysql.service, with the Oracle-compatibility extensions ivorysql_ora and plisql
  • No database cluster. The cluster is created by initdb on your instance's first boot, so it holds no data, role or password that existed anywhere before
  • One login role, ivorysql (the cluster superuser), whose password is generated on your instance's first boot
  • TLS with a certificate generated on your instance's first boot (TLS 1.2 minimum)

Default user: ubuntu · Architecture: x86_64 · Root volume: 8 GiB gp3

Security posture

  • No default password. First boot creates the cluster, generates a unique 32-character password, sets it on every role that can log in and writes it to /root/ivorysql-credentials.txt, readable by root only. On the instance itself the ivorysql system account connects over the local socket with peer authentication and needs no password.
  • Nothing is reachable before the password exists. ivorysql.service will not start until first boot has finished, and during first boot the server runs with no TCP listener at all.
  • Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate in /etc/ssl/ivorysql/. No key ships in the image, so no two instances share one.
  • Localhost only, on both ports. IvorySQL listens on 127.0.0.1:5432 and 127.0.0.1:1521 only, and the listing's security group opens SSH (22) only. Reach the database through an SSH tunnel, or open it to your VPC as described in Step 7.
  • Remote connections require TLS. pg_hba.conf accepts non-loopback clients only over hostssl with scram-sha-256; there is no plain-text remote rule and no trust rule anywhere.
  • No metadata access for the database. A database superuser can make the server open outbound connections (for example COPY ... FROM PROGRAM), so both IvorySQL units are blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works. If you add WAL archiving or backups to S3 from inside the database, give them explicit credentials; they cannot use the instance role.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
  • Fails closed. If first boot cannot finish, the database stays stopped rather than half configured.
  • Per-instance identity. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large (2 vCPU, 8 GiB). The shipped settings (shared_buffers = 512MB) suit it; raise them for larger instances.

Connecting to your instance

Version Operating system SSH user
IvorySQL 5.6 on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose IvorySQL 5.6 on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 5432 or 1521 to the internet.
  6. Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name ivorysql \
  --description "IvorySQL" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=ivorysql}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. It runs initdb, creates this instance's TLS key and certificate, sets the password and writes the credentials file. Show the connection details (the password line is left out here; read it with sudo cat /root/ivorysql-credentials.txt and keep it in your secrets manager):

sudo grep -v '^ivorysql.password=' /root/ivorysql-credentials.txt

# IvorySQL 5.6 (Oracle-compatible PostgreSQL) — cloudimg AWS Marketplace image.
# This password was generated on THIS VM's first boot and exists nowhere else.
# It is shown in plain text only in this file. Rotate it (never on a command
# line) with:  sudo -iu ivorysql psql   then   \password ivorysql
# and update this file to match.
#
# Server: PostgreSQL 18.6 (IvorySQL 5.6) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit

ivorysql.role=ivorysql
ivorysql.database=ivorysql
ivorysql.host=127.0.0.1
ivorysql.port=5432
ivorysql.oracle_port=1521
ivorysql.sslmode=require
ivorysql.private_ip=<private-ip>

# NETWORK ACCESS
#   Both listeners are bound to LOOPBACK ONLY, and ports 5432 and 1521
#   are CLOSED in the default security group (SSH 22 only). Connect through an
#   SSH tunnel from your workstation:
#     ssh -L 5432:127.0.0.1:5432 -L 1521:127.0.0.1:1521 ubuntu@INSTANCE_PUBLIC_IP
...

Step 3: Verify the deployment in one command

The image ships a read-only self-check that proves the security posture and that Oracle compatibility works:

sudo /usr/local/sbin/ivorysql-selfcheck.sh

OK   credentials file mode 600 root:root
OK   per-VM password present (32 chars)
OK   per-VM credential authenticated over TLS on :5432
OK   server version pin: PostgreSQL 18.6 (IvorySQL 5.6)
OK   port 5432 is in native PostgreSQL mode (compatible_mode=pg)
OK   port 1521 is in Oracle-compatibility mode (compatible_mode=oracle)
OK   per-VM credential authenticated over TLS on :1521
OK   Oracle compatibility live: SELECT sysdate FROM dual -> 2026
OK   Oracle compatibility live: nvl() -> ivy-ok
OK   the native port 5432 correctly rejects Oracle-only syntax
OK   Oracle-compat sys schema present
OK   Oracle numeric type registered as sys.number
OK   Oracle-compat extensions loaded: ivorysql_ora,plisql
OK   PL/iSQL package body executed and returned the exact expected total (153000.50)
OK   pg_hba.conf has 0 active trust rules
OK   every remote pg_hba.conf rule is hostssl + scram-sha-256 (no plain host rule off loopback)
OK   TLS floor TLSv1.2
OK   enumerated login-capable roles: ivorysql
OK   every login-capable role has a password set
OK   no login role accepted any of 16 guessable-password probes (both ports)
OK   a random wrong password was rejected (the probe is capable of tripping)
OK   both ports bound to loopback only (127.0.0.1:1521 127.0.0.1:5432 )
OK   database TCP listening set is exactly 127.0.0.1:1521 127.0.0.1:5432
OK   per-VM TLS key /etc/ssl/ivorysql/server.key mode 600 ivorysql:ivorysql
OK   the routable address <private-ip>:5432 refused the connection
OK   the routable address <private-ip>:1521 refused the connection
OK   no databases beyond the four initdb creates
OK   no leftover user tables in ivorysql
OK   no C toolchain present
OK   no swap active
RESULT healthy - IvorySQL 5.6 (PostgreSQL 18.6), locked down, Oracle compatibility live

Step 4: Confirm the server and its listeners

/usr/local/ivorysql/ivorysql-5/bin/postgres --version
systemctl is-active ivorysql.service ivorysql-firstboot.service
sudo ss -Hltn

postgres (PostgreSQL) 18.6
active
active
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      200        127.0.0.1:1521 0.0.0.0:*
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

IvorySQL listens on 127.0.0.1:5432 (PostgreSQL) and 127.0.0.1:1521 (Oracle) only; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub.

Step 5: Open a native PostgreSQL session on port 5432

The ivorysql system account connects over the local socket with peer authentication, so no password is needed on the instance. Interactive logins have the IvorySQL tools (psql, pg_dump and the rest) on PATH:

sudo -iu ivorysql psql -d ivorysql -w -c "SHOW ivorysql.compatible_mode;" -c "SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolcanlogin ORDER BY 1;" -c "SELECT datname FROM pg_database ORDER BY 1;" -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;"

 ivorysql.compatible_mode
--------------------------
 pg
(1 row)

 rolname  | rolsuper | rolcanlogin
----------+----------+-------------
 ivorysql | t        | t
(1 row)

  datname
-----------
 ivorysql
 postgres
 template0
 template1
(4 rows)

   extname    | extversion
--------------+------------
 gb18030_2022 | 1.0
 ivorysql_ora | 1.0
 plisql       | 1.0
 plpgsql      | 1.0
 uuid-ossp    | 1.1
(5 rows)

To connect over TCP with the password, as an application would, read it from the credentials file into the environment (never put it in a connection string on the command line), then connect with sslmode=require to either port:

export PGPASSWORD="$(sudo sed -n 's/^ivorysql\.password=//p' /root/ivorysql-credentials.txt)"
/usr/local/ivorysql/ivorysql-5/bin/psql -w "host=127.0.0.1 port=5432 dbname=ivorysql user=ivorysql sslmode=require" -c "SELECT version();" -c "SELECT ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
/usr/local/ivorysql/ivorysql-5/bin/psql -w "host=127.0.0.1 port=1521 dbname=ivorysql user=ivorysql sslmode=require" -c "SHOW ivorysql.compatible_mode;" -c "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
unset PGPASSWORD

                                                        version
------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 (IvorySQL 5.6) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

 ssl |   tls
-----+---------
 t   | TLSv1.3
(1 row)

 ivorysql.compatible_mode
--------------------------
 oracle
(1 row)

 ssl
-----
 t
(1 row)

ssl is t on both ports: the sessions really are encrypted.

Step 6: Use Oracle SQL on port 1521

Connect to port 1521 and the same server parses Oracle SQL. dual and sysdate do not exist in PostgreSQL:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w -c "SHOW ivorysql.compatible_mode;" -c "SELECT to_char(sysdate, 'YYYY-MM-DD') AS today, nvl(NULL, 'nvl works') AS nvl FROM dual;"

 ivorysql.compatible_mode
--------------------------
 oracle
(1 row)

   today    |    nvl
------------+-----------
 2026-10-19 | nvl works
(1 row)

On port 5432 the same Oracle-only query is rejected, which shows the two dialects really are separate:

sudo -iu ivorysql psql -d ivorysql -w -c "SELECT 1 FROM dual;" 2>/dev/null || echo "rejected on the native port, as expected"

rejected on the native port, as expected

Create a table with Oracle types through the Oracle port:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w <<'SQL'
DROP TABLE IF EXISTS demo_emp;
CREATE TABLE demo_emp (
    id      number(6)     PRIMARY KEY,
    name    varchar2(40)  NOT NULL,
    salary  number(10,2),
    hired   date
);
INSERT INTO demo_emp VALUES (1, 'Ada Lovelace', 72000.00, sysdate);
INSERT INTO demo_emp VALUES (2, 'Grace Hopper', 81000.50, sysdate);
INSERT INTO demo_emp VALUES (3, 'Alan Turing',  NULL,     sysdate);
SELECT id, name, nvl(to_char(salary, 'FM999999.00'), 'unset') AS salary FROM demo_emp ORDER BY id;
SQL

DROP TABLE
NOTICE:  table "demo_emp" does not exist, skipping
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |     name     |  salary
----+--------------+----------
 1  | Ada Lovelace | 72000.00
 2  | Grace Hopper | 81000.50
 3  | Alan Turing  | unset
(3 rows)

Now create a package (a named container for functions and procedures, which PostgreSQL has no equivalent for). Note the / terminator, as in Oracle SQL*Plus:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w <<'SQL'
CREATE OR REPLACE PACKAGE hr_pkg IS
    FUNCTION total_payroll RETURN number;
END hr_pkg;
/
CREATE OR REPLACE PACKAGE BODY hr_pkg IS
    FUNCTION total_payroll RETURN number IS
        t number;
    BEGIN
        SELECT sum(nvl(salary, 0)) INTO t FROM demo_emp;
        RETURN t;
    END;
END hr_pkg;
/
SELECT to_char(hr_pkg.total_payroll(), 'FM999999.00') AS total_payroll FROM dual;
SQL

CREATE PACKAGE
CREATE PACKAGE BODY
 total_payroll
---------------
 153000.50
(1 row)

The rows inserted through the Oracle port are ordinary PostgreSQL rows. Read them through the native port; the NULL salary shows as an empty PostgreSQL NULL instead of the Oracle session's nvl() text:

sudo -iu ivorysql psql -d ivorysql -w -c "SELECT id, name, salary FROM demo_emp ORDER BY id;"

 id |     name     |  salary
----+--------------+----------
 1  | Ada Lovelace | 72000.00
 2  | Grace Hopper | 81000.50
 3  | Alan Turing  |
(3 rows)

Remove the demonstration objects when you are done:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w -c "DROP PACKAGE IF EXISTS hr_pkg;" -c "DROP TABLE IF EXISTS demo_emp;"

DROP PACKAGE
DROP TABLE

Step 7: Connect from your application

Both listeners accept connections on localhost only. Two ways to reach them:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 5432:127.0.0.1:5432 -L 1521:127.0.0.1:1521 ubuntu@<public-ip>, then connect your client to 127.0.0.1:5432 (PostgreSQL) or 127.0.0.1:1521 (Oracle SQL) as ivorysql with sslmode=require.
  • Open it to your VPC. In /var/lib/ivorysql/5/data/conf.d/cloudimg.conf set both listeners to all addresses and restart, then add an inbound rule for 5432 and/or 1521 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0). Remote clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/ssl/ivorysql/server.crt) and the ivorysql password:
listen_addresses = '*'
ivorysql.listen_addresses = '*'

sudo systemctl restart ivorysql.service

A wrong password is refused by scram-sha-256:

OUT="$(PGPASSWORD=wrong-password /usr/local/ivorysql/ivorysql-5/bin/psql -w "host=127.0.0.1 port=5432 dbname=ivorysql user=ivorysql sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "ivorysql"

Step 8: Check the security controls

Both IvorySQL units carry the metadata block, the TLS key belongs to this instance, and every remote pg_hba.conf rule is hostssl with scram-sha-256:

systemctl show -p IPAddressDeny ivorysql.service ivorysql-firstboot.service
sudo ls -l /etc/ssl/ivorysql/
sudo grep -vE '^[[:space:]]*(#|$)' /var/lib/ivorysql/5/data/pg_hba.conf

IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32

IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32
total 8
-rw-r--r-- 1 ivorysql ivorysql 1627 Oct 19 23:18 server.crt
-rw------- 1 ivorysql ivorysql 2484 Oct 19 23:18 server.key
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
hostssl all             all             0.0.0.0/0               scram-sha-256
hostssl all             all             ::/0                    scram-sha-256

Maintenance

  • Change the password. Run sudo -iu ivorysql psql and type \password ivorysql at the prompt (it asks for the new password twice and never puts it on a command line), then update /root/ivorysql-credentials.txt and your secrets manager.
  • Configuration. cloudimg's settings are in /var/lib/ivorysql/5/data/conf.d/cloudimg.conf (listeners, TLS, memory). Edit it rather than postgresql.conf, then sudo systemctl restart ivorysql.service. Do not set ivorysql.compatible_mode there: the port a client connects to selects the dialect.
  • Storage and backups. PGDATA is /var/lib/ivorysql/5/data on the root volume. Grow the root EBS volume as your data grows, and take EBS snapshots or pg_dump backups (sudo -iu ivorysql pg_dump ...) regularly.
  • Updates. Unattended upgrades keep Ubuntu patched. IvorySQL is compiled from source, so it is not upgraded by apt; move to a newer IvorySQL release by launching the newer version from this listing and migrating with pg_dump/pg_restore.
  • Logs. journalctl -u ivorysql.service; first boot logs to /var/log/cloudimg-firstboot.log and journalctl -u ivorysql-firstboot.service.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (IvorySQL 5.6 on Ubuntu 24.04) and what you were doing.

IvorySQL is a trademark of its respective owners. PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. Oracle is a registered trademark of Oracle and/or its affiliates. Ubuntu is a trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by these organisations.