XP-rience

Something that I'd like to share with you!

Sunday, July 09, 2023

SSH private key authentication with WinSCP and PuTTY Gen

No comments :

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

1st step is to run the PuTTYGen software and generate a key.


Save the private key to a file.
Next, login into you server through SSH and create "~/.ssh" directory if it doesn't exists.
chmod the directory to 700.
[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
Create "authorized_keys" if it doesn't exists.
chmod it to 600 and edit the file, in my example, using "vi"
[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

Copy and paste (or add new line) the content of the public key into this file.


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".




Sunday, April 02, 2023

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

No comments :

Install PostgreSQL DB, in my example here, installing it under Ubuntu by referring to below URL.

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