Updated on 2022-07-29 GMT+08:00

Configuring a Data Source in the Linux OS

The ODBC DRIVER (psqlodbcw.so) provided by GaussDB(DWS) can be used after it has been configured in the data source. To configure data sources, users must configure the odbc.ini and odbcinst.ini files on the server. The two files are generated during the unixODBC compilation and installation, and are saved in the /usr/local/etc directory by default.

Procedure

  1. Obtain the source code package of unixODBC at:

    https://sourceforge.net/projects/unixodbc/files/unixODBC/2.3.0/unixODBC-2.3.0.tar.gz/download

  2. Currently, unixODBC-2.2.1 is not supported. Assume you are to install unixODBC-2.3.0. Run the following commands. unixODBC is installed in the /usr/local directory by default. The data source file is generated in the /usr/local/etc directory, and the library file is generated in the /usr/local/lib directory.

    tar zxvf unixODBC-2.3.0.tar.gz
    cd unixODBC-2.3.0
    # Open the configure file. If it does not exist, open the configure.ac file. Find LIB_VERSION.
    # Change the value of LIB_VERSION to 1:0:0 to compile a *.so.1 dynamic library with the same dependency on psqlodbcw.so.
    vim configure
    
    ./configure --enable-gui=no # To perform the compilation on a TaiShan server, add the configure parameter --build=aarch64-unknown-linux-gnu.
    make
    # The installation may require root permissions.
    make install

    Install unixODBC. If another version of unixODBC has been installed, it will be overwritten after installation.

  3. Replace the GaussDB(DWS) client driver.

    Decompress the dws_8.1.x_odbc_driver_for_xxx_xxx.zip package.

    • Obtain the psqlodbcw.la and psqlodbcw.so files in the /dws_8.1.x_odbc_driver_for_xxx_xxx/odbc/lib directory.
    • The lib file is generated in the /dws_8.1.x_odbc_driver_for_xxx_xxx/lib directory.

  4. Configure the data source.

    1. Configure the ODBC driver file.

      Add the following content to the end of the /usr/local/etc/odbcinst.ini file:

      [GaussMPP]
      Driver64=/usr/local/lib/psqlodbcw.so
      setup=/usr/local/lib/psqlodbcw.so

      For descriptions of the parameters in the odbcinst.ini file, see Table 1.

      Table 1 odbcinst.ini configuration parameters

      Parameter

      Description

      Example

      [DriverName]

      Driver name, corresponding to Driver in DSN.

      [DRIVER_N]

      Driver64

      Path of the dynamic driver library

      Driver64=/xxx/odbc/lib/psqlodbcw.so

      setup

      Driver installation path, which is the same as the dynamic library path in Driver64.

      setup=/xxx/odbc/lib/psqlodbcw.so

    2. Configure the data source file.

      Add the following content to the end of the /usr/local/etc/odbc.ini file:

      [MPPODBC]
      Driver=GaussMPP
      Servername=10.10.0.13 (database server IP address)
      Database=gaussdb (database name)
      Username=dbadmin (database username)
      Password= (database user password)
      Port=8000 (database listening port)
      Sslmode=allow

      For descriptions of the parameters in the odbc.ini file, see Table 2.

      Table 2 odbc.ini configuration parameters

      Parameter

      Description

      Example

      [DSN]

      Data source name

      [MPPODBC]

      Driver

      Driver name, corresponding to DriverName in odbcinst.ini

      Driver=DRIVER_N

      Servername

      IP address of the server

      Servername=10.145.130.26

      Database

      Name of the database to connect to

      Database=gaussdb

      Username

      Name of the database user

      Username=dbadmin

      Password

      Password of the database user

      Password=

      NOTE:

      After a user established a connection, the ODBC driver automatically clears their password stored in memory.

      However, if this parameter is configured, unixODBC will cache data source files, which may cause the password to be stored in the memory for a long time.

      When you connect to an application, you are advised to send your password through an API instead of writing it in a data source configuration file. After the connection has been established, immediately clear the memory segment where your password is stored.

      Port

      Port ID of the server

      Port=8000

      Sslmode

      Whether to enable the SSL mode

      Sslmode=allow

      UseServerSidePrepare

      Whether to enable the extended query protocol for the database.

      The value can be 0 or 1. The default value is 1, indicating that the extended query protocol is enabled.

      UseServerSidePrepare=1

      UseBatchProtocol

      Whether to enable the batch query protocol. If it is enabled, the DML performance can be improved. The value can be 0 or 1. The default value is 1.

      If this parameter is set to 0, the batch query protocol is disabled (mainly for communication with earlier database versions).

      If this parameter is set to 1 and the support_batch_bind parameter is set to on, the batch query protocol is enabled.

      UseBatchProtocol=1

      ConnectionExtraInfo

      Whether to display the driver deployment path and process owner in the connection_info parameter mentioned in connection_info

      ConnectionExtraInfo=1

      NOTE:

      The default value is 0. If this parameter is set to 1, the ODBC driver reports the driver deployment path and process owner to the database and displays the information in the connection_info parameter (see connection_info). In this case, you can query the information from PG_STAT_ACTIVITY or PGXC_STAT_ACTIVITY.

      ForExtensionConnector

      ETL tool performance optimization parameter. It can be used to optimize the memory and reduce the memory usage by the peer CN, to avoid system instability caused by excessive CN memory usage.

      The value can be 0 or 1. The default value is 0, indicating that the optimization item is disabled.

      Do not set this parameter for other services outside the database system. Otherwise, the service correctness may be affected.

      ForExtensionConnector=1

      KeepDisallowPremature

      Specifies whether the cursor in the SQL statement has the with hold attribute when the following conditions are met: UseDeclareFetch is set to 1, and the application invokes SQLNumResultCols, SQLDescribeCol, or SQLColAttribute after invoking SQLPrepare to obtain the column information of the result set.

      The value can be 0 or 1. 0 indicates that the with hold attribute is supported, and 1 indicates that the with hold attribute is not supported. The default value is 0.

      KeepDisallowPremature=1

      NOTE:
      • When UseServerSidePrepare is set to 1, the KeepDisallowPremature parameter does not take effect. To use this parameter, set UseServerSidePrepare to 0. For example, set UseDeclareFetch to 1.

        KeepDisallowPremature=1

        UseServerSidePrepare=0

      The valid values of sslmode are as follows:

      Table 3 sslmode options

      sslmode

      Whether SSL Encryption Is Enabled

      Description

      disable

      No

      The SSL secure connection is not used.

      allow

      Probably

      The SSL secure encrypted connection is used if required by the database server, but does not check the authenticity of the server.

      prefer

      Probably

      The SSL secure encrypted connection is used as a preferred mode if supported by the database, but does not check the authenticity of the server.

      require

      Yes

      The SSL secure connection must be used, but it only encrypts data and does not check the authenticity of the server.

      verify-ca

      Yes

      The SSL secure connection must be used, and it checks whether the database has certificates issued by a trusted CA.

      verify-full

      Yes

      The SSL secure connection must be used. In addition to the check scope specified by verify-ca, it checks whether the name of the host where the database resides is the same as that on the certificate. This mode is not supported.

  5. Enable the SSL mode.

    To use SSL certificates for connection, decompress the certificate package contained in the GaussDB(DWS) installation package, and run the source sslcert_env.sh file in a shell environment to deploy certificates in the default location of the current session.

    Or manually declare the following environment variables and ensure that the permission for the client.key* series files is set to 600.

    export PGSSLCERT= "/YOUR/PATH/OF/client.crt" # Change the path to the absolute path of client.crt.
    export PGSSLKEY= "/YOUR/PATH/OF/client.key" # Change the path to the absolute path of client.key.

    In addition, change the value of Sslmode in the data source to verify-ca.

  6. Configure the database server.

    1. Log in to any host where the GaussDB(DWS) cluster is installed as the OS user omm.
    2. Run the following command to add NIC IP addresses or host names, with values separated by commas (,). The NICs and hosts are used to provide external services. In the following command, NodeName specifies the name of the current node.
      gs_guc reload -Z coordinator -N NodeName -I all -c "listen_addresses='localhost,192.168.0.100,10.11.12.13'"

      If direct routing of LVS is used, add the virtual IP address (10.11.12.13) of LVS to the server listening list.

      listen_addresses can be set to * or 0.0.0.0 to listen to all NICs, but this is not recommended because of potential security risks. You are advised to configure the required IP addresses or host names to listen.

    3. Run the following command to add an authentication rule to all the CN configuration files: In this example, the IP address (10.11.12.13) of the client is the remote host IP address.
      gs_guc reload -Z coordinator -N all -I all -h "host all jack 10.11.12.13/32 sha256"
      • -Z coordinator indicates that the instance type is coordinator.
      • -N all indicates all hosts in the cluster.
      • -I all indicates all instances of the host.
      • -h indicates statements that need to be added in the pg_hba.conf file.
      • all indicates that a client can connect to any database.
      • jack indicates the user that accesses the database.
      • 10.11.12.13/32 indicates that only hosts whose IP address is 10.11.12.13 can be connected. Modify its value based on actual network configuration. 32 indicates that there are 32 bits whose value is 1 in the subnet mask. That is, the subnet mask is 255.255.255.255.
      • sha256 indicates that the password of user jack is encrypted using the SHA-256 algorithm.

      If the ODBC client and the CN to be connected are deployed on the same machine, you can use the local trust authentication mode. Run the following command:

      local all all xxx.xxx.xxx.xxx/32 trust
      If the ODBC client and the CN to be connected are deployed on different machines, use the SHA256 authentication mode. Run the following command:
      host all all xxx.xxx.xxx.xxx/32 sha256
    4. Restart the cluster.
      gs_om -t stop
      gs_om -t start

  7. Configure environment variables.

    vim ~/.bashrc

    Add the following content to the end of the configuration file:

    export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH
    export ODBCSYSINI=/usr/local/etc
    export ODBCINI=/usr/local/etc/odbc.ini

  8. Run the following commands to validate the settings:

    source ~/.bashrc

Testing Data Source Configuration

Run the isql -v GaussODBC command (GaussODBC is the data source name).

  • If the following information is displayed, the configuration is correct and the connection succeeds.
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL> 
  • If error information is displayed, the configuration is incorrect. Check the configuration.

Troubleshooting

  • [UnixODBC][Driver Manager]Can't open lib 'xxx/xxx/psqlodbcw.so' : file not found.

    Possible causes:

    • The path configured in the odbcinst.ini file is incorrect.

      Run ls to check the path in the error information, ensuring that the psqlodbcw.so file exists and you have execution permissions on it.

    • The dependent library of psqlodbcw.so does not exist or is not in system environment variables.

      Run ldd to check the path in the error information. If libodbc.so.1 or other unixODBC libraries are lacking, configure unixODBC again following the procedure provided in this section, and add the lib directory under its installation directory to LD_LIBRARY_PATH. If other libraries are lacking, add the lib directory under the ODBC driver package to LD_LIBRARY_PATH.

  • [UnixODBC]connect to server failed: no such file or directory

    Possible causes:

    • An incorrect or unreachable database IP address or port was configured.

      Check the Servername and Port configuration items in data sources.

    • Server monitoring is improper.

      If Servername and Port are correctly configured, ensure the proper network adapter and port are monitored based on database server configurations in the procedure in this section.

    • Firewall and network gatekeeper settings are improper.

      Check firewall settings, ensuring that the database communication port is trusted.

      Check to ensure network gatekeeper settings are proper (if any).

  • [unixODBC]The password-stored method is not supported.

    Possible causes:

    The sslmode configuration item is not configured in the data sources.

    Solution:

    Set it to allow or a higher level. For more details, see Table 3.

  • Server common name "xxxx" does not match host name "xxxxx"

    Possible causes:

    When verify-full is used for SSL encryption, the driver checks whether the host name in certificates is the same as the actual one.

    Solution:

    To solve this problem, use verify-ca to stop checking host names, or generate a set of CA certificates containing the actual host names.

  • Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

    Possible causes:

    The executable file (such as the isql tool of unixODBC) and the database driver (psqlodbcw.so) depend on different library versions of ODBC, such as libodbc.so.1 and libodbc.so.2. You can verify this problem by using the following method:

    ldd `which isql` | grep odbc
    ldd psqlodbcw.so | grep odbc

    If the suffix digits of the outputs libodbc.so are different or indicate different physical disk files, this problem exists. Both isql and psqlodbcw.so load libodbc.so. If different physical files are loaded, different ODBC libraries with the same function list conflict with each other in a visible domain. As a result, the database driver cannot be loaded.

    Solution:

    Uninstall the unnecessary unixODBC, such as libodbc.so.2, and create a soft link with the same name and the .so.2 suffix for the remaining libodbc.so.1 library.

  • FATAL: Forbid remote connection with trust method!

    For security purposes, the CN forbids access from other nodes in the cluster without authentication.

    To access the CN from inside the cluster, deploy the ODBC program on the machine where the CN is located and use 127.0.0.1 as the server address. It is recommended that the service system be deployed outside the cluster. If it is deployed inside, the database performance may be affected.

  • [unixODBC][Driver Manager]Invalid attribute value

    This problem occurs when you use SQL on other GaussDB. The possible cause is that the unixODBC version is not the recommended one. You are advised to run the odbcinst --version command to check the unixODBC version.

  • authentication method 10 not supported.

    If this error occurs on an open source client, the cause may be:

    The database stores only the SHA-256 hash of the password, but the open source client supports only MD5 hashes.

    • The database stores the hashes of user passwords instead of actual passwords.
    • In versions earlier than V100R002C80SPC300, the database stores only SHA-256 hashes and no MD5 hashes. Therefore, MD5 cannot be used for user password authentication.
    • In V100R002C80SPC300 and later, if a password is updated or a user is created, both types of hashes will be stored, compatible with open-source authentication protocols.
    • An MD5 hash can only be generated using the original password, but the password cannot be obtained by reversing its SHA-256 hash. If your database is upgraded from a version earlier than V100R002C80SPC300, passwords in the old version will only have SHA-256 hashes and not support MD5 authentication.

    To solve this problem, you can update the user password. For details, see "ALTER USER" in the SQL Syntax. Alternatively, create a user (see "CREATE USER" in the SQL Syntax), assign the same permissions to the user, and use the new user to connect to the database.

  • unsupported frontend protocol 3.51: server supports 1.0 to 3.0

    The database version is too early or the database is an open-source database. Use the driver of the required version to connect to the database.

  • FATAL: GSS authentication method is not allowed because XXXX user password is not disabled.

    In some cases, the error is: GSSAPI authentication not supported.

    In pg_hba.conf of the target CN, the authentication mode is set to gss for authenticating the IP address of the current client. However, this authentication algorithm cannot authenticate clients. Change the authentication algorithm to sha256 and try again. For details, see 6.

    Note that cross-node connection to the database in the cluster is not supported. If the error is caused by cross-node connection to the CN in the cluster, connect the service program to the database from a node outside the cluster and try again.