Re: [External] Re: About pgagent monitor authority

Hi

On Thu, Mar 11, 2021 at 9:04 AM Zhiyu ZY13 Xu <xuzy13(at)lenovo(dot)com> wrote:

> Hi Dave
>
>
>
> Thanks for your explanation.
>
>
>
> I have some problem about my account on www.postgresql.org. I will log
> on this new feature after resolve it.
>
>
>
> I try to grant the permission to the account. Then use this sql to
> query pgagent jobs latest status.
>
>
>
> select A.jobid as ID, A.jobname as Name,A.jobenabled as Enabled
> ,B.jlgstatus as "Last Result" ,A.jobnextrun as "Next Run"
> ,max(A.joblastrun) as "Last Run" from pga_job A,pga_joblog B ,
> pga_jobstep C where A.jobid=B.jlgjobid and A.jobid=C.jstjobid group by
> A.jobid , A.jobname ,B.jlgstatus ,A.jobenabled ,A.jobnextrun,A.joblastrun
> order by jobname;
>
>
>
> The query result similar with Pgadmin snapshot. But my query result
> will contain duplicate values on “Last Result” if the maximum joblastrun
> have duplicate values. (Job ID=33)
>
> I don’t know which problem impact the result. I export the pgagent table
> data for your reference.
>
> Would you like to help me trouble shooting it ? Why my query result will
> have duplicate “Last Result” values ? Thanks
>
>
>
>
>
>
>
>
>
> postgres=# select A.jobid as ID, A.jobname as Name,A.jobenabled as Enabled
> ,B.jlgstatus as "Last Result" ,A.jobnextrun as "Next Run"
> ,max(A.joblastrun) as "Last Run" from pga_job A,pga_joblog B ,
> pga_jobstep C where A.jobid=B.jlgjobid and A.jobid=C.jstjobid group by
> A.jobid , A.jobname ,B.jlgstatus ,A.jobenabled ,A.jobnextrun,A.joblastrun
> order by jobname;
>
> id | name | enabled | Last
> Result | Next Run |
>
> Last Run
>
>
> ----+------------------------------------------------------+---------+-------------+------------------------+-------------------------------
>
> 42 | monitor_pgagent | t |
> s | 2021-03-11 16:10:00+08 | 2021-03-11 16:05:03.571461+08
>
> 7 | prod_19_03_f_sec_rdctransferfeeresult | t |
> s | 2021-03-12 08:30:00+08 | 2021-03-11 08:30:03.957127+08
>
> 24 | prod_19_04_f_sec_fill_sectransportmodebydistribution | t |
> s | 2021-03-12 08:30:00+08 | 2021-03-11 08:30:03.952616+08
>
> 25 | prod_19_05_f_sec_rdctransferfeeresultdetail | t |
> s | 2021-03-12 08:30:00+08 | 2021-03-11 08:30:03.947736+08
>
> 26 | prod_19_06_f_sec_fill_SECSignInfoByDayResult | t |
> s | 2021-03-12 09:00:00+08 | 2021-03-11 09:00:03.432608+08
>
> 27 | prod_19_07_f_sec_fill_SECDirectOnTimeByDayResult | t |
> s | 2021-03-12 09:00:00+08 | 2021-03-11 09:00:03.423938+08
>
> 40 | prod_19_08_sec_stocknum | t |
> s | 2021-03-11 16:30:00+08 | 2021-03-11 15:30:03.957217+08
>
> 6 | prod_29_01_f_cdc_inventoryinfo | t |
> s | 2021-03-11 16:05:00+08 | 2021-03-11 16:05:03.582335+08
>
> 6 | prod_29_01_f_cdc_inventoryinfo | t |
> r | 2021-03-11 16:05:00+08 | 2021-03-11 16:05:03.582335+08
>
> 10 | prod_29_02_f_cdc_realtimemonitor | t |
> r | 2021-03-11 16:05:00+08 | 2021-03-11 16:05:03.592001+08
>
> 10 | prod_29_02_f_cdc_realtimemonitor | t |
> s | 2021-03-11 16:05:00+08 | 2021-03-11 16:05:03.592001+08
>
> 11 | prod_29_03_f_cdc_stockoutinfo | t |
> s | 2021-03-11 16:05:00+08 | 2021-03-11 16:05:03.589128+08
>
> 11 | prod_29_03_f_cdc_stockoutinfo | t |
> r | 2021-03-11 16:05:00+08 | 2021-03-11 16:05:03.589128+08
>
> 19 | prod_29_04_f_rdc_stockoutinfo | t |
> s | 2021-03-11 17:05:00+08 | 2021-03-11 16:05:03.585052+08
>
> 13 | prod_29_05_f_rdc_inventoryinfo | t |
> s | 2021-03-11 17:05:00+08 | 2021-03-11 16:05:03.576024+08
>
> 15 | prod_29_06_f_transinventoryinfo | t |
> s | 2021-03-11 17:05:00+08 | 2021-03-11 16:05:03.578275+08
>
> 16 | prod_29_07_f_ontimedeliveryrate_byday | t |
> s | 2021-03-12 08:01:00+08 | 2021-03-11 08:01:04.482719+08
>
> 18 | prod_29_08_f_lenovosendinfo | t |
> s | 2021-03-12 08:01:00+08 | 2021-03-11 08:01:04.469943+08
>
> 17 | prod_29_09_f_tmschannelfee | t |
> s | 2021-03-12 08:01:00+08 | 2021-03-11 08:01:04.478619+08
>
> 21 | prod_29_11_f_t_level_1_kpi_result | t |
> s | 2021-03-12 07:01:00+08 | 2021-03-11 07:01:00.780153+08
>
> 14 | prod_29_12_f_export_report_wechatapp | t |
> s | 2021-03-12 05:30:00+08 | 2021-03-11 05:30:02.431488+08
>
> 8 | prod_37_01_f_fbinfo | t |
> s | 2021-03-12 05:01:00+08 | 2021-03-11 05:01:02.917568+08
>
> 28 | prod_37_02_f_doaontimedeliveryrate | t |
> s | 2021-03-12 05:30:00+08 | 2021-03-11 05:30:02.416182+08
>
> 29 | prod_37_03_f_fee_ontimedeliveryrate | t |
> s | 2021-03-12 05:30:00+08 | 2021-03-11 05:30:02.419976+08
>
> 30 | prod_37_04_dbo."f_sec_reportstockreserveresult" | t |
> s | 2021-03-12 06:01:00+08 | 2021-03-11 06:01:01.907286+08
>
> 31 | prod_37_05_dbo."f_sec_stockoutbydayresult" | t |
> s | 2021-03-11 16:38:00+08 | 2021-03-11 15:38:04.232341+08
>
> 32 | prod_37_06_dbo."f_sec_SECProductStockModel" | t |
> s | 2021-03-11 16:28:00+08 | 2021-03-11 15:28:03.896728+08
>
> 33 | prod_37_07_dbo."f_tms_stockXLDC" | t |
> s | 2021-03-11 15:28:00+08 | 2021-03-11 15:28:03.892446+08
>
> 33 | prod_37_07_dbo."f_tms_stockXLDC" | t |
> r | 2021-03-11 15:28:00+08 | 2021-03-11 15:28:03.892446+08
>
> 33 | prod_37_07_dbo."f_tms_stockXLDC" | t |
> d | 2021-03-11 15:28:00+08 | 2021-03-11 15:28:03.892446+08
>
> 34 | prod_37_08_dbo."f_sec_dumpstockoutbyhoursresult" | t |
> s | 2021-03-11 16:28:00+08 | 2021-03-11 15:28:03.889989+08
>
> 35 | prod_37_09_dbo."f_sec_reportstockinefficiencyrate" | t |
> s | 2021-03-12 07:00:00+08 | 2021-03-11 07:00:00.750808+08
>
> 36 | prod_37_10_f_secinoutvolume | t |
> s | 2021-03-12 07:00:00+08 | 2021-03-11 07:00:00.754927+08
>
> 41 | prod_37_11_f_dbo.basic_satisfaction_survey | t |
> s | 2021-03-11 16:15:00+08 | 2021-03-11 16:00:03.395884+08
>
> (34 rows)
>
>
>
>
>
At a quick glance I'd say it's likely because you're looking at the last
and next run times for the job, and you're seeing results for each of three
runs which actually happened at different times. You should look at
pga_joblog.jlgstart to see the actual start time for each individual
execution of the job.

Unfortunately I'm having trouble restoring your dumps so can't check
further. If you want to send them again, please include all tables in one
file, as well as the table definitions.

> 徐志宇（Jack）
>
> Database Engineer
>
>
>
> DB Team,ITS. Lenovo China
>
> Phone: 86-18910860709
>
> Email:xuzy13(at)lenovo(dot)com
>
> No.6 Shangdi West Road, Haidian District Beijing, China, 100085
>
>
>
> *发件人:* Dave Page <dpage(at)pgadmin(dot)org>
> *发送时间:* 2021年3月9日 18:23
> *收件人:* Zhiyu ZY13 Xu <xuzy13(at)lenovo(dot)com>
> *抄送:* pgadmin-support(at)postgresql(dot)org
> *主题:* [External] Re: About pgagent monitor authority
>
>
>
> Hi
>
>
>
> On Mon, Mar 8, 2021 at 4:28 PM Zhiyu ZY13 Xu <xuzy13(at)lenovo(dot)com> wrote:
>
>
>
> Hi pgadmin/pgagent support
>
>
>
> I experience a authority problem when using pgagent. Some developer want
> to check the pgagent jobs running status by pgadmin.
>
> It’s easy to view and check the jobs running status by pgadmin.
>
> But I found that only SUPER user could check the pgagent status by
> pgadmin.
>
> as a PG DBA. I don’t want to others to using SUPER authority user.
>
> Is there any way to grant specify permission to developer to check
> pgagent jobs running status by pgadmin except use SUPER user ? Thanks in
> advance.
>
>
>
> Not within the main UI, no. Please log a feature request at
> https://redmine.postgresql.org/projects/pgadmin4/issues/new
> <https://apc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fredmine.postgresql.org%2Fprojects%2Fpgadmin4%2Fissues%2Fnew&data=04%7C01%7Cxuzy13%40lenovo.com%7C595699cf16ec415f4c3e08d8e2e568e4%7C5c7d0b28bdf8410caa934df372b16203%7C1%7C0%7C637508822235823065%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=lMeE6xSj0EbVCSLuK53YBOHUXKQ6h5EGSdtx96NpE10%3D&reserved=0>.
> I think by default the permissions on the underlying tables will still
> block access - and that's the correct thing to do as the job output may
> contain sensitive information. pgAdmin should gracefully fail with a
> "permission denied" error by default, but it could be made to work by
> explicitly GRANTing SELECT permissions on the underlying tables to users
> who should be able to view the logs.
>
>
>
> In the meantime, you could do that anyway; grant permissions for your
> users to SELECT from pga_joblog and pga_jobsteplog.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> 徐志宇（Jack）
>
> Database Engineer
>
>
>
> DB Team,ITS. Lenovo China
>
> Phone: 86-18910860709
>
> Email:xuzy13(at)lenovo(dot)com
>
> No.6 Shangdi West Road, Haidian District Beijing, China, 100085
>
>
>
>
>
>
> --
>
> Dave Page
> Blog: http://pgsnake.blogspot.com
> <https://apc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fpgsnake.blogspot.com%2F&data=04%7C01%7Cxuzy13%40lenovo.com%7C595699cf16ec415f4c3e08d8e2e568e4%7C5c7d0b28bdf8410caa934df372b16203%7C1%7C0%7C637508822235833050%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=fbnMldv2X4lPoJBwpOnVoAh90USgrr7RC8Gf2FSYVxo%3D&reserved=0>
> Twitter: @pgsnake
>
> EDB: http://www.enterprisedb.com
> <https://apc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.enterprisedb.com%2F&data=04%7C01%7Cxuzy13%40lenovo.com%7C595699cf16ec415f4c3e08d8e2e568e4%7C5c7d0b28bdf8410caa934df372b16203%7C1%7C0%7C637508822235833050%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=qNeRBBzWgTDdHMeUH3nFzJIegnsz3oNCPVhdYCyVe4E%3D&reserved=0>
>

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com