答复: [External] Re: About pgagent monitor authority

Hi Dave

Thanks for your explanation.

I have some problem about my account on www.postgresql.org<http://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

[cid:image002(dot)png(at)01D71698(dot)A86BB4F0]

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)

徐志宇（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<mailto: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.

[cid:image003(dot)png(at)01D71698(dot)A86BB4F0]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13(at)lenovo(dot)com<mailto:Email%3Axuzy13(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>