nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Postgresql with OS authentication as data creator

AlexanderGray

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.

Vind meer berichten getagd met

Enterprise geodatabase

Geodatabase Administration

Accepted answers

MMBR

@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.

All comments

MMBR

@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

AlexanderGray

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.

MMBR

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.

AlexanderGray

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.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.