8000 Don't recommend "DROP SCHEMA information_schema CASCADE". · koderP/postgres@4366192 · GitHub
[go: up one dir, main page]
Skip to content
8000

Commit 4366192

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

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< C84A code class="diff-text syntax-highlighted-line addition">+
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)
0