TechNote (Archived)

ColdFusion MX: Enabling a JDBC data source for PostgreSQL

This TechNote describes how to configure a Macromedia ColdFusion MX (for all platforms) data source to connect to a PostgreSQL database on any platform.

PostgreSQL is an enterprise level database from the open source movement. It differentiates itself from databases such as mySQL by providing robust transactional support, stored procedures and other advanced features.

Although Macromedia does not provide a JDBC driver for PostgreSQL, you can still create a ColdFusion MX data source after configuring both PostgreSQL (running on any platform) and ColdFusion MX for this type of connection.

Configuring PostgreSQL for a ColdFusion data source connection

In order for a ColdFusion MX data source to be able to connect to a PostgreSQL database, ensure that the PostgreSQL Administrator configures the database to accept TCP requests and host-based authentication. In addition, the PostgreSQL Administrator must set up the database and a user account for the ColdFusion username (where the database, username and an associated password are referred to in the ColdFusion MX data source settings).

1. Enable TCP connections by adding the -i flag when starting the postmaster.
   
   By default, PostgreSQL only allows connections from the local machine using Unix domain sockets. ColdFusion will not be able to connect to this database using a JDBC driver unless PostgreSQL is configured to listen to TCP requests.
   
   Refer to Preparing the Database for JDBC in thePostgreSQL Interactive Documentation for more information about how to configure the database for JDBC support.
2. Add a record in the $PGDATA/pg_hba.conf file that describes the ColdFusion server for host-based authentication.
   
   In order for ColdFusion to connect to the database using TCP, the $PGDATA/pg_hba.conf file needs to contain the ColdFusion server IP address and IP mask. This information works in conjunction with the -i flag when the postmaster is started.
   
   Refer to The pg_hba.conf file in the PostgreSQL Documentation for more information about how to configure the database for host-based authentication.
3. Create the ColdFusion user's account and the database they will access.
   
   The ColdFusion data source that you will create will reference a specific PostgreSQL database using a username and password. The user account information and the database itself need to be defined on the database server prior to opening a data source connection.
   
   Refer to Database Users and Privileges in thePostgreSQL Documentation for more information about user accounts.
   
   Refer to Managing Databases in the PostgreSQL Documentation for more information about creating and managing databases.

Configuring ColdFusion MX for a PostgreSQL data source connection

In order for a ColdFusion MX data source to connect to a PostgreSQL database, you'll need to download the PostgreSQL JDBC type IV driver, configure it on the application server and add the data source using the ColdFusion MX Administrator.

1. Download the JDBC driver from the PostgreSQL JDBC website.
2. Enter the JAR file's full path in the Class Path field of the of the ColdFusion MX Administrator's Java and JVM Settings page.
3. Submit the page and restart ColdFusion MX so that the JAR file is recognized.
4. Add a new data source in the ColdFusion Administrator:
   
   Use the Data Sources page to name the new data source and assign it the Other driver in the driver field.
5. Click Add to add the data source.
   
   The Default Driver page will display.
6. Describe the following fields on the Default Driver page:
   

   Field	Description
   JDBC URL	jdbc:postgresql://<pgsqlDbHost>/<databaseName>
   Driver Class	org.postgresql.Driver
   Driver Name	(Optional) The name of the driver
   Username	ColdFusion user account on the database server
   Password	Password for the user account on the database server

   
   Note: The information in the JDBC URL and Driver Class fields is case-sensitive.
7. Submit the data source changes.
8. Verify the data source.

Additional Information

---

• ColdFusion MX: Trailing space in Driver Class field causes data source verification failure (Technote 18612)

Search Support

---