konnosroles.ts48 lines · main
| 1 | import { safeSql } from '../pg-format' |
| 2 | |
| 3 | export const ROLES_SQL = /* SQL */ safeSql` |
| 4 | -- Can't use pg_authid here since some managed Postgres providers don't expose it |
| 5 | -- https://github.com/supabase/postgres-meta/issues/212 |
| 6 | |
| 7 | select |
| 8 | r.oid as id, |
| 9 | rolname as name, |
| 10 | rolsuper as "isSuperuser", |
| 11 | rolcreatedb as "canCreateDb", |
| 12 | rolcreaterole as "canCreateRole", |
| 13 | rolinherit as "inheritRole", |
| 14 | rolcanlogin as "canLogin", |
| 15 | rolreplication as "isReplicationRole", |
| 16 | rolbypassrls as "canBypassRls", |
| 17 | ( |
| 18 | select |
| 19 | count(*) |
| 20 | from |
| 21 | pg_stat_activity |
| 22 | where |
| 23 | r.rolname = pg_stat_activity.usename |
| 24 | ) as "activeConnections", |
| 25 | case when rolconnlimit = -1 then current_setting('max_connections') :: int8 |
| 26 | else rolconnlimit |
| 27 | end as "connectionLimit", |
| 28 | rolvaliduntil as "validUntil", |
| 29 | coalesce(r_config.role_configs, '{}') as config |
| 30 | from |
| 31 | pg_roles r |
| 32 | left join ( |
| 33 | select |
| 34 | oid, |
| 35 | jsonb_object_agg(param, value) filter (where param is not null) as role_configs |
| 36 | from |
| 37 | ( |
| 38 | select |
| 39 | oid, |
| 40 | (string_to_array(unnest(rolconfig), '='))[1] as param, |
| 41 | (string_to_array(unnest(rolconfig), '='))[2] as value |
| 42 | from |
| 43 | pg_roles |
| 44 | ) as _ |
| 45 | group by |
| 46 | oid |
| 47 | ) r_config on r_config.oid = r.oid |
| 48 | ` |