PostgreSQL is an open-source and relational database management system that uses structure query language. It is easy to install and configure.
This blog explains how to install, configure, create users, create a database, grant the permissions and change the password in PostgreSQL on Centos 7, 8 and on Ubuntu 18.04, 20.04.

Git Hub Repo:
Download the following git hub repository for the PostgreSQL installation script.
https://github.com/smarttechfunda/install-postgresql.git
The command to Download git repository:
git clone https://github.com/smarttechfunda/install-postgresql.git

OR use the following step to install and configure the PostgreSQL

Note: Log in as the root user or If you are login as non-root user then make sure that the user has sudo access. For non-root users use sudo before every command.
You will occur the following error in Ubuntu if no sudo access to the logged-in user or run the install-posgtresql script without sudo.
Error:
./install-postgresql: Operation not permitted
Killed

Prerequisite:
Software: wget, net-tools(netstat command to Check the open ports)

Step 1:
Install PostgreSQL package.

CentOS 7 and 8:
sudo yum install postgresql-server -y

Ubuntu 18.04 and 20.04:
sudo apt update
sudo apt install postgresql postgresql-contrib -y

Step 2:
Create a new PostgreSQL database cluster.

CentOS 7:
sudo /usr/bin/postgresql-setup initdb

CentOS 8:
sudo /usr/bin/postgresql-setup --initd

Step 3:
Add the PostgreSQL database user:
sudo -Hiu postgres createuser <user_name>
Example:
sudo -Hiu postgres createuser pguser1

Assign PostgreSQL database user password:
sudo -Hiu postgres psql -d postgres -c "ALTER USER <user_name> PASSWORD '<user_password>'"
Example:
sudo -Hiu postgres psql -d postgres -c "ALTER USER pguser1 PASSWORD 'pguser@123'"

Grant SUPERUSER privilege to PostgreSQL database user:
sudo -Hiu postgres psql -d postgres -c "ALTER USER <user_name> with SUPERUSER"
Example:
sudo -Hiu postgres psql -d postgres -c "ALTER USER pguser1 with SUPERUSER"
OR
Run the following command to create the user, assign the password and grant SUPERUSER privilege:
sudo -Hiu postgres psql -U postgres -c "CREATE USER <user_name> WITH SUPERUSER PASSWORD '<user_password>'"
Example:
sudo -Hiu postgres psql -U postgres -c "CREATE USER pguser1 WITH SUPERUSER PASSWORD 'pguser@123'"

Step 4:
Create the PostgreSQL database:
sudo -Hiu postgres createdb <database_name>
Example:
sudo -Hiu postgres createdb pgdb

Assign the PostgreSQL user as database owner:
sudo -Hiu postgres psql -d postgres -c "GRANT ALL PRIVILEGES ON DATABASE <database_name> to <user_name>"
Example:
sudo -Hiu postgres psql -d postgres -c "GRANT ALL PRIVILEGES ON DATABASE pgdb to pguser1

Step 5:
Grant the CREATEROLE privilege to the PostgreSQL database user:
sudo -Hiu postgres psql -d postgres -c "ALTER USER <user_name> with CREATEROLE"
Example:
sudo -Hiu postgres psql -d postgres -c "ALTER USER pguser1 with CREATEROLE"

Grant the CREATEDB privilege PostgreSQL database user:
sudo -Hiu postgres psql -d postgres -c "ALTER USER <user_name> with CREATEDB"
Example:
sudo -Hiu postgres psql -d postgres -c "ALTER USER pguser1 with CREATEDB"

Step 6: (Note: Perform this step before Step 7)
Change the password of the Postgres user:
sudo -Hiu postgres psql -U postgres -c "\password <user_name>"
Example:
sudo -Hiu postgres psql -U postgres -c "\password postgres"
OR
Log in as a Postgres user:
sudo su - postgres
$ psql
postgres=# \password <user_name>
Example:
postgres=# \password postgres

Step 7:
By default, Postgres user allows you to log in to the Postgres database. Now enable the database user login.
CentOS 7 and 8:
Make the following changes in the /var/lib/pgsql/data/pg_hba.conf file:
Comment the line : # local all all peer
Add the line : local all all md5

Ubuntu 18.04 and 20.04:
Make the following changes in the pg_hba.conf file:
Ubuntu 18.04 file path: /etc/postgresql/10/main/pg_hba.conf
Ubuntu 20.04 file path: /etc/postgresql/12/main/pg_hba.conf
Comment the line : # local all all peer
Add the line : local all all md5

Note: In this file path /etc/postgresql/10/main/pg_hba.conf file the number 10 as per PostgreSQL version. For different PostgreSQL versions, the number may be different.

Restart the PostgreSQL after the above changes.
sudo systemctl restart postgresql

Step 8: (Optional)
Remote login to PostgreSQL:
CentOS 7 and 8:
Make the following changes in the /var/lib/pgsql/data/postgresql.conf file:
The following line open the 5432 port for all (0.0.0.0:5432):
Add the line: listen_addresses = '*'
OR
Instead of '*', you can allow the IP address(es) to listen to the port 5432.
Also, allow the list of comma-separated IPs.
listen_addresses = '192.168.2.4,192.168.2.3'
listen_addresses = '192.168.1.0'

Make the following changes in the /var/lib/pgsql/data/pg_hba.conf:
Comment the line: host all all 127.0.0.1/32 ident
Add the line: host all all 0.0.0.0/0 md5
OR
Instead of 0.0.0.0/0 you can add any IP address or network address.

Ubuntu 18.04 and 20.04:
Make the following changes in the postgresql.conf file:
Ubuntu 18.04 file path: /etc/postgresql/10/main/postgresql.conf
Ubuntu 20.04 file path: /etc/postgresql/12/main/postgresql.conf
file. The following line opens the 5432 port for all (0.0.0.0:5432):
Add the line: listen_addresses = '*'
OR
Instead of '*', you can allow the IP address(es) to listen to the port 5432.
Also, allow the list of comma-separated IPs.
listen_addresses = '192.168.2.4.10,192.168.2.3'
listen_addresses = '192.168.1.0'

Make the following changes in the pg_hba.conf file:
Ubuntu 18.04 file path: /etc/postgresql/10/main/pg_hba.conf
Ubuntu 20.04 file path: /etc/postgresql/12/main/pg_hba.conf
Comment the line: #host all all 127.0.0.1/32 ident
Add the line: host all all 0.0.0.0/0 md5
OR
Instead of 0.0.0.0/0 you can allow any IP address or network address.

Restart the PostgreSQL service:
sudo systemctl restart postgresql

Check the 5432 port is open for all or for the specific IP/network according to setting:
sudo netstat -ntpl | grep 5432

Step 9: (Open port for particular IP)
Firewall setting on CentOS 7 and 8:
Run the following command to stop the firewall.
sudo systemctl stop firewalld
OR Allow 5432 port in the firewall
sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

Step 10: (Perform Step 10 if you allowed the remote access to the database.)
Run the following command from another host to check the remote access:
Install PostgreSQL client if not installed on another host.
Ubuntu: sudo apt install postgresql-client -y
CentOS: sudo yum install postgresql -y
psql -h <postgresql_server> -d <database_name> -U <user_name>
Check Steps 1, 4 and 3 for postgresql_server, database_name and user_name respectively.
Example:
You have installed the PostgreSQL server, created the database user and the database on 192.168.1.5
You have to install Postgresql Client on 192.168.1.10.
Now, login to the 192.168.1.10 and run the following command to check the remote access.
psql -h 192.168.1.5 -d pgdb -U pguser1.


Jenkins Using Nginx

Domain List

  • https://bindhastpanga.com
  • https://lifes24x7.com