sql: support pgadmin's schema security SQL pretty-printer

[knz]

pgadmin issues the query quoted below.

This needs the following support:

• sql: add quote_literal() sql: add quote_literal() #26406 sql: add missing PostgreSQL built-in functions #26586
• sql: support concatenating more types together sql: support concatenating more types together #26585

SELECT array_to_string(array_agg(sql), e'\\n\\n') AS sql 
FROM (
    SELECT (
             (
               (
                 (
                   (
                     (
                       (
                         (
                           (
                             (
                               (
                                 (
                                   (
                                     (
                                       (
                                         (
                                           (
                                             (
                                               (
                                                 (
                                                   (
                                                     CASE WHEN rolcanlogin 
                                                       THEN '-- User: ' 
                                                       ELSE '-- Role: ' 
                                                     END || quote_ident(rolname)
                                                   ) || e'\\n-- DROP '
                                                 ) || CASE WHEN rolcanlogin 
                                                        THEN 'USER ' 
                                                        ELSE 'ROLE ' 
                                                      END
                                               ) || quote_ident(rolname)
                                             ) || e';\\n\\nCREATE '
                                           ) || CASE WHEN rolcanlogin 
                                                  THEN 'USER ' 
                                                  ELSE 'ROLE ' 
                                                END
                                         ) || quote_ident(rolname)
                                       ) || e' WITH\\n  '
                                     ) || CASE WHEN rolcanlogin 
                                            THEN 'LOGIN' 
                                            ELSE 'NOLOGIN' 
                                          END
                                   ) || e'\\n  '
                                 ) || CASE WHEN rolcanlogin AND (rolpassword LIKE 'md5%') 
                                        THEN ('ENCRYPTED PASSWORD ' || quote_literal(rolpassword)) || e'\\n  ' 
                                        ELSE '' 
                                      END
                               ) || CASE WHEN rolsuper 
                                      THEN 'SUPERUSER' 
                                      ELSE 'NOSUPERUSER' 
                                    END
                             ) || e'\\n  '
                           ) || CASE WHEN rolinherit 
                                  THEN 'INHERIT' 
                                  ELSE 'NOINHERIT' 
                                END
                         ) || e'\\n  '
                       ) || CASE WHEN rolcreatedb 
                              THEN 'CREATEDB' 
                              ELSE 'NOCREATEDB' 
                            END
                     ) || e'\\n  '
                   ) || CASE WHEN rolcreaterole 
                          THEN 'CREATEROLE' 
                          ELSE 'NOCREATEROLE' 
                        END
                 ) || e'\\n  '
               ) || CASE WHEN rolreplication 
                      THEN 'REPLICATION' 
                      ELSE 'NOREPLICATION' 
                    END
             ) || CASE WHEN rolconnlimit > 0 
                    THEN e'\\n  CONNECTION LIMIT ' || rolconnlimit 
                    ELSE '' 
                  END
           ) || CASE WHEN rolvaliduntil IS NOT NULL 
                  THEN e'\\n  VALID UNTIL ' || quote_literal(rolvaliduntil::TEXT) 
                  ELSE ';' 
           END AS sql 
    FROM pg_roles AS r 
    WHERE r.oid = 823966177::OID 
  UNION ALL 
    (
      SELECT array_to_string(array_agg(sql), e'\\n') AS sql 
      FROM (
        SELECT (
                 (
                   (
                     'GRANT ' || array_to_string(array_agg(rolname), ', ')
                   ) || ' TO '
                 ) || pg_catalog.quote_ident(pg_get_userbyid(823966177::OID))
               ) || CASE WHEN admin_option 
                      THEN ' WITH ADMIN OPTION;' 
                      ELSE ';' 
                    END AS sql 
        FROM (
          SELECT quote_ident(r.rolname) AS rolname, 
                 m.admin_option AS admin_option 
          FROM pg_auth_members AS m 
          LEFT JOIN pg_roles AS r 
            ON (m.roleid = r.oid) 
          WHERE m.member = 823966177::OID 
          ORDER BY r.rolname
        ) AS a 
        GROUP BY admin_option
      ) AS s
    ) 
  UNION ALL 
    (
      SELECT array_to_string(array_agg(sql), e'\\n') AS sql 
      FROM (
        SELECT (
                 (
                   (
                     (
                       (
                         (
                           'ALTER ' || CASE WHEN rolcanlogin 
                                         THEN 'USER ' 
                                         ELSE 'ROLE ' 
                                       END
                         ) || pg_catalog.quote_ident(rolname)
                       ) || ' SET '
                     ) || param
                   ) || ' TO '
                 ) || CASE WHEN param IN ('search_path', 'temp_tablespaces') 
                        THEN value 
                        ELSE quote_literal(value) 
                     END
                 ) || ';' AS sql 
        FROM (
          SELECT rolcanlogin, 
                 rolname, 
                 split_part(rolconfig, '=', 1) AS param, 
                 replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value 
          FROM (
            SELECT unnest(rolconfig) AS rolconfig, 
                   rolcanlogin, 
                   rolname 
            FROM pg_catalog.pg_roles 
            WHERE oid = 823966177::OID) AS r
        ) AS a
      ) AS b
    ) 
  UNION ALL 
    (
      SELECT array_to_string(array_agg(sql), e'\\n') AS sql 
      FROM (
        SELECT (
                 (
                   (
                     (
                       (
                         (
                           (
                             'ALTER ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(823966177::OID))
                           ) || ' IN DATABASE '
                         ) || pg_catalog.quote_ident(datname)
                       ) || ' SET '
                     ) || param
                   ) || ' TO '
                 ) || CASE WHEN param IN ('search_path', 'temp_tablespaces') 
                        THEN value 
                        ELSE quote_literal(value) 
                     END
               ) || ';' AS sql 
        FROM (
          SELECT datname, 
                 split_part(rolconfig, '=', 1) AS param, 
                 replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value 
          FROM (
            SELECT d.datname, 
                   unnest(c.setconfig) AS rolconfig 
            FROM (
              SELECT * 
              FROM pg_catalog.pg_db_role_setting AS dr 
              WHERE (dr.setrole = 823966177::OID) 
                AND (dr.setdatabase != 0)
            ) AS c 
            LEFT JOIN pg_catalog.pg_database AS d 
            ON (d.oid = c.setdatabase)
          ) AS a
        ) AS b
      ) AS d
    ) 
  UNION ALL 
    (
      SELECT (
               (
                 (
                   'COMMENT ON ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(823966177::OID))
                 ) || ' IS '
               ) || pg_catalog.quote_literal(description)
             ) || ';' AS sql 
      FROM (
        SELECT pg_catalog.shobj_description(823966177::OID, 'pg_authid') AS description
      ) AS a 
      WHERE description IS NOT NULL
    ) 
  UNION ALL 
    (
      SELECT array_to_string(array_agg(sql), e'\\n') AS sql 
      FROM (
        SELECT (
          (
            (
              (
                (
                  'SECURITY LABEL FOR ' || provider
                ) || e'\\n  ON ROLE '
              ) || pg_catalog.quote_ident(rolname)
            ) || e'\\n  IS '
          ) || pg_catalog.quote_literal(label)
        ) || ';' AS sql 
        FROM (
          SELECT label, 
                 provider, 
                 rolname 
          FROM (
            SELECT * 
            FROM pg_shseclabel AS sl1 
            WHERE sl1.objoid = 823966177::OID
          ) AS s 
          LEFT JOIN pg_catalog.pg_roles AS r 
          ON (s.objoid = r.oid)
        ) AS a
      ) AS b
    )
  ) AS a

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[jordanlewis]

This monster query now completes successfully!