Show default privileges in information schema

Hitherto, the information schema only showed explicitly granted
privileges that were visible in the *acl catalog columns.  If no
privileges had been granted, the implicit privileges were not shown.

To fix that, add an SQL-accessible version of the acldefault()
function, and use that inside the aclexplode() calls to substitute the
catalog-specific default privilege set for null values.

reviewed by Abhijit Menon-Sen

2 files changed, 67 insertions, 9 deletions

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 818582833d0..f591f64caf7 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -554,7 +554,7 @@ CREATE VIEW column_privileges AS
                   pr_c.prtype,
                   pr_c.grantable,
                   pr_c.relowner
-           FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(relacl)).*
+           FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
                  FROM pg_class
                  WHERE relkind IN ('r', 'v', 'f')
                 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
@@ -571,8 +571,8 @@ CREATE VIEW column_privileges AS
                   pr_a.prtype,
                   pr_a.grantable,
                   c.relowner
-           FROM (SELECT attrelid, attname, (aclexplode(attacl)).*
-                 FROM pg_attribute
+           FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*
+                 FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid)
                  WHERE attnum > 0
                        AND NOT attisdropped
                 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
@@ -1276,7 +1276,7 @@ CREATE VIEW routine_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT oid, proname, proowner, pronamespace, (aclexplode(proacl)).* FROM pg_proc
+            SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
          ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
          pg_namespace n,
          pg_authid u_grantor,
@@ -1797,7 +1797,7 @@ CREATE VIEW table_privileges AS
            CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
 
     FROM (
-            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(relacl)).* FROM pg_class
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
          pg_namespace nc,
          pg_authid u_grantor,
@@ -2043,7 +2043,7 @@ CREATE VIEW udt_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(typacl)).* FROM pg_type
+            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
          pg_namespace n,
          pg_authid u_grantor,
@@ -2129,7 +2129,7 @@ CREATE VIEW usage_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(typacl)).* FROM pg_type
+            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
          pg_namespace n,
          pg_authid u_grantor,
@@ -2166,7 +2166,7 @@ CREATE VIEW usage_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
+            SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
          pg_authid u_grantor,
          (
@@ -2200,7 +2200,7 @@ CREATE VIEW usage_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
+            SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
          pg_authid u_grantor,
          (
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index b80fc442d9f..9644afc34a2 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -835,6 +835,64 @@ acldefault(GrantObjectType objtype, Oid ownerId)
 
 
 /*
+ * SQL-accessible version of acldefault().  Hackish mapping from "char" type to
+ * ACL_OBJECT_* values, but it's only used in the information schema, not
+ * documented for general use.
+ */
+Datum
+acldefault_sql(PG_FUNCTION_ARGS)
+{
+	char	objtypec = PG_GETARG_CHAR(0);
+	Oid		owner = PG_GETARG_OID(1);
+	GrantObjectType objtype = 0;
+
+	switch (objtypec)
+	{
+		case 'c':
+			objtype = ACL_OBJECT_COLUMN;
+			break;
+		case 'r':
+			objtype = ACL_OBJECT_RELATION;
+			break;
+		case 's':
+			objtype = ACL_OBJECT_SEQUENCE;
+			break;
+		case 'd':
+			objtype = ACL_OBJECT_DATABASE;
+			break;
+		case 'f':
+			objtype = ACL_OBJECT_FUNCTION;
+			break;
+		case 'l':
+			objtype = ACL_OBJECT_LANGUAGE;
+			break;
+		case 'L':
+			objtype = ACL_OBJECT_LARGEOBJECT;
+			break;
+		case 'n':
+			objtype = ACL_OBJECT_NAMESPACE;
+			break;
+		case 't':
+			objtype = ACL_OBJECT_TABLESPACE;
+			break;
+		case 'F':
+			objtype = ACL_OBJECT_FDW;
+			break;
+		case 'S':
+			objtype = ACL_OBJECT_FOREIGN_SERVER;
+			break;
+		case 'T':
+			objtype = ACL_OBJECT_TYPE;
+			break;
+		default:
+			elog(ERROR, "unrecognized objtype abbreviation: %c", objtypec);
+	}
+
+	PG_RETURN_ACL_P(acldefault(objtype, owner));
+}
+
+
+/*
  * Update an ACL array to add or remove specified privileges.
  *
  *	old_acl: the input ACL array