For data reporting or analytics purpose, it is often needed to allow certain users to access a database in read-only. Giving read-only access will prevent your analytics system to run unwanted and unsafe queries, such as DROP TABLE, DELETE, INSERT, UPDATE...
The operation is pretty straight forward by running psql command and editing postgresql client authentication file.
In the below example, we will allow grafana user to access mydb database from IP address 192.168.1.10.
Grant access to PostgreSQL with psql command
By doing the following below, user grafana will be allowed to run SELECT queries on all tables part of mydb database.
$ psql CREATE ROLE grafana with LOGIN PASSWORD 'password'; GRANT CONNECT ON DATABASE mydb to grafana; \c mydb GRANT SELECT ON ALL TABLES IN SCHEMA public to grafana;
Edit PostgreSQL pg_hba.conf file
PostgreSQL has a strong client authentication access control system. PostgreSQL will allow database access based on user, client source address and authentication method. When well configured, it makes PostgreSQL very secured. It is all configurable with pg_hba.conf file.
# nano /etc/postgresql/13/main/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD host mydb grafana 192.168.1.10/32 md5
To know more about pg_hba.conf you can visit official documentation page here: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
You must restart posgresql service in order to apply new configuration.
# systemctl restart postgres
Listening Addresses PostgreSQL
By default, PostgreSQL will listen on localhost address, preventing client external access.
listen_addresses, located in postgresql.conf file, set IP addresses on which the server is to listen for connections from client applications.
For this tutorial, we will use wildcard '*' to listen to any network interfaces available on the server. You should restart again PostgreSQL service to apply the changes.
You can now test your connection from 192.168.1.10 server.
psql -h <postgresql_ip_address> -U grafana -W -d mydb
It doesn't work?
- Did you configure firewall?
- Did you inspect errors in "/var/log/postgresql" PostgreSQL logs?