Difference between revisions of "Create Postgres Read Only User"

Revision as of 12:37, 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 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

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