Difference between revisions of "Create Postgres Read Only User"
From Observer GigaFlow Support | VIAVI Solutions Inc.
Kevin Wilkie (Talk | contribs) |
Kevin Wilkie (Talk | contribs) |
||
(One intermediate revision by one user not shown) | |||
Line 1: | Line 1: | ||
− | 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 | + | 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: | 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: | ||
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