Create Postgres Read Only User

From Observer GigaFlow Support | VIAVI Solutions Inc.
Jump to: navigation, search

To allow remote access, you will need to edit the pg_hba.conf file, this will be in the same folder as all of the database data files (d:\data for appliances)

Open the file in a text editor, go to the end of it and add in the following to the section labeled # IPv4 local connections:

In this example i'm allowing access from the subnet 172.21.40.0/24

host    all             all             172.21.40.0/24            md5

It should now look similar to the below example

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             172.21.40.0/24            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5


Now we need to add the user with permissions to postgres.

Change into the folder with the psql executable

cd C:\GigaFlow\Flow\resources\psql

Connect to the local database (password is myipfix)

psql.exe -U myipfix -h 127.0.0.1

Create the user with permissions

CREATE USER myreadonlyusername WITH PASSWORD 'this1sth3passw0rd';
GRANT CONNECT ON DATABASE myipfix TO myreadonlyusername;
GRANT USAGE ON SCHEMA public TO myreadonlyusername;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myreadonlyusername;
SELECT pg_reload_conf();

Test the credentials

psql.exe -U myreadonlyusername  -h 127.0.0.1 myipfix