5.2.5 Configuring Access from Remote PostgreSQL to BART Host

On the remote host 192.168.2.24, create the public key file for the remote database server user account, postgres, for access to the BART user account, enterprisedb, on the BART host 192.168.2.22.

This is for scenario 1 as described in the Required BART Connections with No Password section of the EDB Postgres Backup and Recovery Installation and Upgrade Guide.

Create the .ssh directory for user account postgres on the remote host:

[root@localhost 9.6]# cd /opt/PostgreSQL/9.6

[root@localhost 9.6]# mkdir .ssh

[root@localhost 9.6]# chown postgres .ssh

[root@localhost 9.6]# chgrp postgres .ssh

[root@localhost 9.6]# chmod 700 .ssh

[root@localhost 9.6]# ls -la | grep ssh

drwx------ 2 postgres postgres 4096 Apr 23 13:32 .ssh

Create and copy the generated public key file, id_rsa.pub, to the BART user account, enterprisedb, on the BART host, 192.168.2.22:

[user@localhost ~]$ su - postgres

Password:

-bash-4.1$ pwd

/opt/PostgreSQL/9.6

 

-bash-4.1$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/opt/PostgreSQL/9.6/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /opt/PostgreSQL/9.6/.ssh/id_rsa.

Your public key has been saved in /opt/PostgreSQL/9.6/.ssh/id_rsa.pub.

The key fingerprint is:

1f:f8:76:d6:fc:a5:1a:c5:5a:66:66:01:d0:a0:ca:ba postgres@localhost.localdomain

The key's randomart image is:

+--[ RSA 2048]----+

| o+. |

| . .. |

| . . |

| . . . . . |

| o S . O |

| . o . @ |

| . + = o .|

| . . o . o.|

| E ... .|

+-----------------+

 

-bash-4.1$ ls -l .ssh

total 8

-rw------- 1 postgres postgres 1671 Apr 23 13:36 id_rsa

-rw-r--r-- 1 postgres postgres 412 Apr 23 13:36 id_rsa.pub

 

-bash-4.1$ scp ~/.ssh/id_rsa.pub enterprisedb@192.168.2.22:/tmp/tmp.pub

The authenticity of host '192.168.2.22 (192.168.2.22)' can't be established.

RSA key fingerprint is b8:a9:97:31:79:16:b8:2b:b0:60:5a:91:38:d7:68:22.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.2.22' (RSA) to the list of known hosts.

enterprisedb@192.168.2.22's password:

id_rsa.pub

Log into the BART host as the BART user account and append the temporary public key file, /tmp/tmp.pub, onto the authorized_keys file owned by the BART user account.

-bash-4.1$ ssh enterprisedb@192.168.2.22

enterprisedb@192.168.2.22's password:

Last login: Thu Apr 23 13:19:25 2015 from 192.168.2.24

-bash-4.1$ pwd

/opt/PostgresPlus/9.6AS

-bash-4.1$ cat /tmp/tmp.pub >> ~/.ssh/authorized_keys

-bash-4.1$ ls -l .ssh

total 16

-rw------- 1 enterprisedb enterprisedb 828 Apr 23 13:40 authorized_keys

-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:04 id_rsa

-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:04 id_rsa.pub

-rw-r--r-- 1 enterprisedb enterprisedb 394 Apr 23 13:24 known_hosts

-bash-4.1$ rm /tmp/tmp.pub

-bash-4.1$ exit

logout

Connection to 192.168.2.22 closed.

Make sure the authorized_keys file has file permission 600 as shown, otherwise the password-less connection fails.

Test the password-less connection. From the remote host, while logged in as user account postgres, verify that you can log into the BART host with the BART user account without being prompted for a password:

-bash-4.1$ pwd

/opt/PostgreSQL/9.6

-bash-4.1$ ssh enterprisedb@192.168.2.22

Last login: Thu Apr 23 13:40:10 2015 from 192.168.2.24

-bash-4.1$ exit

logout

Connection to 192.168.2.22 closed.