{"id":2277,"date":"2022-03-01T15:09:45","date_gmt":"2022-03-01T07:09:45","guid":{"rendered":"https:\/\/www.zeroheart.xyz\/wordpress\/?p=2277"},"modified":"2022-05-17T19:38:38","modified_gmt":"2022-05-17T11:38:38","slug":"pg%e8%b7%a8%e6%a8%a1%e5%bc%8f%e6%9f%a5%e8%af%a2%ef%bc%8c%e4%b8%8d%e7%94%a8%e5%8a%a0%e5%89%8d%e7%bc%80","status":"publish","type":"post","link":"https:\/\/www.zeroheart.xyz\/wordpress\/?p=2277","title":{"rendered":"pg\u8de8\u6a21\u5f0f\u67e5\u8be2\uff0c\u4e0d\u7528\u52a0\u524d\u7f00"},"content":{"rendered":"\n<p>\u4fee\u6539\u6a21\u5f0f\u7684\u6240\u5c5e\u5373\u53ef<\/p>\n\n\n\n<p><s>ALTER TABLE &#8220;xxx&#8221;.&#8221;table&#8221; OWNER TO &#8220;zzz&#8221;;<\/s><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">show search_path ;\n\u6211\u53ef\u4ee5\u8bbe\u7f6e\u5f53\u524d\u4f1a\u8bdd\u7684search_path\uff1a\n\nset search_path = \"$user\", public,postgis;\n\n\u540c\u6837,\u6211\u53ef\u4ee5\u4f7f\u7528\u4ee5\u4e0b\u547d\u4ee4\u6c38\u4e45\u8bbe\u7f6e\u7ed9\u5b9a\u6570\u636e\u5e93\u7684search_path\uff1a\nalter database database set search_path = \"$user\",postgis ;\n\n\u6211\u53ef\u4ee5\u4f7f\u7528\u4ee5\u4e0b\u5185\u5bb9\u4e3a\u7ed9\u5b9a\u89d2\u8272(\u7528\u6237)\u6c38\u4e45\u8bbe\u7f6esearch_path\uff1a\nalter role username set search_path = \"$user\",postgis ;\n<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">\u60a8\u53ef\u4ee5\u5728\u76ee\u5f55\u8868 pg_db_role_setting\u4e2d\u627e\u5230\u89d2\u8272\u548c\u6570\u636e\u5e93\u7684\u914d\u7f6e\u8bbe\u7f6e.\n\u6b64\u67e5\u8be2\u68c0\u7d22\u7ed9\u5b9a\u89d2\u8272\u6216\u6570\u636e\u5e93\u7684\u4efb\u4f55\u8bbe\u7f6e\uff1a\n\nSELECT r.rolname,d.datname,rs.setconfig\nFROM pg_db_role_setting rs\nleft JOIN pg_roles r ON r.oID = rs.setrole\nleft JOIN pg_database d ON d.oID = rs.setdatabase\nWHERE r.rolname = 'username' OR d.datname = 'database';<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">\u8981\u53d6\u6d88\u8bbe\u7f6e\u89d2\u8272\u6216\u6570\u636e\u5e93\u7684\u4efb\u4f55\u8bbe\u7f6e \u2013 \u6b64\u7279\u5b9a\u793a\u4f8b\u4e2d\u7684search_path\uff1a\n\nALTER RolE username reset search_path;\n\u8981\u4e48\uff1a\n\nALTER DATABASE database reset search_path;\n\u8981\u4e48\n\nALTER RolE myrole in DATABASE mydb reset search_path;\n\u5207\u52ff\u624b\u52a8\u64cd\u4f5c\u7cfb\u7edf\u76ee\u5f55(pg_catalog.*)\u4e2d\u7684\u6570\u636e.\u6309\u7167ALTER ROLE\u548cALTER DATABASE\u624b\u518c\u4e2d\u7684\u8bf4\u660e\u4f7f\u7528DDL\u547d\u4ee4.\u5b9e\u9645\u4e0a,reset\u547d\u4ee4\u4ecepg_db_role_setting\u4e2d\u5220\u9664\u4e00\u884c,\u5141\u8bb8\u57fa\u672c\u8bbe\u7f6e\u518d\u6b21\u751f\u6548.\u6211\u4e0d\u4f1a\u79f0\u4e4b\u4e3a\u590d\u6742.\n<\/pre>\n\n\n\n<p>\u53c2\u8003\uff1a<a href=\"https:\/\/www.jb51.cc\/postgresql\/192379.html\">postgresql \u2013 \u7ed9\u5b9a\u6570\u636e\u5e93\u548c\u7528\u6237\u7684search_path\u662f\u4ec0\u4e48\uff1f &#8211; \u7f16\u7a0b\u4e4b\u5bb6 (jb51.cc)<\/a><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4fee\u6539\u6a21\u5f0f\u7684\u6240\u5c5e\u5373\u53ef ALTER TABLE &#8220;xxx&#8221;.&#8221;table&#038;#8 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[],"_links":{"self":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2277"}],"collection":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2277"}],"version-history":[{"count":14,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2277\/revisions"}],"predecessor-version":[{"id":3162,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2277\/revisions\/3162"}],"wp:attachment":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2277"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2277"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}