3

Can a list of postgresql roles be generated that includes the role's timezone?

I've learned, and tested, that a psql session will take on the timezone of the role used at login. Of course we can alter a role to a particular time zone with:

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

But exactly how can that information be accessed to produce a list so that all the roles can be checked? I've tried \du+, but that returns very limited information. Surprisingly (to me anyway) the information_schema doesn't seem to include the timezone values. Internet searches have so far not helped in this quest.

Where else can I try?

3

The settings of a role can be retrieved from pg_db_role_setting, it's an text[] array of <setting name>=<setting value>. So this needs an unnest() and a split_part() to get to the individual components. Furthermore, as a setting can be set for a specific database (see the IN DATABASE option for the ALTER ROLE command), we need to involve the databases from pg_database. We cross join them with all the roles from pg_authid. To also include settings that aren't bound to a database but are valid for all databases we UNION ALL an empty database with a zero OID to the list of databases. From that we can left join the role settings.

So the following will give you the value set for timezone for all databases (including the "all" or "none" database) and all roles or null if the timezone isn't set for a user in a database.

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;
  • Wow! An answer almost immediately AND a few things to do more research on. (It's interesting that a setting can be set for a specific db - this is maybe new to me.) Thanks stick bit! – PatrickReagan Apr 16 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.