How to create a SSH user that can only connect to MySQL / PostgreSQL on Ubuntu with Username and Password

Photo by Kvistholt Photography on Unsplash

Sometimes we need to add a user to a pretty locked down server just for them to login to MySQL / PostgreSQL via their GUI client without giving them any other access. It’s actually pretty easy to do so in Ubuntu.

Let’s get started with these 5 simple steps.

1. Create our new User

Let’s call himdbuser. Then gives him a password via passwd.

sudo useradd -s /usr/sbin/nologin dbuser
sudo passwd dbuser2

Basically what /usr/sbin/nologin does is to create a shell that has no login access. That way when dbuser tries to SSH into the server, his shell access would be closed immediately preventing him from tinkering around in the server.

While this stops the user from accessing SSH, it does not block the user from tunnelling through SSH and accessing the databases as you will see.

2. Update our SSH Configurations

If you follow the best practises around for SSH, you would probably have disabled password authentication for your server. That’s good.

For simplicity sake however, we might want to enable password authentication specifically for dbuser so that it’s easier for a non-technical user to login via his favourite database GUI. So let’s do that.

sudo nano /etc/ssh/sshd_config

Go to the end of the file (very important to be the end of the file) and add the following lines:

Match user dbuser
PasswordAuthentication yes

With these 2 lines, we have enabled PasswordAuthentication specifically for dbuser only and not anyone else.

Then restart the SSH service for the new settings to take effect.

service ssh reload

3. Logging in with GUI

Now setup your respective GUI tool to connect to MySQL / PostgreSQL via SSH, it should work.

Here’s a sample screenshot from TablePlus.

Sample Settings for TablePlus

4. Testing with SSH

While it worked for the GUI client, we still need to make sure that dbuser has no SSH access. Let’s SSH into our server with dbuser and you should see similar output as the following:

Just as expected,dbuser wasn’t able to log into the server.

However, the server seems to be showing a lot of unnecessary info to the user. Personally I don’t like those information to be exposed, so let’s hide them away.

Note: Your server may not show these informations and that’s fine. You can skip step 5 if you are happy with the messages being displayed when dbuser logs in.

5. Update Message of the Day

What we see in step 4 is called MOTD, or Message of the Day.

To hide away the information is really simple. All we need is to create a .hushlogin file in user’s home folder:

mkdir /home/dbuser
touch /home/dbuser/.hushlogin

Now when you try to login, you should see a much simplified output like this:

Much better!

And now you have a happy user who can log into MySQL / PostgreSQL without the risk of disrupting the server.

--

--

--

I write about Laravel, PHP, and web development related articles.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

What I Learned Today -19/04/2020

Why Use A Terraform Template?

602011#LearnWeb3

How To Get Started With Docker

No Code for PMs: How I built an app using no-code in 2 days!

5 Steps: Setup VS Code for Remote Development via SSH from Windows to Linux

Fomoin and MetaLine’s first AMA ended successfully

How To Fix BLU Life Pure Not Charging [Troubleshooting Guide]

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jian Jye

Jian Jye

I write about Laravel, PHP, and web development related articles.

More from Medium

Deploying a Docker Container on EC2

How to setup SSL/TLS for your domain for Free: Cloudflare and Nginx

Auto Build & Push Docker Image to AWS ECR with Github Actions- [Step-By-Step]

Setup Grafana on an Ubuntu EC2 Instance