Databases Azure

PostgreSQL 17 and pgAdmin 4 on Ubuntu 24.04 on Azure User Guide

| Product: PostgreSQL 17 and pgAdmin 4 on Ubuntu 24.04 on Azure

Overview

This image ships PostgreSQL 17 and pgAdmin 4 together on Ubuntu 24.04 LTS, configured and ready to use from the first boot of every deployed virtual machine. PostgreSQL 17 is installed from the official PGDG repository and runs as a single-node relational database on port 5432. pgAdmin 4 is the industry-standard open source web administration console for PostgreSQL, served through Apache 2 on port 80 with a redirect from the virtual machine root URL directly into the pgAdmin 4 interface.

Authentication is independent per virtual machine. On the very first boot a one-time initialisation service generates a unique password for the postgres database role and a unique password for the pgAdmin 4 admin account, writes them to a single credentials file readable only by root, and then permanently marks itself as done so it never runs again. Two virtual machines launched from the same gallery image never share passwords.

Access pgAdmin 4 by navigating to http://<your-vm-public-ip>/pgadmin4/ in any browser. The pgAdmin 4 console has the local PostgreSQL 17 server pre-registered, so after you log in and expand the Servers tree you are one password prompt away from a fully connected database session. The psql command-line client is also installed and available over SSH for scripting, automation, or direct SQL work without the browser.

The image is intended for developers, data engineers, and database administrators who want a production-grade PostgreSQL 17 server with a browser-based management interface ready in minutes, without manually installing packages, configuring authentication, or bootstrapping credentials. It is not a replicated cluster and does not ship with TLS on port 80 out of the box. Section 14 covers TLS termination and the security steps you should take before exposing the server to the public internet.

The brand is lowercase cloudimg throughout this guide. All cloudimg URLs use the form https://www.cloudimg.co.uk.

Prerequisites

Before you deploy this image you need:

  • A Microsoft Azure subscription with permissions to create resource groups, virtual networks, and virtual machines
  • An SSH public key for initial login to the azureuser admin account on the virtual machine
  • A virtual network and subnet in the eastus region with an associated network security group
  • Inbound NSG rules allowing TCP 22 from your management IP and TCP 80 from the IPs that need pgAdmin 4 access
  • The Azure CLI (az version 2.50 or later) if you intend to use the CLI deployment path in Section 2

Step 1: Deploy the Virtual Machine from the Azure Portal

Navigate to Marketplace in the Azure Portal, search for PostgreSQL 17 pgAdmin, and select the cloudimg publisher entry. Click Create.

On the Basics tab choose your subscription, target resource group, and region. Set the virtual machine name. Choose SSH public key as the authentication type, set the username to a name of your choice, and paste your SSH public key. Standard_D2s_v3 is the recommended minimum size for PostgreSQL 17 with pgAdmin. For production workloads with significant query load or concurrent connections, Standard_D4s_v3 or larger is a better starting point.

On the Disks tab select Standard SSD for the OS disk. PostgreSQL data lives under /var/lib/postgresql/17/main. If you expect to store more than 20 GB of data, attach a separate Premium SSD data disk now and configure the tablespace to use it after the server is running.

On the Networking tab select your existing virtual network and subnet. Attach a network security group that opens TCP 22 from your management IP range and TCP 80 from the client IPs that need pgAdmin access. Do not expose port 80 to the entire public internet unless you have added TLS termination as described in Section 14.

On the Management, Monitoring, and Advanced tabs the defaults are appropriate. Click Review + create, wait for validation to pass, then click Create. Deployment takes around two to three minutes.

Step 2: Deploy the Virtual Machine from the Azure CLI

To deploy from the command line, reference the gallery image resource identifier published on the Partner Center plan. A representative invocation:

RG="postgresql-prod"
LOCATION="eastus"
VM_NAME="postgresql-01"
ADMIN_USER="azureuser"
GALLERY_IMAGE_ID="/subscriptions/<sub-id>/resourceGroups/azure-cloudimg/providers/Microsoft.Compute/galleries/cloudimgGallery/images/postgresql-17-pgadmin-ubuntu-24-04/versions/<version>"
SSH_KEY="$(cat ~/.ssh/id_rsa.pub)"

az group create --name "$RG" --location "$LOCATION"

az network vnet create \
  --resource-group "$RG" \
  --name "${VM_NAME}-vnet" \
  --address-prefix "10.0.0.0/16" \
  --subnet-name default \
  --subnet-prefix "10.0.1.0/24"

az network nsg create \
  --resource-group "$RG" \
  --name "${VM_NAME}-nsg"

az network nsg rule create \
  --resource-group "$RG" \
  --nsg-name "${VM_NAME}-nsg" \
  --name AllowSSH \
  --priority 100 \
  --protocol Tcp \
  --destination-port-ranges 22

az network nsg rule create \
  --resource-group "$RG" \
  --nsg-name "${VM_NAME}-nsg" \
  --name AllowHTTP \
  --priority 110 \
  --protocol Tcp \
  --destination-port-ranges 80

az vm create \
  --resource-group "$RG" \
  --name "$VM_NAME" \
  --image "$GALLERY_IMAGE_ID" \
  --size Standard_D2s_v3 \
  --admin-username "$ADMIN_USER" \
  --ssh-key-value "$SSH_KEY" \
  --vnet-name "${VM_NAME}-vnet" \
  --subnet default \
  --nsg "${VM_NAME}-nsg" \
  --storage-sku StandardSSD_LRS \
  --public-ip-sku Standard

The --storage-sku StandardSSD_LRS flag selects a Standard SSD OS disk, which is the correct tier for a database server. The default Premium SSD is more expensive than necessary for the OS disk when the database itself is on an attached data disk.

Step 3: First Boot and Credentials

After deployment finishes, note the public IP address from the Azure Portal or from az vm show. SSH into the virtual machine:

ssh azureuser@<your-vm-public-ip>

On first boot, a one-time initialisation service named postgresql-firstboot.service runs automatically. It generates unique passwords for the PostgreSQL postgres role and the pgAdmin 4 admin account, then writes them to a credentials file. The service typically completes within 15 seconds of the virtual machine reaching the login prompt. Read the credentials file:

sudo cat /root/postgresql-credentials.txt

The output will look like this:

# PostgreSQL 17 + pgAdmin 4 — generated on first boot
# These credentials are unique to this VM. Store them somewhere safe.

# pgAdmin 4 web console (use this to manage PostgreSQL via a browser)
pgadmin.url=http://10.0.1.10/pgadmin4/
pgadmin.email=admin@cloudimg.co.uk
pgadmin.password=<unique-password>

# PostgreSQL 17 (use this from psql or any client driver)
postgres.host=127.0.0.1
postgres.port=5432
postgres.role=postgres
postgres.password=<unique-password>
postgres.database=postgres

Save these credentials. The credentials file is root-readable only (0600) and never leaves the virtual machine.

Verify that PostgreSQL 17 is running and confirm the installed version with the pg_lsclusters command:

sudo -u postgres psql -c 'SELECT version();'
pg_lsclusters
systemctl is-active postgresql apache2

PostgreSQL 17 version and cluster status confirmed via psql and pg_lsclusters

The output confirms PostgreSQL 17.3 is running on port 5432, the cluster is online, and both the postgresql and apache2 services are active.

Step 4: Access pgAdmin 4

Open a browser and navigate to http://<your-vm-public-ip>/pgadmin4/. Apache redirects the root URL automatically, so navigating to http://<your-vm-public-ip>/ also lands on the pgAdmin 4 login page.

pgAdmin 4 login page

Enter the Email Address and Password from the credentials file (pgadmin.email and pgadmin.password). Click Login.

After a successful login, the pgAdmin 4 dashboard opens. The Object Explorer panel on the left shows a Servers group with the local PostgreSQL 17 instance pre-registered as PostgreSQL 17 (localhost).

pgAdmin 4 dashboard with Servers tree

Step 5: Connect to PostgreSQL 17 Through pgAdmin

In the Object Explorer, click the expand arrow next to Servers. pgAdmin immediately prompts for the PostgreSQL role password to connect to the pre-registered server.

Enter the postgres.password value from your credentials file and click OK.

pgAdmin 4 Connect to Server prompt for the preconfigured PostgreSQL 17 localhost server

pgAdmin connects to the PostgreSQL 17 instance and expands the server tree to show the Databases, Login/Group Roles, and Tablespaces nodes. The Dashboard tab on the right updates to display live server activity charts for sessions, transactions per second, tuples, and block I/O. You can browse databases, run queries, manage roles, and inspect server statistics entirely through the browser.

Step 6: Create a Database

Using pgAdmin 4: In the Object Explorer, expand Servers → PostgreSQL 17 (localhost) → Databases. Right-click Databases and select Create → Database. Enter a name in the Database field, optionally set an owner, and click Save. The new database appears in the tree immediately.

Using psql: Connect over SSH and run:

sudo -u postgres psql

Then within the psql session:

CREATE DATABASE myapp;
\l
\q

The \l command lists all databases including the one you just created. The \q command exits psql.

Step 7: Create a Database Role

It is best practice not to connect applications directly as the postgres superuser. Create a dedicated role for each application:

CREATE ROLE appuser WITH LOGIN PASSWORD 'choose-a-strong-password';
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
\q

To verify the role was created:

\du

This lists all roles with their attributes. The new appuser role appears with the Login attribute set.

Step 8: Run a Query from pgAdmin

To open a query window in pgAdmin, expand the Servers tree, click once on the database you want to query, then click the Query Tool button in the toolbar at the top (or select Tools → Query Tool).

Type a query in the editor pane and press F5 to run it, or use the Execute/Refresh button (the play triangle). Results appear in the data output panel below the editor.

For example, to confirm the current PostgreSQL version from inside pgAdmin:

SELECT version();

To list all tables in the public schema of your database:

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

Step 9: Connect with psql and a Connection String

The PostgreSQL role password from the credentials file can be used in any connection string. From the virtual machine itself:

PGPASSWORD='<postgres.password>' psql \
  "postgresql://postgres@127.0.0.1:5432/postgres" \
  -c 'SELECT current_database(), current_user;'

From an application on the same virtual network, using the private IP:

postgresql://postgres:<password>@<private-ip>:5432/myapp

PostgreSQL 17 accepts remote connections from the same virtual network by default. External connections from outside the virtual network require NSG rule changes and pg_hba.conf updates as described in Section 11.

Step 10: Install PostgreSQL Client Libraries

The virtual machine has the full PostgreSQL 17 server package installed, which includes psql and all server utilities. No additional client installation is needed on the VM.

For a separate application server that needs the client libraries only:

sudo apt-get install -y postgresql-client-17

For language-specific drivers: Python uses psycopg2 or psycopg3, Node.js uses pg, Java uses the JDBC driver published at https://jdbc.postgresql.org, and Go uses pgx.

Step 11: Allow Remote Connections

By default PostgreSQL 17 listens on 127.0.0.1 only. To accept connections from other hosts on the virtual network you need two configuration changes.

1. Edit postgresql.conf to listen on all interfaces:

sudo nano /etc/postgresql/17/main/postgresql.conf

Find the listen_addresses line, uncomment it if necessary, and change it to:

listen_addresses = '*'

2. Edit pg_hba.conf to allow the virtual network CIDR:

sudo nano /etc/postgresql/17/main/pg_hba.conf

Add a line for your virtual network. For example, if your subnet is 10.0.1.0/24:

host    all             all             10.0.1.0/24             scram-sha-256

3. Reload PostgreSQL to apply both changes:

sudo systemctl reload postgresql

Restrict this CIDR as tightly as possible. Do not use 0.0.0.0/0 unless you have TLS and a strong firewall policy in place.

Step 12: Backup and Restore

Back up a single database with pg_dump:

sudo -u postgres pg_dump myapp > /tmp/myapp-backup.sql

To compress the backup:

sudo -u postgres pg_dump -Fc myapp > /tmp/myapp-backup.dump

Restore from a plain SQL dump:

sudo -u postgres psql myapp < /tmp/myapp-backup.sql

Restore from a custom-format dump:

sudo -u postgres pg_restore -d myapp /tmp/myapp-backup.dump

Back up all databases with pg_dumpall:

sudo -u postgres pg_dumpall > /tmp/all-databases.sql

For production workloads, schedule pg_dump as a cron job and copy the output to Azure Blob Storage using azcopy or the Azure CLI.

Step 13: Monitor PostgreSQL Logs

PostgreSQL logs are written to /var/log/postgresql/. The main log file for the running cluster is:

/var/log/postgresql/postgresql-17-main.log

To follow the log in real time:

sudo tail -f /var/log/postgresql/postgresql-17-main.log

To check the current log level and logging configuration:

sudo -u postgres psql -c "SHOW log_min_messages;"
sudo -u postgres psql -c "SHOW logging_collector;"

The pgAdmin 4 Logs tab under the server node also provides a browser view of recent log entries.

Step 14: Security Hardening

This section covers the steps to take before exposing PostgreSQL 17 or pgAdmin 4 to public traffic.

Change the postgres role password. Although the firstboot service generates a unique random password, rotate it immediately to one you generate yourself:

sudo -u postgres psql -c "ALTER ROLE postgres WITH PASSWORD 'your-new-password';"

Update the postgres.password value in any applications, then delete the credentials file:

sudo rm /root/postgresql-credentials.txt

Create application-specific roles. Follow the principle of least privilege: create a role per application, grant it the minimum permissions it needs, and never hard-code the postgres superuser password in application code.

Restrict pg_hba.conf entries. Use the most specific CIDR possible for each host entry. Prefer scram-sha-256 over md5 for all new entries. Review and remove any trust entries.

Enable TLS for pgAdmin 4. Apache 2 is already installed. To add TLS, obtain a certificate (Let's Encrypt Certbot works on Ubuntu 24.04), install mod_ssl, and redirect port 80 to 443:

sudo apt-get install -y certbot python3-certbot-apache
sudo certbot --apache -d <your-domain>

The Certbot Apache plugin writes the SSL virtual host configuration automatically and enables automatic renewal.

Enable TLS for PostgreSQL. PostgreSQL 17 supports native TLS on the server socket. Configure it by placing a certificate and key at the paths specified in ssl_cert_file and ssl_key_file in postgresql.conf, then set ssl = on. The PGDG package ships with a self-signed certificate that activates TLS immediately; replace it with a CA-signed certificate before accepting external connections.

Restrict NSG rules. Review your network security group after deployment. Port 22 should allow only your known management IP range. Port 80 (or 443 after TLS) should allow only the browsers and application servers that need access. PostgreSQL port 5432 should not be open in the NSG at all unless you have a specific requirement for direct TCP access from outside the virtual network.

Step 15: Upgrade and Patch the Operating System

To apply Ubuntu 24.04 security updates:

sudo apt-get update
sudo apt-get upgrade -y

To upgrade PostgreSQL minor versions (for example from 17.x to a future 17.y release):

sudo apt-get update
sudo apt-get install -y postgresql-17
sudo systemctl restart postgresql

PostgreSQL minor version upgrades (17.x → 17.y) do not require a cluster dump and restore. Major version upgrades (17 → 18) do require pg_upgrade or a dump-restore cycle.

Additional Resources

  • PostgreSQL 17 documentation: https://www.postgresql.org/docs/17/
  • pgAdmin 4 documentation: https://www.pgadmin.org/docs/pgadmin4/latest/
  • PGDG APT repository: https://wiki.postgresql.org/wiki/Apt
  • cloudimg Azure Marketplace products: https://www.cloudimg.co.uk/azure
  • Support and feedback: https://www.cloudimg.co.uk/support