Databases AWS

PGroonga 4.1.0 on AWS User Guide

| Product: PGroonga 4.1.0 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying PGroonga 4.1.0 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.

PGroonga is a PostgreSQL extension that adds fast full-text search for every language, backed by the Groonga search engine. PostgreSQL's built-in text search needs a word-splitting dictionary per language and does not handle Japanese, Chinese or Korean text well; PGroonga indexes any language out of the box, so the same USING pgroonga index answers English and CJK searches. Searches are ordinary SQL: &@~ takes a query in a familiar search syntax (OR, - to exclude, quoted phrases), &@ matches a keyword, and pgroonga_score() returns a relevance score you can sort on.

What it is not. This is one PostgreSQL instance, not a separate search cluster: there is no sharding across nodes and no search UI (you query it with SQL). PGroonga and Groonga are open source with no licence key, activation check or trial timer.

What is on the image:

  • PostgreSQL 18.6 from the PostgreSQL project's apt repository (postgresql@18-main.service)
  • PGroonga 4.1.0 (postgresql-18-pgdg-pgroonga 4.1.0-1) and the Groonga 16.1.4 library from the Groonga project's apt repository, which stays configured so apt delivers PGroonga and Groonga fixes
  • A brand-new cluster created on your instance's first boot, with a fulltext database, the pgroonga extension, a search_app application role and a 5,005-document demo corpus with PGroonga indexes (five curated English and Japanese documents plus 5,000 generated technical notes)
  • TLS with a certificate generated on your instance's first boot (TLS 1.2 minimum)
  • A credential guard that runs on every boot and refuses to serve if a published or example password ever authenticates

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

Security posture

  • No database ships in the image. The data directory is empty in the AMI. First boot runs initdb on your instance, so no role, password, demo row, index file or cluster identity is shared with any other instance.
  • No default password. First boot generates a 32-character password for the search_app application role and writes it to /root/pgroonga-credentials.txt, readable by root only. The postgres superuser has no password at all: you use it on the instance with sudo -u postgres psql (peer authentication over the local socket), and it cannot log in over the network.
  • Nothing is served before first boot finishes. PostgreSQL will not start until first boot has created the cluster, the certificate and the password. While it sets the password, the server listens on its local socket only, with no TCP port and with statement logging off.
  • Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate under /etc/postgresql/18/main/ and turns TLS on. No key ships in the image, so no two instances share one.
  • Localhost only. PostgreSQL listens on 127.0.0.1:5432, 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.
  • TLS on every TCP connection. pg_hba.conf accepts TCP clients, loopback included, only over hostssl with scram-sha-256; there is no plain-text TCP rule and no trust rule.
  • No metadata access for the database. A database user can make PostgreSQL open outbound connections (for example COPY ... FROM PROGRAM as a superuser), so every PostgreSQL unit and both first-boot services 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 backups to S3 from inside the database, give them explicit credentials; they cannot use the instance role.
  • A pinned third-party package source. PGroonga is not in the PostgreSQL project's repository, so it comes from the Groonga project's own repository. Its signing key is checked against a pinned fingerprint, the source is trusted for that key only (signed-by, never a global keyring), and an apt preference allows that source to provide only the PGroonga, Groonga and Apache Arrow packages, so it can never replace an Ubuntu or PostgreSQL package (Step 8 shows it).
  • 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, PostgreSQL stays stopped and first boot tries again on the next boot.
  • SSH. Key-only login for ubuntu; root login, password and keyboard-interactive login and X11 forwarding are off. 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 image's memory settings (shared_buffers = 1GB, maintenance_work_mem = 256MB) are sized for it; raise them in /etc/postgresql/18/main/conf.d/10-cloudimg.conf on larger instances. Building a PGroonga index uses maintenance_work_mem.

Connecting to your instance

Version Operating system SSH user
PGroonga 4.1.0 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 PGroonga 4.1.0 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 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 pgroonga \
  --description "PGroonga" --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=pgroonga}]'

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. Show the connection details (the password line is left out here; read it with sudo cat /root/pgroonga-credentials.txt and keep it in your secrets manager):

sudo grep -v -e '^search_app.password=' /root/pgroonga-credentials.txt | head -14

# PGroonga 4.1.0 on PostgreSQL 18 - multilingual full-text search (cloudimg AWS Marketplace image)
# Generated on THIS instance's first boot by pgroonga-firstboot.service. This password exists
# nowhere else. The postgres superuser has NO password: use it on this instance with
#   sudo -u postgres psql -d fulltext        (peer authentication over the local socket)
# Rotate the application password (never on a command line) with:
#   sudo -u postgres psql   then   \password search_app
# and update this file to match.

search_app.role=search_app
search_app.host=127.0.0.1
search_app.port=5432
search_app.database=fulltext
search_app.sslmode=require
pgroonga.version=4.1.0

The commands below read the password from that file at run time, so it never appears on screen. In your own sessions you can instead put it in ~/.pgpass (mode 0600) as 127.0.0.1:5432:fulltext:search_app:<APP_PASSWORD>.

Step 3: Confirm PGroonga is running

psql --version
systemctl is-active postgresql@18-main.service pgroonga-firstboot.service pgroonga-credguard.service
sudo ss -Hltn

psql (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
active
active
active
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

PostgreSQL listens on 127.0.0.1:5432 only; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub. Now connect over TLS as search_app, and show the extension, the Groonga library version, the demo corpus and the TLS session:

export PGPASSWORD="$(sudo sed -n 's/^search_app.password=//p' /root/pgroonga-credentials.txt)"
psql "host=127.0.0.1 dbname=fulltext user=search_app sslmode=require" \
  -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;" \
  -c "SELECT pgroonga_command('status')::jsonb->1->>'version' AS groonga_version;" \
  -c "SELECT count(*) AS documents FROM documents;" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

 extname  | extversion
----------+------------
 pgroonga | 4.1.0
 plpgsql  | 1.0
(2 rows)

 groonga_version
-----------------
 16.1.4
(1 row)

 documents
-----------
      5005
(1 row)

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

Step 4: Search in English and Japanese

The demo table documents has PGroonga indexes on body and title. Its first five rows are hand-written; rows 3 and 4 are Japanese. &@~ takes a search query: words separated by spaces must all match, OR widens the search, and a leading - excludes a word:

export PGPASSWORD="$(sudo sed -n 's/^search_app.password=//p' /root/pgroonga-credentials.txt)"
psql "host=127.0.0.1 dbname=fulltext user=search_app sslmode=require" \
  -c "SELECT id, title FROM documents WHERE id <= 5 ORDER BY id;" \
  -c "SELECT id, title FROM documents WHERE body &@~ 'accurate';" \
  -c "SELECT id, title FROM documents WHERE body &@~ '全文検索' ORDER BY id;" \
  -c "SELECT id, title FROM documents WHERE body &@~ 'accurate OR lightning' ORDER BY id;" \
  -c "SELECT count(*) AS matches FROM documents WHERE body &@~ 'replication failover';"

 id |             title
----+--------------------------------
  1 | PostgreSQL basics
  2 | Full text search with PGroonga
  3 | 日本語の全文検索
  4 | 多言語対応
  5 | Ranking and scoring
(5 rows)

 id |             title
----+--------------------------------
  2 | Full text search with PGroonga
(1 row)

 id |      title
----+------------------
  3 | 日本語の全文検索
  4 | 多言語対応
(2 rows)

 id |             title
----+--------------------------------
  2 | Full text search with PGroonga
  5 | Ranking and scoring
(2 rows)

 matches
---------
     744
(1 row)

全文検索 means "full-text search": PGroonga finds it inside the Japanese sentence of row 3 and the mixed English and Japanese text of row 4 without any dictionary or language setting. The 5,000 generated notes are written on your instance's first boot, so your count for replication failover will differ.

Step 5: Rank results and see the index in the plan

pgroonga_score(tableoid, ctid) returns how strongly each row matches. Scores come from the PGroonga index, so they are filled in when the query is answered by the index (as here) and are 0 when PostgreSQL scans the table instead. EXPLAIN shows the planner using the PGroonga index:

export PGPASSWORD="$(sudo sed -n 's/^search_app.password=//p' /root/pgroonga-credentials.txt)"
psql "host=127.0.0.1 dbname=fulltext user=search_app sslmode=require" \
  -c "SELECT id, title, pgroonga_score(tableoid, ctid) AS score FROM documents WHERE body &@~ 'PGroonga OR PostgreSQL OR languages' ORDER BY score DESC, id;" \
  -c "EXPLAIN SELECT id, title FROM documents WHERE body &@~ 'accurate';"

 id |             title              | score
----+--------------------------------+-------
  2 | Full text search with PGroonga |     3
  3 | 日本語の全文検索               |     2
  1 | PostgreSQL basics              |     1
  4 | 多言語対応                     |     1
(4 rows)

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using pgroonga_documents_body on documents  (cost=0.00..1.11 rows=5 width=24)
   Index Cond: (body &@~ 'accurate'::text)
(2 rows)

Step 6: Index your own table

search_app can create tables in fulltext. Create a small articles table with English, Japanese and Chinese text, add a PGroonga index, then search it. &@ matches one keyword; &@~ takes a query; pgroonga_snippet_html() returns the matching text with the keyword marked up:

export PGPASSWORD="$(sudo sed -n 's/^search_app.password=//p' /root/pgroonga-credentials.txt)"
psql "host=127.0.0.1 dbname=fulltext user=search_app sslmode=require" <<'SQL'
CREATE TABLE articles (
    id    bigserial PRIMARY KEY,
    title text NOT NULL,
    body  text NOT NULL
);
CREATE INDEX articles_body_idx ON articles USING pgroonga (body);
INSERT INTO articles (title, body) VALUES
  ('Release notes',      'The new release improves replication and backup speed.'),
  ('東京の天気',          '明日の東京は晴れのち曇り、最高気温は二十度の予報です。'),
  ('Search tips',        'Use OR to widen a search and a minus sign to exclude a word.'),
  ('北京旅游',            '北京的博物馆和公园非常适合周末旅游。');
SELECT id, title FROM articles WHERE body &@~ '東京';
SELECT id, title FROM articles WHERE body &@~ '博物馆';
SELECT id, title FROM articles WHERE body &@~ 'release OR search -backup' ORDER BY id;
SELECT id, title FROM articles WHERE body &@ 'replication';
SELECT pgroonga_snippet_html(body, ARRAY['backup']) AS snippet FROM articles WHERE body &@~ 'backup';
SQL

CREATE TABLE
CREATE INDEX
INSERT 0 4
 id |   title
----+------------
  2 | 東京の天気
(1 row)

 id |  title
----+----------
  4 | 北京旅游
(1 row)

 id |    title
----+-------------
  3 | Search tips
(1 row)

 id |     title
----+---------------
  1 | Release notes
(1 row)

                                          snippet
-------------------------------------------------------------------------------------------
 {"The new release improves replication and <span class=\"keyword\">backup</span> speed."}
(1 row)

The index stays in step with the table: rows you insert, update or delete are searchable (or gone) as soon as the transaction commits. Remove the example when you are done with DROP TABLE articles;.

Step 7: Connect from your application

PostgreSQL accepts connections on localhost only. Two ways to reach it:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 5432:127.0.0.1:5432 ubuntu@<public-ip>, then connect your client to 127.0.0.1:5432 as search_app with sslmode=require and the password from the credentials file. Use UTF-8 as the client encoding (the default for most drivers) so Japanese, Chinese and Korean text round-trips.
  • Open it to your VPC. In /etc/postgresql/18/main/conf.d/10-cloudimg.conf change listen_addresses = '127.0.0.1' to listen_addresses = '*', restart PostgreSQL, then add an inbound rule for 5432 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0). Clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/18/main/cloudimg-server.crt) and the role's password:
sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-cloudimg.conf
sudo systemctl restart postgresql@18-main.service

For more applications, create one least-privilege role each: in sudo -u postgres psql -d fulltext run CREATE ROLE app2 LOGIN; and then \password app2.

A wrong password is refused, the postgres superuser cannot log in over TCP at all (it has no password), and a plain-text TCP connection is refused before authentication:

export PGPASSWORD="$(sudo sed -n 's/^search_app.password=//p' /root/pgroonga-credentials.txt)"
OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 dbname=fulltext user=search_app sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"
OUT="$(psql "host=127.0.0.1 dbname=fulltext user=postgres sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"
OUT="$(psql "host=127.0.0.1 dbname=fulltext user=search_app sslmode=disable" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "search_app"
FATAL:  password authentication failed for user "postgres"
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "search_app", database "fulltext", no encryption

Step 8: Check the security controls

The PostgreSQL unit carries the metadata block, PostgreSQL listens on loopback with this instance's own certificate, only search_app has a password, every TCP rule is TLS plus scram-sha-256, SSH runs with the settings listed above, and the Groonga package source is pinned:

systemctl show -p IPAddressDeny postgresql@18-main.service
sudo -u postgres psql -tAc "SHOW listen_addresses;" -c "SHOW ssl_cert_file;" -c "SHOW ssl_min_protocol_version;"
sudo -u postgres psql -tAc "SELECT rolname, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolcanlogin ORDER BY 1;"
sudo grep -vE '^[[:space:]]*(#|$)' /etc/postgresql/18/main/pg_hba.conf
sudo sshd -T | grep -E '^(permitrootlogin|passwordauthentication|kbdinteractiveauthentication|x11forwarding) '
cat /etc/apt/sources.list.d/groonga.list
grep -vE '^#' /etc/apt/preferences.d/cloudimg-groonga.pref

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
127.0.0.1
/etc/postgresql/18/main/cloudimg-server.crt
TLSv1.2
postgres|f
search_app|t
local     all          all                 peer
hostssl   all          all   127.0.0.1/32  scram-sha-256
hostssl   all          all   ::1/128       scram-sha-256
hostssl   all          all   0.0.0.0/0     scram-sha-256
hostssl   all          all   ::/0          scram-sha-256
local     replication  all                 peer
permitrootlogin no
passwordauthentication no
kbdinteractiveauthentication no
x11forwarding no
deb [signed-by=/usr/share/keyrings/groonga-archive.gpg] https://packages.groonga.org/ubuntu noble universe
Package: postgresql-18-pgdg-pgroonga libgroonga0 libgroonga* groonga* libarrow[0-9]* libarrow-compute[0-9]*
Pin: origin packages.groonga.org
Pin-Priority: 500

Package: *
Pin: origin packages.groonga.org
Pin-Priority: -1

Maintenance

  • Change the application password. Run sudo -u postgres psql and type \password search_app at the prompt; it asks for the new password twice, sends only a SCRAM verifier and never puts it on a command line or in a log. Then update /root/pgroonga-credentials.txt (the credential guard reads it on every boot) and your secrets manager.
  • Configuration. cloudimg's settings are in /etc/postgresql/18/main/conf.d/10-cloudimg.conf (listener, memory, logging) and 20-cloudimg-tls.conf (this instance's certificate). Apply changes with sudo systemctl restart postgresql@18-main.service.
  • Updates. Unattended upgrades apply Ubuntu security updates automatically. sudo apt update && sudo apt upgrade also installs PostgreSQL 18 minor releases from apt.postgresql.org and PGroonga and Groonga releases from packages.groonga.org (the Groonga signing key 2701F317CFCCCB975CADE9C2624CF77434839225 is pinned in /usr/share/keyrings/groonga-archive.gpg). After a PGroonga package update, restart PostgreSQL and run ALTER EXTENSION pgroonga UPDATE; in each database that uses it, as the PGroonga upgrade notes describe.
  • Backups. Take EBS snapshots of the volume or pg_dump -Fc fulltext backups regularly. PGroonga indexes are rebuilt from the table data on restore.
  • Logs. /var/log/postgresql/postgresql-18-main.log and journalctl -u postgresql@18-main.service; PGroonga writes its own log to pgroonga.log in the data directory (/var/lib/postgresql/18/main/). First boot and the credential guard log to journalctl -u pgroonga-firstboot.service and journalctl -u pgroonga-credguard.service.
  • Licences. PGroonga is under The PostgreSQL License; the Groonga library is under the GNU LGPL v2.1 or later (dynamically linked); Apache Arrow is under the Apache License 2.0; PostgreSQL is under The PostgreSQL License. /usr/share/doc/pgroonga-cloudimg/ATTRIBUTION.txt and BUILD_VERSIONS list the exact packages and digests.

Support

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

PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. PGroonga and Groonga are trademarks of their respective owners. Ubuntu is a trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by these organisations.