Just as MariaDB requires the mariadb-client package to connect other hosts to a MariaDB database, PostgreSQL also requires the postgresql-client package to connect other hosts to a PostgreSQL database.

Problem

How to install postgresql-client on Linux?

Solution

Below is the command to install postgresql-client on some Linux distributions:

Ubuntu/Debian

sudo apt update
sudo apt install postgresql-client

RockyLinux/AlmaLinux/RHEL/CentOS

sudo yum install postgresql-client

Then run the command below to see the installed PostgreSQL version:

psql --version

As you can see in the image above, the version of postgresql-client that you installed is version 16.10. But you should know that, usually by default, the package provided by the distro is a stable old version and not the latest stable version. Therefore, if you want the mariadb-client package to use the latest stable version, use the command below if you use a Ubuntu/Debian distro:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Then run the command below to install the latest version of postgresql-client (The last version of postgresql in November 2025 is version 18.0):

sudo apt install postgresql-client-18

If your distro is using RockyLinux/AlmaLinux/RHEL version 10, use the command below to upgrade the postgresql-client package to the latest version:

sudo dnf remove -y postgresql
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf clean all
sudo dnf makecache
sudo dnf install -y postgresql18

Note

If you want to know how to access the MariaDB database from another host, you can go to this article.

References

postgresql.org
docs.risingwave.com
dewanahmed.com

The previous article already explained how to access a MariaDB database from localhost. This article will explain how to access the MariaDB database from another host.

Problem

How to access a MariaDB database from another host?

Solution

To access the MariaDB database, you can follow the format below:

mariadb -h your_server_ip -u username -P port_number -p

By default, the MariaDB database uses port 3306, so if your MariaDB database uses port 3306, then there is no need to write the port when executing the command to access the MariaDB database. I have a MariaDB database server with an IP of 192.168.56.101 and an Ubuntu server with an IP of 192.168.56.11. I want to access the MariaDB database via the Ubuntu server using the user john and run the command:

mariadb -h 192.168.56.101 -u john -p

But I got an error like below:

ERROR 2002 (HY000): Can’t connect to server on ‘192.168.56.101’ (115)

Below are the steps to access the MariaDB database:

1. Open the port

Open port 3306 on both servers. If you use RockyLinux/AlmaLinux/RHEL for your servers, use the command below:

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

But if you use Ubuntu/Debian for your server, type the command below:

sudo ufw allow 3306

2. Check the MariaDB version

You should know that to access the MariaDB database, you need a mariadb-client package whose version is the same as the MariaDB database version. If the mariadb-client version is different from the mariadb database version, then there will usually be an error. To install the mariadb-client package, you can go to this page.

3. Grant access

On the database server, run a query with the format below:

GRANT option ON db_name.* TO username@"ip_address" IDENTIFIED BY "password";

For example, if user john wants to access the entire MariaDB database from the host with IP 192.168.56.11, then use the query below:

GRANT ALL ON *.* TO john@"192.168.56.11" IDENTIFIED BY "123456";

GRANT ALL ON zabbix.* TO john@"192.168.56.101" IDENTIFIED BY "qwerty";

GRANT SELECT ON zabbix.* TO john@"192.168.56.101" IDENTIFIED BY "qwerty";

4. Configure the file

Go to the file /etc/mysql/mariadb.conf.d/50-server.cnf and change the bind-address item to be as below:

bind-address             = 0.0.0.0

[mysqld]
bind-address = 0.0.0.0

5. Restart MariaDB

After that, restart the MariaDB service using the command below:

sudo systemctl restart mariadb

Then, try to access it again, and you should be able to access the database like in the picture below:

Note

If you want your MariaDB database to only be accessed from a certain IP, for example, IP 192.168.56.11, then use the command below if your distro uses Ubuntu/Debian:

sudo ufw allow from 192.168.56.11 to any port 3306

But, if you use RockyLinux/AlmaLinux/RHEL, type the below command to open port 3306 only from IP 192.168.56.11

firewall-cmd --zone=public --add-rich-rule 'rule family=ipv4 source address=192.168.56.11 port port=3306 protocol=tcp accept'
firewall-cmd --reload
firewall-cmd --list-rich-rules

References

mariadb.com
tencentcloud.com
webdock.io

If you want to access MariaDB databases from other hosts, then your device must have a mariadb-client package.

Problem

How to install the mariadb-client in Linux?

Solution

Below is the command to install mariadb-client on some Linux distros:

Ubuntu/Debian

sudo apt update
sudo apt install mariadb-client

RockyLinux/AlmaLinux/RHEL

sudo yum install mariadb-client-*

Use the command below to see the version of mariadb-client after you installed the package:

mariadb --version

As you can see in the image above, the version of mariadb-client that you installed is version 10.11.3. But you should know that, usually by default, the package provided by the distro is a stable old version and not the latest stable version. Therefore, if you want the mariadb-client package to use the latest stable version, use the command below:

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

After that, reinstall the package using the command above (sudo apt install mariadb-client or sudo yum install mariadb-client-*), and your MariaDB version should be the latest.

Note

If you want to know how to access the MariaDB database from another host, you can go to this article.

References

bytebase.com
simplified.guide

I want to access a PostgreSQL database from another host.

Problem

How to access a PostgreSQL database from another host?

Solution

I have 2 servers, the PostgreSQL server IP is 192.168.56.101, and the Ubuntu server IP is 192.168.56.11. I want to access the PostgreSQL database from the Ubuntu server. By default, use the format below if you want to access PostgreSQL:

psql -h ip_db -u username -d db_name -p port_number

By default, the PostgreSQL database uses port 5432, so if you access your PostgreSQL using port 5432, you don’t need to write the port. I accessed my PostgreSQL by writing the command:

psql -h 192.168.56.101 -u postgres -d mydb

But I got an error like the image below:

psql: error: connection to server at ‘192.168.56.101’, port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?

The following are the steps to access a PostgreSQL database from another host:

1. Open port

Open port 5432 on each server. If you use RockyLinux/AlmaLinux/RHEL for your servers, use the command below:

firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload

But if you use Ubuntu/Debian for your server, type the command below:

sudo ufw allow 5432

2. Config postgresql.conf

In the database server, go to /etc/postgresql/18/main/postgresql.conf file if you use PostgreSQL 18, but back up the file first:

sudo cp /etc/postgresql/18/main/postgresql.conf /etc/postgresql/18/main/postgresql.conf.ori 

After that, change the script below in the file:

#listen_addresses = ‘localhost’

to

listen_addresses = ‘*’

3. Configure pg_hba.conf

After that, go to /etc/postgresql/18/main/pg_hba.conf if you use PostgreSQL 18, but backup the file first:

sudo cp /etc/postgresql/18/main/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf.ori

Then, add your IP host (in this case, IP is 192.168.56.11) like the below script:

host    all             all             192.168.56.11/32        scram-sha-256

4. Restart PostgreSQL

Restart postgresql service using the command below:

sudo systemctl restart postgresql

Now, try to access the PostgreSQL again, and you should be able to access the database like the command below:

Note

If you have an error when you want to display data, like the image below:

ERROR: permission denied for table employee

Then you have to change the owner of the database and the tables in the database. You can follow how to change it via this page.

References

dev.to
dba.stackexchange.com
prisma.io
tigerdata.com

Ownership of a database and the tables in it in PostgreSQL is very crucial because this has a big influence on users who want to carry out CRUD (Create, Read, Update, Delete) activities in a PostgreSQL database. If the user is not the owner of the database and the tables in it, then the user will not be able to perform CRUD.

Problem

How to change ownership in PostgreSQL?

Solution

For example, I have a mydb database and mydb_user as a user. When I access the database using the user mydb_user and perform a query:

select * from employee;

There is an error as shown below:

ERROR: permission denied for table employee

After I searched on the internet, it turned out that this was caused by the user mydb_user not being the owner of the mydb database and the tables in it. To see the owner of the database, you can use the query below:

SELECT datname AS database_name,
       pg_catalog.pg_get_userbyid(datdba) AS owner
FROM pg_database;

You can see in the picture above, the postgres user is the owner of the mydb database. This is because when creating the database uses the postgres user and not the mydb_user.

SELECT d.datname AS database_name, r.rolname AS owner
FROM pg_database d
JOIN pg_roles r ON d.datdba = r.oid
WHERE d.datname = 'your_database_name';

\l

Similarly, if you want to see the owners of the tables in the database (but you must first log in to that database to run this query), use the query below:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public';

Then there will be a display like the following:

You can see in the image above that the owner of the table employee in the mydb database is a postgres user. This is because when creating the tables uses user postgres and user mydb.

SELECT 
    schemaname,
    tablename,
    tableowner
FROM pg_tables
ORDER BY schemaname, tablename;

A. Change database owner

If you want to change ownership of the mydb database from user postgres to user mydb_user, then you have to log in to PostgreSQL as an existing user, which in the case of a postgres user:

ALTER DATABASE mydb OWNER to mydb_user;

Run the query below to check ownership of the database:

\l

You can see that the owner of the mydb database has changed to user mydb_user. But even so, you still can’t run CRUD on the database because the tables in the database still belong to the postgres user.

B. Change the owner of the table

Still using the postgres user, log in to the mydb database using the command:

\c mydb;

Then run the query below to change the table, sequence, and view to mydb_user:

DO $$
DECLARE
    r RECORD;
BEGIN
    -- Change the owner of all tables
    FOR r IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I OWNER TO mydb_user;', r.schemaname, r.tablename);
    END LOOP;

    -- Change the owner of all sequences
    FOR r IN SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public'
    LOOP
        EXECUTE format('ALTER SEQUENCE %I.%I OWNER TO mydb_user;', r.sequence_schema, r.sequence_name);
    END LOOP;

    -- Change the owner of all views
    FOR r IN SELECT table_schema, table_name FROM information_schema.views WHERE table_schema = 'public'
    LOOP
        EXECUTE format('ALTER VIEW %I.%I OWNER TO mydb_user;', r.table_schema, r.table_name);
    END LOOP;
END $$;

After that, run the command below to view the ownership of the tables in the mydb database:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname="public";

From the image above, you can see the owner of the tables in the mydb database is not a postgres user but a mydb_user. So, you should be able to do CRUD using the mydb_user user as shown in the image below:

Note

As a reminder, to create databases and tables in the database, you should not use postgres user but create a new user because it is very dangerous to use postgres user to carry out daily activities in the PostgreSQL database.

References

commandprompt.com
stackoverflow.com
w3resource.com

As a sysadmin, you need a tool to monitor the MariaDB database, and one of the tools you can use is mytop.

Problem

How to install mytop?

Solution

Mytop is an open-source utility developed by Jeremy Zawodny with Perl for real-time monitoring of MySQL/MariaDB databases. To install this tool, use the commands below:

wget https://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
tar -zxvf mytop-1.6.tar.gz 
cd mytop-1.6/
perl Makefile.PL 
make
make test
sudo make install

To run this tool, use the format below:

mytop --prompt -d db_name

For example, if you want to monitor the Zabbix database, then use the command below:

mytop --prompt -d zabbix

mytop --prompt -u zabbix_user -d zabbix

By default, Mytop uses the root user to enter the database, then enter the password from the root user, and you should see a display like the one below:

You see from the image above, it looks like the top command in Linux. The following is a brief explanation of what the image above looks like:

• The first line shows the version of the MariaDB Database and the uptime of the server.
• The second line shows the number of queries that have been processed on the server (Queries), the average number of queries per second (qps), the number of slow queries (Slow), and the percentage of Select, Insert, Update, and Delete queries (Se/In/Up/De(%)).
• The third line shows the current value since the last mytop refresh, which defaults to 5 seconds. The first field is the number of queries per second. The second value is the number of slow queries per second. The threads segment indicates there are a total of 32 connected threads, 1 is active (the others are sleeping), and there are 0 threads in the thread cache. The last field in the third line shows the query percentages, like in the previous line, but since last mytop refresh.
• The fourth line shows crucial buffer efficiency (the frequency of key reads from the buffer instead of the disk) and the total number of bytes that MySQL has both sent and received, including data from the last mytop cycle. Key Efficiency: 100.0% indicates that all keys are read from the buffer, not from the disk. Bps in/out: 0.1/5.4 shows that since startup, MySQL has averaged 0.1kbps of inbound traffic and 5.4kbps for outbound traffic. Now in/out shows the traffic again, but since last mytop refresh.
• The next line up to the last line shows a list of current MySQL threads, sorted according to their idle time (least idle first).

If you want to see more details about a query, then you can press the f button, and you will see a display like the one below:

Select the ID number for which you want to display the query in detail, for example, number 65, then type number 65 and press the Enter button, and then there will be a display like the one below:

If you want to see an explanation of a query, then type the e button as in the image below, then there will be a display like the one below:

Type the ID number you want to explain, and after that, press the Enter key. If you want to see the command view, press the c button, then there will be a display like below:

The Command column displays the type of command or query that was executed, and the following is a brief explanation:

• – The Total column represents the overall count of that type of command executed since the server began.
• – The Last column indicates how many of that command type were executed since the last mytop refresh
• – The Pct column indicates the equivalent percentage.

If you want to exit the mytop application, press the q button. If you want to enter the mytop application without having to type a password, you can create a .mytop file. Type the command:

vi ~/.mytop

After that, copy the script below, assuming the Zabbix database that you want to monitor uses the mytop application:

host=localhost
db=zabbix
user=root
pass=qwerty
delay=5
port=3306
socket=
batchmode=0
color=1
idle=1

After that, type the mytop command, and the mytop application should immediately display the mytop application without you having to write arguments and passwords, as in the image below:

You should only make a user a viewer if you are afraid that others will see your database password.

Note

Regrettably, this application is no longer under development, and the final version available is 1.6 from 2007. Nonetheless, after I use this tool in November 2025, it remains effective for monitoring MySQL/MariaDB databases.

References

jeremy.zawodny.com
digitalocean.com
tecmint.com
whplus.com
geeksforgeeks.org

By default, if you want to access the MariaDB database, then you have to access it using the CLI. However, this method is a bit difficult, especially for those who are not used to the CLI. So you need an application that can display the database in the GUI, and you can interact with the database using the application. One application that is often used is the PHPMyAdmin application.

Problem

How to install PHPMyAdmin on Linux?

Solution

phpMyAdmin is an open-source, free MySQL and MariaDB administration tool, and as of this writing (November 2025), this application has version 5.2.3. This application is a web application and is written using PHP. Before installing the PHPMyAdmin application, you must have installed the database on your Linux server, and you must have provided a password for the database. Here is how to install the PHPMyAdmin application on Linux, based on the distro:

Ubuntu/Debian

sudo apt update
sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl

RockyLinux/AlmaLinux/CentOS

yum install httpd
yum install php-phpmysql

OpenSUSE

sudo zypper install apache2
sudo zypper install php7 php7-mysql apache2-mod_php7 phpMyAdmin

This article will install PHPMyAdmin on Ubuntu 24.04 and run the command below:

sudo apt update
sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl

During installation, a pop-up will appear as below:

Select the web server used, and I use the Apache web server, and click OK. Then there will be another pop-up like below:

Choose Yes, and the installation will continue, but after that, you must enter the password for phpadmin as in the image below:

Enter your password, click Ok, and then there will be an email confirmation as in the image below:

Enter your password again, click Ok, and the installation process will continue. And you have to wait until the installation process is complete. After that, open your browser and type the URL below:

http;//your_ip_server/phpmyadmin

There should be a display like the image below:

If there is no display like the one above, it looks like you have to open port 80 on your server. Enter your username and password in your MySQL/MariaDB Database, and if there are no errors, then there will be a display like in the image below:

To see all the databases in your database, click Database as shown in the image below:

You can also perform queries in the SQL section, as in the image below:

And you can see the history of queries that you have done in PHPMyAdmin in the console section, as in the image below:

And in the console section, you can also perform queries by pressing Ctrl+Enter after you write the query. In fact, this application also provides an advisor feature to provide recommendations for your MariaDB database, as shown in the image below:

Note

It is highly recommended to restrict access to the PHPMyAdmin application. Therefore, this application should only be allowed on certain IPs, for example, office IPs. And you can make these restrictions in the /etc/apache2/conf-available/phpmyadmin.conf file in the /usr/share/phpmyadmin section. For example, if you want only IP 192.168.56.1 to be able to access this application, then add the script below:

# Limit IP
    Order Deny,Allow
    Deny from All
    Allow from sysadminpedia.com
    Allow from 192.168.56.1
    Allow from 127.0.0.1

After that, restart your web server. Now you can’t open the phpMyAdmin application except at the IP you have specified in the phpmyadmin.conf file

References

en.wikipedia.org
digitalocean.com
tutorialspoint.com
synaptica.info
linuxblog.io