PostgreSQL or Postgres is an open-source, general-purpose, object-relational database management system with many advanced features to help you build fault-tolerant environments or complex applications.
In this article, we will discuss how to install PostgreSQL database server on CentOS 8. Before choosing a version to install, make sure your applications support it.
We’ll also cover the basics of PostgreSQL database administration.
To be able to install packages, you need to be logged in as root or as a user with sudo privileges.
Installing PostgreSQL on CentOS 8
At the time of this writing, there are two versions of PostgreSQL server available for installation from the standard CentOS repositories: versions 9.6 and 10.0.
To list the available streams of a PostgreSQL module, enter:
dnf module list postgresql
The output shows that the postgresql module is available with two threads. Each stream has two profiles: server and client. By default, stream 10 with a profile server is used:
CentOS-8 - AppStream Name Stream Profiles Summary postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 9.6 client, server [d] PostgreSQL server and client module
- To set the default stream, enter PostgreSQL server version 10.0:
sudo dnf install @postgresql:10
- To install PostgreSQL server version 9.6 enter:
sudo dnf install @postgresql:9.6
You can also install the contrib package, which provides several additional features for PostgreSQL databases:
sudo dnf install postgresql-contrib
After the installation is complete, initialize the PostgreSQL database using the following command:
sudo postgresql-setup initdb
Initializing database ... OK
Start the PostgreSQL service and enable it to start at boot:
sudo systemctl enable --now postgresql
Use the psql tool to verify the installation by connecting to the PostgreSQL database server and printing its version:
sudo -u postgres psql -c "SELECT version();"
PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
PostgreSQL Roles and Authentication Methods
PostgreSQL handles database access permissions using the concept of roles. The role can represent a database user or a group of database users.
PostgreSQL supports several authentication methods. The most commonly used methods are:
- Trust – a role can connect without a password if the conditions defined in it pg_hba.conf are met.
- Password – the role can connect by providing a password. Passwords can be stored as scram-sha-256, md5, and password (plain text).
- Ident – Supported only for TCP / IP connections. It works by obtaining the username of the client’s operating system, with optional mapping of usernames.
- Peer is the same as Ident, but it is only supported on local connections.
PostgreSQL client authentication is defined in a configuration file called pg_hba.conf. By default, PostgreSQL is configured for local connections to use a peer-to-peer authentication method.
The postgreSQL user is automatically created when the PostgreSQL server is installed. This user is the superuser for the PostgreSQL instance. This is equivalent to the MySQL root user.
To log into the PostgreSQL server as the postgres user, first switch to the user and then open the PostgreSQL prompt using the psql utility:
sudo su - postgres psql
From here you can interact with the PostgreSQL instance. To exit the PostgreSQL shell, enter:
You can also access the PostgreSQL command line without switching sudo users with this command:
sudo -u postgres psql
Usually the postgres user is only used from localhost.
Creating a PostgreSQL Role and Database
Only superusers and roles with CREATEROLE privileges can create new roles.
In the following example, we will create a new role named andreyex, a database named andreyexdb, and grant database permissions.
- First, connect to PostgreSQL shell:
sudo -u postgres psql
- Create a new PostgreSQL role using the following thr command:
create role andreyex;
- Creating a new database:
create database andreyexdb;
- Grant the user access to the database by running the following query:
grant all privileges on database andreyexdb to andreyex;
Enable remote access to PostgreSQL server
By default, PostgreSQL server only listens on local interface 127.0.0.1.
To enable remote access to the PostgreSQL server, open the configuration file:
sudo nano /var/lib/pgsql/data/postgresql.conf
Scroll down to the CONNECTIONS AND AUTHENTICATION section and add / edit the following line:
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses="*" # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service with:
sudo systemctl restart postgresql
Check the changes with the ss utility:
ss -nlt | grep 5432
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
The output above shows that the PostgreSQL server is listening on a default port on all interfaces (0.0.0.0).
The final step is to configure the server to accept remote connections by editing the pg_hba.conf file.
Below are some examples showing different use cases:
# TYPE DATABASE USER ADDRESS METHOD # The user jane can access all databases from all locations using an md5 password host all jane 0.0.0.0/0 md5 # The user jane can access only the janedb database from all locations using an md5 password host janedb jane 0.0.0.0/0 md5 # The user jane can access all databases from a trusted location (192.168.1.134) without a password host all jane 192.168.1.134 trust
CentOS 8 provides two versions of PostgreSQL: 9.6 and 10.0.
For more information on this matter visit the PostgreSQL documentation (https://www.postgresql.org/docs/10.0/static/index.html).
If you encounter a problem or have feedback, leave a comment below.