列出用户/用户角色及其默认时区

发布于 2025-01-17 04:10:15 字数 451 浏览 1 评论 0原文

我在 postgres 数据库中有一个表，其中包含“timestamptz”和“timestamp”列。最近我发现数据有些出入。调查后发现，有多个用户被用来插入数据。我假设根据用户角色/用户，默认时区有所不同，从而导致了差异。例如，我相信某些查询已按如下方式执行，从而更改了用户/角色的时区

ALTER ROLE my_db_user IN DATABASE my_database
SET "TimeZone" TO 'UTC';

现在我想列出用户角色及其默认时区以及用户列表及其默认时区，而不是作为每个用户登录并找到时区作为

show timezone();

我怎样才能实现这一目标？这将帮助我确保数据完整性，即使将来某些用户的默认时区发生了变化，我也可以使用参考表来识别时区。

任何文档或查询的路径都会非常有帮助，

提前致谢

原文

I have a table in a postgres DB that consists of "timestamptz" and "timestamp" columns. Recently I have found some discrepancies in the data. Investigating it lead to the fact that multiple users have been used to insert the data. I'm assuming based on user role/user, the default timezone varies which caused the discrepancies.
e.g. I believe some query has been executed as follows, thus changing the timezone for users / roles

ALTER ROLE my_db_user IN DATABASE my_database
SET "TimeZone" TO 'UTC';

Now I would like to list user roles and their default timezones as well as a list of users and their default timezone, Instead of login as every single user and finding the timezone as

show timezone();

How can I achieve this? This would aid me to ensure the data integrity even if in the future some user's default timezone has changed, I can use a reference table to identify the timezones.

Any path to documentation or query would be quite helpful

Thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三五鸿雁 2025-01-24 04:10:15

这将是一个查询：

SELECT r.rolname,
       coalesce(  /* if no per-user setting, use global one */
          split_part(s.pair, '=', 2),  /* the value after the = */
          current_setting('TimeZone', TRUE)  /* global time zone setting */
       )
FROM pg_roles AS r  /* list of roles */
   LEFT JOIN pg_db_role_setting AS rs  /* get the per-role settings if they exist */
      ON r.oid = rs.setrole
   LEFT JOIN LATERAL unnest(rs.setconfig) AS s(pair)  /* unpack the array */
      ON s.pair ^@ 'TimeZone'  /* but only get the settings that start with "TimeZone" */;

This would be a query:

SELECT r.rolname,
       coalesce(  /* if no per-user setting, use global one */
          split_part(s.pair, '=', 2),  /* the value after the = */
          current_setting('TimeZone', TRUE)  /* global time zone setting */
       )
FROM pg_roles AS r  /* list of roles */
   LEFT JOIN pg_db_role_setting AS rs  /* get the per-role settings if they exist */
      ON r.oid = rs.setrole
   LEFT JOIN LATERAL unnest(rs.setconfig) AS s(pair)  /* unpack the array */
      ON s.pair ^@ 'TimeZone'  /* but only get the settings that start with "TimeZone" */;

回复收藏 0 原文

~没有更多了~