Skip to main content
Background Image
  1. PostgreSQL Posts/

Batch Configure SSH Passwordless Login

·297 words·2 mins· ·
Ruohang Feng
Author
Ruohang Feng
Pigsty Founder, @Vonng
Table of Contents

Configuring SSH is fundamental operations work - sometimes the basics need revisiting.


Generate Public-Private Key Pairs
#

Ideally, everything should use public-private key authentication for passwordless direct connection from local to all database machines. Password authentication should be avoided.

First, use ssh-keygen to generate public-private key pairs:

ssh-keygen -t rsa

Pay attention to permissions: SSH files should have permissions set to 0600, and .ssh directory permissions should be set to 0700. Incorrect settings will prevent passwordless login from working.


Configure ssh config to traverse jumphost
#

Replace User with your own name. Put in .ssh/config. Here’s how to configure direct passwordless connection to production database in a jumphost environment:

# Vonng's ssh config

# SpringBoard IP
Host <BastionIP>
	Hostname <your_ip_address>
	IdentityFile ~/.ssh/id_rsa

# Target Machine Wildcard (Proxy via Bastion)
Host 10.xxx.xxx.*
	ProxyCommand ssh <BastionIP> exec nc %h %p 2>/dev/null
	IdentityFile ~/.ssh/id_rsa

# Common Settings
Host *
	User xxxxxxxxxxxxxx
	PreferredAuthentications publickey,password
	Compression yes
	ServerAliveInterval 30
	ControlMaster auto
	ControlPath ~/.ssh/ssh-%r@%h:%p
	ControlPersist yes
	StrictHostKeyChecking no

Copy Public Key to Target Machines
#

Then copy the public key to jumphost, DBA workstation, and all database machines.

ssh-copy-id <target_ip>

Each execution of this command requires password input, which is tedious and boring. It can be automated through expect scripts or using sshpass.


Use expect for Automation
#

Replace <your password> in the following script with your actual password. If the server IP list changes, modify the list accordingly.

#!/usr/bin/expect
foreach id { 
     10.xxx.xxx.xxx
     10.xxx.xxx.xxx
     10.xxx.xxx.xxx
} {
    spawn ssh-copy-id $id
    expect {
    	"*(yes/no)?*"
    	{
            send "yes\n"
            expect "*assword:" { send "<your password>\n"}
    	}
     	"*assword*" { send "<your password>\n"}
    }
}

exit

More Elegant Solution: sshpass
#

sshpass -p <your password> ssh-copy-id <target address>

The downside is that passwords are likely to appear in bash history - clean up traces promptly after execution.

Related

Wireshark Packet Capture Protocol Analysis
·982 words·5 mins
Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here’s a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis.
Common Linux Statistics CLI Tools
·2379 words·12 mins
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools
PostgreSQL MongoFDW Installation and Deployment
·707 words·4 mins
Recently had business requirements to access MongoDB through PostgreSQL FDW, but compiling MongoDB FDW is really a nightmare.
The Versatile file_fdw — Reading System Information from Your Database
·846 words·4 mins
With file_fdw, you can easily view operating system information, fetch network data, and feed various data sources into your database for unified viewing and management.
Installing PostGIS from Source
·630 words·3 mins
PostGIS is PostgreSQL’s killer extension, but compiling and installing it isn’t easy.
Go Database Tutorial: database/sql
·4249 words·20 mins
Similar to JDBC, Go also has a standard database access interface. This article details how to use database/sql in Go and important considerations.