ALTER SERVER

Description

Adds, changes, or deletes the parameters of an existing server. You can query existing servers from the pg_foreign_server system catalog.

Precautions

Only the server owner or a user granted the ALTER permission can run the ALTER SERVER command. The system administrator has this permission by default. To change the owner of a server, the current user must be the owner of the server or the system administrator, and the user must be a member of the new owner role.
When multi-layer quotation marks are used for sensitive columns (such as password and secret_access_key) in OPTIONS, the semantics is different from that in the scenario where quotation marks are not used. Therefore, sensitive columns are not identified for anonymization.

Syntax

Change the parameters for a foreign server.

    
         ALTER SERVER server_name [ VERSION 'new_version' ]
    [ OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ] ) ];

Click to enlarge

In OPTIONS, ADD, SET, and DROP are operations to be performed. If these operations are not specified, ADD operations will be performed by default. option and value are the parameters of the corresponding operation.

Change the owner of a foreign server.

    
         ALTER SERVER server_name 
    OWNER TO new_owner;

Click to enlarge

Change the name of a foreign server.

    
         ALTER SERVER server_name 
    RENAME TO new_name;

Click to enlarge

Parameters

server_name
Specifies the name of the server to be modified.
new_version
Specifies the target version of the server.
OPTIONS
Change options of the server. ADD, SET, and DROP are operations to be performed. If the operation is not set explicitly, ADD is used. The option name must be unique, and the name and value are also validated with the foreign data wrapper library of the server.
new_owner
Specifies the new owner of the server. To change the owner, you must be the owner of the foreign server and a direct or indirect member of the new owner role, and must have the USAGE permission on the encapsulator of the foreign server.
new_name
Specifies the new name of the server.

Examples

    
     
       
       
         -- Create my_server.
gaussdb=# CREATE SERVER my_server FOREIGN DATA WRAPPER log_fdw;

-- Change the name of an external server.
gaussdb=# ALTER SERVER my_server RENAME TO my_server_1;

-- Create a user.
gaussdb=# CREATE USER jack PASSWORD '********';

-- Change the owner of the external server.
gaussdb=# ALTER SERVER my_server_1 OWNER TO jack;

-- Add the dbname and dbuser information to the external server.
gaussdb=# ALTER SERVER my_server_1 OPTIONS(ADD dbname 'test',dbuser' test_user');

-- Query the external server information.
gaussdb=# SELECT * FROM pg_foreign_server WHERE srvname='my_server_1';
   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |           srvoptions           
-------------+----------+--------+---------+------------+--------+--------------------------------
 my_server_1 |    22060 |  11826 |         |            |        | {dbname=test,"dbuser= test_user"}
(1 row)

-- Change the dbname in the external server to 'db_test'.
gaussdb=# ALTER SERVER my_server_1 OPTIONS (SET dbname 'db_test');

-- Query the external server information.
gaussdb=# SELECT srvname,srvoptions FROM pg_foreign_server WHERE srvname='my_server_1';
   srvname   |            srvoptions             
-------------+-----------------------------------
 my_server_1 | {dbname=db_test,"dbuser= test_user"}
(1 row)

-- Delete the dbname information from the external server.
gaussdb=# ALTER SERVER my_server_1 OPTIONS (DROP dbname);

-- Query the external server information.
gaussdb=# SELECT srvname,srvoptions FROM pg_foreign_server WHERE srvname='my_server_1';
   srvname   |     srvoptions     
-------------+--------------------
 my_server_1 | {"dbuser= test_user"}
(1 row)

-- Delete my_server_1.
gaussdb=# DROP SERVER my_server_1;

-- Delete the user.
gaussdb=# DROP USER jack;

        

      

    
   