{"id":3239,"date":"2026-08-18T10:56:34","date_gmt":"2026-08-18T02:56:34","guid":{"rendered":"https:\/\/linuxjk.cn\/?p=3239"},"modified":"2026-09-14T18:00:16","modified_gmt":"2026-09-14T10:00:16","slug":"postgresql-%e6%95%b0%e6%8d%ae%e5%ba%93%e7%ad%89%e4%bf%9d%e9%85%8d%e7%bd%ae%e5%91%bd%e4%bb%a4","status":"publish","type":"post","link":"https:\/\/linuxjk.cn\/?p=3239","title":{"rendered":"postgresql \u6570\u636e\u5e93\u7b49\u4fdd\u914d\u7f6e\u547d\u4ee4"},"content":{"rendered":"\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_88 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">\u76ee\u5f55<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%9F%A5%E8%AF%A21%EF%BC%9A%E7%99%BB%E5%BD%95%E8%B4%A6%E5%8F%B7%E8%A7%92%E8%89%B2%E5%B1%9E%E6%80%A7\" >\u67e5\u8be21\uff1a\u767b\u5f55\u8d26\u53f7\u89d2\u8272\u5c5e\u6027<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%9F%A5%E8%AF%A22%EF%BC%9A%E7%9B%B4%E6%8E%A5%E8%A7%92%E8%89%B2%E7%BB%A7%E6%89%BF%E5%85%B3%E7%B3%BB\" >\u67e5\u8be22\uff1a\u76f4\u63a5\u89d2\u8272\u7ee7\u627f\u5173\u7cfb<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%9F%A5%E8%AF%A23%EF%BC%9A%E6%A3%80%E6%9F%A5%E6%98%AF%E5%90%A6%E9%97%B4%E6%8E%A5%E7%BB%A7%E6%89%BF%E8%B6%85%E7%BA%A7%E7%94%A8%E6%88%B7\" >\u67e5\u8be23\uff1a\u68c0\u67e5\u662f\u5426\u95f4\u63a5\u7ee7\u627f\u8d85\u7ea7\u7528\u6237<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%9F%A5%E8%AF%A24%EF%BC%9A%E4%B8%89%E6%9D%83%E5%88%86%E7%AB%8B%E8%A7%92%E8%89%B2%E7%9F%A9%E9%98%B5\" >\u67e5\u8be24\uff1a\u4e09\u6743\u5206\u7acb\u89d2\u8272\u77e9\u9635<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%9F%A5%E8%AF%A25%EF%BC%9A%E4%B8%89%E4%B8%AA%E8%B4%A6%E5%8F%B7%E5%88%86%E5%88%AB%E7%99%BB%E5%BD%95%E7%9A%84%E8%B6%8A%E6%9D%83%E5%A4%B1%E8%B4%A5%E6%88%AA%E5%9B%BE\" >\u67e5\u8be25\uff1a\u4e09\u4e2a\u8d26\u53f7\u5206\u522b\u767b\u5f55\u7684\u8d8a\u6743\u5931\u8d25\u622a\u56fe<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%9F%A5%E8%AF%A26%EF%BC%9A%E5%AE%A1%E8%AE%A1%E9%85%8D%E7%BD%AE%E5%92%8C%E5%AE%A1%E8%AE%A1%E6%97%A5%E5%BF%97%E6%88%AA%E5%9B%BE\" >\u67e5\u8be26\uff1a\u5ba1\u8ba1\u914d\u7f6e\u548c\u5ba1\u8ba1\u65e5\u5fd7\u622a\u56fe<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%9F%A5%E8%AF%A27%EF%BC%9A%E5%BA%94%E6%80%A5%E8%B6%85%E7%BA%A7%E7%AE%A1%E7%90%86%E5%91%98%E7%AE%A1%E7%90%86%E5%88%B6%E5%BA%A6\" >\u67e5\u8be27\uff1a\u5e94\u6025\u8d85\u7ea7\u7ba1\u7406\u5458\u7ba1\u7406\u5236\u5ea6<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E4%BF%AE%E6%94%B9%E7%94%A8%E6%88%B7%E5%AF%86%E7%A0%81%E5%8F%8A%E6%9C%89%E6%95%88%E6%9C%9F\" >\u4fee\u6539\u7528\u6237\u5bc6\u7801\u53ca\u6709\u6548\u671f<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%AD%A3%E5%B8%B8%E7%99%BB%E5%BD%95\" >\u6b63\u5e38\u767b\u5f55<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E4%BF%AE%E6%94%B9%E5%8D%95%E4%B8%AA%E7%94%A8%E6%88%B7\" >\u4fee\u6539\u5355\u4e2a\u7528\u6237<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%89%B9%E9%87%8F%E8%AE%BE%E7%BD%AE%E6%89%80%E6%9C%89%E7%94%A8%E6%88%B7%E6%B0%B8%E4%B8%8D%E8%BF%87%E6%9C%9F\" >\u6279\u91cf\u8bbe\u7f6e\u6240\u6709\u7528\u6237\u6c38\u4e0d\u8fc7\u671f<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#postgres_%E5%B7%B2%E8%BF%87%E6%9C%9F%E6%88%96%E5%BF%98%E8%AE%B0%E5%AF%86%E7%A0%81\" >postgres \u5df2\u8fc7\u671f\u6216\u5fd8\u8bb0\u5bc6\u7801<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/linuxjk.cn\/?p=3239\/#%E6%A3%80%E6%9F%A5%E7%BB%93%E6%9E%9C\" >\u68c0\u67e5\u7ed3\u679c<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9F%A5%E8%AF%A21%EF%BC%9A%E7%99%BB%E5%BD%95%E8%B4%A6%E5%8F%B7%E8%A7%92%E8%89%B2%E5%B1%9E%E6%80%A7\"><\/span>\u67e5\u8be21\uff1a\u767b\u5f55\u8d26\u53f7\u89d2\u8272\u5c5e\u6027<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u9700\u8981\u5173\u95ed\u9ed8\u8ba4\u8d85\u7ea7\u7ba1\u7406\u5458<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ALTER ROLE postgres NOLOGIN;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u5c55\u793a\u6240\u6709\u53ef\u767b\u5f55\u8d26\u53f7\u81ea\u8eab\u76f4\u63a5\u62e5\u6709\u7684\u96c6\u7fa4\u7ea7\u5c5e\u6027\u3002<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT<br> &nbsp;  rolname AS \"\u767b\u5f55\u8d26\u53f7\",<br> &nbsp; &nbsp;CASE WHEN rolsuper &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426' END AS \"SUPERUSER\",<br> &nbsp; &nbsp;CASE WHEN rolcreaterole THEN '\u662f' ELSE '\u5426' END AS \"CREATEROLE\",<br> &nbsp; &nbsp;CASE WHEN rolcreatedb &nbsp;THEN '\u662f' ELSE '\u5426' END AS \"CREATEDB\",<br> &nbsp; &nbsp;CASE WHEN rolbypassrls THEN '\u662f' ELSE '\u5426' END AS \"BYPASSRLS\",<br> &nbsp; &nbsp;CASE WHEN rolcanlogin &nbsp;THEN '\u662f' ELSE '\u5426' END AS \"LOGIN\",<br> &nbsp;  rolvaliduntil AS \"\u5bc6\u7801\u6709\u6548\u671f\"<br>FROM pg_roles<br>WHERE rolcanlogin = true<br>ORDER BY rolname;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9F%A5%E8%AF%A22%EF%BC%9A%E7%9B%B4%E6%8E%A5%E8%A7%92%E8%89%B2%E7%BB%A7%E6%89%BF%E5%85%B3%E7%B3%BB\"><\/span>\u67e5\u8be22\uff1a\u76f4\u63a5\u89d2\u8272\u7ee7\u627f\u5173\u7cfb<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u4f5c\u7528\uff1a\u5c55\u793a\u5168\u90e8\u76f4\u63a5\u89d2\u8272\u6210\u5458\u5173\u7cfb\uff0c\u5e76\u786e\u8ba4\u88ab\u7ee7\u627f\u89d2\u8272\u662f\u5426\u4e3a\u8d85\u7ea7\u7528\u6237\u3002<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT<br> &nbsp;  member_role.rolname AS \"\u6210\u5458\u8d26\u53f7\u6216\u89d2\u8272\",<br> &nbsp;  granted_role.rolname AS \"\u7ee7\u627f\u7684\u89d2\u8272\",<br> &nbsp; &nbsp;CASE WHEN member_role.rolcanlogin<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426' END AS \"\u6210\u5458\u53ef\u767b\u5f55\",<br> &nbsp; &nbsp;CASE WHEN member_role.rolsuper<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426' END AS \"\u6210\u5458\u81ea\u8eabSUPERUSER\",<br> &nbsp; &nbsp;CASE WHEN granted_role.rolsuper<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426' END AS \"\u88ab\u7ee7\u627f\u89d2\u8272SUPERUSER\",<br> &nbsp; &nbsp;CASE WHEN am.admin_option<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426' END AS \"\u53ef\u8f6c\u6388\u89d2\u8272\"<br>FROM pg_auth_members am<br>JOIN pg_roles member_role<br> &nbsp;ON member_role.oid = am.member<br>JOIN pg_roles granted_role<br> &nbsp;ON granted_role.oid = am.roleid<br>ORDER BY member_role.rolname, granted_role.rolname;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9F%A5%E8%AF%A23%EF%BC%9A%E6%A3%80%E6%9F%A5%E6%98%AF%E5%90%A6%E9%97%B4%E6%8E%A5%E7%BB%A7%E6%89%BF%E8%B6%85%E7%BA%A7%E7%94%A8%E6%88%B7\"><\/span>\u67e5\u8be23\uff1a\u68c0\u67e5\u662f\u5426\u95f4\u63a5\u7ee7\u627f\u8d85\u7ea7\u7528\u6237<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u68c0\u67e5\u767b\u5f55\u8d26\u53f7\u662f\u5426\u901a\u8fc7\u76f4\u63a5\u6216\u95f4\u63a5\u89d2\u8272\u94fe\u83b7\u5f97\u8d85\u7ea7\u7528\u6237\u89d2\u8272<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH RECURSIVE role_chain AS (<br> &nbsp; &nbsp;SELECT<br> &nbsp; &nbsp; &nbsp;  am.member AS login_oid,<br> &nbsp; &nbsp; &nbsp;  am.roleid AS inherited_oid,<br> &nbsp; &nbsp; &nbsp;  ARRAY[am.member, am.roleid] AS visited<br> &nbsp; &nbsp;FROM pg_auth_members am<br>\u200b<br> &nbsp; &nbsp;UNION ALL<br>\u200b<br> &nbsp; &nbsp;SELECT<br> &nbsp; &nbsp; &nbsp;  rc.login_oid,<br> &nbsp; &nbsp; &nbsp;  am.roleid,<br> &nbsp; &nbsp; &nbsp;  rc.visited || am.roleid<br> &nbsp; &nbsp;FROM role_chain rc<br> &nbsp; &nbsp;JOIN pg_auth_members am<br> &nbsp; &nbsp; &nbsp;ON am.member = rc.inherited_oid<br> &nbsp; &nbsp;WHERE NOT am.roleid = ANY(rc.visited)<br>)<br>SELECT DISTINCT<br> &nbsp;  login_role.rolname AS \"\u767b\u5f55\u8d26\u53f7\",<br> &nbsp;  inherited_role.rolname AS \"\u7ee7\u627f\u7684\u8d85\u7ea7\u7528\u6237\u89d2\u8272\",<br> &nbsp; &nbsp;'\u5b58\u5728\u8d85\u7ea7\u6743\u9650\u7ee7\u627f\u98ce\u9669' AS \"\u68c0\u67e5\u7ed3\u8bba\"<br>FROM role_chain rc<br>JOIN pg_roles login_role<br> &nbsp;ON login_role.oid = rc.login_oid<br>JOIN pg_roles inherited_role<br> &nbsp;ON inherited_role.oid = rc.inherited_oid<br>WHERE login_role.rolcanlogin = true<br> &nbsp;AND inherited_role.rolsuper = true<br>ORDER BY login_role.rolname;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9F%A5%E8%AF%A24%EF%BC%9A%E4%B8%89%E6%9D%83%E5%88%86%E7%AB%8B%E8%A7%92%E8%89%B2%E7%9F%A9%E9%98%B5\"><\/span>\u67e5\u8be24\uff1a\u4e09\u6743\u5206\u7acb\u89d2\u8272\u77e9\u9635<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u660e\u786e\u7cfb\u7edf\u7ba1\u7406\u5458\u3001\u5b89\u5168\u7ba1\u7406\u5458\u3001\u5ba1\u8ba1\u7ba1\u7406\u5458\u7684\u804c\u8d23\u3001\u4eba\u5458\u548c\u6743\u9650\u3002<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">\\c \"Dynames_BK_DB\"<br>\u200b<br>\\pset pager off<br>\\x off<br>\u200b<br>WITH management_matrix (<br> &nbsp;  sort_no,<br> &nbsp;  admin_type,<br> &nbsp;  login_account,<br> &nbsp;  management_role,<br> &nbsp;  responsibility<br>) AS (<br> &nbsp; &nbsp;VALUES<br> &nbsp; &nbsp; &nbsp; &nbsp;(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'\u7cfb\u7edf\u7ba1\u7406\u5458',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'u_system_admin',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'r_system_admin',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'\u6570\u636e\u5e93\u521b\u5efa\u3001\u76d1\u63a7\u7ef4\u62a4\u3001\u53c2\u6570\u914d\u7f6e\u3001\u903b\u8f91\u5907\u4efd\u6062\u590d'<br> &nbsp; &nbsp; &nbsp; &nbsp;),<br> &nbsp; &nbsp; &nbsp; &nbsp;(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'\u5b89\u5168\u7ba1\u7406\u5458',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'u_security_admin',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'r_security_admin',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'\u8d26\u53f7\u3001\u89d2\u8272\u53ca\u6743\u9650\u7ba1\u7406'<br> &nbsp; &nbsp; &nbsp; &nbsp;),<br> &nbsp; &nbsp; &nbsp; &nbsp;(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'\u5ba1\u8ba1\u7ba1\u7406\u5458',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'u_audit_admin',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'r_audit_admin',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'\u5b89\u5168\u76d1\u63a7\u3001\u5ba1\u8ba1\u68c0\u67e5\u53ca\u53d6\u8bc1'<br> &nbsp; &nbsp; &nbsp; &nbsp;)<br>),<br>business_tables AS (<br> &nbsp; &nbsp;SELECT c.oid<br> &nbsp; &nbsp;FROM pg_class c<br> &nbsp; &nbsp;JOIN pg_namespace n<br> &nbsp; &nbsp; &nbsp;ON n.oid = c.relnamespace<br> &nbsp; &nbsp;WHERE c.relkind IN ('r', 'p')<br> &nbsp; &nbsp; &nbsp;AND n.nspname &lt;&gt; 'information_schema'<br> &nbsp; &nbsp; &nbsp;AND n.nspname NOT LIKE 'pg_%'<br> &nbsp; &nbsp; &nbsp;AND n.nspname NOT LIKE 'pg_toast%'<br>)<br>SELECT<br> &nbsp;  m.admin_type AS \"\u7ba1\u7406\u7c7b\u522b\",<br> &nbsp;  m.login_account AS \"\u767b\u5f55\u8d26\u53f7\",<br> &nbsp;  m.management_role AS \"\u6388\u6743\u89d2\u8272\",<br> &nbsp;  m.responsibility AS \"\u4e3b\u8981\u804c\u8d23\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN pg_has_role(<br> &nbsp; &nbsp; &nbsp;  m.login_account,<br> &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp;'MEMBER'<br> &nbsp; &nbsp;) THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u8d26\u53f7\u5df2\u7ed1\u5b9a\u89d2\u8272\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN r.rolsuper<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"SUPERUSER\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN r.rolcreaterole<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"CREATEROLE\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN r.rolcreatedb<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"CREATEDB\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN r.rolbypassrls<br> &nbsp; &nbsp; &nbsp; &nbsp; THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"BYPASSRLS\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN pg_has_role(<br> &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp;'pg_monitor',<br> &nbsp; &nbsp; &nbsp; &nbsp;'MEMBER'<br> &nbsp; &nbsp;) THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u76d1\u63a7\u6743\u9650\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN pg_has_role(<br> &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp;'pg_maintain',<br> &nbsp; &nbsp; &nbsp; &nbsp;'MEMBER'<br> &nbsp; &nbsp;) THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u7ef4\u62a4\u6743\u9650\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN pg_has_role(<br> &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp;'pg_checkpoint',<br> &nbsp; &nbsp; &nbsp; &nbsp;'MEMBER'<br> &nbsp; &nbsp;) THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u68c0\u67e5\u70b9\u6743\u9650\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN pg_has_role(<br> &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp;'pg_signal_backend',<br> &nbsp; &nbsp; &nbsp; &nbsp;'MEMBER'<br> &nbsp; &nbsp;) THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u4f1a\u8bdd\u7ba1\u7406\u6743\u9650\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN pg_has_role(<br> &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp;'pg_read_all_data',<br> &nbsp; &nbsp; &nbsp; &nbsp;'MEMBER'<br> &nbsp; &nbsp;) THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u5907\u4efd\u8bfb\u53d6\u6743\u9650\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN<br> &nbsp; &nbsp; &nbsp;  has_parameter_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'work_mem',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'SET'<br> &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp; &nbsp; &nbsp;AND has_parameter_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'maintenance_work_mem',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'SET'<br> &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp; &nbsp; &nbsp;AND has_parameter_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'log_min_duration_statement',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'ALTER SYSTEM'<br> &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp;THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u53c2\u6570\u914d\u7f6e\u6743\u9650\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN<br> &nbsp; &nbsp; &nbsp; &nbsp;EXISTS (SELECT 1 FROM business_tables)<br> &nbsp; &nbsp; &nbsp; &nbsp;AND NOT EXISTS (<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT 1<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM business_tables t<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE NOT has_table_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  t.oid,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'SELECT'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp;THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u6240\u6709\u4e1a\u52a1\u8868\u53ef\u8bfb\",<br>\u200b<br> &nbsp; &nbsp;CASE WHEN EXISTS (<br> &nbsp; &nbsp; &nbsp; &nbsp;SELECT 1<br> &nbsp; &nbsp; &nbsp; &nbsp;FROM business_tables t<br> &nbsp; &nbsp; &nbsp; &nbsp;WHERE has_table_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role, t.oid, 'INSERT'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OR has_table_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role, t.oid, 'UPDATE'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OR has_table_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role, t.oid, 'DELETE'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OR has_table_privilege(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  m.management_role, t.oid, 'TRUNCATE'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp;)<br> &nbsp; &nbsp;THEN '\u662f' ELSE '\u5426'<br> &nbsp; &nbsp;END AS \"\u53ef\u5199\u4e1a\u52a1\u8868\"<br>\u200b<br>FROM management_matrix m<br>JOIN pg_roles r<br> &nbsp;ON r.rolname = m.management_role<br>ORDER BY m.sort_no;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9F%A5%E8%AF%A25%EF%BC%9A%E4%B8%89%E4%B8%AA%E8%B4%A6%E5%8F%B7%E5%88%86%E5%88%AB%E7%99%BB%E5%BD%95%E7%9A%84%E8%B6%8A%E6%9D%83%E5%A4%B1%E8%B4%A5%E6%88%AA%E5%9B%BE\"><\/span>\u67e5\u8be25\uff1a\u4e09\u4e2a\u8d26\u53f7\u5206\u522b\u767b\u5f55\u7684\u8d8a\u6743\u5931\u8d25\u622a\u56fe<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u4e0b\u9762\u8fd9\u91cc\u524d\u56db\u4e2a\u5c5e\u6027\u5305\u542b\u8d26\u53f7\u901a\u8fc7\u6240\u5c5e\u89d2\u8272\u6267\u884c <code>SET ROLE<\/code> \u540e\u53ef\u83b7\u5f97\u7684\u80fd\u529b<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">\\c \"Dynames_BK_DB\"<br>RESET ROLE;<br>\\pset pager off<br>\\x off<br>\u200b<br>RESET ROLE;<br>\u200b<br>WITH RECURSIVE<br>lr AS (<br> &nbsp; &nbsp;SELECT * FROM pg_roles WHERE rolcanlogin<br>),<br>rt AS (<br> &nbsp; &nbsp;SELECT oid AS login_oid, oid AS role_oid FROM lr<br> &nbsp; &nbsp;UNION<br> &nbsp; &nbsp;SELECT rt.login_oid, am.roleid<br> &nbsp; &nbsp;FROM rt<br> &nbsp; &nbsp;JOIN pg_auth_members am ON am.member = rt.role_oid<br> &nbsp; &nbsp;WHERE am.set_option<br>),<br>ra AS (<br> &nbsp; &nbsp;SELECT rt.login_oid,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bool_or(p.rolsuper) superuser,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bool_or(p.rolcreaterole) createrole,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bool_or(p.rolcreatedb) createdb,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bool_or(p.rolbypassrls) bypassrls<br> &nbsp; &nbsp;FROM rt JOIN pg_roles p ON p.oid = rt.role_oid<br> &nbsp; &nbsp;GROUP BY rt.login_oid<br>),<br>rm AS (<br> &nbsp; &nbsp;SELECT am.member,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string_agg(p.rolname, ', ' ORDER BY p.rolname) member_of<br> &nbsp; &nbsp;FROM pg_auth_members am<br> &nbsp; &nbsp;JOIN pg_roles p ON p.oid = am.roleid<br> &nbsp; &nbsp;GROUP BY am.member<br>),<br>bt AS (<br> &nbsp; &nbsp;SELECT c.oid<br> &nbsp; &nbsp;FROM pg_class c<br> &nbsp; &nbsp;JOIN pg_namespace n ON n.oid = c.relnamespace<br> &nbsp; &nbsp;WHERE c.relkind IN ('r','p')<br> &nbsp; &nbsp; &nbsp;AND n.nspname &lt;&gt; 'information_schema'<br> &nbsp; &nbsp; &nbsp;AND n.nspname NOT LIKE 'pg_%'<br> &nbsp; &nbsp; &nbsp;AND n.nspname NOT LIKE 'pg_toast%'<br>),<br>dc AS (<br> &nbsp; &nbsp;SELECT rt.login_oid,<br> &nbsp; &nbsp;  bool_or(has_database_privilege(p.rolname,current_database(),'CONNECT')) conn,<br> &nbsp; &nbsp;  bool_or(has_database_privilege(p.rolname,current_database(),'CREATE')) dbcreate,<br> &nbsp; &nbsp;  bool_or(has_database_privilege(p.rolname,current_database(),'TEMP')) temp,<br> &nbsp; &nbsp;  bool_or(has_schema_privilege(p.rolname,'public','CREATE')) tblcreate<br> &nbsp; &nbsp;FROM rt JOIN pg_roles p ON p.oid = rt.role_oid<br> &nbsp; &nbsp;GROUP BY rt.login_oid<br>),<br>tc AS (<br> &nbsp; &nbsp;SELECT rt.login_oid, bt.oid,<br> &nbsp; &nbsp;  bool_or(has_table_privilege(p.rolname,bt.oid,'SELECT')) sel,<br> &nbsp; &nbsp;  bool_or(has_table_privilege(p.rolname,bt.oid,'INSERT')) ins,<br> &nbsp; &nbsp;  bool_or(has_table_privilege(p.rolname,bt.oid,'UPDATE')) upd,<br> &nbsp; &nbsp;  bool_or(has_table_privilege(p.rolname,bt.oid,'DELETE')) del<br> &nbsp; &nbsp;FROM rt<br> &nbsp; &nbsp;JOIN pg_roles p ON p.oid = rt.role_oid<br> &nbsp; &nbsp;CROSS JOIN bt<br> &nbsp; &nbsp;GROUP BY rt.login_oid, bt.oid<br>),<br>ts AS (<br> &nbsp; &nbsp;SELECT login_oid,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bool_and(sel) sel, bool_and(ins) ins,<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bool_and(upd) upd, bool_and(del) del<br> &nbsp; &nbsp;FROM tc GROUP BY login_oid<br>)<br>SELECT<br> &nbsp;  l.rolname AS \"\u767b\u5f55\u8d26\u53f7\",<br> &nbsp; &nbsp;COALESCE(rm.member_of,'\u65e0') AS \"\u6240\u5c5e\u89d2\u8272\",<br> &nbsp; &nbsp;CASE WHEN ra.superuser THEN '\u662f' ELSE '\u5426' END AS \"SUPERUSER\",<br> &nbsp; &nbsp;CASE WHEN ra.createrole THEN '\u662f' ELSE '\u5426' END AS \"CREATEROLE\",<br> &nbsp; &nbsp;CASE WHEN ra.createdb THEN '\u662f' ELSE '\u5426' END AS \"CREATEDB\",<br> &nbsp; &nbsp;CASE WHEN ra.bypassrls THEN '\u662f' ELSE '\u5426' END AS \"BYPASSRLS\",<br> &nbsp; &nbsp;CASE WHEN dc.conn THEN '\u662f' ELSE '\u5426' END AS \"\u53ef\u8fde\u63a5\u6570\u636e\u5e93\",<br> &nbsp; &nbsp;CASE WHEN dc.dbcreate THEN '\u662f' ELSE '\u5426' END AS \"\u53ef\u521b\u5efaSchema\",<br> &nbsp; &nbsp;CASE WHEN dc.temp THEN '\u662f' ELSE '\u5426' END AS \"\u53ef\u521b\u5efa\u4e34\u65f6\u8868\",<br> &nbsp; &nbsp;CASE WHEN dc.tblcreate THEN '\u662f' ELSE '\u5426' END AS \"\u53ef\u521b\u5efa\u8868\",<br> &nbsp; &nbsp;CASE WHEN COALESCE(ts.sel,false) THEN '\u662f' ELSE '\u5426' END AS \"\u6240\u6709\u8868\u53ef\u67e5\u8be2\",<br> &nbsp; &nbsp;CASE WHEN COALESCE(ts.ins,false) THEN '\u662f' ELSE '\u5426' END AS \"\u6240\u6709\u8868\u53ef\u65b0\u589e\",<br> &nbsp; &nbsp;CASE WHEN COALESCE(ts.upd,false) THEN '\u662f' ELSE '\u5426' END AS \"\u6240\u6709\u8868\u53ef\u4fee\u6539\",<br> &nbsp; &nbsp;CASE WHEN COALESCE(ts.del,false) THEN '\u662f' ELSE '\u5426' END AS \"\u6240\u6709\u8868\u53ef\u5220\u9664\"<br>FROM lr l<br>JOIN ra ON ra.login_oid = l.oid<br>JOIN dc ON dc.login_oid = l.oid<br>LEFT JOIN rm ON rm.member = l.oid<br>LEFT JOIN ts ON ts.login_oid = l.oid<br>ORDER BY l.rolname;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9F%A5%E8%AF%A26%EF%BC%9A%E5%AE%A1%E8%AE%A1%E9%85%8D%E7%BD%AE%E5%92%8C%E5%AE%A1%E8%AE%A1%E6%97%A5%E5%BF%97%E6%88%AA%E5%9B%BE\"><\/span>\u67e5\u8be26\uff1a\u5ba1\u8ba1\u914d\u7f6e\u548c\u5ba1\u8ba1\u65e5\u5fd7\u622a\u56fe<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u81f3\u5c11\u4f53\u73b0\u65f6\u95f4\u3001\u8d26\u53f7\u3001\u64cd\u4f5c\u7c7b\u578b\u3001\u6267\u884c\u7ed3\u679c\u3001\u5ba2\u6237\u7aef\u5730\u5740\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u5ba1\u8ba1\u914d\u7f6e\u73b0\u72b6<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT<br> &nbsp;  name,<br> &nbsp;  setting,<br> &nbsp; &nbsp;source,<br> &nbsp;  pending_restart<br>FROM pg_settings<br>WHERE name IN (<br> &nbsp; &nbsp;'logging_collector',<br> &nbsp; &nbsp;'log_destination',<br> &nbsp; &nbsp;'log_directory',<br> &nbsp; &nbsp;'log_filename',<br> &nbsp; &nbsp;'log_file_mode',<br> &nbsp; &nbsp;'log_connections',<br> &nbsp; &nbsp;'log_disconnections',<br> &nbsp; &nbsp;'log_line_prefix',<br> &nbsp; &nbsp;'log_min_error_statement',<br> &nbsp; &nbsp;'log_min_duration_statement',<br> &nbsp; &nbsp;'log_statement',<br> &nbsp; &nbsp;'shared_preload_libraries'<br>)<br>ORDER BY name;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u6210\u529f\u53ca\u5931\u8d25\u64cd\u4f5c\u7ed3\u679c<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u4e34\u65f6\u5f00\u542f\u5f53\u524d\u4f1a\u8bdd\u7684\u6210\u529f\u8bed\u53e5\u65e5\u5fd7<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SET application_name = 'EQP_AUDIT_06_TEST';<br>SET log_min_duration_statement = 0;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u751f\u6210\u9884\u671f\u6210\u529f\u7684\u53ea\u8bfb\u64cd\u4f5c<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT clock_timestamp() AS event_time, session_user AS account, 'SELECT' AS operation_type, 'SUCCESS' AS execution_result, inet_client_addr() AS client_address, inet_client_port() AS client_port, current_database() AS database_name, 'AUDIT_EVIDENCE_06_SUCCESS' AS marker;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u751f\u6210\u9884\u671f\u5931\u8d25\u7684\u53ea\u8bfb\u64cd\u4f5c<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT CAST('AUDIT_EVIDENCE_06_EXPECTED_FAILURE' AS integer);<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u5173\u95ed\u4e34\u65f6\u6210\u529f\u65e5\u5fd7<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">RESET log_min_duration_statement;<br>RESET application_name;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u5ba1\u8ba1\u65e5\u5fd7\u8bb0\u5f55<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH latest_log AS (<br> &nbsp; &nbsp;SELECT name<br> &nbsp; &nbsp;FROM pg_ls_logdir()<br> &nbsp; &nbsp;ORDER BY modification DESC<br> &nbsp; &nbsp;LIMIT 1<br>),<br>log_lines AS (<br> &nbsp; &nbsp;SELECT<br> &nbsp; &nbsp; &nbsp;  t.ordinality AS line_no,<br> &nbsp; &nbsp; &nbsp;  t.line<br> &nbsp; &nbsp;FROM latest_log l<br> &nbsp; &nbsp;CROSS JOIN LATERAL regexp_split_to_table(<br> &nbsp; &nbsp; &nbsp;  pg_read_file(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  format(<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'%s\/%s',<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  current_setting('log_directory'),<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  l.name<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)<br> &nbsp; &nbsp; &nbsp; &nbsp;),<br> &nbsp; &nbsp; &nbsp;  E'\\n'<br> &nbsp; &nbsp;) WITH ORDINALITY AS t(line, ordinality)<br>)<br>SELECT line<br>FROM log_lines<br>WHERE line LIKE '%AUDIT_EVIDENCE_06%'<br>ORDER BY line_no;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9F%A5%E8%AF%A27%EF%BC%9A%E5%BA%94%E6%80%A5%E8%B6%85%E7%BA%A7%E7%AE%A1%E7%90%86%E5%91%98%E7%AE%A1%E7%90%86%E5%88%B6%E5%BA%A6\"><\/span>\u67e5\u8be27\uff1a\u5e94\u6025\u8d85\u7ea7\u7ba1\u7406\u5458\u7ba1\u7406\u5236\u5ea6<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u8d26\u53f7\u8303\u56f4 \u5e94\u6025\u8d26\u53f7\u4e3a <code>u_pg_breakglass<\/code>\uff0c\u5177\u6709 <code>SUPERUSER<\/code> \u6743\u9650\uff0c\u4ec5\u7528\u4e8e\u7cfb\u7edf\u6545\u969c\u3001\u6743\u9650\u6062\u590d\u3001\u5b89\u5168\u4e8b\u4ef6\u5904\u7f6e\u7b49\u7d27\u6025\u573a\u666f\uff0c\u7981\u6b62\u65e5\u5e38\u4f7f\u7528\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u51ed\u636e\u4fdd\u7ba1 \u8d26\u53f7\u5bc6\u7801\u5b58\u653e\u4e8e\u53d7\u63a7\u5bc6\u7801\u4fdd\u9669\u67dc\uff0c\u7531\u4e24\u540d\u6388\u6743\u7ba1\u7406\u4eba\u5458\u5206\u6743\u4fdd\u7ba1\u3002\u7981\u6b62\u660e\u6587\u8bb0\u5f55\u3001\u4e2a\u4eba\u4fdd\u5b58\u6216\u901a\u8fc7\u804a\u5929\u5de5\u5177\u4f20\u9012\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u4f7f\u7528\u5ba1\u6279 \u4f7f\u7528\u524d\u987b\u63d0\u4ea4\u7533\u8bf7\uff0c\u8bf4\u660e\u4e8b\u7531\u3001\u65f6\u95f4\u3001\u64cd\u4f5c\u5185\u5bb9\u3001\u5f71\u54cd\u8303\u56f4\u548c\u56de\u9000\u65b9\u6848\uff0c\u7ecf\u6570\u636e\u5e93\u8d1f\u8d23\u4eba\u53ca\u5b89\u5168\u8d1f\u8d23\u4eba\u5ba1\u6279\u3002\u7d27\u6025\u60c5\u51b5\u4e0b\u53ef\u5148\u5904\u7f6e\uff0c\u4e8b\u540e\u8865\u529e\u5ba1\u6279\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u4f7f\u7528\u8981\u6c42 \u4f7f\u7528\u65f6\u5e94\u9650\u5b9a\u64cd\u4f5c\u65f6\u95f4\u548c\u8303\u56f4\uff0c\u4f18\u5148\u6267\u884c\u5fc5\u8981\u7684\u6700\u5c0f\u64cd\u4f5c\u3002\u7981\u6b62\u5904\u7406\u4e0e\u5e94\u6025\u4e8b\u9879\u65e0\u5173\u7684\u4e1a\u52a1\u6570\u636e\u3002\u64cd\u4f5c\u5b8c\u6210\u540e\u7acb\u5373\u9000\u51fa\u8d26\u53f7\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u5ba1\u8ba1\u7559\u75d5 \u6bcf\u6b21\u4f7f\u7528\u987b\u8bb0\u5f55\u7533\u8bf7\u4eba\u3001\u5ba1\u6279\u4eba\u3001\u5b9e\u9645\u64cd\u4f5c\u4eba\u3001\u767b\u5f55\u65f6\u95f4\u3001\u5ba2\u6237\u7aef\u5730\u5740\u3001\u6267\u884c\u547d\u4ee4\u3001\u64cd\u4f5c\u7ed3\u679c\u53ca\u9000\u51fa\u65f6\u95f4\u3002\u76f8\u5173\u6570\u636e\u5e93\u65e5\u5fd7\u548c\u5ba1\u6279\u8bb0\u5f55\u5e94\u7edf\u4e00\u5f52\u6863\uff0c\u7981\u6b62\u64cd\u4f5c\u4eba\u5458\u81ea\u884c\u5220\u9664\u6216\u4fee\u6539\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u4f7f\u7528\u540e\u6539\u5bc6 \u6bcf\u6b21\u4f7f\u7528\u7ed3\u675f\u540e\u7acb\u5373\u4fee\u6539\u8d26\u53f7\u5bc6\u7801\uff0c\u66f4\u65b0\u5bc6\u7801\u4fdd\u9669\u67dc\uff0c\u5e76\u786e\u8ba4\u65e7\u5bc6\u7801\u5931\u6548\u3002\u5f02\u5e38\u767b\u5f55\u6216\u51ed\u636e\u7591\u4f3c\u6cc4\u9732\u65f6\u5e94\u7acb\u5373\u505c\u7528\u8d26\u53f7\u5e76\u6539\u5bc6\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u5b9a\u671f\u590d\u6838 \u6bcf\u5b63\u5ea6\u81f3\u5c11\u590d\u6838\u4e00\u6b21\u8d26\u53f7\u72b6\u6001\u3001\u6743\u9650\u3001\u4fdd\u7ba1\u4eba\u5458\u3001\u5bc6\u7801\u6709\u6548\u6027\u548c\u4f7f\u7528\u8bb0\u5f55\u3002\u957f\u671f\u672a\u4f7f\u7528\u4e5f\u987b\u9a8c\u8bc1\u8d26\u53f7\u53ef\u7528\u6027\u3002\u53d1\u73b0\u5f02\u5e38\u6388\u6743\u3001\u65e0\u5ba1\u6279\u4f7f\u7528\u6216\u65e5\u5fd7\u7f3a\u5931\u65f6\uff0c\u5e94\u7acb\u5373\u6574\u6539\u5e76\u8ffd\u8d23\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u8d23\u4efb\u8981\u6c42 \u672a\u7ecf\u5ba1\u6279\u4f7f\u7528\u3001\u5171\u4eab\u51ed\u636e\u3001\u6269\u5927\u64cd\u4f5c\u8303\u56f4\u6216\u5220\u9664\u5ba1\u8ba1\u8bb0\u5f55\uff0c\u6309\u5b89\u5168\u8fdd\u89c4\u4e8b\u4ef6\u5904\u7406\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E4%BF%AE%E6%94%B9%E7%94%A8%E6%88%B7%E5%AF%86%E7%A0%81%E5%8F%8A%E6%9C%89%E6%95%88%E6%9C%9F\"><\/span>\u4fee\u6539\u7528\u6237\u5bc6\u7801\u53ca\u6709\u6548\u671f<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%AD%A3%E5%B8%B8%E7%99%BB%E5%BD%95\"><\/span>\u6b63\u5e38\u767b\u5f55<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>psql -h 127.0.0.1 -p 5432 -U postgres -d Dynames_BK_DB -W<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E4%BF%AE%E6%94%B9%E5%8D%95%E4%B8%AA%E7%94%A8%E6%88%B7\"><\/span>\u4fee\u6539\u5355\u4e2a\u7528\u6237<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>\\password u_system_admin\nALTER ROLE u_system_admin VALID UNTIL 'infinity';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u7b2c\u4e00\u6761\u4fee\u6539\u5bc6\u7801\uff0c\u7b2c\u4e8c\u6761\u8bbe\u7f6e\u5bc6\u7801\u6c38\u4e0d\u8fc7\u671f\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%89%B9%E9%87%8F%E8%AE%BE%E7%BD%AE%E6%89%80%E6%9C%89%E7%94%A8%E6%88%B7%E6%B0%B8%E4%B8%8D%E8%BF%87%E6%9C%9F\"><\/span>\u6279\u91cf\u8bbe\u7f6e\u6240\u6709\u7528\u6237\u6c38\u4e0d\u8fc7\u671f<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>DO $$\nDECLARE u record;\nBEGIN\n  FOR u IN SELECT rolname FROM pg_roles WHERE rolcanlogin LOOP\n    EXECUTE format(\n      'ALTER ROLE %I VALID UNTIL %L',\n      u.rolname,\n      'infinity'\n    );\n  END LOOP;\nEND $$;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u6279\u91cf\u4fee\u6539\u5bc6\u7801\u9700\u8981\u9010\u4e2a\u8f93\u5165\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\\password dynames\n\\password u_admin_01\n\\password u_audit_admin\n\\password u_dev_01\n\\password u_pg_breakglass\n\\password u_ro_01\n\\password u_security_admin\n\\password u_system_admin<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"postgres_%E5%B7%B2%E8%BF%87%E6%9C%9F%E6%88%96%E5%BF%98%E8%AE%B0%E5%AF%86%E7%A0%81\"><\/span><code>postgres<\/code> \u5df2\u8fc7\u671f\u6216\u5fd8\u8bb0\u5bc6\u7801<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">\u76f4\u63a5\u8fdb\u5165\u5bb9\u5668\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>podman exec -it postgres psql -U postgres -d Dynames_BK_DB<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u7136\u540e\u6267\u884c\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\\password postgres\nALTER ROLE postgres LOGIN VALID UNTIL 'infinity';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%A3%80%E6%9F%A5%E7%BB%93%E6%9E%9C\"><\/span>\u68c0\u67e5\u7ed3\u679c<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT rolname, rolvaliduntil\nFROM pg_roles\nWHERE rolcanlogin\nORDER BY rolname;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u663e\u793a <code>infinity<\/code> \u5c31\u662f\u6c38\u4e0d\u8fc7\u671f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u67e5\u8be21\uff1a\u767b\u5f55\u8d26\u53f7\u89d2\u8272\u5c5e\u6027 \u9700\u8981\u5173\u95ed\u9ed8\u8ba4\u8d85\u7ea7\u7ba1\u7406\u5458 ALTER ROLE postgres NOLOGIN; \u5c55 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3239","post","type-post","status-publish","format-standard","hentry","category-vxhs888p"],"_links":{"self":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts\/3239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3239"}],"version-history":[{"count":2,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts\/3239\/revisions"}],"predecessor-version":[{"id":3272,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts\/3239\/revisions\/3272"}],"wp:attachment":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}