sql: add pg_catalog.pg_default_acl

[BramGruneir]

Note: this vtable is empty on pg by default.

PGAdmin issues the following query:

SELECT db.oid AS did,
       db.datname AS name,
       db.dattablespace AS spcoid,
       spcname,
       datallowconn,
       pg_encoding_to_char(encoding) AS encoding,
       pg_get_userbyid(datdba) AS datowner,
       datcollate,
       datctype,
       datconnlimit,
       has_database_privilege(db.oid, 'CREATE') AS cancreate,
       current_setting('default_tablespace') AS default_tablespace,
       descr.description AS comments,
       db.datistemplate AS is_template,
       (
         SELECT array_to_string(ARRAY (
          SELECT array_to_string(defaclacl::TEXT[], ', ')
          FROM pg_default_acl
          WHERE (defaclobjtype = 'r')
            AND (defaclnamespace = 0::OID)
         ), ', ')
        ) AS tblacl,
        (
          SELECT array_to_string(ARRAY (
            SELECT array_to_string(defaclacl::TEXT[], ', ')
            FROM pg_default_acl
            WHERE (defaclobjtype = 'S')
              AND (defaclnamespace = 0::OID)
          ), ', ')
        ) AS seqacl,
        (
          SELECT array_to_string(ARRAY (
            SELECT array_to_string(defaclacl::TEXT[], ', ')
            FROM pg_default_acl
            WHERE (defaclobjtype = 'f')
              AND (defaclnamespace = 0::OID)
          ), ', ')
        ) AS funcacl,
        (
          SELECT array_to_string(ARRAY (
            SELECT array_to_string(defaclacl::TEXT[], ', ')
            FROM pg_default_acl
            WHERE (defaclobjtype = 'T')
              AND (defaclnamespace = 0::OID)
          ), ', ')
        ) AS typeacl,
        (
          SELECT array_agg((provider || '=') || label)
          FROM pg_shseclabel AS sl1
          WHERE sl1.objoid = db.oid
        ) AS seclabels,
        array_to_string(datacl::TEXT[], ', ') AS acl
FROM pg_database AS db
LEFT JOIN pg_tablespace AS ta
  ON db.dattablespace = ta.oid
LEFT JOIN pg_shdescription AS descr
  ON ((db.oid = descr.objoid) AND (descr.classoid = 'pg_database'::REGCLASS))
WHERE db.oid > NULL::OID
ORDER BY datname

Details can be found here: https://www.postgresql.org/docs/current/static/catalog-pg-default-acl.html

[awoods187]

@jordanlewis what's the level of effort to support this? It's a blocker to using PgAdmin

[jordanlewis]

Low level of effort, but pgadmin won't nearly be usable after we fix this.

[BramGruneir]

I think support for pgadmin should not be prioritized over other more database agnostic solutions. Plus, there is no way the pgadmin team would be willing to adapt it for us.

…

On Fri, Apr 26, 2019, 18:58 Jordan Lewis ***@***.***> wrote: Low level of effort, but pgadmin won't nearly be usable after we fix this. — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub <#26378 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAMKDOIC52HBQVFCB4U7PMLPSOCKRANCNFSM4FDGWGFQ> .