Allow arbitrary user to read MonetDB's sys.queue

Question

I created a user foouser and a schema fooschema with some tables in it. Foouser executes long-running queries to its tables in fooschema.

Now I'd like to check on the status of the queries. However, foouser does not have sufficient permissions to access sys.queue table/view:

sql>SELECT * FROM sys.queue;
SELECT: access denied for foouser to table 'sys.queue'
sql>SELECT * FROM sys.queue();
SELECT: no such operator 'queue'

The monetdb user has the necessary permissions, but does not see foosuer's queries but only their own:

sql>SELECT * FROM sys.queue;
+---------+---------+----------------------------+----------------------------+----------+---------+-----------+--------------------------+
| qtag    | user    | started                    | estimate                   | progress | status  | tag       | query                    |
+=========+=========+============================+============================+==========+=========+===========+==========================+
| 2593257 | monetdb | 2018-04-26 15:06:01.000000 | null                       |     null | running | 2593257@0 | select * from sys.queue; |
+---------+---------+----------------------------+----------------------------+----------+---------+-----------+--------------------------+

So the question is: How can foouser view the status of its own queries? Can I grant foouser the access to sys.queue()? If so, how?

Any solution for this , even i am facing the same issue now. — Aspirant, Sep 25 '18 at 11:54
Unfortunately, I did not find a solution so far. The problem hits me every now and then, and I keep ignoring it. ;-) Maybe you could write something on one of the [MonetDB mailing lists](https://www.monetdb.org/Developers/Mailinglists)? — dasup, Sep 25 '18 at 17:02
The fact that the "superuser" *monetdb* does not see other user's queries has been reported as [MonetDB bug 6636](https://www.monetdb.org/bugzilla/show_bug.cgi?id=6636) recently. — dasup, Nov 09 '18 at 18:00
MonetDB bug 6636 has been resolved on 29 Jan 2019 in [changeset 68191](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f06ab9e5f30b). As the lastest release of MonetDB is younger than than, the fix is probably already included. Nevertheless, the change does only enable the sysadmin to see all user's queries (instead of just its own). However, the change does not allow an arbitrary user to access `sys.queue()` to see its own queries. — dasup, Jul 02 '19 at 16:33

score 0 · Answer 1 · answered Nov 09 '18 at 17:57

I found a solution which allows a "normal" user to read its own sys.queue. However, this approach grants (for my liking) way to many permissions to the user and is therefore not optimal.

The approach is to grant the role sysadmin to foouser. This has to be done while being logged in as monetdb user (i.e. running mclient -u monetdb):

sql> grant sysadmin to foouser;
operation successful

Afterwards, if you log in as foouser (i.e. mclient -u foouser), you have to select the sysadmin role and can now see you own running queries by looking into sys.queue:

sql>set role sysadmin;
operation successful
sql>select * from sys.queue;
+---------+---------+-------------+-------------+------+---------+-----------+--------------+
| qtag    | user    | started     | estimate    | prog | status  | tag       | query        |
:         :         :             :             : ress :         :           :              :
+=========+=========+=============+=============+======+=========+===========+==============+
| 1627134 | foouser | 2018-11-09  | null        | null | running | 1627134@0 | insert into  |
:         :         : 17:48:57.00 :             :      :         :           : REDACTED     :
:         :         : 0000        :             :      :         :           : REDACTED     :
| 1627135 | foouser | 2018-11-09  | null        | null | running | 1627135@0 | select * fro |
:         :         : 17:48:57.00 :             :      :         :           : m sys.queue; :
:         :         : 0000        :             :      :         :           :              :
+---------+---------+-------------+-------------+------+---------+-----------+--------------+
2 tuples !1 field truncated!

However, now the (intentionally) restricted user foouser has full administrative permissions what makes the use of a restricted user somewhat pointless.

Allow arbitrary user to read MonetDB's sys.queue

1 Answers1