XP-rience

SSH private key authentication with WinSCP and PuTTY Gen

Simplify your login process by using SSH keys for authentication when you are connecting to your server.

Save the private key to a file.

[applmgr@vm-centos7 ~]$ cd ~
[applmgr@vm-centos7 ~]$ mkdir -p .ssh
[applmgr@vm-centos7 ~]$ chmod 700 .ssh
[applmgr@vm-centos7 ~]$ ls -al
total 12
drwx------. 6 applmgr applmgr 119 Jul  9 20:27 .
drwxr-xr-x. 4 root    root     36 Jul  9 20:05 ..
-rw-r--r--. 1 applmgr applmgr  18 Oct 31  2018 .bash_logout
-rw-r--r--. 1 applmgr applmgr 193 Oct 31  2018 .bash_profile
-rw-r--r--. 1 applmgr applmgr 231 Oct 31  2018 .bashrc
drwxrwxr-x. 3 applmgr applmgr  18 Jul  9 20:06 .cache
drwxrwxr-x. 3 applmgr applmgr  18 Jul  9 20:06 .config
drwxr-xr-x. 4 applmgr applmgr  39 Jul  4 19:01 .mozilla
drwx------. 2 applmgr applmgr   6 Jul  9 20:27 .ssh

[applmgr@vm-centos7 ~]$ cd .ssh/
[applmgr@vm-centos7 .ssh]$ touch authorized_keys
[applmgr@vm-centos7 .ssh]$ chmod 600 authorized_keys
[applmgr@vm-centos7 .ssh]$ vi authorized_keys

Save the file and now run your WinSCP, enter hostname, username and click "Advanced ..."

Under "SSH" > "Authentication", check the "Allow agent forwarding", and select the privates key file that you've saved just now.

And "login".

PostgreSQL installation & connecting it to to Oracle SQL Developer/HeidiSQL

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-20-04

Login to your shell and execute below command lines.

$ sudo apt update

$ sudo apt install postgresql postgresql-contrib

Now enable remote access to your PostgreSQL by editing 2 files under "/etc/postgresql/12/main/".

https://support.cpanel.net/hc/en-us/articles/4419265023383-How-to-enable-remote-PostgreSQL-access

Note that "12" is the version number. Change it according to the installed version number.

Edit "postgresql.conf" file and add...

listen_addresses = '*'

Edit "pg_hba.conf" file and add...

host all all 0.0.0.0/0 md5

Now start your PostgreSQL DB using "systemsql" command.

$ sudo systemctl start postgresql

Before you can connect to the PostgreSQL , you need a user. Create PostgreSQL user "puser" with below command line.

$ sudo -u postgres createuser --interactive

And you also need a DB to start with. Create PostgreSQL DB named "pdbase" with below command line.

$ sudo -u postgres createdb pdbase

Create Linux user matching with the PostgreSQL user "puser".

$ sudo adduser puser

Switch user to "puser" and run "plsql" to login to the PostgreSQL DB.

$sudo -u puser psql -d pdbase

You should now get the "plsql" prompt as below indicating that you have successfully login to the your PostgreSQL DB.

pdbase=#

Use "\l" to list databases or use "\dt" to list tables, these is just to make sure the DB is running fine.

pdbase=# \l

pdbase=# \dt

Now alter password for "puser", This password will be used to connect through remote connection such as Oracle SQL Developer or HeidlSQL.

pdbase=# ALTER USER puser WITH PASSWORD 'mypass';

Example connecting from SQL Developer

https://www.oracle.com/database/technologies/jdbc-migration.html

Please refer above URL to add JDBC driver to your SQL developer.

Fill up necessary info such as user/pass/host/port/db and hit Test/Connect.

Example connecting from HeidiSQL.

Same goes to HeidiSQL, fill up necessary info such as user/pass/host/port/db and hit Open