After How to : install and test PostgreSql on Centos, we need to change several things to enable C# Client Access to the remote DB.
- Create a User (login) with a password
- Change the pg_hba.conf to turn on the user access from the giving IP range. change postgres.conf to change the listening ip address to * instead of just localhost by default.
- install Npgsql .net assembly on the client machine and write C# code.
To Create a user demo with password demo
|\h Create User # \h will list the help tips for every command |
Create User demo superuser login password ’demo’;
after that, you can query pg_shdow system table to list all users and their hashed password
PG_HBA.conf is located in the pgdata directory, here it will /usr/local/pgsql/data
add one line in the top , to enable demo user from host 192.168.209.1 to access all db with the md5 credential.
host all demo 192.168.209.1/32 md5
for postgresql.conf, change the listen address to * include all ips
Download the NPGsql .net library, create a C# Console application and reference the download Assembly Npgsql.dll
Here is 10 lines of code to connect to remote postgresql db hellodb and do a basic query.
string connString = "Server=192.168.209.130;port=5432;user id=demo;password=demo;database=hellodb";
using (NpgsqlConnection conn = new NpgsqlConnection(connString))
NpgsqlCommand cmd = new NpgsqlCommand("SELECT name, countrycode from City limit 10", conn);
hit f5 to run