How to enable windows authentication in PostgreSQL

1.    Steps to create user or role in PostgreSQL

·        Open pgAdmin III

·        Login to PostgreSQL database

·        Select “Login Roles” and right click on mouse to view the context menu

·        Select “New Login Role” from context menu

·        Enter desired user name in “Role name” text box

·        Select “Definition” tab in “New Login Role” window

·        Enter desired Password in the given text box

·        Select “Role privileges” tab in “New Login Role” window

·        Select the privileges for the entered user or role

·        Select “SQL” tab in “New Login Role” window

·        This will display the auto generated script

·        Review the script and click on “OK” button

·        Now in the “Login Roles” the newly created role or user “newUser” is displayed

·        Now in pgAdmin III object browser select the database to which the newly created user should be mapped

·        Select the database and right click to view the context menu

·        Select “Properties” from context menu

·        Select the “newUser” as owner

2.    Modify pg_hba.conf

In pg_hba.conf modify 127.0.0.1 to client IP address and md5 to trust as shown below

# IPv4 local connections:

host    all             all             10.145.16.224/32            trust

3.    Sample c# code to test the PostgreSQL connection

static void Main(string[] args)

       {

            bool isWindows = true;

            var res = DBConnect(isWindows);

            Console.WriteLine(" Connection - " + res.ToString());

            Console.ReadLine();

        }

        public static bool DBConnect(bool isWindows)

        {

            string localServ = "localhost";

            string localDBase = "TestDB";

            string localUser = ""; //postgres

            string localPwd = ""; // admin

            string localPort = "5432";

            NpgsqlConnection _localServerConn;

            string constr = string.Empty;

            constr = (isWindows) ? "Server=" + localServ + "; Port=" + localPort + ";Database=" + localDBase + "; Integrated Security=true;"

                                 : "Server=" + localServ + ";Port=" + localPort + ";User Id=" + localUser + ";Password=" + localPwd + ";Database=" + localDBase;

            _localServerConn = new NpgsqlConnection(constr);

            try

            {

                _localServerConn.Open();

                _localServerConn.Close();

                return true;

            }

            catch (Exception ex)

            {

                _localServerConn.Close();

                return false;

            }

        }

4.    Note:

If login fails then check the current log in PostgreSQL\9.2\data\pg_log folder

It will display the unsuccessful login info as shown below

2013-04-02 13:56:03 IST FATAL:  role "SYSTEM" does not exist

2013-04-02 13:56:18 IST FATAL:  role "[Windows ID Name]" does not exist

Create the user or role of "[Windows ID Name]" and run the code again.