How to Install PostgreSQL and phpPgAdmin on Debian

PhpPgAdmin is a free, open-source, web-based application that allows you to manage PostgreSQL databases via your web browser. It is written in PHP and helps beginners to manage PostgreSQL databases easily. It supports PostgreSQL version 9.2 or higher and can be installed on Linux, Mac OS X, and Windows operating systems.

In this article, we will show you how to install PhpPgAdmin on Debian 11.

Requirements

• A server running Debian 11.
• A root password is set up on the server.

Install PhpPgAdmin on Debian

By default, the PhpPgAdmin package is included in the standard Debian 11 repository. You can install it with the following command:

apt-get install postgresql postgresql-contrib phppgadmin -y

Once the installation is complete, start the PostgreSQL service and enable it so that it starts when the system reboots:

systemctl start postgresql
systemctl enable postgresql

You can check the status of PostgreSQL with the following command:

systemctl status postgresql

You should see the following output:

? postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2021-10-09 07:48:50 UTC; 41s ago
   Main PID: 2501 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2341)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

Oct 09 07:48:50 debian11 systemd[1]: Starting PostgreSQL RDBMS...
Oct 09 07:48:50 debian11 systemd[1]: Finished PostgreSQL RDBMS.

Create a new user in PostgreSQL

PostgreSQL uses roles to authenticate and authorize users. In this section, we will create a user with superuser rights.

To do this, log in to the PostgreSQL shell with the following command:

sudo -i -u postgres psql

Once you are logged in, you should get the following output:

psql (13.3 (Debian 13.3-1))
Type "help" for help.

postgres=# 

Next, create a new user named admin and set a password with the following command:

postgres=# CREATE ROLE admin WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'securepassword';

Now you can verify your created user with the following command:

postgres=# \du

You should see the following output:

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

Next, exit the PostgreSQL shell with the following command:

postgres=# exit

Configure Apache for PhpPgAdmin

By default, PhpPgAdmin can only be accessed from the local computer. If you want to access it from another location, you need to edit the PhpPgAdmin configuration file and make some changes.

nano /etc/apache2/conf-available/phppgadmin.conf

Find the following line:

Require local

And replace it with the following line:

Require all granted

Save and close the file and check Apache for a syntax error:

apachectl configtest

If everything is OK, you should get the following output:

Syntax OK

Finally, restart the Apache service to apply the changes:

systemctl restart apache2

You can also check the status of the Apache service with the following command:

systemctl status apache2

You should see the following output:

? apache2.service - The Apache HTTP Server
     Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2021-10-09 07:53:42 UTC; 6s ago
       Docs: https://httpd.apache.org/docs/2.4/
    Process: 13844 ExecStart=/usr/sbin/apachectl start (code=exited, status=0/SUCCESS)
   Main PID: 13849 (apache2)
      Tasks: 6 (limit: 2341)
     Memory: 12.7M
        CPU: 66ms
     CGroup: /system.slice/apache2.service
             ??13849 /usr/sbin/apache2 -k start
             ??13850 /usr/sbin/apache2 -k start
             ??13851 /usr/sbin/apache2 -k start
             ??13852 /usr/sbin/apache2 -k start
             ??13853 /usr/sbin/apache2 -k start
             ??13854 /usr/sbin/apache2 -k start

Oct 09 07:53:42 debian11 systemd[1]: Starting The Apache HTTP Server...

Accessing PhpPgAdmin

PhpPgAdmin is now installed and configured. Now you can access it via the URL http://your-server-ip/phppgadmin/ . You should see the following screen:

Click on the Server tab. You will see the PhpPgAdmin login screen:

Enter your PostgreSQL username and password and click the Login button. You will get the PhpPgAdmin dashboard on the following screen:

Conclusion

Congratulations! You have successfully installed PhpPgAdmin on Debian 11. You can easily create and manage the PostgreSQL database via the web-based interface.