Configuring a Data Source in the Linux OS

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

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.