PostgreSQL is a well-known relational database management system that boasts a secure environment for developers and users. But as remote work continues to be a part of every business’s new normal, IT professionals face a new set of challenges when it comes to managing the security and accessibility of their servers.
Using a bastion server to access a PostgreSQL database adds an extra layer of security. But because bastion servers should act as firewalls, it’s necessary to use a Secure Shell (SSH) tunnel to lower the level of the private network exposure.
Many PostgreSQL database management tools can automate this connection process, but interacting with the shell requires the manual creation of an SSH tunnel. Understanding the process and precautions can surely help your company maintain secure databases.
Benefits of Creating and Using an SSH Secure Shell Tunnel
SSH tunneling has been used in network communications for over thirty years, and it is just as relevant for today’s computing needs.
Developers consistently turn to SSH Secure Shell protocols to manage database access on all kinds of servers, including PostgreSQL. Secure remote access is critical today, especially when 50% of workplace devices are mobile (mobile devices are among the most vulnerable to cyberattacks). This makes it even more important that organizations can remotely manage devices and user access to their networks.
Companies handling large amounts of data need to give employees express access to major databases and take a proactive approach to database security. Using SSH protocols is the number one in providing secure access to users from remote servers. It also makes it simpler to authenticate automated programs. Therefore, businesses can run their daily tasks more efficiently.
As security standards are finally catching up to mobile devices and remote servers, we can expect to see encryption and authentication protocols improve as well. Flexibility and interoperability are the future of IoT security. Still, SSH protocols will continue to be a standard part for the foreseeable future.
How Does SSH Work?
SSH protocols allow you to remotely control access to your servers across the internet in a secure environment. Not only is the initial connection highly secured, but so is the entire communication session between the two parties. Users can log in to a remote server using an open SSH Secure Shell tunnel or other SSH Secure Shell clients.
The server looks for an open port connection. Once it finds one, the server must authenticate the connection. It determines whether a secure environment has been established for communication and file transfer. If the connection is initiated by the client, the client must authenticate the server in addition to providing its credentials.
When both parties are authenticated and the connection is established, the SSH encryption protocol ensures the continued privacy of all data transmissions between the server and the client.
There are three types of encryption SSH tunnels use to secure connections:
- symmetric encryption
- asymmetric encryption
- hashes.
Symmetric encryption is used for the duration of the connection to keep communication secure. Both client and server share the same secret key to encrypt and decrypt data. This algorithm is secure because an actual key is never really exchanged. Rather, both sides share public information from which they can independently derive the key.
Asymmetric encryption, in contrast, requires both a public and a private key. It is used at the beginning of the tunneling process to authenticate the servers. The server will use a public key to encrypt that data, and the client must authenticate it with a private key. Since a particular private key is the only thing that could decrypt messages encrypted with the public key, this helps authenticate the parties seeking connection.
Cryptographic hashing creates a unique signature for the set of data. This is helpful in an SSH connection because it allows the server to know whether a MAC is acceptable. A matching hash can only be created by the piece of data identical to that which created the original. Thus, hashing can be used to check input to ensure it is the correct one.
SSH Authentication
After establishing the encryption, an authentication process takes place. The simplest form of authentication is using a password. Although this is an easy way for clients, it is also very easy for malicious automated scripts. Even an encrypted password has a limited complexity. It makes it insecure. Using asymmetric keys is a common measure instead of using passwords.
Once a public key is established, the client must provide the private key that goes with the public key. Without this combination, the authentication fails. This helps to ensure the encrypted data cannot be decrypted by an intruder.
The entire authentication process is negotiated during setup, and only parties with complementary keys can communicate. This process also makes the sign-in process quick. So, it’s optimal for automated procedures.
The authentication process will vary based on the following factors:
- the kind of database being protected
- the relationship and location of the server
- the user’s permissions within the server.
The great thing about using an SSH protocol is that the entire process is encrypted and highly secured.
Setup SSH Tunnel
If you want to interact with a database using the shell, you must know how to create an SSH tunnel. While using an automated SSH management application can be convenient, many database admins prefer to set up database management tools from scratch because interacting with the shell is the best way to debug, audit, and have maximal control.
Using an SSH tunnel is a very secure way to open up a session without fear of losing data or getting hacked (as long as you close your sessions and monitor your servers).
Here is how to create an SSH tunnel:
Verify the Bastion Server
To create an SSH Secure Shell tunnel, you need to know the hostnames of the bastion server and the PostgreSQL database, as well as your username on the bastion server. Run the command:
$ ssh <username>@<bastion_server>Then enter your password when the appropriate window pops up.
Open SSH Tunnel
Open the tunnel using this command:
$ ssh -L localhost:port_number:<sql_server>:port_number<username>@<bastion_server>Leave the window open to maintain the connection. The two-port numbers are for your computer and the remote server, respectively.
Verify your Connection
To make sure that your tunnel is open and connected to the PostgreSQL server, use the following command:
$ psql --port=X --host=localhost -c "SELECT * FROM pg_catalog.pg_tables"See if the tables are returned. If they are, then your connection was successful and secure.
A couple of optional steps can increase the efficiency of your access from the bastion host to the SQL server. Keep in mind though that automating access all the time is not a secure strategy. It is wise to always log in whenever you want to open an SSH tunnel to keep sessions completely private.
Create User Profile
To avoid the necessity of entering passwords every single time, you can create a profile on the bastion server. To do this, you must add your SSH key to the server by using the following command:
$ ssh-copy-id <username>@<bastion_server>Then, your key will be used automatically anytime you want to log in. However, it has both positive and negative aspects. It’s more convenient, but you must ensure having other security protocols in place to protect the machine from malware or theft. If someone else gains access to your machine, they might be able to automatically log in to your servers because your key is saved.
Update your SSH Config File
It might be sophisticated to remember the exact names and port numbers of the servers you want to connect to. That’s why it is a part of the SSH Secure Shell tunnel protocol.
You are keeping track of the hostnames and authenticate yourself with your username. It is a part of what makes the SSH connection secure in the first place. But there is a way to configure your procedure to make access quicker and easier without fully automating the process. You can add an entry to your SSH configuration file:
Host bastion-production
- HostName <bastion_server>
- User <username>
- LocalForward localhost:port_number<sql_server>:port_number
Then, run the command:
$ ssh bastion-production Now you can connect in just one step.
SSH Privacy Tips
It is imperative to keep the databases secure. Data is the most valuable currency in the world, and with the rise of ransomware and other cybercrime, failing to protect customer data is an unacceptable lapse.
The whole point of using the bastion server in the first place is to protect your database. However, a few missteps would leave your servers wide open for anyone to come in.
Here are some tips to stay safe while using SSH tunnels to connect to PostgreSQL servers:
Properly Authenticate Remote Workers
If your organization uses SSH to connect remotely, then everyone must know the basics of digital hygiene. Use key-based authentication that is protected by a strong password. Two-step verification for every login is also a good place to start, as well as ensuring that public SSH keys are authentic.
Limit SSH logins only to those who need them, and set privileges that reflect the users’ tasks.
Disable Port Forwarding
Only around half of developers enforce port forwarding prevention procedures. Port forwarding can leave you open to encrypted communications with unapproved users and servers. Thus, hackers can walk right into your database. Filter all of your connections through the bastion server and consider using port knocking before allowing a connection.
Make sure you are running the latest software and your SSH connections are up to the most recent compliance standards.
Audit Frequently
Use a continuous monitoring tool in addition to regular manual audits. Limit your connections only to those which are necessary, and be sure to check for any changes to your configuration settings that were not approved beforehand.
By keeping an eye on who is logging in and what kind of activity they are engaged in, you can limit the attack surface and make it easy to pinpoint vulnerabilities from the start.
Conclusion
Using encrypted SSH tunnels is a secure way to access PostgreSQL databases from remote servers. The process maintains encryption for the connected sessions’ duration. Although automated SSH programs are useful for recurring tasks and other administrative system processes, opening an SSH tunnel manually can be beneficial. In that case, you can easily identify all traffic and communication as opposed to using an automated version or client.
If you use PostgreSQL or any SQL database management system, you need to know how to utilize SSH tunneling. It can be crucial for monitoring and managing system processes and data transfers.
SSH protocols make for a highly secure connection, and following extra precautions will help maintain the integrity of your server connections.
