ALTER PUBLIC DATABASE LINK v18

Name

ALTER [PUBLIC] DATABASE LINK — Alter an existing database link.

Synopsis

ALTER DATABASE LINK <name>
  OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

ALTER [ PUBLIC ] DATABASE LINK [ IF EXISTS ] <name> CONNECT TO <new_user> IDENTIFIED BY <new_password> [ USING [connector_name] <connection_string> ];

Description

Use the ALTER [PUBLIC] DATABASE LINK command to modify user mappings and foreign server options associated with a database link. EDB Postgres Advanced Server supports two forms of the command:

• Use ALTER DATABASE LINK.. TO to change the ownership of the database link.
• Use ALTER [PUBLIC] DATABASE LINK.. CONNECT TO to modify the credentials used by a database link to connect to the remote database.

To alter a database link, a user needs the appropriate privilege:

• The ALTER DATABASE LINK privilege is required to alter a private database link.
• The ALTER PUBLIC DATABASE LINK privilege is required to alter a public database link.

Parameters

name

The name of the database link.

new_owner

The name of a specific user or role you want to assign as the new owner.

CURRENT_ROLE

Assigns the currently active role as the owner.

CURRENT_USER

Assigns the current user (the user who is running the command) as the owner.

SESSION_USER Assigns the user who started the current database session as the owner.

PUBLIC An optional parameter. If you omit this parameter, then the database link is private and only database link's owner can use it. If the database link is public it is accessible to all users.

IF EXISTS An optional clause that prevents an error if the specified database link does not exist. The command will simply do nothing instead of failing.

CONNECT TO <new_user> IDENTIFIED BY <new_password> This clause changes the credentials used by the database link to connect to the remote database.

• <new_user> The username for the new connection.
• <new_password> The password for the new user.

[ USING [ connector_name ] <connection_string> ] This optional clause changes the remote database you're connecting to.

• connector_name An optional name of the connector used for the database link.
• <connection_string> A string that specifies the location and port of the remote database.

Examples

• This example shows how to change private database link's ownership:

ALTER DATABASE LINK sales_db_link
  OWNER TO user1;

• This example shows how to change the public database link's remote database connection string:

ALTER PUBLIC DATABASE LINK public_reporting_link
  USING 'new_reporting_link';

Viewing privileges

There are functions designed to check redwood specific privileges for a user or role. They are useful for verifying permissions within SQL commands or for troubleshooting access issues. For more information see, functions to view redwood specific privileges.

See also

CREATE PUBLIC DATABASE LINK,
DROP PUBLIC DATABASE LINK