PGroonga 4.1.0

Option A: AWS Marketplace console

• Open the SecureAnyCloud 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.
• Once the subscription is active, choose Launch your software.
• 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.
• Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
• 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.
• 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.

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 with the command below. It leaves out the password line. 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 (sac 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>.

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)

The demo table documents has PGroonga indexes on body and title. Its first five rows are hand-written, and 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.

pgroonga_score(tableoid, ctid) returns how strongly each row matches. Scores come from the PGroonga index. They are filled in when the index answers the query (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)

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, and 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. When you are done, remove the example with DROP TABLE articles;.

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-sac.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/sac-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-sac.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=<set-at-first-boot> 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

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/sac-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/sac-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