5 Replies Latest reply on Apr 12, 2018 12:29 AM by Michael Held

    Getting site roles from postgres

    iwona.wiktorowska.0

      I was looking for a SQL to query Tableau postgres database to get site roles for all sites for all server users. I found a way to do it with powershell here: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db, but I cannot add my answer there and I was able to figure out the SQL to get the site roles (with help of Tableau Singapore team):

       

      SELECT _system_users.name, _system_users.friendly_name, _users.licensing_role_id, _users.licensing_role_name, users.publisher_tristate, _sites.name as Site,

      CASE

      WHEN users.admin_level = 5 then 'Site Administrator'

      WHEN _system_users.admin_level = 10 then 'Server Administrator'

      WHEN _users.licensing_role_name = 'Viewer' and users.publisher_tristate = 2 then 'Viewer (can publish)'

      WHEN _users.licensing_role_name = 'Unlicensed' and users.publisher_tristate = 2 then 'Unlicensed (can publish)'

      WHEN users.publisher_tristate = 1 then 'publisher (Implicit)'

      WHEN users.publisher_tristate = 2 then 'Publisher'

      else _users.licensing_role_name

      END as CorrectRoles

      FROM _users

      LEFT JOIN _system_users

      ON _users.system_user_id = _system_users.id

      LEFT JOIN _sites

      ON _users.site_id = _sites.id

      INNER JOIN users

      ON _users.id = users.id

      ORDER BY _system_users.friendly_name

       

      I then created a spreadsheet with site, correctroles, name and friendly name. Pretty useful if user management is delegated to the separate team of for audit purposes.