<p>本文主要介绍 RDS-PostgreSQL 高权限账号具有的功能，以及在不同场景下的最佳实践。</p> <p><span style="font-size:18px"><strong>背景信息</strong></span></p> <p>RDS-PostgreSQL 支持高权限账号。在创建 RDS-PostgreSQL 实例时，系统自动创建的账号即为高权限账号。通过高权限账号，您可以根据业务需要创建并管理多个业务账号和业务数据库，从而在一个实例内实现权限隔离和数据隔离。</p> <p><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <p>由于系统创建的高权限账号的默认 Database 是 postgres，是系统 Database，不适用于创建业务数据库。因此建议您使用高权限账号创建业务账号，通过业务账号创建业务数据库。</p> <p>1. 多账号权限隔离</p> <p>在很多场景，您可能希望不同职能的人员对数据库具有不同的操作权限。例如：部分职能人员的账号可以创建数据库和表，部分账号可以对数据进行增删改查操作，而部分人员的账号只能对数据进行读取操作。通过高权限账号，您可以创建多个业务账号，并对不同的业务账号授予不同的操作权限，从而实现权限分离。</p> <p>2. 多个应用数据隔离共享实例资源</p> <p>为节省开支，您可以将多个应用部署在同一个数据库实例中，通过不同的业务数据库对应用进行隔离。不同应用之间共享 RDS-PostgreSQL 的资源，包括：CPU 资源、内存资源及存储资源。因此对于多个应用，您只需维护一个数据库实例即可。</p> <p><span style="font-size:18px"><strong>前提条件</strong></span></p> <p>您已成功创建 RDS-PostgreSQL 实例，可参考<a href="https://yun.pingan.com/ssr/help/database/RDS/User_Guide_RDS_PostgreSQL.Instance_Management.Create_Instance.Create_Common_Instance" target="_blank">创建常规实例</a>。</p> <p><span style="font-size:18px"><strong>权限隔离：创建业务数据库及业务账号</strong></span></p> <p><strong>方案1： 业务账号为业务数据库的 Owner，拥有业务数据库的所有权限。</strong></p> <p>1. 高权限帐号通过以下命令创建有登录权限的业务账号。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">CREATE USER <em>Newuser-Name</em> LOGIN PASSWORD &#39;<em>Password</em>&#39;;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_2" o:spid="_x0000_i1034" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li>&nbsp;&nbsp;<em>Newuser-Name</em>：业务账号的名称。</li> <li>&nbsp; <em>Password</em>：业务账号的密码。</li> </ul> <p>2. 通过以下命令，为业务账号创建业务数据库。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">GRANT <em>Newuser-Name </em>to <em>Rootuser-Name</em>;</span></p> <p><span style="font-family:Consolas">CREATE DATABASE<em> Newdb-Name</em> OWNER<em> Newuser-Name</em>;</span></p> <p><span style="font-family:Consolas">REVOKE <em>Newuser-Name </em>from <em>Rootuser-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_3" o:spid="_x0000_i1033" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li><em>Rootuser-Name</em>：您在创建 RDS-PostgreSQL 实例时创建的高权限账号的名称。</li> <li><em>Newdb-Name</em>：业务数据库的名称。</li> </ul> <p>3. 业务账号通过以下命令访问业务数据库进行建表等操作。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">psql -U <em>Newuser-Name r</em> -h <em>Domain-Name</em> -p <em>Port</em> <em>Newdb-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_4" o:spid="_x0000_i1032" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li><em>Domain-Name</em>：RDS-PostgreSQL 实例的访问域名，可参考<a href="https://yun.pingan.com/ssr/help/database/RDS/User_Guide_RDS_PostgreSQL.Instance_Management.View_Instance.View_Common_Instance" target="_blank">查看实例基本信息</a>。</li> <li><em>Port</em>：RDS-PostgreSQL 实例的连接端口，可参考<a href="https://yun.pingan.com/ssr/help/database/RDS/User_Guide_RDS_PostgreSQL.Instance_Management.View_Instance.View_Common_Instance" target="_blank">查看实例基本信息</a>。</li> </ul> <p><strong>方案2：高权限账号为业务数据库的 Owner，高权限账号向业务账号授予业务数据库的权限。</strong></p> <p>1. 高权限帐号通过以下命令创建有登录权限的业务账号。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">CREATE USER <em>Newuser-Name</em> LOGIN PASSWORD <em>&#39;Password&#39;</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_5" o:spid="_x0000_i1031" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li><em>Newuser-Name</em>：业务账号的名称。</li> <li><em>Password</em>：业务账号的密码。</li> </ul> <p>2. 通过以下命令，创建业务数据库。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">CREATE DATABASE <em>Newdb-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_6" o:spid="_x0000_i1030" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <p><em>Newdb-Name</em>：业务数据库的名称。</p> <p>3. 业务账号通过以下命令授权业务数据库相应的权限。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">GRANT ALL ON DATABASE <em>Newdb-Name</em> TO <em>Newuser-Name</em>;</span></p> </td> </tr> </tbody> </table> <p>4. 业务账号通过以下命令访问业务数据库并进行建表等操作。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">psql -U <em>Newuser-Name</em> -h <em>Domain-Name</em> -p <em>Port Newdb-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_7" o:spid="_x0000_i1029" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li><em>Domain-Name</em>：RDS-PostgreSQL 实例的访问域名。</li> <li><em>Port</em>：RDS-PostgreSQL 实例的连接端口。</li> </ul> <p><strong>数据隔离：业务数据库权限分离</strong></p> <p>1. 通过以下命令，创建业务数据库。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">CREATE DATABASE <em>Newdb-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_8" o:spid="_x0000_i1028" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <p><em>Newdb-Name</em>：数据库的名称。</p> <p>2. 通过以下命令，创建 DDL 账号，并授予 DDL 账号对业务数据库的所有权限。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">CREATE USER <em>Deployuser-Name</em> LOGIN PASSWORD &#39;<em>Password-ddl</em>&#39;;</span></p> <p><span style="font-family:Consolas">GRANT ALL ON DATABASE <em>Newdb-Name</em> TO <em>Deployuser-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li><em>Deployuser-Name</em>：DDL 账号的名称。</li> <li><em>Password-ddl</em>：DDL 账号的密码。</li> <li><em>Newdb-Name</em>：业务数据库的名称。</li> </ul> <p>3. 通过以下命令，创建 DML 账号，并授予 DML 账号对指定业务数据库增删改查所有业务表的权限。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">CREATE USER <em>Dmluser-Name</em> LOGIN PASSWORD &#39;<em>Password-dml</em>&#39;;</span></p> <p><span style="font-family:Consolas">GRANT CONNECT ON DATABASE <em>Newdb-Name</em> TO <em>Dmluser-Name</em>;</span></p> <p><span style="font-family:Consolas">\c <em>Newdb-Name</em>;</span></p> <p><span style="font-family:Consolas">GRANT INSERT,UPDATE,DELETE,SELECT ON ALL TABLES IN SCHEMA public TO <em>Dmluser-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_10" o:spid="_x0000_i1026" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li><em>Dmluser-Name</em>：DML 账号的名称。</li> <li><em>Password-dml</em>：DML 账号的密码。</li> </ul> <p>4. 通过以下命令，创建只读账号，并授予只读账号查询业务库中所有业务表的权限。</p> <table border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#f7f8fa"> <p><span style="font-family:Consolas">CREATE USER <em>Readonlyuser-Name</em> LOGIN PASSWORD &#39;<em>Password-readonly</em>&#39;;</span></p> <p><span style="font-family:Consolas">GRANT CONNECT ON DATABASE <em>newdb</em> TO <em>readonlyuser</em>;</span></p> <p><span style="font-family:Consolas">\c <em>Newdb-Name</em>;</span></p> <p><span style="font-family:Consolas">GRANT SELECT ON ALL TABLES IN SCHEMA public TO <em>Readonlyuser-Name</em>;</span></p> </td> </tr> </tbody> </table> <p><!--[if gte vml 1]><v:shape id="图片_x0020_11" o:spid="_x0000_i1025" type="#_x0000_t75" alt="https://obs-cn-shenzhen.yun.city.pingan.com/scsp-portal/20201603105704-1153f32e9fd6.png" style='width:47.5pt;height:19pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///D:/Users/CHENGJING534/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="20201603105704-1153f32e9fd6"/> </v:shape><![endif]--><img src="https://pcp-portal-sca.obs-cn-shenzhen.pinganyun.com/pcp-portal-sca/20201603170138-16c487ec9208.png" style="height:25px; margin:0px; width:63px" />：</p> <ul> <li><em>Readonlyuser-Name</em>：只读账号的名称。</li> <li><em>Password-readonly</em>：只读账号的密码。</li> </ul>