Once you have installed the PostgreSQL database server, you may notice that the remote access mode is unavailable. This is a default setting implemented for effective security. But you might prefer to enable PostgreSQL remote access to the PostgreSQL database server so you can use it remotely from different locations, such as your house or workplace. So, how do you do it? Read on to find out all the key information on Plesk PostgreSQL remote access.
Plesk: What it is and how it works
Plesk and PostgreSQL go together beautifully. You may have heard of Plesk: it’s one of the U.S.’s and Europe’s biggest paid hosting platforms. Different editions are available, and Plesk is designed to support Windows as well as various editions of Linux. These include CentOS, Debian, Ubuntu, Cloud Linux and RedHat.
Database servers are required for Plesk, for the storage of its databases as well as those utilized by its various elements (such as the webmail service). Databases developed by hosting clients’ sites and APS apps (e.g. WordPress) are necessary too.
Plesk can support most of the popular database engines. The list of compatible options includes MySQL and, of course, PostgreSQL. It’s shipped with relevant tools for effective database management, and Plesk is able to work with database servers located on the same server or a remote machine.
We’ll take a closer look at connecting Plesk and PostgreSQL below.
PostgreSQL: What it is and how it works
This database system both utilizes and extends the SQL language. To do this, it leverages an object-relational model that stands apart from others. PostgreSQL is capable of handling highly-demanding workloads, designed to keep data stored safely and affording outstanding scalability. PostgreSQL was created at the University of California at Berkeley, as part of its POSTGRES initiative in the mid-1980s. In the decades since, PostgreSQL has undergone considerable work and adjustment — the core has expanded consistently through rigorous ongoing development.
The open source PostgreSQL community is incredibly committed, which makes this database system one of the best. It enjoys a reputation for ongoing data integrity and extensibility, as well as its strong out-of-the-box functionality. As a result, PostgreSQL can be run on the majority of the biggest operating systems in the world.
Another key facet of PostgreSQL is that it complies with ACID requirements, and has done so for almost two decades. Many solid add-ons can be used with PostgreSQL, too, such as POSTGIS. You can use this extension to utilize geospatial data for your database.
With all this in mind, it’s no surprise that PostgreSQL is regarded as one of the open source community’s biggest relational databases. It’s the primary option for a vast range of companies, individuals and organizations.
Last but not least, PostgreSQL is simple to set up and get running. All you need to do is pick the app you’d prefer to make and rely on PostgreSQL to safeguard your data in a strong database.
Using a Plesk server to configure remote PostgreSQL access
PostgreSQL is set to “localhost” by default — you’ll be refused entry if you attempt to connect to the server from outside the machine.
So, to enable access to PostgreSQL server remotely:
Step 1: Connect to PostgreSQL through SSH
Step 2: Execute the right command to get the location of postgresql.conf file (such as /var/lib/pgsql/data/postgresql.conf): psql -U postgres -c ‘SHOW config_file’
Step 3: Open postgresql.conf file and put this line at the end: listen_addresses = ‘*’
Step 4: Get the location of pg_hba.conf file:
grep pg_hba.conf /var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/pg_hba.conf
where /var/lib/pgsql/data/postgresql.conf is the file resulting from the second step
Step 5: Put this at the end of /var/lib/pgsql/data/pg_hba.conf file: host samerole all 203.0.113.2/32 md5
Some important points:
Connection is allowed from this remote IP: 203.0.113.2/32. If you’re aiming to enable connection from any IP, make sure to specify 0.0.0.0/0 .
The authentication method is md5. This demands that clients provide a double-md5-hashed password for secure authentication.
The user “john.doe” from database example1 can only access that database.
For different methods of authentication, check PostgreSQL documentation.
To put the changes into effect, restart PostgreSQL server through: Plesk > Tools & Settings > Services
with
No comment yet, add your voice below!