• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. User Guide
  4. Connecting to a Cluster
  5. Connecting to a Cluster Using IAM Authentication
  6. Configuring the JDBC Connection to Connect to a Cluster Using IAM Authentication

Configuring the JDBC Connection to Connect to a Cluster Using IAM Authentication

When you use the JDBC application program to connect to a cluster, set the IAM username, credential, and other information as you configure the JDBC URL. After doing this, when you try to access a database, the system will automatically generate a temporary credential and a connection will be set up.

NOTE:

Currently, only clusters whose version is 1.3.1 or later (including 1.3.1) and corresponding JDBC driver provided by DWS can access the databases in IAM authentication mode. Download the JDBC driver. For details, see section Downloading the JDBC or ODBC Driver.

Configuring JDBC Connection Parameters

Table 1 Database connection parameters

Parameter

Description

url

gsjdbc4.jar/gsjdbc200.jar database connection descriptor The following is an example:

jdbc:dws:iam://dws-IAM-demo:eu-de/postgres?AccessKeyID=XXXXXXXXXXXXXXXXXXXX&SecretAccessKey=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX&DbUser=user_test&AutoCreate=true

NOTE:
  • jdbc:dws:iam is a prefix in the URL format.
  • dws-IAM-demo indicates the name of the cluster containing the database.
  • eu-de is the region where the cluster resides. For details about the DWS regions, see Regions and Endpoints.
  • postgres indicates the name of the database to which you want to connect.
  • AccessKeyID and SecretAccessKey is the access key ID and secret access key corresponding to the IAM user specified by DbUser.
  • Set DbUser to the IAM username. Note that the current version does not support hyphens (-) in the IAM username.
    • If the user specified by DbUser exists in the database, the temporary user credential has the same permissions as the existing user.
    • If the user specified by DbUser does not exist in the database and the value of AutoCreate is true, a new user named by the value of DbUser is automatically created. The created user is a common database user by default.
  • Parameter AutoCreate is optional. The default value is false. It indicates whether to automatically create a database user named by the value of DbUser in the database.
    • The value true indicates that a user is automatically created. If the user already exists, the user will not be created again.
    • The value false indicates that a user is not created. If the username specified by DbUser does not exist in the database, an error is returned.

info

Database connection properties. Common properties include the following:

  • ssl: a Boolean type. It indicates whether the SSL connection is used.
  • loglevel: an Integer type. It sets the log amount recorded in DriverManager for LogStream or LogWriter.

    Currently, org.postgresql.Driver.DEBUG and org.postgresql.Driver.INFO logs are supported. If the value is 1, only org.postgresql.Driver.INFO (little information) is recorded. If the value is greater than or equal to 2, org.postgresql.Driver.DEBUG and org.postgresql.Driver.INFO logs are printed, and detailed log information is generated. Its default value is 0, which indicates that no logs are printed.

  • charSet: a string type. It indicates character sets used when data is sent from the database or the database receives data.
  • prepareThreshold: an Integer type. It is used to determine the execution times of PreparedStatement before the information is converted into prepared statements on the server. The default value is 5.

Example

//The following uses gsjdbc4.jar as an example. 
//The following encapsulates the database connection obtaining operations into an interface. You can connect to the database by specifying the region where the cluster is located, cluster name, access key ID, secret access key, and the corresponding IAM username.
public static Connection GetConnection(String clustername, String regionname, String AK, String SK, String username)
    {
        //Driver class
        String driver = "org.postgresql.Driver";
        //Database connection descriptor
        String sourceURL = "jdbc:dws:iam://" + clustername + ":" + regionname + "/postgres?" + "AccessKeyID=" + AK + "&SecretAccessKey=" + SK + "&DbUser=" + username + "&autoCreate=true";
        
        Connection conn = null;
        
        try
        {
            //Load the driver.
            Class.forName(driver);
        }
        catch( Exception e )
        {
             return null;
        }
        
        try
        {
             //Create a connection.
            conn = DriverManager.getConnection(sourceURL);
            System.out.println("Connection succeed!");
         }
        catch(Exception e)
        {
             return null;
        }
        
        return conn;
    };