Community

How do you configure PostgreSQL to use OS Authentication

5315
1
10-22-2015 01:18 PM
JohnDye
by
Occasional Contributor III
‎10-22-2015 01:18 PM

We downloaded PostgreSQL 9.3.5 from my.esri.com and installed it on a Windows 2008 R2 Server. Everything went fine and we were able to create an enterprise geodatabase using the Create Enterprise Geodatabase tool, connect to it, load some data, post and reconcile, all that good stuff.

We went to create some database users using the Create Database User tool and found the OS Authentication method to be disabled. We assume that's because there's probably some additional configuration work that we need to do on the database in order to enable that. We found a Postgres wikin article (Configuring for single sign-on using SSPI on Windows - PostgreSQL wiki)as well as a super unhelpful Esri KB (38151 - Configure operating system authentication with PostgreSQL for non-production servers )but they quite confusing. We then called Esri Technical Support and as usual, they were absolutely useless.

0 Kudos
1 Reply
George_Thompson
by Esri Frequent Contributor
Esri Frequent Contributor
‎10-23-2015 08:03 AM

Hi John,

I have not seen anyone use OS Authentication for PostgreSQL yet........

As for the Create Database User GP tool, it only supports creation of an OS user on SQL Server and Oracle.

Use this only if an operating system login exists for which you want to create a database user. Only enabled for SQL Server and Oracle databases.

  • DATABASE_USER —Create a database-authenticated user. This is the default. If your database management system is not configured to allow database authentication, do not use this option.
  • OPERATING_SYSTEM_USER —Create an operating system-authenticated user. The corresponding login must already exist. If your database management system is not configured to allow operating system authentication, do not use this option.

Create Database User—Help | ArcGIS for Desktop

I was able to find some documentation from the PostgreSQL site on Client Authentication. PostgreSQL: Documentation: 9.3: Client Authentication

I also looked at the KB article you mentioned and it says:

Trust authentication should not be used in a production environment.

Where you attempting this for a production machine or in a development environment?

Here some more documentation from the ArcGIS 10.2.x Help:

It is not recommended to use OS authentication with geodatabases in PostgreSQL, because you would have to change to an unsecured authentication type in the database.

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

I would try to get it working outside of the Esri Client (ArcMap/ArcCatalog) and attempt to connect via PGAdmin client using O/S authentication first. Everything that I have seen is related to how you have the pg_hba.config set up and there may be a mix-match of authentications for the IP that you are specifying. i.e. using 1.1.1.1 md5 and 1.1.1.1 trust at the same time.

It is usually not within the scope of Esri support to help with some specific RDBMS configurations (i.e. OS Authentication on Oracle, performance tuning, system recommendations, etc...). There are also other items that are outside of Esri scope, especially related to other products that we have a dependency on, like Linux OS issues that impact PostgreSQL/Oracle RDBMS installed on them and then directly impact our product. We have no control over when or if those items get corrected by the appropriate people.

Hope this helps clear up some possible confusion.....

-George

Managing Data​

--- George T.