=> 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,'Раз'),(2,'Два');
INSERT 0 2
=> CREATE ROLE r LOGIN PASSWORD 'rpass';
CREATE ROLE
IP-адрес можно узнать из выдачи ifconfig:
postgres$ ifconfig
enp0s3 Link encap:Ethernet HWaddr 08:00:27:a2:2b:71
inet addr:10.0.2.15 Bcast:10.0.2.255 Mask:255.255.255.0
inet6 addr: fe80::1b49:d907:3db3:8f69/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:31140 errors:0 dropped:0 overruns:0 frame:0
TX packets:5514 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:42940623 (42.9 MB) TX bytes:476127 (476.1 KB)
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:34021 errors:0 dropped:0 overruns:0 frame:0
TX packets:34021 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1
RX bytes:14608140 (14.6 MB) TX bytes:14608140 (14.6 MB)
В нашем случае это 10.0.2.15 и маска подсети 255.255.255.0.
Сохраним текущий файл настроек и добавим нужную строку:
postgres$ cp -n /etc/postgresql/9.6/main/pg_hba.conf /etc/postgresql/9.6/main/pg_hba.conf.backup
postgres$ echo 'host access_overview r 10.0.2.15 255.255.255.0 md5' >> /etc/postgresql/9.6/main/pg_hba.conf
Изменяем параметр:
=> ALTER SYSTEM SET listen_addresses = '*';
ALTER SYSTEM
=> \q
pg_ctlcluster 9.6 main restart
Проверим:
postgres$ psql postgresql://r@10.0.2.15/access_overview?password=rpass -c 'SELECT now();'
now
-------------------------------
2017-07-22 23:42:40.029629+03
(1 row)
=> 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
Проверим:
=> SELECT * FROM s.t;
key | value -----+------- 1 | Раз 2 | Два (2 rows)
=> UPDATE s.t SET value = 'One' WHERE key = 1;
UPDATE 1
=> UPDATE s.t SET key = key+1 WHERE key = 2;
ERROR: permission denied for relation t
Все работает, как требуется.