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:
OR
sudo -Hiu postgres psql -U postgres -c "\password postgres"
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 firewallsudo 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
No comments! Be the first commenter?