To enhance the security of sensitive columns in PolarDB for PostgreSQL and and prevent unauthorized users from accessing plaintext data, you can use the column encryption feature of Data Security Center (DSC). This feature ensures data remains available within the database while being invisible to unauthorized users. It helps you defend against external and internal security threats, making your cloud data a private asset.
Prerequisites
Kernel version: The major version of your instance must be PostgreSQL 14 or , and the minor kernel version must be 2.0.14.15.31.0 or later.
NoteYou can view the minor kernel version on the console or by running the
SHOW polardb_version;command. If the minor kernel version does not meet the requirement, you must upgrade the minor kernel version.You can view the minor kernel version in the console or run the
SHOW polardb_version;command. If the minor kernel version requirement is not met, upgrade the minor kernel version.Instance region:
The Chinese mainland
China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Hangzhou), China (Shanghai), China (Shenzhen), China (Guangzhou), China (Chengdu).
Outside the Chinese mainland
China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Germany (Frankfurt).
Features
The column encryption feature for PolarDB for PostgreSQL and is a feature of DSC. It uses the AES-256-GCM encryption algorithm with local keys to encrypt sensitive data columns. This ensures that sensitive data is stored in an encrypted format. Authorized users can decrypt and access the plaintext data using a fully encrypted client. You can select and modify the encryption scope, including which PolarDB for PostgreSQL and instances, databases, tables, and columns to encrypt.
Before you begin
Before you enable column encryption, you must complete these steps: purchase or upgrade your DSC service, authorize DSC to access cloud resources, authorize database assets, connect to the database, and run a sensitive data identification task.
1. Purchase or upgrade DSC
2. Authorize DSC access
3. Synchronize database assets
4. Enable classification and grading
To use column encryption, you must first authorize a database connection and complete a data identification task.
-
In the left-side navigation pane, choose Asset Center.
-
In the Structured Data section, click the data type for which you want to configure column encryption.
-
Click the
icon in the Data Classification column of the target asset instance.NoteMake sure that the instance has a database and its Instance Status is Running. If no database is created, you cannot enable data classification and grading or create the corresponding discovery job.
-
In the Enable Classification and Grading dialog box, configure the parameters as described in the following table.
Parameter
Description
Activation Method
Configure the account information that is used to connect to the database for data detection. Two methods are supported:
Automatically create database accounts : DSC automatically creates a read-only account that starts with the
sddp_autoprefix in the target data asset. DSC uses this account to connect to the target database and perform data detection tasks.NoteThis method is available only for data types that support one-click enablement.
Manually enter username and password: Enter the account and password that you use to connect to the database.
Authorization Scope
The authorization scope for data detection.
Entire data source.
Manage authorization scope in the data source list: Select the desired authorization scope.
Automatically create and start a default scan task
If you select this option, DSC automatically creates a default scan task after the database is successfully connected.
On the tab, you can click Default Tasks to view the execution status of the scan task. For more information, see Scan for sensitive data using a detection task.
Automatically connect to new databases.
If you select this option, DSC automatically connects to new databases that are detected in your database instance after a manual or automatic asset sync.
-
After completing the configuration, click OK.
5. Review database information
Enable column encryption
After you confirm the target database instance information and the Encryption Check shows Passed, complete the column encryption configuration.
Click Rapid Encryption above the database instance list to configure column encryption for all unencrypted columns. Alternatively, click Rapid Encryption in the Actions column for a target database instance to configure column encryption for that instance.
In the Encryption Configuration panel, select the Asset Type, Instance name, Encryption Algorithm, Encryption Method, and Plaintext Permission Accounts. Then, select the target Databases, Table, and Column for which you want to configure column encryption, and click OK. Note the following:
While DSC supports multiple encryption algorithms, PolarDB for PostgreSQL and currently only support the
AES-256-GCMencryption algorithm and the Local Encryption method.After you configure encryption, the default permission for PolarDB for PostgreSQL, , and database accounts is Ciphertext Permission (JDBC Decryption). These accounts access ciphertext data by default and can use client-side code with a local key to decrypt and view the original plaintext data.
If you need to access plaintext data directly, you can add the corresponding database account to the Plaintext Permission Accounts list. This account will have plaintext permission and can directly access the plaintext data of encrypted columns.
ImportantIf you need to perform sensitive data classification on the latest data in the database, the database account used as a credential (the account used to connect DSC to the PolarDB for PostgreSQL, , or cluster) must have plaintext permission.
Modify encryption configuration
Modify encryption scope
After you enable column encryption, you can modify the encryption scope by enabling or disabling column encryption for specific columns within a database instance as needed.
In the left-side navigation pane, choose .
In the instance list, expand the target instance. In the database list, find the target Databases, Table, and Column, and then click Enable Encryption or Disable Encryption to configure encryption for a single column.
Modify account permissions
Except for accounts that are set to Plaintext Permissions, all other accounts in the database instance have Ciphertext Permission (JDBC Decryption). You can modify account permissions to Plaintext Permissions or Ciphertext Permission (JDBC Decryption) based on your business requirements.
-
On the page, click Permission Settings in the Accounts section.
Alternatively, in the Actions column of the instance list, click Edit. In the Edit panel, click Configure on Account Permissions.
-
In the Permission Settings panel, search for the target instance and account to view the current account permissions.
NoteIf a newly added database account is not displayed in the list, perform an Asset synchronization and then check again.
-
Click Modify Permissions in the Actions column of the target account.
You can also select multiple target accounts with the same permission and click Batch Modify Permissions below the list.
-
In the Modify Permissions dialog box, select the target permission and click OK.
Verify encryption results
You can verify access to encrypted columns based on the column encryption and database account permissions you have configured.
The column encryption feature is not fully compatible with third-party clients (for example, you may experience issues when you view encrypted data by using Data Management (DMS)), so we recommend that you use the column encryption driver (JDBC) or the PolarDB-Tools client to access encrypted data.
For example, assume the birth_date column in the students01 table of a test PolarDB for PostgreSQL, , or cluster is encrypted. One database account in the cluster is granted Plaintext Permissions, while another account retains Ciphertext Permission (JDBC Decryption).
On the Column Encryption page, you can expand a PolarDB instance to view the sensitivity level, encryption status, and available actions for each column. Columns that have been encrypted, such as a column with sensitivity level S2, show an Encrypted status, and you can click Disable Encryption. Unencrypted columns show a Not Encrypted status, and you can click Enable Encryption.
Connect to the database using the account with Ciphertext Permission (JDBC Decryption). Run the
SELECT * FROM students01;statement. The encrypted column returns ciphertext data.Connect to the database using the account with Plaintext Permissions. Run the
SELECT * FROM students01;statement. The encrypted column returns plaintext data. The query results show that thestudents01table contains four columns:id,sid,name, andbirth_date. Thebirth_datecolumn, which was encrypted, now displays plaintext dates such as 1991-04-16 and 2005-05-13.
Client usage
If your database account has ciphertext permission (JDBC decryption), you can use the column encryption driver (JDBC) to connect to a PolarDB for PostgreSQL cluster and access encrypted column data from your Java application. The JDBC driver automatically decrypts ciphertext data and returns plaintext data. The process is transparent to the application. For more information, see column encryption driver (JDBC).
FAQ
Related content
-
For information about the features and working principles of database column encryption, see Column encryption overview.
-
If sensitive column data in a database changes after authorization, you must rescan it. For more information, see Scan for sensitive data by using a discovery job.