[Postgresql] SSH connection to the external DB server from the client

Install postgresql on an external DB server, Note that it fits a little when accessing the DB server with SSH connection from the client side.

·environment

--DB server OS: Red Hat 7.2.1-2 (Linux) --Client OS: Windows 10 --DB Connection Tool: A5M2

・ Construction

We will carry out in order.

Install postgreSQL on DB server

Install postgresql on Linux and make it a DB server

$ sudo yum install -y postgresql96 postgresql96-server postgresql96-libs postgresql96-contrib

DB initialization

I'm not sure, but there are various files, so Yoshi!

$ sudo /etc/init.d/postgresql96 initdb

Change password for postgre user

Enter a new password as in New password :, and then enter it again with Retype new password :.

$ sudo passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

Changes to postgresql.conf and pg_hba.conf

After initializing the DB with initdb, in the / var / lib / pgsql96 / data / directory Since postgresql.conf has been generated, edit it.

$ sudo vi /var/lib/pgsql96/data/postgresql.conf

When opened, move to near line 53 (It is convenient to remember that you can display the line number by entering: set number)

postgresql.conf


※Excerpt
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

Uncomment listen_addresses and add the client's global IP address. I also uncomment the port.

~ After editing ~

postgresql.conf


※Excerpt
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost,Global IP address'  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

There are many ways to look up a global IP, It is recommended because you can see it instantly by accessing www.cman.jp with a client.  https://www.cman.jp/network/support/go_access.cgi

Next, pg_hba.conf is generated in the same directory, so edit it.

$ sudo vi /var/lib/pgsql96/data/pg_hba.conf

This is at the bottom so you can skip to the end. Change as follows. (You can fly with Shift + g)

pg_hba.conf


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host all all global IP address/32   trust

You have to set the client-side global IP address in the ADDRESS column and set the METHOD to trust. Please note that the client cannot connect to the DB server.

start postgreSQL

$ /etc/init.d/postgresql96 start

Database creation

If you don't create a database here I am having trouble connecting with the software for DB connection (A5M2 this time) on the client. Switch to postgres user first

$ sudo su - postgres
Last login: Sun Dec 9 14:02:32 UTC 2019 on pts/0
-bash-4.2$

Access DB with psql

-bash-4.2$ psql
psql (9.6.11)
Type "help" for help.

postgres=#

When postgres = # is displayed, literally create an empty database.

postgres=# create database hogeDB;

This completes the settings on the DB server side. Next is the client side settings (mainly A5M2)

・ Connection

Start A5M2 Add and remove databases image.png Add → PostgreSQL (direct connection) image.png For the settings on the "Basic" tab, enter the DB settings and enter them. image.png Select the user name and private key required for the SSH connection in the SSH2 Tunnel tab. image.png

If you make a test connection here and get the message "The connection was successful", you are successful! image.png

After that, save the settings and boil it.

reference: Access the DB of the external server (postgresql) https://liginc.co.jp/programmer/archives/1598

Recommended Posts

[Postgresql] SSH connection to the external DB server from the client
Ssh to an external server under http proxy [from Ubuntu 18.04]
POST images from ESP32-CAM (MicroPython) to the server
SSH login to the target server from Windows with a click of a shortcut
Install and configure PyFilter client to monitor SSH connection to Alibaba Cloud Ubuntu server
Setting to specify the IP allowed for SSH connection
Try setting SSH (Exscript) from the software to the router
Send log data from the server to Splunk Cloud
SSH connection to a private server using a bastion server on EC2
Connect to centos6 on virtualbox with ssh connection from Mac
I want to get information from fstab at the ssh connection destination and execute a command
A note on how to check the connection to the license server port
Terminal association from the server side to Amazon SNS (python + boto3)
Ssh connect to GCP from Windows
I want to operate DB using Django's ORM from an external application
Try using the Python web framework Django (1)-From installation to server startup
Edit the file of the SSH connection destination server on the server with VS Code
[python] Send the image captured from the webcam to the server and save it
The story of trying to reconnect the client
Push notification from Python server to Android
How to operate Linux from the console
How to access the Datastore from the outside
SSH connection from Windows via SSL VPN
[IBM Cloud] I tried to access the Db2 on Cloud table from Cloud Funtions (python)