PostgreSQL on CentOS: Secure Installation and Remote Access

Publication date: December 17, 2020

Updated: July 29, 2026

Overview

This is the initial configuration procedure required to install and start PostgreSQL on CentOS. This description assumes CentOS7. The section on security is also included.

PostgreSQL is an open-source relational database with a broad feature set. Database selection should be based on application requirements, team expertise, support, operations, and measured workload rather than a general performance claim.

Tested versions:

1. install

This section describes the procedure for installing PostgreSQL.

1-1. Installing PostgreSQL

The historical CentOS procedure installed PostgreSQL with yum under an administrative account.

[username@hostname ~]$ su -

[root@hostname ~]# yum -y install postgresql-server

2. Legacy PostgreSQL configuration walkthrough

This section describes the setup before starting PostgreSQL.

2-1. Create a dedicated PostgreSQL user.

I wanted to create a dedicated user to operate PostgreSQL (can use psql commands), but the user was already created. I remember creating the user when I created it in the past, so maybe the specs have changed.

Try switching to a user with the following command.

[root@hostname ~]# su - postgres

As some of you may be using an older version of PostgreSQL, it should be noted that the following procedure to change ".bashrc" and set environment variables is no longer necessary.

export PGHOME=/var/lib/pgsql

export PGDATA=/var/lib/pgsql/data

export PGHOST=localhost

The default PGDATA for the postgres user seems to be now "/var/lib/pgsql/data". Refer to "/usr/lib/systemd/system/postgresql.service" for the description.

2-2. PostgreSQL Data Preparation

Initialize the PostgreSQL data directory with UTF-8 encoding. The package normally prepares /var/lib/pgsql/data with the required ownership and SELinux labels; do not create or relabel it manually without following the package documentation.

-bash-4.2$ initdb --encoding=UNICODE

Since "/usr/lib/systemd/system/postgresql.service" is set to "PGDATA=/var/lib/pgsql/data", "initdb --encoding=UNICODE" will create a DB under "/var/lib/pgsql/ data/", DB will be constructed under "/var/lib/pgsql/data/".

2-3. Modification of configuration files

Allow IP addresses to access PostgreSQL. For non-local access, configure the system to be password-authenticated.

[root@hostname ~]# vi /var/lib/pgsql/data/postgresql.conf

Modify "postgresql.conf" as follows

#------------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

#------------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'         # what IP address(es) to listen on;

This setting listens on all available addresses. It does not by itself grant database access, but it increases network exposure. Bind only the required interfaces and combine the setting with a host firewall and narrowly scoped pg_hba.conf rules.

This setting is required when logging in from a server other than the server where PostgreSQL is installed, such as pgadmin. This setting is not necessary when logging in to the server and executing psql commands. (since the connection originates from the server itself (localhost)).

This is an important security setting, so minimize the number of users who can access it.

Then modify "pg_hba.conf".

[root@hostname ~]# vi /var/lib/pgsql/data/pg_hba.conf

The historical example appended this rule:

#Password authentication from outside

host    all             all             0.0.0.0/0            md5

This rule matches every IPv4 address and uses the older MD5 authentication method. Do not copy it into a current deployment. Restrict the address range, use the authentication method recommended by the supported PostgreSQL release, require TLS where traffic crosses an untrusted network, and test rule order carefully.

2-4. Startup Confirmation

Now that the preconfiguration is complete, check to see if PostgreSQL starts. Since we built the DB as a postgres user, we switch to the postgres user and then start it.

[root@hostname ~]# su - postgres

-bash-4.2$ pg_ctl start

After PostgreSQL is successfully started, check that the psql command works without problems. Run the psql command as the postgres user.

-bash-4.2$ psql -l

                                         Database List

   Name    |  Owner   | Encoding         |  Collation Order   | Ctype(conversion operator) |      Access rights

-----------+----------+------------------+--------------------+----------------------------+-----------------------

 postgres  | postgres | UTF8             | ja_JP.UTF-8        | ja_JP.UTF-8                |

 template0 | postgres | UTF8             | ja_JP.UTF-8        | ja_JP.UTF-8                | =c/postgres          +

           |          |                  |                    |                            | postgres=CTc/postgres

 template1 | postgres | UTF8             | ja_JP.UTF-8        | ja_JP.UTF-8                | =c/postgres          +

           |          |                  |                    |                            | postgres=CTc/postgres

(3 lines)

The output confirms that the test server process started. Review the PostgreSQL log for warnings before proceeding.

2-5. Automatic startup setting

Use the service unit supplied by the PostgreSQL package to manage startup at boot. Unit names differ by distribution and PostgreSQL packaging, so verify the installed unit rather than assuming the historical CentOS 7 name.

Check if "/usr/lib/systemd/system/postgresql.service" exists. If not, create "postgresql.service" as follows. Execute as root user.

[root@hostname ~]# touch /etc/systemd/system/postgresql.service

[root@hostname ~]# vi /etc/systemd/system/postgresql.service

Use the following settings:

[Unit]

#Description.

Description=PostgreSQL

#Control before and after execution

#Before=xxx.service

After=network.target

[Service]

#User and group designation

User=postgres

Group=postgres

#Once activated, set the status to Activated.

Type=oneshot

RemainAfterExit=yes

#Start, stop, reload

Environment=PGDATA=/var/lib/pgsql/data

ExecStart=/usr/bin/pg_ctl start -D /var/lib/pgsql/data

ExecStop=/usr/bin/pg_ctl stop

ExecReload=/usr/bin/pg_ctl reload

[Install]

#runlevel3 equivalent setting

WantedBy=multi-user.target

Next, register and enable the service with systemctl.

[root@hostname ~]# systemctl enable postgresql

[root@hostname ~]# systemctl is-enabled postgresql

enabled

[root@hostname ~]# systemctl list-unit-files --type=service | grep postgresql

postgresql.service                                enabled

[root@hostname ~]# systemctl daemon-reload

2-6. Adding an Administrative User to PostgreSQL

Create a user to be used within the PostgreSQL application, separate from the user who operates PostgreSQL from Linux. This user will be the user used when operating PostgreSQL via pgadmin, etc. We want to change the password of the default postgres user.

Execute the following command as the postgres user

-bash-4.2$ psql

postgres=# alter role postgres with password 'password';

postgres=# \q

Note: Replace the placeholder with a strong, protected database credential and avoid exposing it in shell history or documentation.

2-7. Drilling holes in firewalls

Permit access to the PostgreSQL port since the default value for CentOS7 is that the firewall (iptables for CentOS6 and earlier) only allows ssh access. Add the "permanent" option to make the configuration permanent.

[root@hostname ~]# firewall-cmd --permanent --zone=public --add-port=5432/tcp

[root@hostname ~]# firewall-cmd --reload

[root@hostname ~]# firewall-cmd --list-all

public (active)

  target: default

  icmp-block-inversion: no

  interfaces: eth0

  sources:

  services: dhcpv6-client http https ssh

  ports: 8080/tcp 5432/tcp

  protocols:

  masquerade: no

  forward-ports:

  source-ports:

  icmp-blocks:

  rich rules:

Confirm that 5432/tcp appears only if remote database access is required, and restrict the firewall source range rather than exposing the port globally.

Try accessing the site with pgadmin. You should be able to log in as the postgres user with the password you just set.

3. Conclusion

The historical steps cover initialization, listening addresses, client authentication, service management, passwords, and firewall access. A current deployment should start from a supported PostgreSQL version and use least-privilege network and authentication rules.

Official references

• PostgreSQL Red Hat family packages
• PostgreSQL pg_hba.conf documentation
• PostgreSQL password authentication and SCRAM