1

I need to grant somebody read only access to a postgres sitting inside a ubuntu 18.04 server

For the postgres I did the following

CREATE ROLE read_only_user WITH LOGIN PASSWORD 'FAKEPASSWORD123' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
GRANT CONNECT ON DATABASE "somedb" TO read_only_user;
GRANT USAGE ON SCHEMA public TO read_only_user;

On SSH /etc/ssh/sshd_config

PasswordAuthentication no
PermitRootLogin no

On the ubuntu I did this

sudo mkdir /var/read_only_on_ubuntu_user
sudo groupadd read_only
sudo usermod -g read_only read_only_on_ubuntu_user
sudo useradd -d /var/read_only_on_ubuntu_user  read_only_on_ubuntu_user
sudo chown read_only_on_ubuntu_user:read_only /var/read_only_on_ubuntu_user

cd /var/read_only_on_ubuntu_user && mkdir .ssh
vim .ssh/authorized_keys # this is to add the public key for this user account

This user successfully accesses the postgres on localhost:5432 over SSH

What else can I do to further restrict this user?

Update

Forgot to add that the user currently uses a desktop GUI app that tunnels to the server and then accesses the database via SSH remotely from their own desktop.

They do not SSH in and then run psql. Though they might ask for that next time, but I think extremely unlikely

3 Answers 3

2

Add a ForceCommand to sshd_config (you could include ¡it into a match block so it only affects this user) that runs the client (in your case psql).

Alternatively, you can set the ForceCommand restriction on the authorized_keys file by prepending a command="...". You should probably also add restrict flag there, followed by pty.

A different approach would be to, rather than letting the user execute psql on the remote server, to let them perform a ssh connection that only lets them create a tunnel to the localhost port quere postgresql is running, and have them connect locally to it.

3
  • Sorry I forgot to add another point. Right now, the user uses a GUI desktop app which will connect to the postgres database via SSH access remotely from their desktop. They do not access via psql right now. So I am not entirely sure if that already fits with your last suggestion?
    – Kim Stacks
    Sep 24, 2020 at 1:31
  • and what's the settings I need to make so that the user can "only create tunnel to localhost port 5432 where postgres is running"?
    – Kim Stacks
    Sep 24, 2020 at 1:32
  • 1
    Once connected and running psql, a full shell is available by simply typing \! and enter
    – symcbean
    Feb 24 at 20:21
2

If you only want to allow tunneling, add the following to your /etc/ssh/sshd_config:

Match User myusername
  # PasswordAuthentication yes
  PermitOpen 127.0.0.1:5432
  X11Forwarding no
  AllowAgentForwarding no
  ForceCommand /bin/false

Uncomment PasswordAuthentication yes if this user requires a password to login, but everyone else must not login using a password.

This requires connecting using -N when using an SSH client. Otherwise, the server will greet the user and then disconnect.

The following would connect to the config above and open a tunnel. Postgres would then be accessible on port 10123 on the local machine.

ssh -N -L 10123:localhost:5432 myusername@myserver
0

Use a restricted shell, e.g. lshell, for the user. This allows you to specify a whitelist of commands the shell will permit (and the contraints are also applied to a shell started from within the psql client).

Use the chsh command to set a user's shell.

3
  • While the upvoted answer has a trivial security bypass?
    – symcbean
    Feb 24 at 17:33
  • I hadn't looked at the other answers, actually; I was only concerned with improving yours. If you see an issue with one of the other answers, I believe you have enough reputation points to either comment on it or edit it to improve it. I'd encourage improvement on any post on SE!
    – Jeff Schaller
    Feb 24 at 17:38
  • @symcbean What's the trivial security bypass of the other answers? Would you mind commenting on them? Thanks!
    – nyi
    Feb 24 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.