Create a database, objects, and a role

α=> CREATE DATABASE access_overview;

CREATE DATABASE

α=> \c access_overview

You are now connected to database "access_overview" as user "student".

α=> CREATE SCHEMA s;

CREATE SCHEMA

α=> CREATE TABLE s.t(
    key integer PRIMARY KEY,
    value text
);

CREATE TABLE

α=> INSERT INTO s.t VALUES (1,'One'),(2,'Two');

INSERT 0 2

α=> CREATE ROLE r LOGIN PASSWORD 'rpass';

CREATE ROLE

Set up authentication

IP address of the VM is shown by the ifconfig command:

postgres$ ifconfig

enp0s3    Link encap:Ethernet  HWaddr 08:00:27:82:8d:7b  
          inet addr:10.0.2.15  Bcast:10.0.2.255  Mask:255.255.255.0
          inet6 addr: fe80::8dc1:cc73:7d97:23a6/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:93287 errors:0 dropped:0 overruns:0 frame:0
          TX packets:34816 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:105531585 (105.5 MB)  TX bytes:6038482 (6.0 MB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:150850 errors:0 dropped:0 overruns:0 frame:0
          TX packets:150850 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:48995081 (48.9 MB)  TX bytes:48995081 (48.9 MB)

In this case IP is 10.0.2.15, and subnet mask is 255.255.255.0.

We'll back the file up just in case, and add the record to the configuration:

postgres$ cp -n /etc/postgresql/11/alpha/pg_hba.conf /etc/postgresql/11/alpha/pg_hba.conf.backup

postgres$ echo 'host access_overview r 10.0.2.15 255.255.255.0 md5' >> /etc/postgresql/11/alpha/pg_hba.conf

Set the listen_addresses parameter:

α=> SHOW listen_addresses;

 listen_addresses 
------------------
 localhost
(1 row)

α=> ALTER SYSTEM SET listen_addresses = '*';

ALTER SYSTEM

α=> \q

student$ sudo pg_ctlcluster 11 alpha restart

Test the changes:

postgres$ psql postgresql://r@10.0.2.15/access_overview?password=rpass -c 'SELECT now();'

              now              
-------------------------------
 2019-02-27 15:39:15.111036+03
(1 row)

Restrict access

student$ psql -d access_overview

α=> GRANT ALL ON SCHEMA s TO r;

GRANT

α=> GRANT SELECT ON s.t TO r;

GRANT

α=> GRANT UPDATE(value) ON s.t TO r;

GRANT

Test the privileges:

student$ psql postgresql://r@10.0.2.15/access_overview?password=rpass

α=> SELECT * FROM s.t;

 key | value 
-----+-------
   1 | One
   2 | Two
(2 rows)

α=> UPDATE s.t SET value = 'Unity' WHERE key = 1;

UPDATE 1

α=> UPDATE s.t SET key = key+1 WHERE key = 2;

ERROR:  permission denied for table t

Everything works as expected.