By default, PostgreSQL uses port 5432. But for security’s sake, I want to change the default port to another one.

Problem

How to change the default port in PostgreSQL?

Solution

If you want to see the port used by PostgreSQL, you can see it in the postgresql.conf file by using the command (I’m using Ubuntu distro and PostgreSQL version 18):

cat /etc/postgresql/18/main/postgresql.conf | grep 'port ='

Or you can use the command below to see the PostgreSQL port:

sudo ss -ptuln | grep postgres

Or you can use the query in this post to see the port used by PostgreSQL:

SHOW PORT;

From the images above, you can see that PostgreSQL uses port 5432. If you want to change the PostgreSQL port to port 6543, for example, then go to the /etc/postgresql/18/main/postgresql.conf file if you use the Ubuntu distro and PostgreSQL version 18, and change the port value from 5432 to 6432.

sudo find / -type f -name "*postgresql.conf"

Then restart PostgreSQL using the command:

sudo systemctl restart postgresql

After that, you can check the PostgreSQL port by using one of the commands above, and the PostgreSQL port should have changed according to the port you want as shown in the image below:

Note

If you have changed the default port of PostgreSQL from 5432 to 6432, for example, then you don’t need to write the port in the Linux command to access PostgreSQL if you access from localhost: 

But, if you access PostgreSQL from another host, you have to write the option for the port, like in the picture below:

References

stackoverflow.com
dbvis.com
geeksforgeeks.org

Just as MariaDB requires the mariadb-client package to connect other hosts to a MariaDB database, PostgreSQL also requires the postgresql-client package to connect other hosts to a PostgreSQL database.

Problem

How to install postgresql-client on Linux?

Solution

Below is the command to install postgresql-client on some Linux distributions:

Ubuntu/Debian

sudo apt update
sudo apt install postgresql-client

RockyLinux/AlmaLinux/RHEL/CentOS

sudo yum install postgresql-client

Then run the command below to see the installed PostgreSQL version:

psql --version

As you can see in the image above, the version of postgresql-client that you installed is version 16.10. But you should know that, usually by default, the package provided by the distro is a stable old version and not the latest stable version. Therefore, if you want the mariadb-client package to use the latest stable version, use the command below if you use a Ubuntu/Debian distro:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Then run the command below to install the latest version of postgresql-client (The last version of postgresql in November 2025 is version 18.0):

sudo apt install postgresql-client-18

If your distro is using RockyLinux/AlmaLinux/RHEL version 10, use the command below to upgrade the postgresql-client package to the latest version:

sudo dnf remove -y postgresql
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf clean all
sudo dnf makecache
sudo dnf install -y postgresql18

Note

If you want to know how to access the MariaDB database from another host, you can go to this article.

References

postgresql.org
docs.risingwave.com
dewanahmed.com

I want to access a PostgreSQL database from another host.

Problem

How to access a PostgreSQL database from another host?

Solution

I have 2 servers, the PostgreSQL server IP is 192.168.56.101, and the Ubuntu server IP is 192.168.56.11. I want to access the PostgreSQL database from the Ubuntu server. By default, use the format below if you want to access PostgreSQL:

psql -h ip_db -u username -d db_name -p port_number

By default, the PostgreSQL database uses port 5432, so if you access your PostgreSQL using port 5432, you don’t need to write the port. I accessed my PostgreSQL by writing the command:

psql -h 192.168.56.101 -u postgres -d mydb

But I got an error like the image below:

psql: error: connection to server at ‘192.168.56.101’, port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?

The following are the steps to access a PostgreSQL database from another host:

1. Open port

Open port 5432 on each server. If you use RockyLinux/AlmaLinux/RHEL for your servers, use the command below:

firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload

But if you use Ubuntu/Debian for your server, type the command below:

sudo ufw allow 5432

2. Config postgresql.conf

In the database server, go to /etc/postgresql/18/main/postgresql.conf file if you use PostgreSQL 18, but back up the file first:

sudo cp /etc/postgresql/18/main/postgresql.conf /etc/postgresql/18/main/postgresql.conf.ori 

After that, change the script below in the file:

#listen_addresses = ‘localhost’

to

listen_addresses = ‘*’

3. Configure pg_hba.conf

After that, go to /etc/postgresql/18/main/pg_hba.conf if you use PostgreSQL 18, but backup the file first:

sudo cp /etc/postgresql/18/main/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf.ori

Then, add your IP host (in this case, IP is 192.168.56.11) like the below script:

host    all             all             192.168.56.11/32        scram-sha-256

4. Restart PostgreSQL

Restart postgresql service using the command below:

sudo systemctl restart postgresql

Now, try to access the PostgreSQL again, and you should be able to access the database like the command below:

Note

If you have an error when you want to display data, like the image below:

ERROR: permission denied for table employee

Then you have to change the owner of the database and the tables in the database. You can follow how to change it via this page.

References

dev.to
dba.stackexchange.com
prisma.io
tigerdata.com

Ownership of a database and the tables in it in PostgreSQL is very crucial because this has a big influence on users who want to carry out CRUD (Create, Read, Update, Delete) activities in a PostgreSQL database. If the user is not the owner of the database and the tables in it, then the user will not be able to perform CRUD.

Problem

How to change ownership in PostgreSQL?

Solution

For example, I have a mydb database and mydb_user as a user. When I access the database using the user mydb_user and perform a query:

select * from employee;

There is an error as shown below:

ERROR: permission denied for table employee

After I searched on the internet, it turned out that this was caused by the user mydb_user not being the owner of the mydb database and the tables in it. To see the owner of the database, you can use the query below:

SELECT datname AS database_name,
       pg_catalog.pg_get_userbyid(datdba) AS owner
FROM pg_database;

You can see in the picture above, the postgres user is the owner of the mydb database. This is because when creating the database uses the postgres user and not the mydb_user.

SELECT d.datname AS database_name, r.rolname AS owner
FROM pg_database d
JOIN pg_roles r ON d.datdba = r.oid
WHERE d.datname = 'your_database_name';

\l

Similarly, if you want to see the owners of the tables in the database (but you must first log in to that database to run this query), use the query below:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public';

Then there will be a display like the following:

You can see in the image above that the owner of the table employee in the mydb database is a postgres user. This is because when creating the tables uses user postgres and user mydb.

SELECT 
    schemaname,
    tablename,
    tableowner
FROM pg_tables
ORDER BY schemaname, tablename;

A. Change database owner

If you want to change ownership of the mydb database from user postgres to user mydb_user, then you have to log in to PostgreSQL as an existing user, which in the case of a postgres user:

ALTER DATABASE mydb OWNER to mydb_user;

Run the query below to check ownership of the database:

\l

You can see that the owner of the mydb database has changed to user mydb_user. But even so, you still can’t run CRUD on the database because the tables in the database still belong to the postgres user.

B. Change the owner of the table

Still using the postgres user, log in to the mydb database using the command:

\c mydb;

Then run the query below to change the table, sequence, and view to mydb_user:

DO $$
DECLARE
    r RECORD;
BEGIN
    -- Change the owner of all tables
    FOR r IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I OWNER TO mydb_user;', r.schemaname, r.tablename);
    END LOOP;

    -- Change the owner of all sequences
    FOR r IN SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public'
    LOOP
        EXECUTE format('ALTER SEQUENCE %I.%I OWNER TO mydb_user;', r.sequence_schema, r.sequence_name);
    END LOOP;

    -- Change the owner of all views
    FOR r IN SELECT table_schema, table_name FROM information_schema.views WHERE table_schema = 'public'
    LOOP
        EXECUTE format('ALTER VIEW %I.%I OWNER TO mydb_user;', r.table_schema, r.table_name);
    END LOOP;
END $$;

After that, run the command below to view the ownership of the tables in the mydb database:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname="public";

From the image above, you can see the owner of the tables in the mydb database is not a postgres user but a mydb_user. So, you should be able to do CRUD using the mydb_user user as shown in the image below:

Note

As a reminder, to create databases and tables in the database, you should not use postgres user but create a new user because it is very dangerous to use postgres user to carry out daily activities in the PostgreSQL database.

References

commandprompt.com
stackoverflow.com
w3resource.com

The previous article explained the basic commands in the PostgreSQL database. This article will explain how to set up a user in PostgreSQL.

Problem

How to manage a user in PostgreSQL?

Solution

By default, PostgreSQL runs MD5 encryption to save PostgreSQL user passwords in the pg_authid table. However, since PostgreSQL version 10 and later, PostgreSQL has added scram-sha-256 encryption, which is more secure compared to MD5 encryption. To use this encryption,  you have to go to the /etc/postgresql/18/main/postgresql.conf file if you use PostgreSQL 18 and look for the word scram-sha-256 in the file, and open the fence located to the left of the word so it looks like the one below:

password_encryption = scram-sha-256 

Then restart PostgresQL using the command:

sudo systemctl restart postgresql

A. List all users

To see all users in PostgreSQL, type the following command:

\du

By default, PostgreSQL uses the postgres user to access all databases in PostgreSQL. 

B. Create a new user

To create a new user in PostgreSQL, use the format below on the PostgreSQL server:

create user username with encrypted password 'your-password';

For example, if you want to create a mydb_user user with the password qwerty, then use the command below:

create user mydb_user with encrypted password 'qwerty';

C. Change user password

To change the password in PostgreSQL, use the format below:

ALTER USER username WITH PASSWORD 'your_password';

For example, if you want to change the password for the mydb_user to q1w2e3, then use the command below:

ALTER USER mydb_user WITH PASSWORD 'q1w2e3';

D. Create a user privilege

To give the user privileges for a database in PostgreSQL using the format below:

GRANT permission_type ON db_name TO username;

For example, in the previous article, you created a mydb database, so use the command below to grant all permission types:

grant all privileges on database mydb to mydb_user;

If you want a user to only be able to view the mydb database for the sysadmin user, use the command below:

CREATE user john with encrypted password '123456';
GRANT CONNECT ON DATABASE mydb TO john;
\c mydb
GRANT USAGE ON SCHEMA public TO john;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO john;

To see the grants you have made in PostgreSQL, use the command:

\l+

E. Remove a user privilege

If you want to remove a privilege from a user,  you can use the format below:

REVOKE permission_type ON table_name FROM user_name;

For example, if you want to remove a privilege for mydb_user, use the command below:

REVOKE all privileges on database mydb FROM mydb_user;

Or, use the below command if you want to revoke from user john:

REVOKE CONNECT on database mydb FROM john;

F. Delete a user

Use the format below to delete a user in PostgreSQL:

DROP USER username;

If you want to delete mydb_user, use the command below:

DROP user mydb_user;

You can see from the image above, there is an error when you want to delete a user. So, you have to remove some objects that depend on it. Use the command below:

REVOKE all privileges on database mydb FROM mydb_user;

and then delete the user using the below command:

DROP user mydb_user;

Note

If you want a user to only be able to access the PostgreSQL database for a certain time, for example, until January 31, 2030, use the command below:

ALTER USER mydb_user VALID UNTIL 'Jan 31 2030';

References

medium.com
datacamp.com
digitalocean.com
sentry.io

The previous article explained how to install a PostgreSQL database on Linux. This article will explain the basics of commands in a PostgreSQL database.

Problem

How to manage a database and its table(s) in PostgreSQL?

Solution

Below are the basic commands of PostgreSQL to manage a database and its table(s):

1. Access to the PostgreSQL database

Use the command below to access PostgreSQL:

sudo -u postgres psql

2. List all databases

Use the command below to list all databases in PostgreSQL:

\l

3. Creating a new database

Use the format below to create a new database:

CREATE DATABASE database_name;

You can see the options for this command on this page. For example, if you want to create a new database called mydb, use the command below;

CREATE DATABASE mydb; 

4. Connect to the database

Use the format below to connect to the database:

\c db_name;

For example, if you want to connect to the mydb database, type the following command:

\c mydb; 

5. Create a table

Use the format below to create a new table:

CREATE TABLE table_name (name_of_column1 column_data_type1, name_of_column2 column_data_type2, …);

You can see the options for this command on this page. Type the command below to create an employee table:

CREATE TABLE employee (name varchar (100), age int);

6. Display all tables

Use the command below to display all the tables:

\dt 

7. Display the table structure

Use the format below to display the table structure:

\d table_name;

For example, if you want to display the employee table structure, run the command below:

\d employee;

If you want to display more information about the table, type the command below:

\d+ employee;

8. Add the column

Use the format below to make a column in the table:

ALTER TABLE table_name ADD column column_name type(nnn);

You can see the options for this command on this page. For example, if you want to add to the city column in the employee table, use the command below:

ALTER TABLE employee ADD column city varchar(100);

9. Insert data into the table

Use the format below to enter data in a table:

INSERT INTO table_name (Column1, Column2,…, ColumnN) VALUES (Value1,…ValueN), (Value1,…ValueN);

You can see the options for this command on this page. Type the command below if you want to insert 2 data to the employee table:

INSERT INTO employee (name,age,city) VALUES ('bob',21,'New York'), ('John',22,'Chicago');

10. Displays data in a table

Use the format below to display all data in a table:

SELECT option1 FROM table_name option2;

You can see the options for this command on this page. For example, use the command below to display all data in the employee table:

SELECT * from employee; 

11. Update data

Use the format below to update data in a table:

UPDATE table_name SET columnX=valueX WHERE columnY=valueY;

You can see the options for this command on this page. For example, if you want to update the age of the employee named Bob, use the command below:

UPDATE employee SET age=23 WHERE name='bob'; 

12. Delete data

Use the format below to delete one or more rows of a table:

DELETE FROM table_name WHERE column=value;

You can see the options for this command on this page. For example, if you want to delete the data where the user is in Chicago, use the command below:

DELETE FROM employee WHERE city='Chicago'; 

13. Delete the column

Use the format below to make changes in the table:

ALTER TABLE db_name DROP COLUMN column_name;

If you want to delete the column, for example, city, you can use the following command:

ALTER TABLE employee DROP COLUMN city;

14. Empty the table

You can delete all data in a table with the format as below:

TRUNCATE table_name;

You can see the options for this command on this page. For example, if you want to delete all the data in the employee table,  type the command below:

TRUNCATE employee; 

15. Change a table name

You can change the name of the table using the format below:

ALTER TABLE old_table_name RENAME TO new_table_name;

You can see the options for this command on this page. Use the command below if, for example, you want to change the name of the employee table to employees:

ALTER TABLE employee RENAME TO employees; 

16. Delete a table

Use the format below to delete a table:

DROP TABLE table_name;

You can see the options for this command on this page. For example, if you want to delete the employee table,  use the command below:

DROP TABLE employees;

17. Delete a database

To delete a database, use the format below:

DROP DATABASE database_name;

You can see the options for this command on this page. For example, if you want to delete the mydb database,  type the command below:

DROP DATABASE mydb; 

17. Quit from the database

To quit from the database, run the command below:

\q

Note

Actually, the basic commands for PostgreSQL and MariaDB are almost the same, with only there is a slight difference between the two databases.

References

hasura.io
postgresql.org
w3schools.com
geeksforgeeks.org

PostgreSQL, also known as Postgres, is a free and open-source Relational Database Management System (RDBMS) emphasizing extensibility and SQL compliance.

Problem

How to install PostgreSQL on a Linux server?

Solution

In this article, I use the Linux distro RockyLinux server version 9.5, Ubuntu Server version 24.04, and OpenSUSE version 15.6. As of this writing (January 2025), the version of PostgreSQL that has been released is version 17.2.

A. Install PostgreSQL

Here is how to install PostgreSQL on some Linux distros:

RockyLinux

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
dnf install -y postgresql17-server
/usr/pgsql-17/bin/postgresql-17-setup initdb
systemctl enable postgresql-17
systemctl start postgresql-17

Ubuntu

sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt -y install postgresql
sudo systemctl enable postgresql
sudo systemctl start postgresql

Debian

sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt -y install postgresql
sudo systemctl enable postgresql
sudo systemctl start postgresql

OpenSUSE

sudo zypper install -y postgresql17-server
sudo systemctl enable postgresql
sudo systemctl start postgresql

If you want to install the latest version of PostgreSQL, go to this page and choose based on your Linux distro.

B. Connect to PostgreSQL

Now, connect to PostgreSQL using the command below:

sudo -u postgres psql

Note

By default, only Postgres users can enter the PostgreSQL server, so you run the command sudo -u postgres psql to connect to PostgreSQL. If you want to connect to PostgreSQL using other users, such as the root user, use the command below:

sudo -u root psql

There will be an error like below:

psql: error: connection to server on socket “/run/postgresql/.s.PGSQL.5432” failed: FATAL: role “root” does not exist

So, you have to define a new role for the root user. Connect to PostgreSQL, and run the command below:

CREATE ROLE root WITH SUPERUSER LOGIN;
CREATE DATABASE root;
\q

After that, try to connect to PostgreSQL using the root user; it should connect to PostgreSQL as shown in the image below:

References

en.wikipedia.org
postgresql.org
devart.com
neon.com
openbasesystems.com