Bitnesia
Web UI for Database Management

Part of a series

Linux 102: Server Administration with Ubuntu

Web UI for Database Management

Bitnesia • Aug 28, 2026 2 •ID

Four databases have been running on our lab server since Part V began: PostgreSQL in Chapter 18, MySQL in Chapter 19, MariaDB in Chapter 20, and MongoDB in Chapter 21. All of them are fully managed via the command line, ranging from psql, mysql/mariadb, to mongosh. This approach is sufficient for Sysadmins who are used to working in the terminal, but Developers who only need to check the contents of a single table or add a small index often feel that the command line is too slow for such a simple task, especially if that Developer has just joined and has not memorized the complete SQL syntax or MongoDB queries. That need is met by a web UI for database management, which is a browser-based application that wraps CRUD operations, user management, and raw query execution into a visual interface that is friendlier for daily use.

This chapter installs three web UIs simultaneously, each for a different database: phpMyAdmin for MySQL/MariaDB, pgAdmin 4 for PostgreSQL which this time runs in web mode instead of a desktop application, and mongo-express for MongoDB installed via npm following the Node.js conventions from Chapter 15. All three will be placed behind separate Nginx virtual hosts with their respective subdomains, following the pattern of adding subdomains to the example.local zone that we have practiced repeatedly since Chapter 12. This chapter concludes with a section that must not be missed, namely adding a reverse proxy layer and additional authentication. This is important because these three tools are essentially direct entry points to all data on the server, making them very attractive targets for Attackers if left open without layered security.

22.1 phpMyAdmin for MySQL/MariaDB

phpMyAdmin is a PHP-based web UI for MySQL and MariaDB that has existed since the late 1990s and remains one of the most widely used database administration tools in the field, especially in shared hosting environments and control panels like cPanel. Because phpMyAdmin runs on top of PHP-FPM, which we prepared in Chapter 14, its installation does not require heavy new components; we simply reuse the existing infrastructure.

22.1.1 Installing phpMyAdmin from Ubuntu Repository

Ubuntu Server provides phpMyAdmin directly from its official repository as the phpmyadmin package, without needing to add third-party repositories. This package uses dbconfig-common to set up its own internal database, which stores additional features such as query bookmarks and search history.

Practical Steps

  1. Update the package list, then install phpMyAdmin.
    sudo apt update
sudo apt install phpmyadmin
  2. The installation process displays an interactive debconf dialog. On the first question, "Web server to reconfigure automatically", do not select anything because the available choices are only apache2 and lighttpd, whereas our main web server since Chapter 12 is Nginx, which is not listed. Press Tab then Enter to proceed without selecting any.
  3. On the next question, "Configure database for phpmyadmin with dbconfig-common?", agree by selecting yes. Since root for both MySQL and MariaDB has been locked via auth_socket/unix_socket since Section 19.2.2 and 20.1.2, dbconfig-common running with system root permissions can connect directly without needing any password, then automatically creates the phpmyadmin database along with a dedicated internal account to store this configuration in /etc/phpmyadmin/config-inc.php.
  4. Ensure the required PHP extensions are active. The php-mbstring extension and php-mysql driver should already be installed since Section 14.1.2, so this step is merely a confirmation.
    php -m | grep -Ei 'mbstring|mysqli'

Verification and Troubleshooting

  • The phpMyAdmin application files should already be available in /usr/share/phpmyadmin. Confirm with ls /usr/share/phpmyadmin; this directory will later serve as the document root for the Nginx virtual host in Section 22.1.2.
  • If the debconf dialog in step two was missed because the installation ran non-interactively, re-run sudo dpkg-reconfigure phpmyadmin to trigger all questions again.
  • The warning "The configuration file now needs a secret passphrase (blowfish_secret)" that sometimes appears on the login page means the line $cfg['blowfish_secret'] in /etc/phpmyadmin/config.inc.php is still empty. Fill it with a random string of at least 32 characters, then save it again.

22.1.2 Nginx Virtual Host and PHP-FPM Integration

This section connects phpMyAdmin to Nginx via the FastCGI pattern identical to Section 14.3.1, except that this time the document root directly points to the phpMyAdmin application folder instead of the existing example.local folder.

Practical Steps

  1. Add a new DNS record for the subdomain phpmyadmin.example.local, following the same pattern as the blog subdomain in Section 12.2.2 and app in Section 16.2.2.
    sudo nano /etc/bind/db.example.local
    phpmyadmin	IN	A	192.168.1.20
    Increment the Serial number in SOA, then validate and reload.
    sudo named-checkzone example.local /etc/bind/db.example.local
sudo systemctl reload bind9
  2. Create a new virtual host file.
    sudo nano /etc/nginx/sites-available/phpmyadmin.example.local
  3. Fill it with the following configuration, reusing the exact same PHP-FPM socket from Section 14.1.1.
    server {
    listen 80;
    server_name phpmyadmin.example.local;

    root /usr/share/phpmyadmin;
    index index.php;

    location / {
        try_files $uri $uri/ =404;
    }

    location ~ \.php$ {
        include snippets/fastcgi-php.conf;
        fastcgi_pass unix:/run/php/php8.4-fpm.sock;
    }
}
  4. Enable this virtual host, test the syntax, and apply changes.
    sudo ln -s /etc/nginx/sites-available/phpmyadmin.example.local /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl reload nginx

Verification and Troubleshooting

  • Test reachability first via curl from another computer on the lab network, sufficient to ensure Nginx is responding and forwarding to PHP-FPM correctly.
    curl -s http://phpmyadmin.example.local | grep -i title
    A healthy output displays the line <title>phpMyAdmin</title>.
  • Actual login testing still requires a real browser, because phpMyAdmin uses sessions and JavaScript that cannot be represented by curl. Open http://phpmyadmin.example.local from a browser on another computer within the same lab network.
  • A 502 Bad Gateway error here has the same cause as noted in Section 14.3.1: PHP-FPM is not active or the socket path is misspelled. Check with systemctl status php8.4-fpm.service.

22.1.3 Login and Access Verification to MySQL/MariaDB

In accordance with field notes in Section 19.2.2, accounts relying on auth_socket/unix_socket such as root cannot be used to log in through phpMyAdmin because its connection always uses TCP, not a Unix socket. The login process must use an account with a password, such as webapp_user created in Section 19.3.2 for MySQL or Section 20.3.1 for MariaDB.

Practical Steps

  1. Open http://phpmyadmin.example.local in a browser.
  2. Log in using the username webapp_user along with the password created in the previous chapter, depending on which instance is active on this lab server, whether MySQL from Chapter 19 or MariaDB from Chapter 20.
  3. After logging in successfully, select the webapp_db database in the left panel, open the SQL tab, and run a simple query to prove the connection works properly.
    SELECT DATABASE(), CURRENT_USER();

Verification and Troubleshooting

  • The query result must display webapp_db and webapp_user@localhost, identical to the command-line test in Section 19.3.2.
  • The message #1045 Cannot log in to the MySQL server on the login page usually means the password was mistyped, not a phpMyAdmin configuration issue itself, as phpMyAdmin simply passes credentials as-is to the database server.
  • Field note: never allow the root account to have a TCP password that can be used for phpMyAdmin login merely for convenience. A separate account with limited privileges like webapp_user remains the safest choice for routine daily access, following the least privilege principle applied since Chapter 18.

22.2 pgAdmin 4 for PostgreSQL (Web Mode)

pgAdmin 4 is the official administration tool recommended by the PostgreSQL community itself, available in two modes: desktop mode running as an Electron application on a Developer's laptop, and web mode installed on a server and accessed via browser by multiple users simultaneously. This section focuses on web mode, as the goal is to provide centralized access to the lab PostgreSQL server without requiring every Developer to install a separate desktop application.

22.2.1 Installing pgAdmin 4 Web Mode from Official Repository

pgAdmin 4 is not available in the official Ubuntu repository, so its installation requires adding the official repository owned by the pgAdmin project itself, following the same pattern as adding the MongoDB repository in Section 21.2.1.

Practical Steps

  1. Download and import the official pgAdmin public key.
    curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | \
  sudo gpg -o /usr/share/keyrings/packages-pgadmin-org.gpg --dearmor
  2. Add the repository to /etc/apt/sources.list.d/. The line below intentionally uses the codename noble (Ubuntu 24.04 LTS) rather than resolute for Ubuntu 26.04 that we are using, for the exact same reason as the MongoDB compatibility note in Section 21.5: third-party repositories take time to provide builds for every new Ubuntu release. Check https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/dists/ first to verify if resolute is available before following this example as-is.
    echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/noble pgadmin4 main" | \
  sudo tee /etc/apt/sources.list.d/pgadmin4.list
  3. Update the package list, then install only its web variant.
    sudo apt update
sudo apt install pgadmin4-web
    This package pulls libapache2-mod-wsgi-py3 as a dependency, because pgAdmin 4 web mode runs as a Python WSGI application executed directly by Apache, not by Nginx or PHP-FPM.

Verification and Troubleshooting

  • A 404 Not Found error during apt update pointing to path dists/noble/ on the ftp.postgresql.org domain means builds for that codename have been discontinued or mistyped, not a release compatibility issue. For such cases, recheck the supported codenames list at the URL in step two.
  • The apache2 package which was intentionally disabled (disabled, not removed) since the end of Chapter 14 will automatically be pulled as a dependency if it is not yet installed on this server.

22.2.2 Running setup-web.sh and Nginx Reverse Proxy

pgAdmin 4 web mode requires Apache to function properly, while Nginx still holds ports 80 and 443 as the main web server since Chapter 12. The solution is to run Apache on a local port unused by other applications, then let Nginx forward traffic to that port via reverse proxy, following the exact same upstream concept pattern from Section 16.2.2.

Practical Steps

  1. Change the port Apache listens on to avoid conflicts with Nginx. Change Listen 80 to a new local port.
    sudo nano /etc/apache2/ports.conf
    Listen 127.0.0.1:8082
  2. Match Apache's default virtual host to fit the new port.
    sudo nano /etc/apache2/sites-available/000-default.conf
    Change the opening line from <VirtualHost *:80> to the following.
    <VirtualHost 127.0.0.1:8082>
  3. Test configuration, then enable and start Apache.
    sudo apache2ctl configtest
sudo systemctl enable --now apache2
  4. Run the official pgAdmin 4 setup script for web mode.
    sudo /usr/pgadmin4/bin/setup-web.sh
    This script is interactive, asking for an email address and password for the initial pgAdmin administrator account, then offering automatic Apache configuration. Accept the Apache configuration offer, as we have prepared Apache specifically for this purpose.
  5. Create a new Nginx virtual host file to forward traffic to Apache.
    sudo nano /etc/nginx/sites-available/pgadmin.example.local
    server {
    listen 80;
    server_name pgadmin.example.local;

    location / {
        proxy_pass http://127.0.0.1:8082;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}
  6. Add a DNS record for pgadmin.example.local, following the same pattern as Section 22.1.2.
    sudo nano /etc/bind/db.example.local
    pgadmin	IN	A	192.168.1.20
    sudo named-checkzone example.local /etc/bind/db.example.local
sudo systemctl reload bind9
  7. Enable virtual host, test syntax, and apply changes.
    sudo ln -s /etc/nginx/sites-available/pgadmin.example.local /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl reload nginx

Verification and Troubleshooting

  • Ensure Apache is truly listening on loopback only, not all interfaces.
    ss -tlnp | grep 8082
    Correct output shows 127.0.0.1:8082, not 0.0.0.0:8082, because Apache here should only be accessed through Nginx, not directly from external networks.
  • Access the application via http://pgadmin.example.local/pgadmin4/, not the domain without suffix, because setup-web.sh installs the application by default at path /pgadmin4, not at the domain root.
  • A 502 Bad Gateway error from Nginx means Apache is not running or still listening on the old port. Confirm with systemctl status apache2 and repeat step one if necessary.

22.2.3 Login and Connecting PostgreSQL Server

pgAdmin becomes useful only after being registered to the PostgreSQL instance you wish to manage, as this application is essentially just an interface, not the database itself. Server registration is done once and saved permanently in the administrator account created in Section 22.2.2.

Practical Steps

  1. Open http://pgadmin.example.local/pgadmin4/ in a browser, then log in using the administrator email and password created during setup-web.sh in Section 22.2.2.
  2. Right-click on Servers in the left panel, select Register > Server, and fill the General tab with any name, for example Lab PostgreSQL.
  3. Switch to the Connection tab, fill Host name/address with 127.0.0.1, Port with 5432, Maintenance database with webapp_db, Username with webapp_user, and the password created in Section 18.3.1. This connection is automatically allowed by the rule host all all 127.0.0.1/32 scram-sha-256 present since initial installation in Section 18.2.1, without needing new rules in pg_hba.conf.
  4. Save, then navigate to Lab PostgreSQL > Databases > webapp_db > Schemas > public > Tables to ensure the notes table from Section 18.3.2 is visible with its data.

Verification and Troubleshooting

  • A connection refused message when saving a new server usually means PostgreSQL is not running on the same server as Apache/pgAdmin, check again with pg_lsclusters from Section 18.1.2.
  • The message password authentication failed for user "webapp_user" indicates a mistyped password when filling the connection form, not an issue with pgAdmin itself.
  • The built-in pgAdmin query tool (lightning icon in toolbar) can be used directly to run raw SQL such as SELECT * FROM notes;, useful for quick troubleshooting without switching to the psql terminal.

22.3 mongo-express for MongoDB

mongo-express is a Node.js and Express-based web UI for MongoDB, installed via npm instead of APT packages because it is distributed as a standard Node.js package. Unlike phpMyAdmin and pgAdmin 4 which are actively developed by large communities, mongo-express release cadence has been much slower in recent years. Check the latest available version before installing, and consider that this tool is best suited for internal or lab needs like this series, rather than being the sole MongoDB administration reliance in large-scale production environments.

22.3.1 Installation via npm and Environment Variable Configuration

This section installs mongo-express as a global package, following the habit of installing command-line tools via npm install -g introduced in Chapter 15, then sets up a dedicated system user to run it as a service.

Practical Steps

  1. Check the latest available version of mongo-express in the npm registry before installation, given its slow release cadence mentioned earlier.
    npm view mongo-express version
  2. Install mongo-express globally, using Node.js from NodeSource installed since Chapter 15.
    sudo npm install -g mongo-express
    If installation fails due to peer dependency conflicts with newer Node.js versions, retry with the following additional flag.
    sudo npm install -g mongo-express --legacy-peer-deps
  3. Note the binary location of this global installation because systemd requires a full path, just like the important note in Section 15.4.3 regarding ExecStart.
    which mongo-express
  4. Create a dedicated system user for this service, following the same least privilege principle as Section 5.2.2 and Section 15.4.3.
    sudo useradd --system --no-create-home --shell /usr/sbin/nologin mongoexpress

Verification and Troubleshooting

  • A healthy which mongo-express command returns a path like /usr/bin/mongo-express, matching the npm global bin location from NodeSource installation. Save this path as it will be used directly in Section 22.3.2.
  • An EACCES: permission denied error during npm install -g usually means the command was run without sudo, as the npm global directory from NodeSource installation is owned by root.

22.3.2 systemd Service and Nginx Reverse Proxy

mongo-express is configured purely via environment variables without separate configuration files needing edits, so all MongoDB connection settings and access credentials can be written directly into the systemd unit file.

Practical Steps

  1. Create a new unit file.
    sudo nano /etc/systemd/system/mongo-express.service
  2. Fill it with the following configuration. Replace the values of ME_CONFIG_MONGODB_ADMINPASSWORD and ME_CONFIG_BASICAUTH_PASSWORD with strong passwords of your own before practicing on a real server.
    [Unit]
Description=mongo-express Web UI for MongoDB
After=network-online.target mongod.service
Wants=network-online.target

[Service]
Type=simple
User=mongoexpress
Group=mongoexpress
Environment=ME_CONFIG_MONGODB_SERVER=127.0.0.1
Environment=ME_CONFIG_MONGODB_PORT=27017
Environment=ME_CONFIG_MONGODB_ADMINUSERNAME=admin
Environment=ME_CONFIG_MONGODB_ADMINPASSWORD=AdminMongoAman!2026
Environment=ME_CONFIG_MONGODB_AUTH_DATABASE=admin
Environment=ME_CONFIG_MONGODB_ENABLE_ADMIN=true
Environment=ME_CONFIG_SITE_HOST=127.0.0.1
Environment=ME_CONFIG_SITE_PORT=8081
Environment=ME_CONFIG_BASICAUTH_USERNAME=sysadmin
Environment=ME_CONFIG_BASICAUTH_PASSWORD=UbahDariDefault!2026
ExecStart=/usr/bin/mongo-express
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
    The values ME_CONFIG_MONGODB_ADMINUSERNAME and ME_CONFIG_MONGODB_ADMINPASSWORD refer directly to the admin account created in Section 21.4.1. The two ME_CONFIG_BASICAUTH_* variables set mongo-express's built-in HTTP Basic Authentication, the first layer before the additional layer we will install in Section 22.4. A critical field note: older mongo-express versions used default basic auth credentials admin/pass which are widely known and frequently targeted automatically by Attackers scanning the internet for unmanaged instances, so never leave these default values unchanged.
  3. Adjust the path in ExecStart if the output of which mongo-express in Section 22.3.1 was not exactly /usr/bin/mongo-express.
  4. Reload systemd, then enable and start the service.
    sudo systemctl daemon-reload
sudo systemctl enable --now mongo-express.service
  5. Create an Nginx virtual host file to forward traffic to mongo-express.
    sudo nano /etc/nginx/sites-available/mongoexpress.example.local
    server {
    listen 80;
    server_name mongoexpress.example.local;

    location / {
        proxy_pass http://127.0.0.1:8081;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}
  6. Add a DNS record for mongoexpress.example.local.
    sudo nano /etc/bind/db.example.local
    mongoexpress	IN	A	192.168.1.20
    sudo named-checkzone example.local /etc/bind/db.example.local
sudo systemctl reload bind9
  7. Enable virtual host, test syntax, and apply changes.
    sudo ln -s /etc/nginx/sites-available/mongoexpress.example.local /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl reload nginx

Verification and Troubleshooting

  • Check service logs if mongo-express fails to start.
    sudo journalctl -u mongo-express -n 50
  • Open http://mongoexpress.example.local in a browser. The browser will first prompt mongo-express's built-in HTTP Basic Auth login dialog, asking for username and password according to ME_CONFIG_BASICAUTH_USERNAME/ME_CONFIG_BASICAUTH_PASSWORD set in step two, before accessing the main view containing webapp_catalog database from Section 21.3.2.
  • The error MongoServerError: Authentication failed displayed in journalctl indicates that the MongoDB admin username and password combination is incorrect; verify against those created in Section 21.4.1.

22.4 Access Security: Reverse Proxy and Additional Authentication

All three web UIs above can now be accessed by anyone who knows the subdomain and is on a network that can reach port 80 of this server. This is a serious problem. phpMyAdmin, pgAdmin 4, and mongo-express each represent direct entry points to all data on the server. Furthermore, real-world internet security incident histories are filled with cases of database administration panels exposed without protection and exploited by Attackers within hours. The defense in depth principle requires adding a protection layer at the Nginx level, separate from each application's authentication, so that a single failed or bypassed layer does not grant full access immediately.

22.4.1 HTTP Basic Auth Layer with htpasswd

Adding HTTP Basic Authentication at the Nginx level means an Attacker must pass two sets of credentials before reaching the actual application login page: one from Nginx and another from the application itself (phpMyAdmin, pgAdmin 4, or mongo-express's built-in basic auth from Section 22.3.2).

Practical Steps

  1. Install apache2-utils to obtain the htpasswd command. This package is safe to install even if Apache itself is disabled, because htpasswd is a standalone utility that does not rely on a running Apache service.
    sudo apt install apache2-utils
  2. Create a new password file for the first Sysadmin, using the -c flag only once to create a new file.
    sudo htpasswd -c /etc/nginx/.htpasswd-dbadmin sysadmin
  3. Create a separate snippet file so the same rules can be reused across all three virtual hosts without rewriting identical lines, following the include convention used for snippets/fastcgi-php.conf since Section 14.3.1.
    sudo nano /etc/nginx/snippets/dbadmin-restrict.conf
    auth_basic "Restricted Area - Database Admin";
auth_basic_user_file /etc/nginx/.htpasswd-dbadmin;
  4. Include this snippet in all three virtual host files created in Sections 22.1.2, 22.2.2, and 22.3.2. Add the following line inside the server { } block of each file, for example for phpmyadmin.example.local.
    sudo nano /etc/nginx/sites-available/phpmyadmin.example.local
    server {
    listen 80;
    server_name phpmyadmin.example.local;

    include snippets/dbadmin-restrict.conf;

    root /usr/share/phpmyadmin;
    index index.php;
    ...
}
    Repeat the exact same include line for pgadmin.example.local and mongoexpress.example.local.
  5. Test syntax, then apply to all three virtual hosts at once.
    sudo nginx -t
sudo systemctl reload nginx

Verification and Troubleshooting

  • Access without credentials must be rejected with status 401 Unauthorized.
    curl -I http://phpmyadmin.example.local
  • Access with correct credentials must successfully pass this layer.
    curl -I -u sysadmin http://phpmyadmin.example.local
  • Add another user to the same password file without the -c flag, as that flag overwrites the entire existing file contents.
    sudo htpasswd /etc/nginx/.htpasswd-dbadmin developer

22.4.2 Restricting Access Based on Source IP

The second layer restricts access based on source IP address by evaluating rules sequentially from top to bottom, exactly like how pg_hba.conf works as discussed in Section 18.2.1. Note that this restriction cannot be done via UFW as in Section 18.4.2 or 19.4.1, because UFW filters by port, whereas these three subdomains share the exact same port 80 as example.local and other public domains that must remain open for general Visitors. Nginx, which knows which domain is requested via the Host header, is the only layer capable of distinguishing access per subdomain like this.

Practical Steps

  1. Add allow and deny rules to the same snippet file from Section 22.4.1, placed before the auth_basic directive.
    sudo nano /etc/nginx/snippets/dbadmin-restrict.conf
    allow 192.168.1.0/24;
deny all;

auth_basic "Restricted Area - Database Admin";
auth_basic_user_file /etc/nginx/.htpasswd-dbadmin;
  2. Test syntax, then apply changes.
    sudo nginx -t
sudo systemctl reload nginx

Verification and Troubleshooting

  • Access from IPs outside 192.168.1.0/24 must be directly rejected with status 403 Forbidden, even before being prompted for HTTP Basic Auth credentials, because allow/deny directives are evaluated prior to auth_basic.
  • The combination of allow/deny and auth_basic forms two complementary layers rather than replacements for each other. Allowed IPs are still required to input correct credentials, while valid credentials alone will be useless if the source IP is not included in the allow rule.
  • For comprehensive security, these three subdomains should also be protected by TLS using the same SAN self-signed certificate as Section 17.3.1, because HTTP Basic Auth traffic is merely Base64 encoded, not encrypted, making it readable by Attackers sniffing network traffic if not running over HTTPS.

At this point, the server runs three database web UIs side by side: phpMyAdmin for MySQL/MariaDB, pgAdmin 4 web mode for PostgreSQL, and mongo-express for MongoDB, each on separate subdomains protected by two simultaneous layers: HTTP Basic Auth and IP restriction, on top of each application's built-in authentication. Chapter 23 continues Part V with an equally crucial topic: database backup and restore, covering all four databases installed from Chapter 18 through Chapter 21.

Share

Previous

NoSQL with MongoDB

Next

Database Backup and Restore