Difference between revisions of "Create Postgres Read Only User"

From Observer GigaFlow Support | VIAVI Solutions Inc.
Jump to: navigation, search
 
Line 5: Line 5:
 
In this example i'm allowing access from the subnet 172.21.40.0/24
 
In this example i'm allowing access from the subnet 172.21.40.0/24
 
  host    all            all            172.21.40.0/24            md5
 
  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.
 
Now we need to add the user with permissions to postgres.

Latest revision as of 12:39, 7 December 2021

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