Access an ApsaraDB RDS for PostgreSQL database though a VPC for enhanced security - Function Compute

This topic provides provides guidance for establishing access to an ApsaraDB RDS for PostgreSQL database from Function Compute through a virtual private cloud (VPC). Specifically, you can configure VPC-related settings in a function and a whitelist in the database to access the ApsaraDB RDS for PostgreSQL database and perform related operations. In this topic, Serverless Devs is used to deploy a function to access an ApsaraDB RDS for PostgreSQL database in the Python 3 runtime.

Before you start

Create an ApsaraDB RDS for PostgreSQL instance
Important
- Make sure that the database instance that you create is in the same region as the function that needs to access the database instance.
- We recommend that you create the database instance in a zone that Function Compute supports. For more information, see Zones where Function Compute is available.
  If your database instance is not in a zone that is supported by Function Compute, you can create a vSwitch in your VPC in the same zone as Function Compute and use this vSwitch ID in the VPC configurations of the function. vSwitches in the same VPC can communicate with each other over the private network. Therefore, Function Compute can use the vSwitch to access resources in VPCs that reside in other zones. For more information, see What do I do if the vSwitch is in unsupported zone error is reported?
Create a table named COMPANY and insert the ID, NAME, AGE, ADDRESS, and SALARY columns into the table.
In this example, the code logic of index.py is to insert a data record into the COMPANY database table. The schema of the table contains the ID, NAME, AGE, ADDRESS, and SALARY columns. Therefore, you need to create the table and the columns in advance.

Procedure

Install Serverless Devs and Docker and configure the AccessKey information.
For more information, see Install Serverless Devs and dependencies and Configure Serverless Devs.
Run the following command to initialize your project.
```
sudo s init
```
In the CLI, specify Alibaba Cloud as the vendor, specify the quick start mode, and select a built-in Python runtime. Configure the project name and the region where the project is deployed. In this example, the start-fc-postgresql-python project is deployed in the China (Hangzhou) region.
Run the following command to go to the project directory.
```
cd start-fc-postgresql-python
```

Modify the directory file based on your own business requirements.

Edit the s.yaml file. Example:

edition: 3.0.0
name: hello-world-app
access: "default"

vars: # The global variables.
  region: "cn-hangzhou"

resources:
  hello_world:
    component: fc3 # The component name.
    props:
      region: ${vars.region}              # For information about how to use variables, visit https://docs.serverless-devs.com/serverless-devs/yaml#%E5%8F%98%E9%87%8F%E8%B5%8B%E5%80%BC.
      functionName: "start-python-erhg"
      description: 'hello world by serverless devs'
      runtime: "python3.9"
      code: ./code
      handler: index.handler
      memorySize: 128
      timeout: 30
      internetAccess: true
      vpcConfig:
        vpcId: vpc-bp11y195luy47h8cm****  # The ID of the VPC in which the database instance resides.
        securityGroupId: sg-bp1el3hto8hhkdup****  # The ID of the security group.
        vSwitchIds:
          - vsw-bp116uemmj7fniubi****  # Make sure that the CIDR block of the vSwitch is added to the whitelist of the database instance.
      environmentVariables:
        DATABASE: psql****
        HOST: pgm-bp1w1u08pc6m****.pg.rds.aliyuncs.com
        PORT: "5432"
        USER: z****
        PASSWORD: z****24

Important

Make sure that the CIDR block of the vSwitch that you configure for the function is added to the whitelist of the database instance. For more information, see Configure an IP address whitelist for the database.

Edit the index.py code file. The following code snippet provides an example. The code logic is to insert a data record into the COMPANY table in the database.

# -*- coding: utf-8 -*-
import logging
import psycopg2 # You need to install the psycopg2 library first.
import os
import sys
logger = logging.getLogger()


# Create a database connection.
def getConnection():
    try:
        conn = psycopg2.connect(
            database=os.environ['DATABASE'],
            user=os.environ['USER'],
            password=os.environ['PASSWORD'],
            host=os.environ['HOST'],
            port=os.environ['PORT'],
        )
        return conn
    except Exception as e:
        logger.error(e)
        logger.error(
            "ERROR: Unexpected error: Could not connect to PostgreSQL instance.")
        raise Exception(str(e))


def handler(event, context):
    conn = getConnection()
    try:
        cur = conn.cursor()
        # Insert a row of data into the COMPANY table.
        cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (7, 'Paul', 32, 'California', 20000.00 )")
        conn.commit()
        return 'successfully'
    finally:
        # Close the database connection.
        conn.close()

Run the following command to build the project.
```
sudo s build --use-docker
```
Run the following command to deploy the project.
```
sudo s deploy -y
```

Run the following command to invoke the function.

sudo s invoke -e "{}"

Expected command output:

========= FC invoke Logs begin =========
FunctionCompute python3 runtime inited.
FC Invoke Start RequestId: 1-65d47954-1589bf20-c06837b09c3c
FC Invoke End RequestId: 1-65d47954-1589bf20-c06837b09c3c

Duration: 76.43 ms, Billed Duration: 77 ms, Memory Size: 128 MB, Max Memory Used: 12.83 MB
========= FC invoke Logs end =========

Invoke instanceId: c-65d4794d-15f440b6-88291569b9e7
Code Checksum: 2023398494764556055
Qualifier: LATEST
RequestId: 1-65d47954-1589bf20-c06837b09c3c

Invoke Result:
successfully
✔ [hello_world] completed (0.38s)

After the execution, you can log on to the database to view the inserted data. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.

Configure an IP address whitelist for the database

Important

Use an IP address whitelist to authorize functions to access the database. Do not use the security group mode. Otherwise, functions may occasionally fail to connect to the database, which affects the businesses.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Whitelist and SecGroup.
On the Whitelist Settings tab, you can view the mode of the IP address whitelist.
Note
Existing RDS instances may run in enhanced whitelist mode. All new RDS instances run in standard whitelist mode.
On the Whitelist Settings tab that appears, find the default whitelist group and click Modify.
In the Edit Whitelist dialog box, configure IP Addresses and click OK.
Important You must specify an IP address that belongs to the CIDR block of the vSwitch that you add when you configure the network for Function Compute.

More information

For more information about accessing an ApsaraDB RDS for PostgreSQL database, see Python access postgresql database in Function Compute.
For more information about how to view the information about the configured vSwiches in Function Compute and add CIDR blocks of Function Compute vSwiches to the whitelist of a ApsaraDB RDS for MySQL database, see Configure network settings and Configure an IP address whitelist.
You can troubleshoot a database connection failure by referring to How to troubleshoot database access failures?