Blog_
Create read-only access to PostgreSQL database

Create read-only access to PostgreSQL database

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.

Test connection

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?