Select to view content in your preferred language

Postgresql with OS authentication as data creator

188
4
Jump to solution
3 weeks ago
AlexanderGray
Honored Contributor

I have create a test postgresql 15 geodatabase 11.4 on windows 11 (my own laptop).  I need to be able to connect as OS authenticated sde connection.  I created a user using pgAdmin.  The user is mixed case (LastnameF) which matches what windows is doing in the OS authentication.  I use a membership in a role and I am able to view and edit data in a schema I already loaded through OS authentication connection.  This is all local so I don't have ldap set up or anything like that, I assume the os authentication is passing the user name from windows to postgresql as-is and the database accepts it. 

The problem is when I try to create data in the LastnameF schema.  It seems like it sends everything to lower case.  I tried creating the user schema as LastnameF and as lastnamef but it both cases I get "Database user name and current user schema do not match"  I tried ArcGIS pro 3.4 and ArcGIS 10.8.1 for good measure.

If I create the schema as lower case and the user as lower case, I can connect using database authentication and create feature classes.  However, I cannot connect as OS authentication in that case which is a requirements for our application.

 

0 Kudos
1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

@AlexanderGray - You wrote: "Postgresql with OS authentication as data creator"
Note, the recommendation is to not use a Domain Login User to be the Data Owner User in the Geodatabase. This leads to several issues. Instead you should create a PostgreSQL user to be the data owner user, then grant permissions for editor and viewer users via roles, then if there are Domain Login Users that needs to connect via Windows Authentication you can grant the proper role(s) to the users to be able to edit or view the data. You can find more details in my database guide books for production mapping and in my database template scripts for PostgreSQL, see links below.

Please read the Database Connections Best Practices

Best Practices Production Mapping 3.x Workspace in PostgreSQL® 
 PostgreSQL
   ArcGIS Pro 3.x & ArcMap 10.8.x
Postgres on Windows July 05, 2023 *New
Postgres on Linux July 05, 2023 *New

For more best practices visit my community.esri.com blog.
Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

I hope this helps.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |

View solution in original post

4 Replies
MarceloMarques
Esri Regular Contributor

@AlexanderGray - I have not tested with mix case for the user name, but I am not surprised that you are encountering issues in PostgreSQL to connect via Windows Authentication. This might not be an ArcGIS issue, but a limitation on the PostgreSQL side. You can try to create the Windows Authentication user name all in lower case to see it that resolves the problem. If the issue persists then I suggest to open a ticket with Esri Technical Support to continue to investigate.

I am adding my white paper below as a reference to others. It might help.

How to Configure Windows Authentication for the PostgreSQL Enterprise Geodatabase


Other white papers that folks might be interested.

How to Upgrade the PostgreSQL and PostGIS version for the Enterprise Geodatabase on Windows
How to Upgrade the PostgreSQL and PostGIS version for the Enterprise Geodatabase on Linux
How to Move the PostgreSQL Enterprise Geodatabase with pg_dump and pg_restore

For more best practices visit my community.esri.com blog.
Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |
AlexanderGray
Honored Contributor

Thanks Marcello, I work in a large organization and have no control over the user names and is an unlikely avenue to follow.  We currently use Oracle but are looking at moving to PostgreSQL.  Using the OS authenticated user as a data user only (not owner) is possible but would require some changes in the application.

Looking at the postgresql documentation and doing some tests with upper case database names and users, I have found that I have to "" quote any object names that have upper case or the object is not found.  I do not have to do that with lower case names.  I suspect the ArcGIS client is not quoting the names so although the connection works, it can't find the schema with the mixed case...  The user names seem to work in mixed case though because the connection works.

 

I wonder if anyone has tried to do this...  I don't really want to be the first.

0 Kudos
MarceloMarques
Esri Regular Contributor

@AlexanderGray - You wrote: "Postgresql with OS authentication as data creator"
Note, the recommendation is to not use a Domain Login User to be the Data Owner User in the Geodatabase. This leads to several issues. Instead you should create a PostgreSQL user to be the data owner user, then grant permissions for editor and viewer users via roles, then if there are Domain Login Users that needs to connect via Windows Authentication you can grant the proper role(s) to the users to be able to edit or view the data. You can find more details in my database guide books for production mapping and in my database template scripts for PostgreSQL, see links below.

Please read the Database Connections Best Practices

Best Practices Production Mapping 3.x Workspace in PostgreSQL® 
 PostgreSQL
   ArcGIS Pro 3.x & ArcMap 10.8.x
Postgres on Windows July 05, 2023 *New
Postgres on Linux July 05, 2023 *New

For more best practices visit my community.esri.com blog.
Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

I hope this helps.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |
AlexanderGray
Honored Contributor

Thanks again, this is a lot of help.  The application is mainly an editing application with an Oracle geodatabase.  We have a workflow that copies a feature class from a database account schema to the OS authenticated user schema, the user is then the only one who can edit the data and then when the work is complete the data gets pushed into a database account table (no geometry) and the feature class gets deleted.   So it is a "temporary" feature class that gets created and destroyed over the course of the workflow.  There are other ways to achieve this including file geodatabase or shapefiles but then if the workflow gets interrupted, the data would remain on the local workstation and could be lost.  The idea was to create the data in the enterprise geodatabase so the work could be moved to a different workstation or a different user.  I supposed we could create a schema with a database authenticated user specifically to host these and prefix the feature classes with the user name and use this second connection to create, grant and delete the feature class.

 

0 Kudos