Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Creating a Foreign Server

Updated on 2022-08-16 GMT+08:00

This section describes how to create a foreign server that is used to define the information about OBS servers 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 gaussdb as a database administrator through the database client tool provided by GaussDB(DWS).

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

    1
    gsql -d gaussdb -h 192.168.2.30 -U dbadmin -p 8000 -W password -r
    

  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 'password';
    
    Switch to the created user.
    1
    SET ROLE dbuser PASSWORD 'password';
    
    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.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    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/Ruby,Ruby=CTc/Ruby}
     template0  |     10 |        0 | C          | C        | t             | f            |           -1 |         14146 |         1350 |          1663 | ORA              | {=c/Ruby,Ruby=CTc/Ruby}
     gaussdb   |     10 |        0 | C          | C        | f             | t            |           -1 |         14146 |         1352 |          1663 | ORA              | {=Tc/Ruby,Ruby=CTc/Ruby,chaojun=C/Ruby,hu
    obinru=C/Ruby}
     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 GaussDB(DWS).

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

    1
    \c mydatabase dbadmin;
    

    Enter the password of the system administrator as prompted.

    NOTE:

    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
    2
    GRANT ALL ON SCHEMA public TO dbuser;
    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.

    1
    2
    3
    4
    5
    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
     Ruby       | 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 GaussDB(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
    8
    CREATE SERVER obs_server FOREIGN DATA WRAPPER dfs_fdw 
    OPTIONS ( 
      address 'obs.xxx.com' , 
      ACCESS_KEY 'access_key_value_to_be_replaced', 
      SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', 
      encrypt 'on', 
      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

        Specifies the endpoint of the OBS service.

        Obtain the address as follows:

        1. Obtain the OBS path by performing 2 in Preparing Data on OBS.
        2. The OBS endpoint viewed on the OBS is obs.xxx.myhuaweicloud.com.
      • (Mandatory) Access keys (AK and SK)
        GaussDB(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.

        For details about how to obtain the access keys, see Creating Access Keys (AK and SK).

      • 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:

    1
    2
    3
    4
    5
    6
      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)
    

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback