How to Install and Configure PostgreSQL on Ubuntu 26.04

PostgreSQL is a robust, open-source object-relational database system that has been trusted in production environments worldwide for decades. If you’re looking to install and configure PostgreSQL on Ubuntu 26.04, this guide provides a complete walkthrough—from installing server packages and creating roles to enabling remote access and securing your setup. Ubuntu 26.04 includes PostgreSQL 18 in its default repositories, allowing you to deploy a modern, feature-rich database engine without relying on third-party sources.

Whether you’re building a backend for a web application, preparing a development environment, or migrating from databases like MariaDB or MySQL, PostgreSQL delivers powerful capabilities such as ACID compliance, native JSON support, full-text search, and extensibility through custom functions and data types. This tutorial covers everything required to set up a fully functional and secure PostgreSQL server on Ubuntu 26.04.

Software Requirements

Category	Requirements, Conventions or Software Version Used
System	Ubuntu 26.04 Resolute Raccoon
Software	PostgreSQL 18
Other	Privileged access to your Linux system as root or via the sudo command.
Conventions	# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command $ – requires given linux commands to be executed as a regular non-privileged user

Installing PostgreSQL on Ubuntu 26.04

Ubuntu 26.04 includes PostgreSQL 18 in its default repositories, so installation requires only a few commands. The postgresql metapackage installs the server, while postgresql-contrib adds useful utilities and extensions such as pgcrypto, pg_stat_statements, and tablefunc.

1. Update the package index: Refresh the repository cache to ensure you are installing the latest available packages.$ sudo apt update
2. Install PostgreSQL and contrib packages: This installs the PostgreSQL 18 server along with additional modules.$ sudo apt install postgresql postgresql-contribThe installation automatically creates a postgres system user, initializes the database cluster, and starts the service.
3. Verify the service is running: Check that PostgreSQL started successfully after installation.$ sudo systemctl status postgresqlYou should see active (exited) for the main postgresql service. The actual server process runs under the version-specific unit postgresql@18-main.service.
4. Check the installed version: Confirm the PostgreSQL version from the command line.$ psql –version

POSTGRESQL SERVICE ARCHITECTURE
PostgreSQL on Ubuntu uses a two-tier systemd setup. The postgresql.service unit manages all clusters, while individual clusters run under postgresql@<version>-<cluster>.service. On a fresh installation, the default cluster is 18-main, so the actual running service is postgresql@18-main.service.

Understanding PostgreSQL Roles and Authentication

Before creating users and databases, it is important to understand how PostgreSQL handles authentication. Unlike MySQL or MariaDB, PostgreSQL uses a concept called roles for access management and relies on a file called 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 the PostgreSQL server 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. This means the database server checks whether the Linux username matches the PostgreSQL role name. Consequently, to connect as the postgres role, you must first switch to the postgres system user:

$ sudo -u postgres psql

This opens the psql interactive terminal as the postgres superuser. You can verify the current connection with:

postgres=# \conninfo

Type \q to exit the psql shell at any time. Understanding this authentication model is essential for the rest of the configuration steps, particularly when you later switch to password-based authentication for remote connections.

Creating PostgreSQL Roles and Databases

Now that you understand the authentication model, you can create dedicated roles and databases for your applications. It is a best practice to never use the postgres superuser for application connections. Instead, create separate roles with only the privileges they need.

Using Command-Line Tools

PostgreSQL provides the createuser and createdb wrapper commands that simplify common tasks without entering the SQL shell.

1. Create an interactive role: The --interactive flag prompts you for role attributes.$ sudo -u postgres createuser –interactiveYou will be prompted for the role name and whether it should be a superuser. For an application role, answer n to the superuser question.Enter name of role to add: linuxconfig Shall the new role be a superuser? (y/n) n
2. Create a database: Create a database owned by the new role.$ sudo -u postgres createdb -O linuxconfig linuxconfig_dbThe -O flag sets the owner of the database to the linuxconfig role.

Using SQL Statements

For more control over role attributes, you can use SQL statements directly within the psql shell. This approach allows you to set passwords, connection limits, and specific privileges.

1. Connect as the postgres superuser:$ sudo -u postgres psql
2. Create a role with a password: Define a new role with login capability and a password.postgres=# CREATE ROLE linuxconfig_admin WITH LOGIN PASSWORD ‘your_secure_password’;
3. Create a database and assign ownership:postgres=# CREATE DATABASE linuxconfig_db OWNER linuxconfig_admin;
4. Grant all privileges on the database:postgres=# GRANT ALL PRIVILEGES ON DATABASE linuxconfig_db TO linuxconfig_admin;

PASSWORD SECURITY
Always use strong, unique passwords for database roles. Avoid simple or dictionary-based passwords. In production environments, consider using scram-sha-256 authentication (the default in PostgreSQL 18) for improved password hashing security.

Basic PostgreSQL Database Operations

With a role and database created, you can now connect and perform common database operations. This section covers the essential psql commands and SQL statements you will use daily.

Connecting to a Database

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

$ sudo -u linuxconfig psql -d linuxconfig_db

Alternatively, connect as the postgres superuser and switch to the target database:

$ sudo -u postgres psql -d linuxconfig_db

Useful psql Meta-Commands

The psql shell provides a set of backslash commands for navigating databases and objects:

Command	Description
\l	List all databases
\c dbname	Connect to a different database
\dt	List tables in the current database
\du	List all roles
\d tablename	Describe a table structure
\q	Quit psql

Creating Tables and Inserting Data

The following example demonstrates creating a simple table, inserting data, and querying it:

1. Create a table: Define a table to store sample data.linuxconfig_db=# CREATE TABLE articles ( id SERIAL PRIMARY KEY, title VARCHAR(200) NOT NULL, author VARCHAR(100) DEFAULT ‘linuxconfig’, 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.
2. Insert sample rows: Add data to the table.linuxconfig_db=# INSERT INTO articles (title, author) VALUES (‘Hello from LinuxConfig.org’, ‘linuxconfig’), (‘Greetings from LinuxConfig.org’, ‘linuxconfig_admin’);
3. Query the table: Retrieve all rows from the table.linuxconfig_db=# SELECT * FROM articles;
4. Grant table permissions to other roles: Tables created by the postgres superuser are not automatically accessible to other roles. Grant the linuxconfig_admin role access to the table and any future tables in the schema:linuxconfig_db=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO linuxconfig_admin; linuxconfig_db=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO linuxconfig_admin; linuxconfig_db=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO linuxconfig_admin;The first command grants privileges on existing tables, the second covers sequences used by SERIAL columns, and the third ensures the linuxconfig_admin role automatically receives privileges on any new tables created in the public schema.

Configuring Remote Access to PostgreSQL on Ubuntu 26.04

By default, PostgreSQL listens only on localhost (127.0.0.1), which means external clients cannot connect. To allow remote connections, you need to modify two configuration files and adjust your firewall rules. This is necessary if your application server runs on a different machine than your database server.

1. Edit postgresql.conf to listen on all interfaces: Open the main PostgreSQL configuration file.$ sudo nano /etc/postgresql/18/main/postgresql.confFind the listen_addresses directive, which is commented out by default:#listen_addresses = ‘localhost’Uncomment the line by removing the # prefix and change the value to * to accept connections on all network interfaces:listen_addresses = ‘*’If you want to restrict listening to a specific IP address, replace * with that address instead. Save and close the file.
2. Edit pg_hba.conf to allow remote clients: This file controls which hosts can connect and which authentication method they use. Open it for editing:$ sudo nano /etc/postgresql/18/main/pg_hba.confAdd a line at the end to permit connections from your network. For example, to allow all hosts on the 192.168.178.0/24 subnet using password authentication:# Allow remote connections from local network host all all 192.168.178.0/24 scram-sha-256This entry means: for host (TCP/IP) connections, any database (all), any role (all), from the specified subnet, use scram-sha-256 authentication.Adding a remote access rule to pg_hba.conf for the 192.168.178.0/24 subnet
3. Restart PostgreSQL to apply changes:$ sudo systemctl restart postgresql
4. Allow port 5432 through the firewall: If you are running UFW, allow the PostgreSQL port for your trusted subnet only:$ sudo ufw allow from 192.168.178.0/24 to any port 5432Verify the rule was added:$ sudo ufw status
5. Test the remote connection: On the remote client machine, install the PostgreSQL client if it is not already available:$ sudo apt install postgresql-clientThen connect to the server using psql:$ psql -h 192.168.178.79 -U linuxconfig_admin -d linuxconfig_dbReplace 192.168.178.79 with the IP address of your PostgreSQL server. You will be prompted for the role password.

SECURITY CONSIDERATION
Never use 0.0.0.0/0 in pg_hba.conf on a production server unless you have additional network-level protections such as a VPN or cloud security group. Exposing PostgreSQL to the entire internet significantly increases the risk of brute-force attacks and unauthorized access.

Securing Your PostgreSQL Installation

A freshly installed PostgreSQL server works out of the box, but additional hardening steps are recommended, especially for servers accessible over a network. This section covers essential security measures to protect your PostgreSQL installation on Ubuntu 26.04.

Setting a Password for the postgres Superuser

The postgres superuser role has no password by default because local connections use peer authentication. However, if you enable remote access, you should set a strong password for this role:

$ sudo -u postgres psql
postgres=# ALTER ROLE postgres WITH PASSWORD 'your_strong_password';

Enforcing Password Authentication for Local Connections

For environments where multiple users share a server, you may want to require password authentication even for local connections. Edit pg_hba.conf:

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

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

# "local" is for Unix domain socket connections only
local   all   all   scram-sha-256

After making this change, restart PostgreSQL:

$ sudo systemctl restart postgresql

From this point on, all local connections require a password. You can connect by specifying the role explicitly:

$ psql -U linuxconfig_admin -d linuxconfig_db

AUTHENTICATION METHODS
PostgreSQL 18 uses scram-sha-256 as its default password authentication method. This is more secure than the older md5 method because it never transmits the password hash over the network and resists replay attacks. Avoid using md5 or trust in production configurations.

Restricting Remote Access Scope

Rather than opening PostgreSQL to an entire subnet, consider limiting access to specific IP addresses. In pg_hba.conf, use individual host entries:

host    linuxconfig_db    linuxconfig_admin    192.168.178.50/32    scram-sha-256

This rule allows only the host at 192.168.178.50 to connect to linuxconfig_db as the linuxconfig_admin role. Additionally, review and remove any overly broad rules that were added during initial setup. Combining restrictive pg_hba.conf entries with UFW firewall rules provides defense in depth.

Keeping PostgreSQL Updated

Security patches are delivered through the standard Ubuntu package repositories. Regularly update your system to receive the latest fixes:

$ sudo apt update && sudo apt upgrade

For more information on PostgreSQL security best practices, configuration directives, and advanced tuning, consult the official PostgreSQL 18 documentation.