SQLize Online / PHPize Online  /  SQLtest Online

WITH exemplo as ( SELECT '{"allowlist":[{"collection":"allowed-queries","scope":{"global":true}}],"sources":[{"kind":"postgres","name":"UPADS","tables":[{"select_permissions":[{"role":"bla","permission":{"columns":["id","nome"],"filter":{}}},{"role":"master","permission":{"columns":["id","nome"],"filter":{}}}],"insert_permissions":[{"role":"bla","permission":{"backend_only":false,"check":{},"columns":["nome"]}},{"role":"master","permission":{"backend_only":false,"check":{},"columns":["nome"]}}],"table":{"schema":"rmcontact","name":"l001"},"update_permissions":[{"role":"bla","permission":{"check":null,"columns":["id","nome"],"filter":{}}},{"role":"master","permission":{"check":null,"columns":["nome"],"filter":{}}}],"delete_permissions":[{"role":"bla","permission":{"filter":{}}},{"role":"master","permission":{"filter":{}}}]},{"select_permissions":[{"role":"bla","permission":{"columns":["created_at","roles","updated_at","username"],"filter":{}}},{"role":"master","permission":{"columns":["created_at","roles","updated_at","username"],"filter":{}}}],"insert_permissions":[{"role":"master","permission":{"backend_only":false,"check":{},"columns":["created_at","roles","updated_at","username"]}},{"role":"teste2","permission":{"backend_only":false,"check":{},"columns":["created_at","roles","updated_at","username"]}}],"table":{"schema":"rmcontact","name":"users"},"update_permissions":[{"role":"master","permission":{"check":null,"columns":["created_at","roles","updated_at","username"],"filter":{}}}],"delete_permissions":[{"role":"master","permission":{"filter":{}}}]}],"configuration":{"connection_info":{"use_prepared_statements":false,"database_url":"postgres://upads:1@postgres:5432/upads","isolation_level":"read-committed"}}}],"version":3,"query_collections":[{"definition":{"queries":[]},"name":"allowed-queries"}]}'::jsonb as metadata ), SELECT_PERM AS ( select upper(t.table_data #>> '{kind}') as dsn, upper(t.table_data #>> '{name}') as NOME_BANCO, upper(json.* #>> '{table,schema}') as SCHEM, upper(json.* #>> '{table,name}') as NOME_TABELA, t3.nome_regra, t.table_data, json, ACOES.* from EXEMPLO A , jsonb_path_query(A.metadata::jsonb, '$.sources[*]') as t(table_data) CROSS JOIN LATERAL jsonb_path_query(T.table_data::jsonb,'$.tables[*]') as json CROSS JOIN LATERAL ( SELECT DISTINCT UNNEST(ARRAY[ UPPER(jsonb_array_elements(json -> 'select_permissions')->>'role'), UPPER(jsonb_array_elements(json -> 'insert_permissions')->>'role'), UPPER(jsonb_array_elements(json -> 'update_permissions')->>'role'), UPPER(jsonb_array_elements(json -> 'delete_permissions')->>'role') ] ) as nome_regra) as t3 CROSS JOIN LATERAL ( ( SELECT true as SELECIONA,true as ADICIONA,true as ATUALIZA,true as APAGA ) ) ACOES WHERE nome_regra is not null ) SELECT * FROM SELECT_PERM