HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v2.1

Documentation:v2.1

Supported Versions:
Current(v5.0)/
v3.1/
v3.0/
v2.1/
v2.0/
v1.1

Configuring a Data Source in the Linux OS

The ODBC driver (psqlodbcw.so) provided by MogDB can be used after it has been configured in a data source. To configure a data source, you 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 by following link:

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

    After the download, validate the integrity based on the integrity validation algorithm provided by the community.

  2. Install unixODBC. It does not matter if unixODBC of another version has been installed.

    Currently, unixODBC-2.2.1 is not supported. For example, to install unixODBC-2.3.0, run the commands below. 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
#Modify the configure file. (If it does not exist, modify 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 compilation on a Kunpeng server, add the configure parameter --build=aarch64-unknown-linux-gnu.
make
#The installation may require root permissions.
make install

  3. Replace the openGauss driver on the client.

    a. Decompress openGauss- 1.1.0-ODBC.tar.gz to the /usr/local/lib directory. psqlodbcw.la and psqlodbcw.so files are obtained.

    b. Copy the library in the lib directory obtained after decompressing openGauss-1.1.0-ODBC.tar.gz to the /usr/local/lib directory.

  4. Configure a data source.

    a. Configure the ODBC driver file.

    Add the following content to the /xxx/odbc/etc/odbcinst.ini file:

    [GaussMPP]
Driver64=/xxx/odbc/lib/psqlodbcw.so
setup=/xxx/odbc/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 the 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

    b. Configure the data source file.

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

    [MPPODBC]
Driver=GaussMPP
Servername=10.145.130.26 (IP address of the server where the database resides)
Database=postgres  (database name)
Username=omm  (database username)
Password=    (user password of the database)
Port=8000 (listening port of the database)
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 Server IP address Servername=10.145.130.26
    Database Name of the database to connect to Database=postgres
    Username Database username Username=omm
    Password Database user password 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 number of the server Port=8000
    Sslmode Whether to enable SSL Sslmode=allow
    Debug If this parameter is set to 1, the mylog file of the PostgreSQL ODBC driver will be printed. The directory generated for storing logs is /tmp/. If this parameter is set to 0, no directory is generated. Debug=1
    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, 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 support_batch_bind is set to on, the batch query protocol is enabled.    		 UseBatchProtocol=1
    ForExtensionConnector This parameter specifies whether the savepoint is sent. ForExtensionConnector=1
    UnamedPrepStmtThreshold Each time SQLFreeHandle is invoked to release statements, ODBC sends a Deallocate plan_name statement to the server. A large number of such a statement exist in the service. To reduce the number of the statements to be sent, stmt->plan_name is left empty so that the database can identify them as unnamed statements. This parameter is added to control the threshold for unnamed statements. UnamedPrepStmtThreshold=100
    ConnectionExtraInfo Whether to display the driver deployment path and process owner in the connection_info parameter mentioned in connection_info. ConnectionExtraInfo=1NOTE: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 BEGIN). In this case, you can query the information from PG_STAT_ACTIVITY.
    BoolAsChar If this parameter is set to Yes, the Boolean value is mapped to the SQL_CHAR type. If this parameter is not set, the value is mapped to the SQL_BIT type. BoolsAsChar = Yes
    RowVersioning When an attempt is made to update a row of data, setting this parameter to Yes allows the application to detect whether the data has been modified by other users. RowVersioning = Yes
    ShowSystemTables By default, the driver regards the system table as a common SQL table. ShowSystemTables = Yes

    The valid values of Sslmode are as follows:

    Table 3 Sslmode options

    sslmode Whether SSL Encryption Is Enabled Description
    disable No SSL connection is not enabled.
    allow Possible If the database server requires SSL connection, SSL connection can be enabled. However, authenticity of the database server will not be verified.
    prefer Possible If the database supports SSL connection, SSL connection is recommended. However, authenticity of the database server will not be verified.
    require Yes SSL connection is required and data is encrypted. However, authenticity of the database server will not be verified.
    verify-ca Yes SSL connection is required and whether the database has a trusted certificate will be verified.
    verify-full Yes SSL connection is required. In addition to the check scope specified by verify-ca, the system checks whether the name of the host where the database resides is the same as that in the certificate. MogDB does not support this mode.

  5. (Optional) Generate an SSL certificate. For details, see Generating Certificates.This step and step 6 need to be performed when the server and the client are connected via ssl. It can be skipped in case of non-ssl connection.

  6. (Optional) Replace an SSL certificate. For details, see Replacing Certificates.

  7. SSL mode:

    Return to the root directory, create the .postgresql directory, and put root.crt, client.crt, client.key, client.key.cipher, client.key.rand, client.req, server.crt, server.key, server.key.cipher, server.key.rand, and server.req in the directory.
Copy root.crt and related certificate files starting with "server" to the install/data directory of the database (the postgresql.conf file is also stored in the install/data directory.)
Modify the postgresql.conf file.
    ssl = on
    ssl_cert_file = 'server.crt'
    ssl_key_file = 'server.key'
    ssl_ca_file = 'root.crt'
Restart the database after the modification is complete.
Modify the sslmode parameter in the odbc.ini configuration file (require or verify-ca).

  8. Configure the database server.

    a. Log in as the OS user omm to the primary node of the database.

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

    You can also set listen_addresses to * or 0.0.0.0 to listen to all NICs, but this incurs security risks and is not recommended.

    c. Run the following command to add an authentication rule to the configuration file of the primary database node. In this example, the IP address (10.11.12.13) of the client is the remote host IP address.

    gs_guc reload -N all -I all -h "host all jack 10.11.12.13/32 sha256"

    img NOTE:

    • -N all indicates all hosts in MogDB.
    • -I all indicates all instances of the host.
    • -h specifies 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 hosts whose IP address is 10.11.12.13 can be connected. Configure the parameter based on your network conditions. 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 primary database node to connect are deployed on the same machine, you can use the local trust authentication mode. Run the following command:

    local all all trust

    If the ODBC client and the primary database node to connect are deployed on different machines, use the SHA-256 authentication mode. Run the following command:

    host all all xxx.xxx.xxx.xxx/32 sha256

    d. Restart MogDB.

    gs_om -t stop
gs_om -t start

  9. Configure environment variables on the client.

    vim ~/.bashrc

    Add the following information to 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

  10. Run the following command to validate the addition:

source ~/.bashrc

Verifying the Data Source Configuration

Run the ./isql-v MPPODBC command (MPPODBC 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.

FAQs

  • [UnixODBC]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, and ensure that the psqlodbcw.so file exists and you have execute 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 do not exist, 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 do not exist, 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 number 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 by following the database server configurations in the procedure in this section.

    • Firewall and network gatekeeper settings are improper.

      Check firewall settings, and ensure 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 the configuration item to allow or a higher level. For 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 primary database node forbids access from other nodes in MogDB without authentication.

    To access the primary database node from inside MogDB, deploy the ODBC program on the host where the primary database node is located and set the server address to 127.0.0.1. It is recommended that the service system be deployed outside MogDB. If it is deployed inside, database performance may be affected.

  • [unixODBC]Invalid attribute value

    This problem occurs when you use SQL on other MogDB. 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.

    img NOTE:

    • The database stores the hashes of user passwords instead of actual passwords.
    • 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. Passwords in the old version will only have SHA-256 hashes and not support MD5 authentication.
    • The MD5 encryption algorithm has lower security and poses security risks. Therefore, you are advised to use a more secure encryption algorithm.

    To solve this problem, you can update the user password (see ALTER USER) or create a user (see CREATE USER) having the same permissions as the faulty user.

  • 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 pg_hba.conf of the target primary database node, 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 8.

HOME

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.