平安云|Ping An Cloud- Building Your Smart Future

Best Practices of Privileged Account

This article describes the functions of a privileged account of RDS PostgreSQL and its best practice in different scenarios. Overview RDS PostgreSQL supports a privileged account. When you create an RDS PostgreSQL instance, the account that is automatically created by the system is the privileged account. You can create and manage multiple business accounts and database with the privileged account to realize privilege separation and data separation in an instance. <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110229-1d6b45f19ba5.png" style="height:23px; margin:0px; width:90px" />: Default database of the privileged account created by the system is postgres and also the system database. It is not applicable for creating business database. Therefore, we recommend that you create a business account and use it to create business database. 1.  Permission separation of multiple accounts In many scenarios, you may hope that different functional staff have different operation permissions of database. For example, some accounts can create database and tables, while some accounts can add, delete, modify, and view data, and some accounts can only read data. You can create multiple business accounts and authorize these accounts with different operation permissions by using the privileged account to realize permission separation. 2.  Multiple applications that are separated by database share instance resource To save costs, you can deploy multiple applications in a database instance and separate these applications with different business databases. Different applications can share RDS PostgreSQL resources such as CPU, memory, and storage resource. For multiple applications, you only need to maintain a database instance. Prerequisites You have successfully created an RDS PostgreSQL instance.For more information, see <a href="http://yun.pingan.com/ssr/help/database/RDS/User_Guide_RDS_PostgreSQL.Instance_Management.Create_Instance.Create_Common_Instance" target="_blank">Create a General Instance</a>. Permission Separation: Create Business Database and Account Solution 1: The business account is the owner of the business database and has all permissions of the business database. 1. The privileged account creates a business account that has login permission by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> CREATE USER Newuser-Name LOGIN PASSWORD 'Password'; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Newuser-Name: Name of a business account.     •   Password: Password of a business account. 2.  Create business database for a business account by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> GRANT Newuser-Name to Rootuser-Name; CREATE DATABASE Newdb-Name OWNER Newuser-Name; REVOKE Newuser-Name from Rootuser-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Rootuser-Name: Name of the privileged account that is created when you create an RDS PostgreSQL instance.     •   Newdb-Name: Name of the business database. 3.  A business account can access the business database and create a table by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> psql -U Newuser-Name r -h Domain-Name -p Port Newdb-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Domain-Name: Access domain name of an RDS PostgreSQL instance. For more information, see <a href="http://yun.pingan.com/ssr/help/database/RDS/User_Guide_RDS_PostgreSQL.Instance_Management.View_Instance.View_Common_Instance" target="_blank">the basic information of the instance</a>.     •   Port: Connecting port of an RDS PostgreSQL instance. For more information, see <a href="http://yun.pingan.com/ssr/help/database/RDS/User_Guide_RDS_PostgreSQL.Instance_Management.View_Instance.View_Common_Instance" target="_blank">the basic information of the instance</a>. Solution 2: The privileged account is the owner of the business database and authorizes database permission to the business accounts. 1.  The privileged account creates a business account that has login permission by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> CREATE USER Newuser-Name LOGIN PASSWORD 'Password'; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Newuser-Name: Name of a business account.     •   Password: Password of a business account. 2.  Create business database by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> CREATE DATABASE Newdb-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />: Newdb-Name: Name of the business database. 3.  The business account authorizes the business database with relevant permission by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> GRANT ALL ON DATABASE Newdb-Name TO Newuser-Name; </td> </tr> </tbody> </table> 4.  The business account accesses the business database and creates a table by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> psql -U Newuser-Name -h Domain-Name -p Port Newdb-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Domain-Name: Access domain name of an RDS PostgreSQL instance. For more information, see the basic information of the instance.     •   Port: Connecting port of an RDS PostgreSQL instance. For more information, see the basic information of the instance. Data Separation: Permission Separation of Business Database 1.  Create business database by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> CREATE DATABASE Newdb-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />: Newdb-Name: Name of the database being created. 2.  Create a DDL account and authorize the DDL account with all permissions of the business database by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> CREATE USER Deployuser-Name LOGIN PASSWORD 'Password-ddl'; GRANT ALL ON DATABASE Newdb-Name TO Deployuser-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Deployuser-Name: Name of the DDL account.     •   Password-ddl: Password of the DDL account.     •   Newdb-Name: Name of the business database. 3.  Create a DML account and authorize the DML account with permissions of adding, deleting, modifying, and viewing all tables in the designated business database. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> CREATE USER Dmluser-Name LOGIN PASSWORD 'Password-dml'; GRANT CONNECT ON DATABASE Newdb-Name TO Dmluser-Name; \c Newdb-Name; GRANT INSERT,UPDATE,DELETE,SELECT ON ALL TABLES IN SCHEMA public TO Dmluser-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Dmluser-Name: Name of the DML account.     •   Password-dml: Password of the DML account. 4.  Create a read-only account and authorize the read-only account with query permission of all tables in the business database by executing the following command. <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa; vertical-align:top"> CREATE USER Readonlyuser-Name LOGIN PASSWORD 'Password-readonly'; GRANT CONNECT ON DATABASE newdb TO readonlyuser; \c Newdb-Name; GRANT SELECT ON ALL TABLES IN SCHEMA public TO Readonlyuser-Name; </td> </tr> </tbody> </table>     <img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20200707110735-16349b2b9ab4.png" style="height:23px; margin:0px; width:90px" />:     •   Readonlyuser-Name: Name of the read-only account.     •   Password-readonly: Password of the read-only account.

Did the above content solve your problem? Yes No