Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4366192

Browse files
committed
Don't recommend "DROP SCHEMA information_schema CASCADE".
It drops objects outside information_schema that depend on objectsinside information_schema. For example, it will drop a user-definedview if the view query refers to information_schema.Discussion:https://postgr.es/m/20170831025345.GE3963697@rfd.leadboat.com
1 parent6c77b47 commit4366192

File tree

2 files changed

+74
-14
lines changed

2 files changed

+74
-14
lines changed

‎doc/src/sgml/release-9.2.sgml‎

Lines changed: 37 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -58,14 +58,44 @@
5858
in an existing installation, you can, as a superuser, do this
5959
in <application>psql</>:
6060
<programlisting>
61-
BEGIN;
62-
DROP SCHEMA information_schema CASCADE;
63-
\i <replaceable>SHAREDIR</>/information_schema.sql
64-
COMMIT;
61+
SET search_path TO information_schema;
62+
CREATE OR REPLACE VIEW table_privileges AS
63+
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
64+
CAST(grantee.rolname AS sql_identifier) AS grantee,
65+
CAST(current_database() AS sql_identifier) AS table_catalog,
66+
CAST(nc.nspname AS sql_identifier) AS table_schema,
67+
CAST(c.relname AS sql_identifier) AS table_name,
68+
CAST(c.prtype AS character_data) AS privilege_type,
69+
CAST(
70+
CASE WHEN
71+
-- object owner always has grant options
72+
pg_has_role(grantee.oid, c.relowner, 'USAGE')
73+
OR c.grantable
74+
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
75+
CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
76+
77+
FROM (
78+
SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
79+
) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
80+
pg_namespace nc,
81+
pg_authid u_grantor,
82+
(
83+
SELECT oid, rolname FROM pg_authid
84+
UNION ALL
85+
SELECT 0::oid, 'PUBLIC'
86+
) AS grantee (oid, rolname)
87+
88+
WHERE c.relnamespace = nc.oid
89+
AND c.relkind IN ('r', 'v', 'f')
90+
AND c.grantee = grantee.oid
91+
AND c.grantor = u_grantor.oid
92+
AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
93+
AND (pg_has_role(u_grantor.oid, 'USAGE')
94+
OR pg_has_role(grantee.oid, 'USAGE')
95+
OR grantee.rolname = 'PUBLIC');
6596
</programlisting>
66-
(Run <literal>pg_config --sharedir</> if you're uncertain
67-
where <replaceable>SHAREDIR</> is.) This must be repeated in each
68-
database to be fixed.
97+
This must be repeated in each database to be fixed,
98+
including <literal>template0</>.
6999
</para>
70100
</listitem>
71101

‎doc/src/sgml/release-9.3.sgml‎

Lines changed: 37 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -52,14 +52,44 @@
5252
in an existing installation, you can, as a superuser, do this
5353
in <application>psql</>:
5454
<programlisting>
55-
BEGIN;
56-
DROP SCHEMA information_schema CASCADE;
57-
\i <replaceable>SHAREDIR</>/information_schema.sql
58-
COMMIT;
55+
SET search_path TO information_schema;
56+
CREATE OR REPLACE VIEW table_privileges AS
57+
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
58+
CAST(grantee.rolname AS sql_identifier) AS grantee,
59+
CAST(current_database() AS sql_identifier) AS table_catalog,
60+
CAST(nc.nspname AS sql_identifier) AS table_schema,
61+
CAST(c.relname AS sql_identifier) AS table_name,
62+
CAST(c.prtype AS character_data) AS privilege_type,
63+
CAST(
64+
CASE WHEN
65+
-- object owner always has grant options
66+
pg_has_role(grantee.oid, c.relowner, 'USAGE')
67+
OR c.grantable
68+
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
69+
CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
70+
71+
FROM (
72+
SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
73+
) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
74+
pg_namespace nc,
75+
pg_authid u_grantor,
76+
(
77+
SELECT oid, rolname FROM pg_authid
78+
UNION ALL
79+
SELECT 0::oid, 'PUBLIC'
80+
) AS grantee (oid, rolname)
81+
82+
WHERE c.relnamespace = nc.oid
83+
AND c.relkind IN ('r', 'v', 'f')
84+
AND c.grantee = grantee.oid
85+
AND c.grantor = u_grantor.oid
86+
AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
87+
AND (pg_has_role(u_grantor.oid, 'USAGE')
88+
OR pg_has_role(grantee.oid, 'USAGE')
89+
OR grantee.rolname = 'PUBLIC');
5990
</programlisting>
60-
(Run <literal>pg_config --sharedir</> if you're uncertain
61-
where <replaceable>SHAREDIR</> is.) This must be repeated in each
62-
database to be fixed.
91+
This must be repeated in each database to be fixed,
92+
including <literal>template0</>.
6393
</para>
6494
</listitem>
6595

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp