GVM 20.08 Postgres user/ownership issues for creating GVM admin users

Hi,

I’m a newbie trying to use the Ubuntu PPA on a VM in AWS. I’ve reviewed the installation notes provided in the PPA, as well as several others regarding the setting up of GVM admin users (specifically to get the scan config downloads working) but find myself unable to follow the instructions exactly because:

  1. If I use the instructions at https://github.com/greenbone/gvmd/blob/master/INSTALL.md#setting-up-the-postgresql-database to createdb with the owner set to ubuntu (the AWS default user), I see errors at run time in /var/log/gvm/gvmd.log along these lines:

    md main:MESSAGE:2021-02-26 11h49.03 utc:27398:    Greenbone Vulnerability Manager version 20.08.1 (DB revision 233)
    md manage:WARNING:2021-02-26 11h49.03 utc:27400: sql_exec_internal: PQexec failed: ERROR:  must be owner of aggregate group_concat (7)
    md manage:WARNING:2021-02-26 11h49.03 utc:27400: sql_exec_internal: SQL: DROP AGGREGATE IF EXISTS group_concat (text, text);
    md manage:WARNING:2021-02-26 11h49.03 utc:27400: sqlv: sql_exec_internal failed
    

    after installation.

  2. Instead of following those instructions, if I allow the PPA packages to perform the createdb, then the gvmd database owner is set to the builtin postgres account, and I can avoid those errors above using commands like sudo -u postgres gvmd --create-user=ubuntu and so on. However, I am unable to get the scan configs to load even after using

    sudo -u postgres gvmd --modify-setting 78eceaec-3385-11ea-b237-28d24461215b --value <uuid-of-ubuntu-or-admin-user>
    

    to set the Feed Import Owner. In the Web GUI, I can see that all feeds are listed as current, so I guessing that the setting of the Feed Import Owner is not correct.

AFAIK, without the scan configs, I’m pretty much stuck. Any thoughts as to what could be wrong, or what to look at to diagnose further?

Thanks, Shaheed

I forgot to say, I also checked that /var/run/ospd/ospd.sock exists and is listed by --get-scanners.

After a restart, I noticed the following appearing in gvmd.log every 90s or so:

md   main:MESSAGE:2021-02-26 18h04.21 utc:7947:    Greenbone Vulnerability Manager version 20.08.1 (DB revision 233)
md manage:WARNING:2021-02-26 18h04.21 utc:7948: sql_exec_internal: PQexec failed: ERROR:  must be owner of aggregate group_concat
 (7)
md manage:WARNING:2021-02-26 18h04.21 utc:7948: sql_exec_internal: SQL: DROP AGGREGATE IF EXISTS group_concat (text, text);
md manage:WARNING:2021-02-26 18h04.21 utc:7948: sqlv: sql_exec_internal failed

I presume this is pointing ot the root cause. FWIW, this is what the database ownership looks like:

psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \l
                               List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |    Access privileges    
-----------+----------+----------+---------+---------+-------------------------
 foo       | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres           +
           |          |          |         |         | postgres=CTc/postgres  +
           |          |          |         |         | dbcoreuser=CTc/postgres
 gvmd      | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres           +
           |          |          |         |         | postgres=CTc/postgres  +
           |          |          |         |         | gvmduser=C/postgres
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
(5 rows)

Oh, and I also cannot log in via the web any more…

Why don´t you file a bug report with your uncoordinated 3rd party packet provider ?

There is nothing we can do or help you here, i suggest you compile your installation from the source and not using packages that are not working.

2 Likes

That’s a fair point, but I guess what I am hoping for is some insight as to who (user) or what (role) should be the owner of group_concat. I’d then be in a position either fix the configuration myself, or file a bug with the packager with the needed details.

OK, after discussion with the PPA packager about another matter, it seems that the resolution to that solves this problem too.

FWIW, the fix is in openvas 1:20.8.1-2.

In addition, for anybody else who finds this thread, to run a gvmd CLI command such as gvmd --get-users when using the PPA, it is necessary to do something like the following:

export $(sudo cat /etc/default/gvmd-pg)
sudo -E -u gvm -g gvm gvmd ...

in order to load the postgres credentials. However, setting the feed import owner for the “admin” user will be done automatically by the gvmd post-installation script, so this may not be needed at all. With that, I have everything running - except that I have yet to get a scan config to load.

1 Like