How to install PostgreSQL and phpPgAdmin Fedora 34

How to install PostgreSQL and phpPgAdmin 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 to safely store and expand complex data workloads. phpPgAdmin is a PHP-based application for managing and editing PostgreSQL databases.

This tutorial will demonstrate how to install PostgreSQL and phpPgAdmin on one machine Fedora Based on 34 servers.

prerequisites

  1. A sort of Fedora Based on 34 servers.

  2. Keep the system updated.

    $ sudo dnf update
    
  3. A non-root user sudo privilege.

Step 1-Install PostgreSQL

Fedora 34 Comes with different versions of PostgreSQL, available from its repository. You can choose between these versions by enabling the appropriate modules (collections of packages and dependencies).

First, check the available streams postgresql Module usage dnf Order.

$ 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 Enable streaming.

Next, install the PostgreSQL server.

$ sudo dnf install postgresql-server

You may want to install 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 managed by a single server instance. Creating a cluster will create a directory where the database will be placed. This whole process is also similar to initializing PostgreSQL.

This process produces template1 with postgres database.this template1 database is a template used by PostgreSQL to create a new database.this postgres database is the default database used by users, utilities, and third-party applications.

To create a 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:

  • Believe -A role that can be connected without a password, as long as the conditions defined in are met /var/lib/pgsql/pg_hba.conf meet.
  • password -A role that uses a password to connect.The password can be stored as md5, scram-sha-256 And plaintext.
  • identity -It requires the client’s operating system username and is only supported on TCP/IP connections.
  • Peer -Same as Ident, but only supports local host connection.

Postgres uses peer authentication by default, which means it associates Postgres roles with matching Linux user accounts. If there is a role in Postgres, a Linux user name with the same name can log in as the role.The details of client authentication are stored in /var/lib/pgsql/pg_hba.conf document.

The installation process of Postgres creates a user account named postgres Associated with the default Postgres role.

Switch to postgres account.

$ sudo -i -u postgres

You can use the following command to access the Postgres prompt.

$ psql

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

postgres-# q

It will bring you back to the Linux account of the Postgres account.To log out back to your own user account, type exit At the prompt.

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

$ sudo -u postgres psql

To exit the prompt, type:

postgres-# q

Step 4-Create a new role and database.

You can go directly from bash Shell or psql shell.For our purposes, we will use psql shell, because it can complete work faster.

First, connect to psql shell.

$ sudo -u postgres psql

Create a new PostgreSQL role with super user, database creation, role creation and login permissions.This user will be used to log in further in the tutorial phpPgAdmin.

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

You can use the following command to check all available users.

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 permissions to the user of the database we just created by running the following query.

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

You can use the following command to check all available databases.

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 the PostgreSQL server

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

$ 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;

Press save file Ctrl + X And enter Yes When prompted.

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

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

You can access remote connections in many ways. You can limit remote connections to a single user or a single database or all users, and limit connections to each IP address or 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, and the authentication method listed for them is ident. The value you will need to change ident arrive md5 If you want to log in through the web interface.

Add the required configuration and press save file Ctrl + X And enter Yes When prompted.

Restart the PostgreSQL server for the changes to take effect.

$ sudo systemctl restart postgresql

Verify changes ss Utilities.

$ ss -nlt | grep 5432
LISTEN   0    128    0.0.0.0:5432    0.0.0.0:*       
LISTEN   0    128    [::]:5432      [::]:*  

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

Step 6-Install phpPgAdmin, Nginx and PHP

phpPgAdmin usually comes with PostgreSQL or Fedora repository but not in Fedora 34. Therefore, you need to install it manually. In addition to phpPgAdmin, you also need to install Nginx web server and PHP software.

Download the latest version from phpPgAdmin Github release pageAt 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 is located.

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

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

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

Install Nginx server and PHP and appropriate PHP modules.

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

Configuration 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 a file /etc/php-fpm.d/www.conf Edit use nano editor.

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

We need to set the Unix user/PHP process group to nginxFind out user=apache with group=apache And change the lines in the file 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
...

Press save file Ctrl + X And enter Yes 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 into 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;
  }
}

according to Ctrl + X arrive close Editors and news Yes When prompted to save the file.

This document assumes that we will host phppgadmin.example.com In the catalog /var/www/html/phppgadmin.

Test the Nginx configuration.

$ sudo nginx -t

You should see the following output, indicating that 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

emission https://phppgadmin.example.com In your browser, you should see the default phpPgAdmin page in your browser.

Click on PostgreSQL Link 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 use Let’s Encrypt SSL service to enable SSL. To do this, install the Certbot tool.

$ sudo dnf install certbot-nginx

Generate an SSL certificate.

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

You will be asked to provide your email address and agree to the terms of service. When HTTPS access is required, select a 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 automatic renewal

Open the Crontab editor.

$ EDITOR=nano sudo crontab -e

Paste the following line at the bottom.

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

this 25 2 * * * Part of this line means “Run the following command at 2:25 AM every day”. You can choose at any time.

Press save file Ctrl + X And enter Yes When prompted.

Your SSL certificate will be automatically renewed.

in conclusion

This concludes our tutorial on how to install PostgreSQL and phpPgAdmin on your computer. Fedora Based on 34 servers. If you have any questions, please raise them in the comments below.