How to Install PostgreSQL and phpPgAdmin on Fedora 34

PostgreSQL or Postgres is a powerful, open-source, relational database management system that uses and extends the SQL language. It has many advanced features that safely store and scale complicated data workloads. phpPgAdmin is a PHP-based application to manage and edit PostgreSQL databases.

This tutorial will demonstrate how to install PostgreSQL and phpPgAdmin on a Fedora 34 based server.

Prerequisites

  1. A Fedora 34 based server.

  2. Keep your systems updated.

    $ sudo dnf update

  3. A non-root user with sudo privileges.

Step 1 - Install PostgreSQL

Fedora 34 comes with different versions of PostgreSQL available from their repository. You can choose between these versions by enabling the appropriate module, which is a collection of packages and dependencies.

First, check the available streams for the postgresql module using the dnf command.

$ dnf module list postgresql
Name                        Stream                  Profiles                           Summary
postgresql                  9.6                     client, server [d]                 PostgreSQL module
postgresql                  10                      client, server [d]                 PostgreSQL module
postgresql                  11                      client, server [d]                 PostgreSQL module
postgresql                  12                      client, server                     PostgreSQL module
postgresql                  13                      client, server                     PostgreSQL module

To install PostgreSQL, we need to enable the corresponding module stream.

$ sudo dnf module enable postgresql:13

When prompted, enter Y to enable the stream.

Next, install the PostgreSQL server.

$ sudo dnf install postgresql-server

You may want to install the contrib package, which adds several additional features to the PostgreSQL database system.

$ sudo dnf install postgresql-contrib

Now that the server is installed, we need to create a new user.

Step 2 - Create a new PostgreSQL Database Cluster

First, you need to create a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance. Creating a cluster will create directories in which the database will be placed. This whole process is also akin to initializing PostgreSQL.

This process creates  template1 and postgres databases. The template1 database is a template used by PostgreSQL to create new databases. The postgres database is the default database for use by users, utilities, and third-party applications.

To create the database cluster to initialize PostgreSQL, use the following command.

$ sudo postgresql-setup --initdb

Now, start the PostgreSQL service.

$ sudo systemctl start postgresql

Enable the PostgreSQL service.

$ sudo systemctl enable postgresql

Now that PostgreSQL is up and running, we can create new users and databases and start using them.

Step 3 - PostgreSQL Roles and Authentication Methods

Postgres uses the concept of "roles" to handle authentication and permissions. PostgreSQL supports multiple authentication methods. The most commonly used methods are:

  • Trust - A role that can connect without a password, as long as the conditions defined in /var/lib/pgsql/pg_hba.conf are met.
  • Password - A role that uses the password to connect. Passwords can be stored as md5, scram-sha-256 and clear-text.
  • Ident - It requires the client's OS username and is only supported on TCP/IP connections.
  • Peer - Same as Ident but supported only on localhost connections.

Postgres uses peer authentication by default, which means it associates Postgres roles with matching a Linux user account. If a role exists inside Postgres, a Linux username with the same name can sign in as that role. The details of the client authentication are stored in the /var/lib/pgsql/pg_hba.conf file.

The installation process of Postgres creates a user account called postgres that is associated with the default Postgres role.

Switch to the postgres account.

$ sudo -i -u postgres

You can access the Postgres prompt using the following command.

$ psql

This will log you into the PostgreSQL prompt, where you can access and use databases via various commands. You can exit the prompt by typing:

postgres-# \q

It will bring you back to the Postgres account's Linux account. To exit back to your own user account, type exit at the prompt.

You can also access Postgres prompt without switching accounts by typing the following command.

$ sudo -u postgres psql

To exit the prompt, type:

postgres-# \q

Step 4 - Creating new roles and databases.

You can create roles and databases directly from the bash shell or the psql shell. For our purposes, we will use the psql shell since it does the job faster.

First, connect to the psql shell.

$ sudo -u postgres psql

Create a new PostgreSQL role with permissions like Superuser, Create database, Create role, and Login. This user will be used further in the tutorial for logging into phpPgAdmin.

postgres-# CREATE ROLE username WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'yourpassword';

You can check all the available users with the following command.

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 username  | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Create a new database.

postgres-# CREATE DATABASE userdb;

Grant privileges to the user on the database we just created by running the following query.

postgres-# GRANT ALL PRIVILEGES ON DATABASE userdb TO username;

You can check all the available databases with the following command.

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 userdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | navjot=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Step 5 - Enable remote access to PostgreSQL server

By default, the PostgreSQL server only listens on the localhost interface. To enable remote access to your PostgreSQL server, open the postgresql.conf file.

$ sudo nano /var/lib/pgsql/data/postgresql.conf

Scroll down to the CONNECTIONS AND AUTHENTICATION section and change/edit the following line by removing the hash symbol.

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

Change the line to the following.

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

Save the file by pressing Ctrl + X and entering Y when prompted.

The next step is to configure the server to accept remote connections by editing the pg_hba.conf file.

$ sudo nano /var/lib/pgsql/data/pg_hba.conf

There are various ways you can give access to remote connections. You can either restrict remote connections to a single user or a single database or all of them and restrict the connections to either every IP address or a trusted location.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane can access all databases from all locations using an md5 password
host    all             john            0.0.0.0/0                md5

# The user jane can access only the janedb database from all locations using an md5 password
host    johndb          john            0.0.0.0/0                md5

# The user jane can access all databases from a trusted location (192.168.1.110) without a password
host    all             john            192.168.1.110            trust

There are other entries in the file as well, and the authentication method listed for them is ident. You will need to change the value of ident to md5 if you want to log in via the web interface.

Add the required configuration and then save the file by pressing Ctrl + X and entering Y when prompted.

Restart the PostgreSQL server for the change to take effect.

$ sudo systemctl restart postgresql

Verify 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      [::]:*

This shows that the PostgreSQL server is listening on the default port on all interfaces.

Step 6 - Install phpPgAdmin, Nginx, and PHP

phpPgAdmin usually ships with PostgreSQL's or Fedora's repository but not in Fedora 34. Therefore, you will need to install it manually. Along with phpPgAdmin, you also need to install the Nginx web server and PHP software.

Download the latest version of phpPgAdmin from the Github releases page. At the time of writing this tutorial, the latest version available is 7.13.0.

$ wget https://github.com/phppgadmin/phppgadmin/releases/download/REL_7-13-0/phpPgAdmin-7.13.0.tar.gz

Extract the downloaded archive.

$ tar -zxvf phpPgAdmin-7.13.0.tar.gz

Create the directory where your site will live.

$ sudo mkdir /var/www/phppgadmin/html -p

Move the extracted files to /var/www/phppgadmin/html directory.

$ sudo mv phpPgAdmin-7.13.0/ /var/www/phppgadmin/html

Install Nginx server and PHP along with appropriate PHP modules.

$  sudo dnf install nginx php-fpm php-cli php-pgsql php-mbstring

Configure the Fedora firewall.

$ sudo firewall-cmd --permanent --add-service=http
$ sudo firewall-cmd --permanent --add-service=https

Reload the firewall to activate the rules.

$ sudo firewall-cmd --reload

Configure PHP

Open the file /etc/php-fpm.d/www.conf for editing using nano editor.

$ sudo nano /etc/php-fpm.d/www.conf

We need to set the Unix user/group of PHP processes to nginx. Find the user=apache and group=apache lines in the file and change them to nginx.

...
; Unix user/group of processes
; Note: The user is mandatory. If the group is not set, the default user's group
;       will be used.
; RPM: apache user chosen to provide access to the same directories as httpd
user = nginx
; RPM: Keep a group allowed to write in log dir.
group = nginx
...

Save the file by pressing Ctrl + X and entering Y when prompted.

Restart the PHP-fpm process.

$ sudo systemctl restart php-fpm

Configure Nginx

Run the following command to add a configuration file for your site.

$ sudo nano /etc/nginx/conf.d/phppgadmin.conf

Paste the following code in the editor.

server {
  listen          *:80;
  server_name     phppgadmin.example.com;
  root            /var/www/phppgadmin/html;
  index           index.php;

  location / {
    try_files   $uri $uri/ =404;
  }
    
  access_log /var/log/nginx/phppgadmin.access.log;
  error_log /var/log/nginx/phppgadmin.error.log;

  location ~ \.php$ {
    try_files $uri =404;
    fastcgi_pass  unix:/run/php-fpm/www.sock;
    fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    fastcgi_index index.php;
    include  fastcgi_params;
  }
}

Press Ctrl + X to close the editor and press Y when prompted to save the file.

This file assumes that we will be hosting phppgadmin.example.com in the directory /var/www/html/phppgadmin.

Test the Nginx configuration.

$ sudo nginx -t

You should see the following output indicating your configuration is correct.

nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

Start and enable the Nginx service.

$ sudo systemctl start nginx
$ sudo systemctl enable nginx

Launch http://phppgadmin.example.com in your browser, and you should see the default phpPgAdmin page in your browser.

phpPgAdmin Homepage

Click on the PostgreSQL link on the left sidebar and enter the user and password you created earlier to log in and access the portal.

phpPgAdmin Dashboard

Step 7 - Install SSL (Optional)

We can enable SSL using the Let's Encrypt SSL service. For that, install the Certbot tool.

$ sudo dnf install certbot-nginx

Generate the SSL certificate.

$ sudo certbot --nginx -d phppgadmin.example.com

You will be asked for your email address and to agree to the terms of service. When asked for HTTPS access, choose the Secure method to redirect all requests from HTTP to HTTPS.

You should be able to access phpPgAdmin by typing https://phppgadmin.example.com in your browser.

Configure SSL for Auto-Renewal

Open the Crontab editor.

$ EDITOR=nano sudo crontab -e

Paste the following line at the bottom.

. . .
25 2 * * * /usr/bin/certbot renew --quiet

The 25 2 * * * part of this line means “run the following command at 2:25 am, every day”. You may choose any time.

Save the file by pressing Ctrl + X and entering Y when prompted.

Your SSL certificate will be automatically renewed.

Conclusion

This concludes our tutorial on how to install PostgreSQL and phpPgAdmin on a Fedora 34 based server. If you have any questions, post them in the comments below.

Share this page:

0 Comment(s)