Connect to PostgreSQL from ArcGIS

The following list summarizes the steps required to connect from ArcGIS to a PostgreSQL database, including a database that contains an enterprise geodatabase:

• Confirm that the database name meets ArcGIS requirements.
• Ensure PostgreSQL accepts connections from the ArcGIS client machines that need access.
• Create a database connection.
• To publish ArcGIS Server web services that reference the data in the PostgreSQL database, register the database connection file with your ArcGIS Server sites.

PostgreSQL client files are installed with ArcGIS AllSource and ArcGIS Server; you do not need to install a PostgreSQL client or set environment variables to access the client.

Configure the database to allow connections

By default, only clients running on the same machine as the PostgreSQL database cluster can connect to it. To allow remote clients to connect to the database cluster and control what databases they can access, alter the PostgreSQL pg_hba.conf configuration file. The entries you place in the pg_hba.conf file will vary depending on security policies and configurations at your site.

Read the PostgreSQL documentation for instructions.

Connect to the database

You can use the Database Connection dialog box in ArcGIS AllSource to connect to the database, as described below.

Alternatively, you can run the Create Database Connection tool in ArcGIS AllSource or use Python to run the Create Database Connection command from an ArcGIS Server machine to create a database connection file (.sde) that connects to the database.

To use the database connection file with ArcGIS Server, you must save the user information with the connection file.

If you choose to use operating system authentication, ensure PostgreSQL can authenticate the ArcGIS Server account.

Tip:

Ensure the account used for the connection has the appropriate privileges in the database and on the data that the account needs to access. If the connection uses operating system authentication and you will publish web services using this connection, ensure the ArcGIS Server account has the required privileges on the data included in the web service. See Privileges for geodatabases in PostgreSQL and Privileges for using ArcGIS with a PostgreSQL database for more information.

Follow the steps below to connect to a PostgreSQL database from the Database Connection dialog box in ArcGIS AllSource.

1. Open the Catalog pane in ArcGIS AllSource.
2. Right-click Databases or a folder under Folders and click New Database Connection.
3. Choose PostgreSQL from the Database Platform drop-down list.
4. In the Instance text box, type the database cluster name or IP address of the server where PostgreSQL is installed.

   The following are examples of information to include in the Instance text box:

   • If the PostgreSQL database cluster is on a server named ficus, type ficus in the Instance text box.
   • If the PostgreSQL database cluster is listening on a port other than the default (5432), include the port number in the instance. For example, if PostgreSQL is installed on a server named mamabear and is listening on port 49200, type mamabear,49200 in the Instance text box.
   • To use an IPV6 address to access the server where PostgreSQL is installed, type the address in brackets. For example, if the IPV6 address of the server is 1111:aa1:0:1:f111:a222:33f3:b4bb, type [1111:aa1:0:1:f111:a222:33f3:b4bb] in the Instance text box.
   • For an Amazon Aurora (PostgreSQL-compatible edition) or Amazon RDS for PostgreSQL instance, provide the instance name in the format <database_instance_identifier>.<region_id>.rds.amazonaws.com. For example, if created the instance with the identifier mypgrdsdb in the us-east-1 Amazon Web Services region, type mypgrdsdb.us-east-1.rds.amazonaws.com in the Instance text box.
   • For a Google Cloud SQL for PostgreSQL instance, type the public IP address of the instance in the Instance text box.
   • For a Microsoft Azure Cosmos DB for PostgreSQL instance, the connection string is in the format c.<cluster_name>.postgres.database.azure.com. For example, you named the cluster allmydata when you created it, type c.allmydata.postgres.database.azure.com in the Instance text box.
   • For a Microsoft Azure Database for PostgreSQL instance, the instance name is in the format <server_name>.postgres.database.azure.com. For example, you named the server spatialdata when you created it, type spatialdata.postgres.database.azure.com in the Instance text box.

   Note:

   If the PostgreSQL database cluster is listening on a port other than the default (5432), you can include the port number in the instance. For example, if PostgreSQL is installed on server mamabear and is listening on port 49200, type mamabear,49200 in the Instance text box. Alternatively, provide the port number as an additional property (see step 8 below).

5. Choose the type of authentication to use when connecting to the database: Database authentication or Operating system authentication.
   • If you choose Operating system authentication, you do not need to type a username and password—the connection is made using the Security Support Provider Interface (SSPI), Lightweight Directory Access Protocol (LDAP), or Generic Security Service Application Program Interface (GSSAPI) login name and password used to sign in to the operating system. If the login used for the operating system is not mapped to a valid database login, the connection fails.

   • If you choose Database authentication, you must provide a valid username and password in the User name and Password text boxes, respectively. Usernames can be a maximum of 30 characters.

     Uncheck Save user name and password if you prefer not to save your login information as part of the connection; doing this can help maintain the security of the database. However, if you do this, you will be prompted to provide a username and password every time you connect.

   Note:

   Save user name and password must be checked for connection files that use database authentication to provide ArcGIS web services with access to the database, or if you want to search ArcGIS AllSource to locate data accessed through this connection file.

6. Type or choose the name of the database on the PostgreSQL database cluster to which you want to connect.
7. Type a name for the connection file in the Connection File Name field.

   As you provide connection information, a default name is created. You can use the default, or type a more descriptive file name.

   This is the name that will appear in the Catalog pane and view, and the name of the .sde file stored on disk.

8. If the PostgreSQL database cluster communicates over a nondefault port, expand the Additional Properties section, choose Port from the Property drop-down menu, and type the port in the Value field.
9. Click Validate to confirm that the connection information is valid and the database is accessible.

   If the database contains a geodatabase and the connection is valid, the Geodatabase Properties tab is now active. If necessary, you can alter geodatabase connection properties to connect to a traditional version other than the default version, configure the connection as a branch version connection, or connect to a historical moment.

10. Click OK to create the connection file.

A database connection appears under Databases in the Catalog pane, and a connection file (.sde) is created in the ArcGIS AllSource project directory.

Register the connection with ArcGIS Enterprise

To allow ArcGIS Server sites to access the data, use the database connection file you created to add a registered data store in ArcGIS AllSource or add a data store item in the portal.

To register a connection using operating system authentication requires ArcGIS Enterprise 11.1 or later. The ArcGIS Server account used to run ArcGIS Server must be an SSPI or LDAP login mapped to a login role in PostgreSQL. That login role must be granted privileges to the tables to be published from the geodatabase or PostgreSQL database.