Install and Configure PostgreSQL on Ubuntu 26.04

PostgreSQL is a powerful, open-source object-relational database management system trusted for decades in production environments. Ubuntu 26.04 ships with PostgreSQL 18 in its default repositories, giving you immediate access to a modern database engine without third-party repositories. This guide covers installation, role management, database operations, remote access configuration, and security hardening.

[!TIP] Real-World Scenario: Your app just hit the front page of Hacker News, and suddenly your ‘simple’ database is gasping for air. This is the moment you’re glad you picked PostgreSQL—the database that grows with your ambition without breaking its back.

Prerequisites

Before you start, you need:

• Ubuntu 26.04 server with sudo access
• Basic understanding of SQL and command-line tools

How do I install PostgreSQL?

Ubuntu 26.04 includes PostgreSQL 18 in its default repositories. The postgresql metapackage installs the server, while postgresql-contrib adds utilities and extensions like pgcrypto, pg_stat_statements, and tablefunc.

sudo apt update
sudo apt install postgresql postgresql-contrib

The installation automatically creates a postgres system user, initializes the database cluster, and starts the service.

Verify the service is running:

sudo systemctl status postgresql

You should see active (exited) for the main postgresql service. The actual server process runs under the version-specific unit postgresql@18-main.service.

Check the installed version:

psql --version

How do roles and authentication work?

PostgreSQL uses roles for access management and relies on pg_hba.conf to define authentication methods for different connection types.

Roles vs. Users

In PostgreSQL, a role is an entity that can own database objects and have privileges. A role with the LOGIN attribute is functionally equivalent to a “user” in other database systems. Roles without LOGIN act as groups for organizing permissions.

When PostgreSQL is installed, a superuser role named postgres is created automatically, along with a matching Linux system account.

Peer Authentication

By default, PostgreSQL on Ubuntu 26.04 uses peer authentication for local connections. The database server checks whether the Linux username matches the PostgreSQL role name. To connect as the postgres role, switch to the postgres system user:

sudo -u postgres psql

Verify the current connection:

\conninfo

Type \q to exit the psql shell.

How do I create roles and databases?

Never use the postgres superuser for application connections. Create separate roles with only the privileges they need.

Using Command-Line Tools

Create an interactive role:

sudo -u postgres createuser --interactive

You’ll be prompted for the role name and whether it should be a superuser. For an application role, answer n to the superuser question.

Create a database owned by the new role:

sudo -u postgres createdb -O username dbname

The -O flag sets the owner of the database.

Using SQL Statements

For more control over role attributes, use SQL statements directly within the psql shell:

sudo -u postgres psql

Create a role with a password:

CREATE ROLE app_admin WITH LOGIN PASSWORD 'your_secure_password';

Create a database and assign ownership:

CREATE DATABASE app_db OWNER app_admin;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_admin;

What are the basic database operations?

With a role and database created, you can now connect and perform common operations.

Connecting to a Database

If you created a Linux user matching the role name, connect directly via peer authentication:

sudo -u username psql -d dbname

Alternatively, connect as the postgres superuser:

sudo -u postgres psql -d dbname

Useful psql Meta-Commands

Creating Tables and Inserting Data

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100) DEFAULT 'admin',
    published_date DATE DEFAULT CURRENT_DATE
);

The SERIAL type creates an auto-incrementing integer column. VARCHAR limits the text length, and DEFAULT provides fallback values.

Insert sample rows:

INSERT INTO articles (title, author) VALUES
    ('First Post', 'admin'),
    ('Second Post', 'editor');

Query the table:

SELECT * FROM articles;

Grant table permissions to other roles:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO app_admin;

How do I configure remote access?

By default, PostgreSQL listens only on localhost (127.0.0.1). To allow remote connections, modify two configuration files and adjust firewall rules.

Edit postgresql.conf

Open the main PostgreSQL configuration file:

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

Find the listen_addresses directive (commented out by default):

#listen_addresses = 'localhost'

Uncomment and change to * to accept connections on all interfaces:

listen_addresses = '*'

Or specify a particular IP address for restricted access.

Edit pg_hba.conf

This file controls which hosts can connect and which authentication method they use:

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

Add a line at the end to permit connections from your network:

# Allow remote connections from local network
host    all    all    192.168.1.0/24    scram-sha-256

This entry means: for host (TCP/IP) connections, any database, any role, from the specified subnet, use scram-sha-256 authentication.

Restart PostgreSQL:

sudo systemctl restart postgresql

Allow Port Through Firewall

If running UFW, allow the PostgreSQL port for your trusted subnet:

sudo ufw allow from 192.168.1.0/24 to any port 5432

Verify the rule:

sudo ufw status

Test Remote Connection

On the remote client machine, install the PostgreSQL client:

sudo apt install postgresql-client

Connect to the server:

psql -h server-ip -U app_admin -d app_db

How do I secure my installation?

Set a Password for the postgres Superuser

The postgres superuser has no password by default because local connections use peer authentication. If you enable remote access, set a strong password:

sudo -u postgres psql

ALTER ROLE postgres WITH PASSWORD 'your_strong_password';

Enforce Password Authentication for Local Connections

For environments where multiple users share a server, require password authentication even for local connections:

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

Change the local connection method from peer to scram-sha-256:

local   all   all   scram-sha-256

Restart PostgreSQL:

sudo systemctl restart postgresql

From this point on, all local connections require a password:

psql -U app_admin -d app_db

Restrict Remote Access Scope

Rather than opening PostgreSQL to an entire subnet, limit access to specific IP addresses:

host    app_db    app_admin    192.168.1.50/32    scram-sha-256

This rule allows only the host at 192.168.1.50 to connect to app_db as the app_admin role.

Keep PostgreSQL Updated

Security patches are delivered through Ubuntu’s package repositories:

sudo apt update && sudo apt upgrade

Summary

• Install PostgreSQL with sudo apt install postgresql postgresql-contrib
• Use sudo -u postgres psql to connect as the superuser
• Create dedicated roles with createuser and databases with createdb
• Understand peer authentication for local connections
• Configure remote access in postgresql.conf and pg_hba.conf
• Use scram-sha-256 authentication for secure password handling
• Restrict access with UFW firewall rules and specific IP addresses

FAQ

What version of PostgreSQL does Ubuntu 26.04 include by default? Ubuntu 26.04 ships with PostgreSQL 18. Install it directly with apt install postgresql without adding third-party repositories.

How do I reset a forgotten PostgreSQL role password? Connect as the postgres superuser using peer authentication with sudo -u postgres psql, then run ALTER ROLE rolename WITH PASSWORD 'newpassword';.

What is the difference between peer and scram-sha-256 authentication? Peer authentication verifies that the Linux system username matches the PostgreSQL role name and works only for local connections. Scram-sha-256 requires a password and works for both local and remote connections, making it the recommended method for network-accessible servers.

Can I run PostgreSQL alongside MySQL or MariaDB on the same server? Yes, PostgreSQL and MySQL/MariaDB use different ports (5432 and 3306) and separate data directories. Both can run simultaneously on the same Ubuntu 26.04 system without conflicts.

How do I completely remove PostgreSQL from Ubuntu 26.04? Run sudo apt remove --purge postgresql postgresql-contrib postgresql-common to remove the packages and configuration. You may also want to remove the /var/lib/postgresql/ data directory manually.

What to Read Next

• Install and Configure PHP on Ubuntu 26.04 — connect PHP applications to PostgreSQL
• PostgreSQL Performance Tuning — optimize PostgreSQL for production workloads
• Set Up PgBouncer Connection Pooling — handle high-traffic applications

Related Articles

Deepen your understanding with these curated continuations.

View All Articles

mysql 5 min read

Install and Secure MySQL on Ubuntu 26.04

Complete guide to install and harden MySQL on Ubuntu 26.04. Run mysql_secure_installation, create users and databases, configure remote access with firewall rules.

Jena Apr 26, 2026

git 5 min read

Install and Configure Git on Ubuntu 26.04

Complete guide to install Git on Ubuntu 26.04. Configure user identity, generate SSH keys for GitHub/GitLab, master essential workflow commands, and customize with aliases.

Jena Apr 26, 2026

ubuntu 5 min read

Install .deb Packages on Ubuntu 26.04

Complete guide to install .deb packages on Ubuntu 26.04 using apt, dpkg, and GDebi. Handle dependencies, verify installations, and troubleshoot common errors.

Jena Apr 26, 2026