PostgreSQL remote access is highly desirable for many users. PostgreSQL is a relational database system that is state-of-the-art, enterprise-class, and open-source. It supports SQL and JSON querying which are relational and non-relational respectively.
PostgreSQL will work on pretty much any platform’s operating system including MS Windows and various flavors of UNIX server, such as Linux. Many platforms also support PostgreSQL remote access.
What is PostgreSQL?
PostgreSQL is a powerful, open-source object-relational database system that utilizes and adds to the SQL language. It offers numerous impressive capabilities to take the safe storage and scaling of complex data workloads in its stride.
PostgreSQL has established a pedigree that goes all the way back to 1986, starting out under the umbrella of the POSTGRES project at UC Berkeley. Its core platform has benefited from over three decades of input to improve it.
This is only one of a few reasons why remote access to PostgreSQL is attractive to so many users.
PostgreSQL’s robust architecture is known to be reliable. It protects data, offers strong features, can be extended, and is underpinned by a dedicated open-source community that consistently comes up with clever ways of and enhancing it further.
PostgreSQL will work happily on the majority of operating systems, having been compliant with ACID since 2001. It features impressive extras like the much-used PostGIS geospatial database extender. It’s hardly surprising that PostgreSQL is now the preferred open-source relational database for a lot of individuals and organizations, which goes to further explain the need for PostgreSQL remote access.
PostgreSQL offers multitudinous features that aim to assist developers in building applications. Others help administrators to preserve the integrity of data and construct environments that tolerate faults. From the largest to the smallest datasets, it is eminently capable of helping users to manage them all more effectively, so why wouldn’t they want remote access to PostgreSQL.
Open-source means that you don’t have to pay a penny for it, which must have helped to contribute to its popularity, but it’s the sturdy features that have done the bulk of the work in attracting so many loyal fans.
For instance, it lets you create your own data type definitions, create your own functions, even pen code written in other programming languages with no need to recompile your database!
PostgreSQL attempts to conform with the SQL standard in situations where doing so doesn’t contravene traditional features or could precipitate bad architecture choices. It supports numerous features that the SQL standard calls for, albeit occasionally with mild syntactical or functional differences. Additional efforts towards conformity will be implemented going forward. As of September 2021’s version 14 release, PostgreSQL shows conformity with a minimum of 170 of the 179 statutory features for SQL:2016 Core compliance. As we write this, there isn’t a single relational database that fully complies with this standard.
How Does PostgreSQL Integrate with Plesk?
Remote access to PostgresSQL raises questions for Plesk users. Plesk is database-driven by nature so it requires a database server that can facilitate the variety of database services used by its components. For instance, some databases assist with hosting Plesk webmail. Others will store content whenever customer websites require APS application databases.
All Plesk installations provide a number of tools to assist with database management, and Plesk for Linux can work with various databases including PostgreSQL (to facilitate PostgreSQL remote access). Plesk is capable of interacting with database servers that are situated on the device where Plesk is resident, but Plesk is also capable of linking to database servers on external machines. Remote access to PostgreSQL as possible through Plesk, which you may want to do if you have a remote app that needs to access your PostgreSQL databases.
Plesk PostgreSQL Remote Access
In lots of cases, users need to facilitate connections to databases via another developer’s software tools that are not operating on the server the database is operating on. You can set up Plesk PostgreSQL remote access in order to let remote servers and hosts look at your Plesk account’s PostgreSQL databases.
Turning on PostgreSQL Remote Access Within Plesk
To turn on remote access to the PostgreSQL server:
Connect to the PostgreSQL server via SSH.
Get the location of the postgresql.conf file by typing in the appropriate command, which should resemble this: /var/lib/pgsql/data/postgresql.conf:
psql -U postgres -c 'SHOW config_file'
Open postgresql.conf file and add this line at the end:
listen_addresses = '*'
Locate the 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 from the output of step 2
Add this line to the end of the /var/lib/pgsql/data/pg_hba.conf file:
host samerole all 202.0.113.2/32 md5
202.0.113.2/32 is the remote IP that you are allowing connection from. You can do the same from any IP by specifying 0.0.0.0/0.
The authentication method is md5, which needs the client to come up with a double-MD5-hashed authentication password.
User jane.smith from database example1 can only access database example1.
To find out about additional authentication methods take a look at PostgreSQL documentation.
To apply the changes, restart your PostgreSQL server: Plesk > Tools & Settings > Services
Be aware that if the PostgreSQL port has not been customized then port 5432 can be set up on the firewall to permit specific IPs to gain access.
with
No comment yet, add your voice below!