Help Center > > Developer Guide> Querying External Data> Querying Data on OBS> Creating a Foreign Server

Creating a Foreign Server

Updated at: Jul 15, 2020 GMT+08:00

This section describes how to create a foreign server that is used to define the information about the OBS server and is invoked by foreign tables. For details about the syntax for creating foreign servers, see CREATE SERVER.

(Optional) Creating a User and a Database and Granting the User Foreign Table Permissions

Common users do not have permissions to create foreign servers and tables. If you want to use a common user to create foreign servers and tables in a customized database, perform the following steps to create a user and a database, and grant the user foreign table permissions.

In the following example, a common user dbuser and a database mydatabase are created. Then, an administrator is used to grant foreign table permissions to user dbuser.

  1. Connect to the default database postgres as a database administrator through the database client tool provided by DWS.

    For example, use the gsql client to connect to the database by running the following command:

    1
    gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -r
    

    Enter your password as prompted.

  2. Create a common user and use it to create a database.

    Create a user named dbuser that has the permission to create databases.

    1
    CREATE USER dbuser WITH CREATEDB PASSWORD "Bigdata@123";
    
    Switch to the created user.
    1
    SET ROLE dbuser PASSWORD "Bigdata@123";
    
    Run the following command to create the database demo:
    1
    CREATE DATABASE mydatabase;
    

    Query the database.

    1
    SELECT * FROM pg_database;
    

    The database is successfully created if the returned result contains information about mydatabase.

    datname   | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility |                       datacl
    
    ------------+--------+----------+------------+----------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+--------------------------------------
    --------------
     template1  |     10 |        0 | C          | C        | t             | t            |           -1 |         14146 |         1351 |          1663 | ORA              | {=c/omm,omm=CTc/omm}
     template0  |     10 |        0 | C          | C        | t             | f            |           -1 |         14146 |         1350 |          1663 | ORA              | {=c/omm,omm=CTc/omm}
     postgres   |     10 |        0 | C          | C        | f             | t            |           -1 |         14146 |         1352 |          1663 | ORA              | {=Tc/omm,omm=CTc/omm,chaojun=C/omm,hu
    obinru=C/omm}
     mydatabase |  17000 |        0 | C          | C        | f             | t            |           -1 |         14146 |         1351 |          1663 | ORA              |
    (4 rows)

  3. Grant the permissions for creating foreign servers and using foreign tables to a common user as the administrator.

    Connect to the new database as a database administrator through the database client tool provided by DWS.

    You can use the gsql client to run the following command, switching to an administrator user, and connect to the new database:

    1
    \c mydatabase dbadmin;
    

    Enter the password of the system administrator as prompted.

    Note that you must use the administrator account to connect to the database where a foreign server is to be created and foreign tables are used; and then grant permissions to the common user.

    By default, only system administrators can create foreign servers. Common users can create foreign servers only after being authorized. Run the following command to grant the permission:
    1
    GRANT ALL ON FOREIGN DATA WRAPPER dfs_fdw TO dbuser;
    

    where fdw_name can be hdfs_fdw or dfs_fdw, and dbuser is the name of the user who creates SERVER.

    Run the following command to grant the user the permission to use foreign tables:

    1
    ALTER USER dbuser USEFT;
    

    Query for the user.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    SELECT r.rolname, r.rolsuper, r.rolinherit,
      r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
      r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
    , r.rolreplication
    , r.rolauditadmin
    , r.rolsystemadmin
    , r.roluseft
    FROM pg_catalog.pg_roles r
    ORDER BY 1;
    

    The authorization is successful if the dbuser information in the returned result contains the UseFT permission.

    rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolconnlimit | rolvalidbegin | rolvaliduntil | memberof | rolreplication | rolauditadmin | rolsystemadmin | roluseft
    -----------+----------+------------+---------------+-------------+-------------+--------------+---------------+---------------+----------+----------------+---------------+----------------+----------
     dbuser    | f        | t          | f             | t           | t           |           -1 |               |               | {}       | f              | f             | f              | t
     lily      | f        | t          | f             | f           | t           |           -1 |               |               | {}       | f              | f             | f              | f
     omm       | t        | t          | t             | t           | t           |           -1 |               |               | {}       | t              | t             | t              | t  

Creating a Foreign Server

  1. Use the user who is about to create a foreign server to connect to the corresponding database.

    In this example, use common user dbuser created in (Optional) Creating a User and a Database and Granting the User Foreign Table Permissions to connect to mydatabase created by the user. You need to connect to the database through the database client tool provided by DWS.

    You can use the gsql client to log in to the database in either of the following ways:

    • If you have logged in to the gsql client, run the following command to switch the database and user:
      1
      \c mydatabase dbuser;
      

      Enter the password as prompted.

    • If you have not logged in to the gsql client or have exited the gsql client by running the \q command, run the following command to reconnect to it:
      1
      gsql -d mydatabase -h 192.168.2.30 -U dbuser -p 8000 -r
      

      Enter the password as prompted.

  2. Create a foreign server.

    For details about the syntax for creating foreign servers, see CREATE SERVER.

    For example, run the following command to create a foreign server named obs_server.

    1
    2
    3
    4
    5
    6
    7
    CREATE SERVER obs_server FOREIGN DATA WRAPPER dfs_fdw 
    OPTIONS ( 
      address 'obs.ap-southeast-1.myhuaweicloud.com' , 
      ACCESS_KEY 'access_key_value_to_be_replaced', 
      SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', 
      type 'obs' 
    );
    

    Mandatory parameters are described as follows:

    • Name of the foreign server

      You can customize a name.

      In this example, the name is set to obs_server.

    • FOREIGN DATA WRAPPER

      fdw_name can be hdfs_fdw or dfs_fdw, which already exists in the database.

    • OPTIONS parameters
      • address

        This parameter specifies the IP address or domain name of the OBS server.

        Obtain the address as follows:

        1. Obtain the OBS path by performing 2 in Preparing Data on OBS.
        2. An OBS path is displayed in either of the following modes, which include the IP address or domain name of OBS:
          • https://IP address or domain name of the storage server/Bucket name/Directory level/Object name
          • https://Bucket name.Domain name/Directory level/Object name
      • (Optional) Access keys (AK and SK)
        DWS needs to use the access keys (AK and SK) to access OBS. Therefore, you must obtain the access keys first.
        • (Mandatory) access_key: specifies users' AK information.
        • (Mandatory) secret_access_key: specifies users' SK information.

        To obtain access keys, log in to the management console, move the cursor over the username in the upper right corner, and click My Credentials. Then choose Access Keys in the navigation tree on the left. On the Access Keys page, you can view the existing AKs or click Create Access Key to create an AK/SK pair.

      • type

        Its value is obs, which indicates that dfs_fdw connects to OBS.

  3. View the foreign server.

    1
    SELECT * FROM pg_foreign_server WHERE srvname='obs_server';
    

    The server is successfully created if the returned result is as follows:

      srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                                                      srvoptions
    
    ------------+----------+--------+---------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -----------
     obs_server |    24661 |  13686 |         |            |        | {address=xxx.xxx.x.xxx,access_key=xxxxxxxxxxxxxxxxxxxx,type=obs,secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx}
    (1 row)

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel