10 Replies Latest reply on Jun 2, 2017 3:23 PM by Luke Brady

    Using Postgres To Examine Permissions

    simon harvey

      Hi,

       

      I've been spending some time immersing myself in the dark depths of Postgres. One task I set myself today was to be able to pull together permission data.

       

      The end goal was to have a report we could use at work to find out who has what permission to specific workbooks. I quickly found out this wasn't going to be a simple as tapping into a single table, as we have users who, as a user don't have assigned permission to view a workbook, but inherit permission from the group they're a member of.

       

      Also, as any Tableau admins will know permission are overwritten by higher level permissions. So just looking at user level permissions can often give a confusing picture.

       

      So I pulled the below query together....

       

      We're using v8.3

       

      What i'm wondering is has anyone else done this?, Can anyone spot any glaring errors ( I've been testing as I go but I'm still not 100% with Postgres), Is there an easier way?

       

      Any feedback would be greatly received.

       

      --Workbook User Level Permissions

      SELECT

      CAST ('Workbook' as varchar) as object, --Set to either workbook or datasource

      p.name as project, --project where item sits

      s.name as Site_name,

      w.name as Object_Name, --Workbook or datasource name

      w.revision as Version, --Version where applicable

      CAST ('User' as varchar) as Grantee_type, --means permission given to user or inherited from group

      su.name as User_or_group_Name, --User name

      c.name as Permission_type, --Type of permission

      CASE

        WHEN ngp.permission = 1 THEN 'Allow by Group'

        WHEN ngp.permission = 2 THEN 'Deny by Group'

        WHEN ngp.permission = 3 THEN 'Allow to User'

        WHEN ngp.permission = 4 THEN 'Deny to User'

      END as Granted_Denied --This shows if restriction is applied to user or group

      FROM public.next_gen_permissions ngp

      JOIN public.workbooks w ON ngp.authorizable_id=w.id

      JOIN public.users u ON ngp.grantee_id=u.id

      JOIN public.system_users su ON u.system_user_id=su.id

      JOIN public.capabilities c ON ngp.capability_id=c.id

      JOIN public.projects p ON w.project_id=p.id

      JOIN public.sites s ON w.site_id=s.id

      WHERE ngp.authorizable_type = 'Workbook'

      AND ngp.grantee_type = 'User'

      AND su.name <> 'guest'

      UNION ALL

      --Workbook Group Level Permissions

      SELECT

      CAST ('Workbook' as varchar) as object,

      p.name as project,

      s.name as Site_name,

      w.name as Object_Name,

      w.revision as Version,

      CAST ('Group' as varchar) as Grantee_type,

      u.name as User_or_group_Name,

      c.name as Permission_type,

      CASE

        WHEN ngp.permission = 1 THEN 'Allow by Group'

        WHEN ngp.permission = 2 THEN 'Deny by Group'

        WHEN ngp.permission = 3 THEN 'Allow to User'

        WHEN ngp.permission = 4 THEN 'Deny to User'

      END as Granted_Denied

      FROM public.next_gen_permissions ngp

      JOIN public.workbooks w ON ngp.authorizable_id=w.id

      JOIN public.groups u ON ngp.grantee_id=u.id

      JOIN public.capabilities c ON ngp.capability_id=c.id

      JOIN public.projects p ON w.project_id=p.id

      JOIN public.sites s ON w.site_id=s.id

      WHERE ngp.authorizable_type = 'Workbook'

      AND ngp.grantee_type = 'Group'

      UNION ALL

      --Datasource User Level Permissions

      SELECT

      CAST ('Datasource' as varchar) as object,

      p.name as project,

      s.name as Site_name,

      ds.name as Object_Name,

      ds.revision as Version,

      CAST ('User' as varchar) as Grantee_type,

      su.name as User_or_group_Name,

      c.name as Permission_type,

      CASE

        WHEN ngp.permission = 1 THEN 'Allow by Group'

        WHEN ngp.permission = 2 THEN 'Deny by Group'

        WHEN ngp.permission = 3 THEN 'Allow to User'

        WHEN ngp.permission = 4 THEN 'Deny to User'

      END as Granted_Denied

      FROM public.next_gen_permissions ngp

      JOIN public.datasources ds ON ngp.authorizable_id=ds.id

      JOIN public.projects p ON ds.project_id=p.id

      JOIN public.users u ON ngp.grantee_id=u.id

      JOIN public.system_users su ON u.system_user_id=su.id

      JOIN public.capabilities c ON ngp.capability_id=c.id

      JOIN public.sites s on ds.site_id=s.id

      WHERE ngp.authorizable_type = 'Datasource'

      AND ngp.grantee_type = 'User'

      AND su.name <> 'guest'

      UNION ALL

      --Datasource Group Level Permissions

      SELECT

      CAST ('Datasource' as varchar) as object,

      p.name as project,

      s.name as Site_name,

      ds.name as Object_Name,

      ds.revision as Version,

      CAST ('Group' as varchar) as Grantee_type,

      g.name as User_or_group_Name,

      c.name as Permission_type,

      CASE

        WHEN ngp.permission = 1 THEN 'Allow by Group'

        WHEN ngp.permission = 2 THEN 'Deny by Group'

        WHEN ngp.permission = 3 THEN 'Allow to User'

        WHEN ngp.permission = 4 THEN 'Deny to User'

      END as Granted_Denied

      FROM public.next_gen_permissions ngp

      JOIN public.datasources ds ON ngp.authorizable_id=ds.id

      JOIN public.projects p ON ds.project_id=p.id

      JOIN public.groups g ON ngp.grantee_id=g.id

      JOIN public.capabilities c ON ngp.capability_id=c.id

      JOIN public.sites s on ds.site_id=s.id

      WHERE ngp.authorizable_type = 'Datasource'

      AND ngp.grantee_type = 'Group'

        • 1. Re: Using Postgres To Examine Permissions
          Patrick A Van Der Hyde

          Moving to Server Administration where others that play in the Postgres DB post and hang out. 

          • 2. Re: Using Postgres To Examine Permissions
            John Kuo

            Simon,

             

            I have a permissions workbook (I don't remember who created it) that I've attached to this post. Can you compare and perhaps use it to refine yours and let us know?

             

            Thanks,

             

            John

            2 of 2 people found this helpful
            • 3. Re: Using Postgres To Examine Permissions
              David Ryan

              John,

              The permissions workbook was amazing! Looks like the SQL was developed by David Mannering with input from Toby Erkson. I used the SQL statement in Alteryx to push the data to a scheduled Tableau Workbook which eliminated the need to use the PostgreSQL login every time the dashboard is used on the Tableau server.

               

              Thanks for sharing.

               

              -- by David Mannering

              -- unions the next_gen_permissions table with itself to get the six combinations of permissions:

              -- project group, project user, workbook group, workbook user, view group, and view user

              -- the joins are required to get names and the relationships between project/workbook and view

              -- Toby Erkson, Jan2016: Placing admin checking at end of data pull (code from confidential source); tidied up the SQL

              • 4. Re: Using Postgres To Examine Permissions
                Amanda May

                Do you know if this workbook is available for server version 10.1? I love the layout, and that I can see everything in one view, but the Capatility section does not match Server Version 10.1.

                 

                Thanks!

                Amanda

                • 6. Re: Using Postgres To Examine Permissions
                  Amanda May

                  I did find that one, and it's what I've been working with, but it's for version 9.1, correct? Some of the Capibilities are different in our Server Version, and I wasn't sure how to update the workbook to reflect that. The provided workbook shows "Filter, View Underlying Data, Share Customized, Web Authoring, and Write. My options only include Filter, Download Full Data, Share Customized and Web Edit (Under the Interact section). On the Edit section, the provided workbook has Download File, Move, Delete, and Set Permissions. My choices are Save, Download Workbook/Save As, MOve, Delete, and Set Permissions.

                   

                  How can I get these Capabilities to match my server version? I am not as good at SQL as this is written, to update it myself. Also, these are just the Capabilities for Workbooks, is there anyway to see the Capabilities for Project and Data Source?

                   

                  Thanks for your help!

                   

                  Amanda

                  • 7. Re: Using Postgres To Examine Permissions
                    Toby Erkson

                    No, the workbook is version 9.1 but the custom SQL will work with version 9 & 10 and most likely 11 when that comes out.  If you look at the second SELECT statement

                    SELECT X.*, C.display_name AS "Capability"
                    

                    you can see that there is no aliasing so the options that appear come directly from the Tableau Server table, that's how they are internally.  Those options are then made human-friendly when displayed on the permissions page in Tableau Server, that's all.  So you can use the workbook for your version of Server   You just need to mentally "convert" what you see in the workbook to what you see on the Server permissions page.

                     

                    I thought Project permissions would display if workbooks/views wasn't included but apparently not...I wonder if possibly something changed from v.9 to v.10...?  I could be wrong on any point in this sentence, it's been a while since I played with the workbook.  However, the workbook permissions are displaying correctly for v.10.

                     

                    As for Data Source permissions, I'm sure it can be done but someone would need to build it...since David was the brains behind this I nominate him

                    1 of 1 people found this helpful
                    • 8. Re: Using Postgres To Examine Permissions
                      Amanda May

                      Thanks for your help, I really appreciate it!

                      • 9. Re: Using Postgres To Examine Permissions
                        Toby Erkson

                        Simon,

                        Really good effort!

                         

                        • This cannot be used to see Project level permissions.
                        • Project Leader permissions are missing.  For example, my Default project has permissions locked to the Project so the Workbooks/Views won't have different permissions:

                        You'll see that the User "DTNA_CVD_s_VTService" doesn't appear:

                         

                        • I didn't checked the accuracy of Data Sources.

                         

                        BTW, I used

                        c.display_name as Permission_type, --Type of permission
                        

                        instead of c.name to make reading the permissions easier.