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;
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?
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