Блог

Можно ли создать список ролей postgresql, который включает часовой пояс роли?

Я узнал и протестировал, что сеанс psql будет принимать часовой пояс роли, используемой при входе в систему. Конечно, мы можем изменить роль на определенный часовой пояс с помощью:

 # ALTER ROLE testUser SET timezone TO 'America/Chicago';
 

Но как именно можно получить доступ к этой информации для создания списка, чтобы можно было проверить все роли? Я пробовал du , но это возвращает очень ограниченную информацию. Удивительно (во всяком случае, для меня), что information_schema, похоже, не включает значения часового пояса. Поиск в Интернете до сих пор не помог в этом поиске.

Где еще я могу попробовать?

Настройки роли можно получить из pg_db_role_setting , это text[] массив <setting name>=<setting value> . Итак, для этого нужны an unnest() и a split_part() , чтобы добраться до отдельных компонентов. Кроме того, поскольку настройка может быть установлена для конкретной базы данных (см. IN DATABASE Опцию для ALTER ROLE команды), нам нужно задействовать базы данных из pg_database . Мы пересекаем их со всеми ролями из pg_authid . Чтобы также включить настройки, которые не привязаны к базе данных, но действительны для всех баз данных, мы UNION ALL добавим пустую базу данных с нулевым идентификатором OID в список баз данных. Из этого мы можем слева присоединиться к настройкам роли.

Итак, следующее даст вам значение, установленное для timezone для всех баз данных (включая базу данных «все» или «нет») и все роли или null, если значение timezone не установлено для пользователя в базе данных.

 SELECT rol.rolname,
       dat.datname,
       split_part(kvp.kvp, '=', 2) timezone
       FROM pg_authid rol
            CROSS JOIN (SELECT dat.oid,
                               dat.datname
                               FROM pg_database dat
                         UNION ALL
                         SELECT 0::oid oid,
                                '' datname) dat
            LEFT JOIN pg_db_role_setting set
                      ON set.setdatabase = dat.oid
                         AND set.setrole = rol.oid
            LEFT JOIN LATERAL unnest(set.setconfig) kvp (kvp)
                              ON lower(split_part(kvp.kvp, '=', 1)) = 'timezone'
       ORDER BY 1,
                2;