# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all scram-sha-256
sudo nano /etc/postgresql/18/main/pg_hba.conf
Understanding PostgreSQL Roles
Postgres is also highly extensible with features such as advanced indexing, full-text search, and comes with APIs so that you can develop your own solutions to solve your data storage challenges.
If you want access to newer PostgreSQL versions, then you can use the following automated repository configuration script that automatically set up the official PostgreSQL Apt repository.
How to Install PostgreSQL on Ubuntu 24.04
sudo -i -u postgres
psql # to launch the postgres shell program
That’s it! In this article, we have explained how to install and use the PostgreSQL database management system on Ubuntu 24.04 LTS.
CREATE ROLE admin WITH LOGIN PASSWORD ‘admin_password’ SUPERUSER;
sudo ufw allow 5432/tcp
You can switch from one database to another easily:
The default authentication method is "peer" for the database administrator, meaning it gets the client’s operating system user name and checks if it matches the requested database user name to allow access for local connections.
du+
Modify PostgreSQL Database Roles
SELECT name, city FROM authors;
dt
PostgreSQL comes prepackaged with all Ubuntu versions by default. However, Ubuntu includes a specific “snapshot” of PostgreSQL that remains fixed for the entire lifecycle of that Ubuntu release.
In this article, we will explain how to install PostgreSQL 18 (which was just released on September 25, 2025) on an Ubuntu 24.04 LTS server and learn essential ways to use it effectively.
PostgreSQL (Postgres in short) is an open source, powerful, advanced, high-performance, and stable relational-document database system, which extends the SQL language and includes a wide range of features for secure data storage and management.
listen_addresses = ‘*’
CREATE ROLE tecmint LOGIN;
SELECT * FROM authors ORDER BY joined_on DESC;
Update Data in PostgreSQL Table
sudo apt install -y curl ca-certificates gnupg
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg –dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c ‘echo “deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main” > /etc/apt/sources.list.d/pgadmin4.list && apt update’
dt+
sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
ALTER TABLE authors ADD COLUMN email VARCHAR(100);
We’ll create a test table called authors, which stores information about TecMint.com authors:
sudo systemctl restart postgresql
Basic PostgreSQL Performance Tuning
psql -U tecmint -d tecmint
sudo -u postgres psql
connect database_name
ALTER ROLE tecmint WITH NOSUPERUSER;
Drop a PostgreSQL Database Role
After creating a table, populate it with some data:
l
Delete/Drop a PostgreSQL Database
sudo nano /etc/postgresql/18/main/postgresql.conf
SELECT * FROM authors;
http://127.0.0.1/pgadmin4
How to Use PostgreSQL Roles and Databases
Once you have created a role with a particular name (for instance tecmint user), you can create a database which will be managed by that role:
On Ubuntu 24.04, pgAdmin 4 isn’t available in the default repositories, so we’ll use the official pgAdmin repository.
SELECT datname FROM pg_database;
CREATE ROLE tecmint WITH LOGIN PASSWORD ‘secure_password_here’;
sudo systemctl restart postgresql
Backup and Restore PostgreSQL Databases
To configure roles to use encrypted passwords instead of peer authentication, you need to modify the pg_hba.conf file. Change the authentication method from peer to scram-sha-256 (the modern secure method) or md5 for password authentication.
It is efficient, reliable, and scalable for handling large, complicated volumes of data and setting up enterprise-level and fault-tolerant environments, while ensuring high data integrity.
ALTER TABLE authors RENAME COLUMN code TO author_id;
Delete/Drop a PostgreSQL Table
sudo nano /etc/postgresql/18/main/pg_hba.conf
pg_dumpall -U postgres > all_databases_backup.sql
DELETE FROM authors WHERE name = ‘Ravi Saive’;
List PostgreSQL Database Tables
CREATE TABLE authors (
code SERIAL PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40) NOT NULL,
joined_on DATE NOT NULL
);
sudo systemctl restart postgresql
How to Use PostgreSQL on Ubuntu
list
DROP DATABASE tecmint;
CREATE DATABASE tecmint ENCODING ‘UTF8′ LC_COLLATE=’en_US.UTF-8′ LC_CTYPE=’en_US.UTF-8’ OWNER tecmint;
Grant Privileges to a Role
ALTER TABLE authors DROP COLUMN email;
We covered installation, user management, database operations, remote access configuration, performance tuning, and backup strategies. You can send us your queries or thoughts in the comments below.
DROP ROLE tecmint;
psql -U postgres tecmint < tecmint_backup.sql
Useful PostgreSQL Commands Reference
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.1.0/24 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
ALTER ROLE tecmint WITH PASSWORD ‘new_password’;
sudo apt update
sudo apt install postgresql-18 postgresql-contrib-18
Install pgAdmin 4 for PostgreSQL Administration
INSERT INTO authors (name, city, joined_on) VALUES
(‘Ravi Saive’, ‘Mumbai’, ‘2012-08-15’),
(‘Aaron Kili’, ‘Nairobi’, ‘2014-03-20’),
(‘Matei Cezar’, ‘Bucharest’, ‘2015-06-10’);
Query Data from PostgreSQL Table
CREATE USER tecmint;
Create a Role with a Password
pg_dump -U postgres tecmint > tecmint_backup.sql
During the installation process, a system user account called postgres was created without a password, which is also the default database administrator user name.
d authors
Alter PostgreSQL Table Structure
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
The default role is also postgres. Importantly, database roles are conceptually fully unconnected to operating system users, but practically they may be linked (particularly for authentication purposes).
# Install for both desktop and web modes
sudo apt install pgadmin4
# Install for desktop mode only
sudo apt install pgadmin4-desktop
# Install for web mode only
sudo apt install pgadmin4-web
# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh
sudo systemctl status postgresql
Roles can:
SELECT rolname FROM pg_roles;
GRANT ALL PRIVILEGES ON DATABASE tecmint TO tecmint;
sudo systemctl enable postgresql
DROP TABLE authors CASCADE;
List All PostgreSQL Databases
sudo nano /etc/postgresql/18/main/postgresql.conf
q
Create PostgreSQL Database Roles
UPDATE authors SET city = ‘Delhi’ WHERE name = ‘Ravi Saive’;
Delete Data from PostgreSQL Table
shared_buffers = 256MB # 25% of RAM
effective_cache_size = 1GB # 50-75% of RAM
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4MB
#listen_addresses = ‘localhost’
c database_name
Configure PostgreSQL for Remote Access
CREATE DATABASE tecmint OWNER tecmint;
CREATE ROLE developer WITH LOGIN PASSWORD ‘dev_password’ CREATEDB;
List Existing PostgreSQL Database Roles
CREATE ROLE tecmint;
sudo nano /etc/postgresql/18/main/pg_hba.conf
CREATE USER tecmint WITH PASSWORD ‘secure_password_here’;
Create a Role with Additional Privileges
SELECT * FROM authors WHERE city = ‘Mumbai’;
DROP TABLE authors;
ALTER ROLE tecmint WITH SUPERUSER;
To allow remote connections, you need to edit postgresql.conf file.
du — shows actual users with details
For more information, refer to the PostgreSQL 18 Official Documentation, or explore useful websites for learning PostgreSQL.
CREATE DATABASE tecmint;
Then restart the PostgreSQL service to apply the changes:
