PGroonga

Prerequisites

• An active Azure subscription, an SSH public key, and a VNet with a subnet in the target region
• A subscription to this listing on Azure Marketplace
• A network security group (NSG) that allows TCP 22 (administration) and TCP 5432 (the PostgreSQL wire protocol) from your client network. In production, restrict 5432 to your application subnet

Recommended VM size: Standard_B2s (2 vCPU, 4 GB RAM) for development, evaluation and light workloads. For larger corpora and higher query rates, choose a memory-rich size such as Standard_E2s_v5 or larger.

Deploy the virtual machine

Create the VM from the image. Open only SSH and the PostgreSQL port, and only to your own network:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:pgroonga:default:latest
az vm create \
  --resource-group my-rg \
  --name pgroonga-1 \
  --image sac1647283583153:pgroonga:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name pgroonga-1 --port 5432 --priority 900

Retrieve your per-VM credentials

On first boot, the VM mints its own passwords and TLS certificate. SSH in and read the root-only credentials file:

sudo cat /root/pgroonga-credentials.txt

The file holds the postgres superuser password, the search_app application role password, and ready-to-paste connection strings. The file is mode 0600 and owned by root, and the values in it exist only on this VM.

The postgres.host line is filled in from the VM's own network metadata. Azure's instance metadata service returns an empty value for Standard SKU public IPs, so the image falls back to the load-balancer metadata and then to the VM's private address.

When you connect from outside Azure, use the VM's public IP address from the portal or from az vm list-ip-addresses.

Confirm the service is healthy

Check that PostgreSQL and the credential guard are active, and that the extension is loaded:

systemctl is-active postgresql.service pgroonga-credguard.service
sudo -u postgres psql -tAc "SELECT version();"
sudo -u postgres psql -d fulltext -tAc "SELECT extname || ' ' || extversion FROM pg_extension WHERE extname='pgroonga';"
sudo -u postgres psql -d fulltext -tAc "SELECT a.amname FROM pg_class c JOIN pg_am a ON a.oid = c.relam WHERE c.relname = 'pgroonga_documents_body';"

The last query must return pgroonga. That proves the demo index really uses the PGroonga access method, so &@~ queries against it are index-backed rather than sequential scans.

Full-text search: English and Japanese, one index

The fulltext database ships a documents table with a mix of English and Japanese rows and a USING pgroonga index on the body column. The &@~ operator runs a full-text query against that index.

First, search the English text. A query for full text search returns the documents that are about it:

sudo -u postgres psql -d fulltext -c "SELECT id, title FROM documents WHERE body &@~ 'full text search' ORDER BY id;"

Now search the Japanese text. The same index, queried with the CJK term 全文検索 (zenbun kensaku, "full-text search"), returns the Japanese and mixed-language documents, and none of the English-only ones:

sudo -u postgres psql -d fulltext -c "SELECT id, title, lang FROM documents WHERE body &@~ '全文検索' ORDER BY id;"

This multilingual behavior is what PGroonga adds over PostgreSQL's built-in search. CJK text is tokenized correctly, so a Japanese or Chinese query finds the right rows without any per-language configuration.

Remote connections use TLS. Connect as the least-privilege application role and verify the TLS session directly:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -tAc "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

Rank results by relevance

PGroonga scores each match, so you can order the most relevant documents first with pgroonga_score(). Run the query as the application role over TLS:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -c "SELECT id, title, pgroonga_score(tableoid, ctid) AS score FROM documents WHERE body &@~ 'search' ORDER BY score DESC;"

pgroonga_score() takes the row's tableoid and ctid and returns a relevance score for the current &@~ query: a positive number for a match. Ordering by the score in descending order puts the best hits first, which is what you want behind a search box.

Build your own full-text index

Adding PGroonga search to your own table takes two statements: create the table, then build a USING pgroonga index on the text column you want to search.

Create the table and index as the postgres owner, because schema DDL is an owner operation. The least-privilege search_app role then reads and writes the rows:

sudo -u postgres psql -d fulltext -c "CREATE TABLE IF NOT EXISTS notes (id int PRIMARY KEY, memo text);"
sudo -u postgres psql -d fulltext -c "CREATE INDEX IF NOT EXISTS pgroonga_notes_memo ON notes USING pgroonga (memo);"
sudo -u postgres psql -d fulltext -c "GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO search_app;" PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -c "INSERT INTO notes (id, memo) VALUES (1, 'PGroonga handles English text'), (2, 'PGroongaは日本語も検索できます') ON CONFLICT (id) DO NOTHING;"

Now search your new index in either language:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -c "SELECT id, memo FROM notes WHERE memo &@~ '日本語';"

The &@~ operator is the query-syntax match. PGroonga also offers &@ for a single-keyword match and &@| for an OR of several keywords. See the upstream reference for the full operator set.

Security posture

• No baked credential. Every role ships with no password at all. Both role passwords are minted on first boot from a cryptographic random source and are unique per VM.
• No shared certificate. TLS is off in the image and the certificate is generated per VM on first boot, so no two VMs share a server key.
• TLS enforced for remote clients. pg_hba.conf accepts remote connections only through hostssl with scram-sha-256. There is no trust rule anywhere. On-box administration stays password-less through the local Unix socket.
• A credential guard that fails closed. On every boot, pgroonga-credguard.service proves that the recorded per-VM credentials genuinely authenticate. It then attempts authentication with a list of published and example values. If any of them succeeds, the guard stops PostgreSQL rather than serve a database with a known credential.
• Least privilege for applications. The search_app role has DML on the demo schema but is not a superuser. Point your application at it rather than at postgres.

You can rotate a password at any time. Generate a fresh random secret and record it in the credentials file in the same step, so the credential guard continues to agree with reality:

NEWPW="$(openssl rand -base64 40 | tr -dc 'A-Za-z0-9' | cut -c1-28)" sudo -u postgres psql -c "ALTER ROLE search_app PASSWORD '${NEWPW}';"
sudo sed -i "s|^search_app.password=.*|search_app.password=${NEWPW}|" /root/pgroonga-credentials.txt echo "search_app password rotated and recorded in the credentials file"

Always update /root/pgroonga-credentials.txt when you rotate. The credential guard verifies on every boot that the recorded password genuinely authenticates, and it stops PostgreSQL if it does not. That check makes a stale or mismatched credential impossible to ignore.

Never set a password to a value published in documentation. The guard actively tries a list of such values and refuses to serve if one of them works.

Operations

Service control and logs:

systemctl status postgresql.service --no-pager sudo journalctl -u pgroonga-firstboot.service --no-pager | tail -20

Back up the demo database:

sudo -u postgres pg_dump -d fulltext -f /tmp/fulltext-backup.sql && ls -la /tmp/fulltext-backup.sql

Third-party license attribution for PGroonga and the Groonga library is shipped inside the image:

cat /usr/share/doc/pgroonga-sac/ATTRIBUTION.txt

Support

SecureAnyCloud images include 24/7 support. Raise an issue through the Azure Marketplace listing, or contact SecureAnyCloud support with the VM name, the region and the output of systemctl status postgresql.service.

PGroonga is developed by the Groonga project and distributed under The PostgreSQL License. The Groonga library it links is LGPL-2.1+. Upstream documentation lives at pgroonga.github.io.

Deploy on Azure

Launch PostgreSQL 16 with PGroonga on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com