How to 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 worldwide. If you need to postgresql install configure ubuntu 26.04, this guide walks you through the entire process, from installing the server packages and creating roles to setting up remote access and hardening security. Ubuntu 26.04 Resolute Raccoon ships PostgreSQL 18 in its default repositories, giving you immediate access to a modern, feature-rich database engine without third-party repositories.

Whether you are deploying a backend for a web application, setting up a development environment, or migrating from another database system such as MariaDB or MySQL, PostgreSQL offers advanced features like ACID compliance, JSON support, full-text search, and extensibility through custom functions and data types. This tutorial covers everything you need to get a fully functional and secure PostgreSQL server running on Ubuntu 26.04.

Software Requirements

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-contrib

   The 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 postgresql

   You should see active (exited) for the main postgresql service. The actual server process runs under the version-specific unit [email protected].

4. Check the installed version: Confirm the PostgreSQL version from the command line.

   $ psql --version

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 --interactive

   You 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_db

   The -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;

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:

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.conf

   Find 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.conf

   Add 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-256

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

   

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 5432

   Verify 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-client

   Then connect to the server using psql:

   $ psql -h 192.168.178.79 -U linuxconfig_admin -d linuxconfig_db

   Replace 192.168.178.79 with the IP address of your PostgreSQL server. You will be prompted for the role password.

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

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.

Conclusion

You have successfully installed and configured PostgreSQL on Ubuntu 26.04. This guide covered the complete workflow: installing the server from the default repositories, understanding the role-based authentication model, creating roles and databases, performing basic SQL operations, configuring remote access, and hardening security. PostgreSQL is now ready to serve as the backend for your applications, development projects, or data analysis workflows.

As a next step, consider exploring PostgreSQL extensions, setting up automated backups with pg_dump, or configuring connection pooling with PgBouncer for high-traffic applications.

Frequently Asked Questions

1. What version of PostgreSQL does Ubuntu 26.04 include by default? Ubuntu 26.04 Resolute Raccoon ships PostgreSQL 18 in its default repositories. You can install it directly with apt install postgresql without adding any third-party repositories.
2. 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'; to set a new password for the affected role.
3. 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.
4. Can I run PostgreSQL alongside MySQL or MariaDB on the same server? Yes, PostgreSQL and MySQL/MariaDB use different ports (5432 and 3306, respectively) and separate data directories. Both can run simultaneously on the same Ubuntu 26.04 system without conflicts.
5. 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. The purge flag ensures configuration files are deleted. You may also want to remove the /var/lib/postgresql/ data directory manually if you no longer need the databases.