Add the owner of the Schema to the target user in apsaradb RDS for PostgreSQL -

Overview

This article describes how to set the Schema owner in the apsaradb RDS for PostgreSQL instance as a new user.

Detail

Alibaba Cloud reminds you that:

Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.

You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.

If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

For more information about how to set the owner of a Schema as a new user, follow these steps.

Log on to the server and run the following command to log on to the database:
```
psql -U [$User_Name] -h [$Host] -p [$Port]
```
Note:
- [$User_Name]: specifies the username of the apsaradb RDS for PostgreSQL instance.
- [$Host]: hostname or IP address. If you want to connect to the RDS instance over an internal network, enter the internal endpoint of the RDS instance. If you want to connect to the RDS instance over the Internet, enter the public endpoint of the RDS instance. For more information about how to view the intranet /internet address and port number of an RDS instance, see view or modify intranet /internet address and port number.
- [$Port]: the Port number corresponding to the endpoint.
- For more information about how to connect through a client or DMS, see connect PostgreSQL instances.
Run the following SQL statement in the database to create a user:
```
CREATE USER [$New_User] LOGIN PASSWORD '[$Password]';
```
Note:
- [$New_User]: indicates the new user name.
- [$Password]: indicates the Password of the new user.
Run the following SQL statement to create a database:
```
create database [$New_Database];
```
Note:[$New_Database] is the name of a new database.
Run the following SQL statements to switch to the new database and create a Schema:
```
\c [$New_Database]
create schema [$Database_Name];
```

Run the following SQL statements in sequence to authorize the new user and set the owner of the new Schema to the new user:

grant [$New_User] to [$User_Name]; alter schema [$New_Database] owner to [$New_User]; revoke [$New_User] from [$User_Name];

Exit the database. Log on to the database with a new username, and then run the following SQL statement to make sure that the Schema of the new database is owned by the new user:
```
select * from information_schema.schemata;
```

Application scope

ApsaraDB RDS for PostgreSQL