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

Commit59597e6

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 parentfa41461 commit59597e6

File tree

5 files changed

+185
-35
lines changed

5 files changed

+185
-35
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

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

Lines changed: 37 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -68,14 +68,44 @@ Branch: REL9_4_STABLE [b51c8efc6] 2017-08-24 15:21:32 -0700
6868
in an existing installation, you can, as a superuser, do this
6969
in <application>psql</>:
7070
<programlisting>
71-
BEGIN;
72-
DROP SCHEMA information_schema CASCADE;
73-
\i <replaceable>SHAREDIR</>/information_schema.sql
74-
COMMIT;
71+
SET search_path TO information_schema;
72+
CREATE OR REPLACE VIEW table_privileges AS
73+
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
74+
CAST(grantee.rolname AS sql_identifier) AS grantee,
75+
CAST(current_database() AS sql_identifier) AS table_catalog,
76+
CAST(nc.nspname AS sql_identifier) AS table_schema,
77+
CAST(c.relname AS sql_identifier) AS table_name,
78+
CAST(c.prtype AS character_data) AS privilege_type,
79+
CAST(
80+
CASE WHEN
81+
-- object owner always has grant options
82+
pg_has_role(grantee.oid, c.relowner, 'USAGE')
83+
OR c.grantable
84+
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
85+
CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
86+
87+
FROM (
88+
SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
89+
) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
90+
pg_namespace nc,
91+
pg_authid u_grantor,
92+
(
93+
SELECT oid, rolname FROM pg_authid
94+
UNION ALL
95+
SELECT 0::oid, 'PUBLIC'
96+
) AS grantee (oid, rolname)
97+
98+
WHERE c.relnamespace = nc.oid
99+
AND c.relkind IN ('r', 'v', 'f')
100+
AND c.grantee = grantee.oid
101+
AND c.grantor = u_grantor.oid
102+
AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
103+
AND (pg_has_role(u_grantor.oid, 'USAGE')
104+
OR pg_has_role(grantee.oid, 'USAGE')
105+
OR grantee.rolname = 'PUBLIC');
75106
</programlisting>
76-
(Run <literal>pg_config --sharedir</> if you're uncertain
77-
where <replaceable>SHAREDIR</> is.) This must be repeated in each
78-
database to be fixed.
107+
This must be repeated in each database to be fixed,
108+
including <literal>template0</>.
79109
</para>
80110
</listitem>
81111

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

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

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

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

0 commit comments

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