创建外部服务器

（可选）新建用户及数据库并授予外表权限

如果您将使用普通用户在自定义数据库中创建外部服务器和外表，由于普通用户没有外表权限无法创建，所以，您必须参照以下步骤新建用户和数据库，并授予该用户外表权限。

以下示例，是新建一个普通用户dbuser并创建一个数据库mydatabase，然后使用管理员用户授予dbuser外表权限。

1. 使用数据库管理员通过GaussDB(DWS)提供的数据库客户端连接默认数据库gaussdb。

   例如，使用gsql客户端的用户执行下面命令连接数据库：

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

2. 新建一个普通用户，并用它创建一个数据库。

   新建一个具有创建数据库权限的用户dbuser：

   1	CREATE USER dbuser WITH CREATEDB PASSWORD 'password';

   切换为新建的用户：

   1	SET ROLE dbuser PASSWORD 'password';

   执行以下命令创建数据库：

   1	CREATE DATABASE mydatabase;

   查询数据库：

   1	SELECT * FROM pg_database;

   返回结果中有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. 使用管理员用户给普通用户赋予创建外部服务器的权限和使用外表的权限。

   使用数据库管理员用户通过GaussDB(DWS)提供的数据库客户端连接新建的数据库。

   例如，使用gsql客户端的用户可以直接使用如下语句切换为管理员用户去连接新建的数据库：

   1	\c mydatabase dbadmin;

   根据提示输入管理员用户密码。

   

   注意，必须先使用管理员用户连接到将要创建外部服务器和使用外表的数据库，再对普通用户进行授权。

   默认只有系统管理员才可以创建外部服务器，普通用户需要授权才可以创建，执行以下命令授权：

   1 2	GRANT ALL ON SCHEMA public TO dbuser; GRANT ALL ON FOREIGN DATA WRAPPER dfs_fdw TO dbuser;

   其中fdw_name的名字可以是hdfs_fdw或者dfs_fdw，dbuser为创建SERVER的用户名。

   执行以下命令赋予用户使用外表的权限。

   1	ALTER USER dbuser USEFT;

   查看用户：

   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;

   返回结果中dbuser的信息中包含了UseFT权限，表示授权成功：

   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

创建外部服务器

1. 使用即将创建外部服务器的用户去连接其对应的数据库。

   在本示例中，将使用（可选）新建用户及数据库并授予外表权限中创建的普通用户dbuser连接其创建的数据库mydatabase 。用户需要通过GaussDB(DWS)提供的数据库客户端连接数据库。

   例如，使用gsql客户端的用户可以通过以下两种方法中的一种进行连接：

   • 如果已经登录了gsql客户端，可以执行以下命令切换数据库和用户：

     1	\c mydatabase dbuser;

     根据提示输入密码。

   • 如果尚未登录gsql客户端，或者已经登录了gsql客户端执行“\q”退出gsql后，执行以下命令重新进行连接：

     1	gsql -d mydatabase -h 192.168.2.30 -U dbuser -p 8000 -r

     根据提示输入密码。

2. 创建外部服务器。

   例如，执行以下命令创建外部服务器“obs_server”：

   

   认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。

   1 2 3 4 5 6 7 8

   CREATE SERVER obs_server FOREIGN DATA WRAPPER dfs_fdw OPTIONS ( address 'obs.cn-north-1.myhuaweicloud.com' , ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', encrypt 'on', type 'obs' );

   以下为必选参数的说明：

   • 外部服务器名称

     允许用户自定义名字。

     在本例中指定为“obs_server” 。

   • FOREIGN DATA WRAPPER

     fdw_name的名字可以是hdfs_fdw或者dfs_fdw，它在数据库中已经存在。

   • OPTIONS参数
     • address

       指定OBS服务的终端节点。

       address的获取方法如下：

       1. 先通过OBS上的数据准备中的2获取OBS路径。
       2. 在OBS上查看到的OBS路径，为OBS服务终端节点（Endpoint）：obs.example.com。
     • 访问密钥（AK和SK）（必选）
       GaussDB(DWS)需要通过访问密钥（AK和SK）访问OBS，因此，必须先获取访问密钥。

       • “access_key”（必选）：表示用户的AK信息。
       • “secret_access_key”（必选）：表示用户的SK信息。

       获取访问密钥的具体步骤，请参见创建访问密钥（AK和SK）。

     • type

       取值为“obs”，表示dfs_fdw连接的是OBS。

3. 查看外部服务器：

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

   返回结果如下所示，表示已经创建成功：

   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)