Not working installation - Login SQL problem

[ppatrik]

Hello, we installed taranis 3.7.4 and after success installation we cannot login to web interface.

In logs we have found this errors

==> postgresql-12-main.log <==
2021-02-23 09:22:52.961 UTC [27722] jana@taranis ERROR:  missing FROM-clause entry for table "rol" at character 8
2021-02-23 09:22:52.961 UTC [27722] jana@taranis STATEMENT:  SELECT rol.name, rol.description, ur.role_id FROM user_role AS ur WHERE ur.username = $1  ORDER BY rol.name

==> error_log <==
[Tue Feb 23 09:22:52.962660 2021] [:error] [pid 27617:tid 139940973246208] Database error, please check log for info. at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 78.\n\teval {...} called at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 77\n\tTaranis::FunctionalWrapper::Croaker::AUTOLOAD(Taranis::FunctionalWrapper::Croaker=HASH(0x7f467c063700), "admin") called at /home/taranis/taranis-3.7.4/perl5/Taranis/Role.pm line 291\n\tTaranis::Role::getRolesFromUser(Taranis::FunctionalWrapper::Croaker=HASH(0x7f467805bf30), "username", "admin") called at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 78\n\teval {...} called at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 77\n\tTaranis::FunctionalWrapper::Croaker::AUTOLOAD(Taranis::FunctionalWrapper::Croaker=HASH(0x7f467805bf30), "username", "admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 96\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::processSuccessfulLogin("admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 49\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::handler(Apache2::RequestRec=SCALAR(0x7f46938d9a88)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\teval {...} called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\tModPerl::RegistryCooker::run(ModPerl::Registry=HASH(0x7f46901b01f8)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 173\n\tModPerl::RegistryCooker::default_handler(ModPerl::Registry=HASH(0x7f46901b01f8)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/Registry.pm line 32\n\tModPerl::Registry::handler("ModPerl::Registry", Apache2::RequestRec=SCALAR(0x7f46938d9a88)) called at -e line 0\n\teval {...} called at -e line 0\n at /home/taranis/taranis-3.7.4/perl5/Taranis/Role.pm line 291.\n\tTaranis::Role::getRolesFromUser(Taranis::FunctionalWrapper::Croaker=HASH(0x7f467805bf30), "username", "admin") called at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 78\n\teval {...} called at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 77\n\tTaranis::FunctionalWrapper::Croaker::AUTOLOAD(Taranis::FunctionalWrapper::Croaker=HASH(0x7f467805bf30), "username", "admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 96\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::processSuccessfulLogin("admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 49\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::handler(Apache2::RequestRec=SCALAR(0x7f46938d9a88)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\teval {...} called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\tModPerl::RegistryCooker::run(ModPerl::Registry=HASH(0x7f46901b01f8)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 173\n\tModPerl::RegistryCooker::default_handler(ModPerl::Registry=HASH(0x7f46901b01f8)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/Registry.pm line 32\n\tModPerl::Registry::handler("ModPerl::Registry", Apache2::RequestRec=SCALAR(0x7f46938d9a88)) called at -e line 0\n\teval {...} called at -e line 0\n at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 86.\n\tTaranis::FunctionalWrapper::Croaker::AUTOLOAD(Taranis::FunctionalWrapper::Croaker=HASH(0x7f467805bf30), "username", "admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 96\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::processSuccessfulLogin("admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 49\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::handler(Apache2::RequestRec=SCALAR(0x7f46938d9a88)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\teval {...} called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\tModPerl::RegistryCooker::run(ModPerl::Registry=HASH(0x7f46901b01f8)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 173\n\tModPerl::RegistryCooker::default_handler(ModPerl::Registry=HASH(0x7f46901b01f8)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/Registry.pm line 32\n\tModPerl::Registry::handler("ModPerl::Registry", Apache2::RequestRec=SCALAR(0x7f46938d9a88)) called at -e line 0\n\teval {...} called at -e line 0\n

==> access_log <==
158.197.233.23 - - [23/Feb/2021:09:22:52 +0000] "POST /taranis/login/?cause=nosession&goto=%2Ftaranis%2F HTTP/1.1" 500 2954

I can see missing join in database request, and after looking to Role.pm source file i think that problem is in SQL framework.

[markov2]

This is weird: no-one else is experiencing this issue.

In pm/Taranis/Role.pm line 284, you should see this:

    my $select = 'rol.name, rol.description, ur.role_id';
    my ( $stmnt, @bind ) = $self->{sql}->select( 'user_role AS ur', $select, \%where, "rol.name" );

    my %join = ( "JOIN role AS rol" => { "ur.role_id" => "rol.id" } );
    $stmnt = $self->{dbh}->sqlJoin( \%join, $stmnt );

which is actually a really ugly way to build the database query. But, in your error message I do see the JOIN. This is code which dates from before we started to use git in 2013!

Can you please tell me which version of SQL::Abstract you have installed (and which OS you are using?)

[ppatrik]

Thanks for reply, additional info is: OS:

Ubuntu 20.04.2 LTS

SQL:

patrik_pekarcik@janakovalicova:~$ sudo apt list --installed | grep postgres

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

postgresql-12/focal-updates,focal-security,now 12.6-0ubuntu0.20.04.1 amd64 [installed,automatic]
postgresql-client-12/focal-updates,focal-security,now 12.6-0ubuntu0.20.04.1 amd64 [installed,automatic]
postgresql-client-common/focal-updates,focal-security,now 214ubuntu0.1 all [installed,automatic]
postgresql-common/focal-updates,focal-security,now 214ubuntu0.1 all [installed,automatic]
postgresql-contrib/focal-updates,focal-security,now 12+214ubuntu0.1 all [installed]
postgresql/focal-updates,focal-security,now 12+214ubuntu0.1 all [installed]

I am not sure how to find correct version of SQL::Abstract but, in .cpan/Metadata file i have found this rows

-Y/YU/YUSRIDEB/CellBIS-SQL-Abstract-1.3.tar.gz^@^@^@    CPAN_FILE
^C1.3^@^@^@^LCPAN_VERSION^@^@^@#CellBIS::SQL::Abstract::Test::Table^D^C^@^@^@^C

[markov2]

Getting the version with

perl -MSQL::Abstract::More -we 'print $SQL::Abstract::More::VERSION, "\n"'

The "CellBIS..." is an other module. But the chances are slim that it is the cause. when I read the details of the code.

Maybe the STATEMENT shown in the error message is what PostgreSQL understood from the query, not a direct copy of what the original request was. Is there anything in your database environment which could confuse PG? For instance, a database which is named 'rol'?

Maybe something broken when the database schema was loaded or got it's initial filling. Could you take a look at:

   su - tarans
   psql       # this is a wrapper which brings you to the right db immediately
   SELECT * FROM role;
   SELECT * FROM user_role;

[ppatrik]

We installed it on clean Ubuntu 20.04 virtual server. Database was installed with taranis-bootstrap script. Below are requested responses from system.

taranis[3.7.4]@jana:~/etc> perl -MSQL::Abstract::More -we 'print $SQL::Abstract::More::VERSION, "\n"'
1.37
taranis[3.7.4]@jana:~/etc> psql -d taranis
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

taranis=# SELECT * FROM role;
 id |         name          | description 
----+-----------------------+-------------
  1 | Taranis Administrator | Admin
(1 row)

taranis=#    SELECT * FROM user_role;
    id     | role_id | username 
-----------+---------+----------
 460000000 |       1 | admin
(1 row)

[markov2]

I run PSQL 12.4 myself, on openSUSE. Probably that's not really a difference.

Are you a bit acquainted with Perl and debugging? I wonder how the statement which PSQL reports is different from the statement I expect. Could you try this for me?

su - taranis
vi ~/taranis-3.7.4/perl5/Taranis/Role.pm

Then add a debug line after 288:

    my %join = ( "JOIN role AS rol" => { "ur.role_id" => "rol.id" } );
    $stmnt = $self->{dbh}->sqlJoin( \%join, $stmnt );
warn $stmnt;                                        # <---- this one
    $self->{dbh}->prepare($stmnt);
    $self->{dbh}->executeWithBinds(@bind);

As user taranis then restart the daemon: taranis restart Try to login.

And now the hardest part: where did the message get logged? journalctl? /var/log/taranis/taranis-3.7.4/...? /tmp/systemd*apache/tmp/taranis.errors? It's on all platforms difference :-(

[ppatrik]

Unfortunately I have never debuged Perl. But i will try my best, to find solution with some help in domain.

So far i found this in systemctl status taranis

Feb 23 09:20:51 jana TARANIS[27617]: DBD::Pg::st execute failed: ERROR:  missing FROM-clause entry for table "rol"
                                               LINE 1: SELECT rol.name, rol.description, ur.role_id FROM user_role ...
                                                              ^ at /home/taranis/taranis-3.7.4/perl5/Taranis/Database.pm line 225.

                                               ==> STMNT: SELECT rol.name, rol.description, ur.role_id FROM user_role AS ur WHERE ur.username = ?  ORDER BY ro>
                                               ==> BINDS: admin

I've changed warn with die and found sql in apache's error_log

==> error_log <==
[Tue Feb 23 12:46:18.884636 2021] [:error] [pid 29922:tid 139941216544512] SELECT rol.name, rol.description, ur.role_id FROM user_role AS ur WHERE ur.username = ?  ORDER BY rol.name at /home/taranis/taranis-3.7.4/perl5/Taranis/Role.pm line 289.\n at /home/taranis/taranis-3.7.4/perl5/Taranis/FunctionalWrapper/Croaker.pm line 86.\n\tTaranis::FunctionalWrapper::Croaker::AUTOLOAD(Taranis::FunctionalWrapper::Croaker=HASH(0x7f468f8b73d8), "username", "admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 96\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::processSuccessfulLogin("admin") called at /home/taranis/taranis-3.7.4/modperl/login.pl line 49\n\tModPerl::ROOT::ModPerl::Registry::home_taranis_taranis_2d3_2e7_2e4_modperl_login_2epl::handler(Apache2::RequestRec=SCALAR(0x55cfe94c0be0)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\teval {...} called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 207\n\tModPerl::RegistryCooker::run(ModPerl::Registry=HASH(0x7f468c025d80)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/RegistryCooker.pm line 173\n\tModPerl::RegistryCooker::default_handler(ModPerl::Registry=HASH(0x7f468c025d80)) called at /usr/lib/x86_64-linux-gnu/perl5/5.30/ModPerl/Registry.pm line 32\n\tModPerl::Registry::handler("ModPerl::Registry", Apache2::RequestRec=SCALAR(0x55cfe94c0be0)) called at -e line 0\n\teval {...} called at -e line 0\n

[markov2]

I made a little Perl script which runs the code which produces your db-query. Can you run this?

#!/usr/bin/env perl
use warnings;
use strict;

use lib '/home/taranis/taranis-3.7.4/perl5';
use Taranis::Database;
use Taranis::DB;

use Taranis::FunctionalWrapper qw(Database Sql);
use SQL::Abstract::More;

my $dbh = Database;
my $sql = Sql;

my %where;
$where{'ur.username'} = 'admin';

my $select = 'rol.name, rol.description, ur.role_id';
my ( $stmnt, @bind ) = $sql->select( 'user_role AS ur', $select, \%where, "rol.name" );

my %join = ( "JOIN role AS rol" => { "ur.role_id" => "rol.id" } );
$stmnt = $dbh->sqlJoin( \%join, $stmnt );

warn $stmnt;

For me, this correctly produces

SELECT rol.name, rol.description, ur.role_id FROM user_role AS ur JOIN role AS rol ON ur.role_id = rol.id WHERE ( ur.username = ? ) ORDER BY rol.name;

So: with the JOIN clause which I do not see in your error message. Is this the same for you? Can you paste that line on the database command line ("su - taranis; psql; ")

[ppatrik]

This is the output :/

taranis=# SELECT rol.name, rol.description, ur.role_id FROM user_role AS ur JOIN role AS rol ON ur.role_id = rol.id WHERE ( ur.username = 'admin' ) ORDER BY ro
l.name;
         name          | description | role_id 
-----------------------+-------------+---------
 Taranis Administrator | Admin       |       1
(1 row)

taranis[3.7.4]@jana:~> ./test.pm 
SELECT rol.name, rol.description, ur.role_id FROM user_role AS ur WHERE ur.username = ?  ORDER BY rol.name at ./test.pm line 17.

[markov2]

I do see the difference: my SQL::Abstract produces WHERE ( ... ), and yours is without superfluous parenthesis. The tricky sqlJoin uses WHERE ( to determine the location where the JOIN must be inserted textually.

When you change line 295 in ~taranis/taranis-3.7.4/perl5/Taranis/Database

  -       $stmnt =~ s/( WHERE \()/$str WHERE \(/;
  +       $stmnt =~ s/( WHERE )/$str WHERE /;

And then taranis restart, you probably have a working system. I'll study whether this is a change which doesn't kill other things.

[markov2]

Apparently, this output changed with SQL::Abstract v2.000000 which was released on Jan 21st. I'll have to create a new release for Taranis :-( Thank you for your help investigating this!

[ppatrik]

Thank you too. Login is working now. I'll let me kolegues to look at it. Once again Thanks.

[Pyr0technicien]

Hey,

I believe I have a similare issue here, I made the modification for the login to work but, I receive an error when I try to send an email with a news :

Feb 23 15:00:16 taranis TARANIS[864259]: DBD::Pg::st execute failed: ERROR:  missing FROM-clause entry for table "publication_endofshift"
                                         LINE 1: SELECT DISTINCT(users.fullname) FROM users WHERE publication...
                                                                                                  ^ at /home/taranis/taranis-3.7.4/perl5/Taranis/Database.pm line 225.

                                         ==> STMNT: SELECT DISTINCT(users.fullname) FROM users WHERE publication_endofshift.id = ?

[markov2]

Yes, very probably... JOINs are missing because a supporting module changed behavior. I'll have to make a new release of Taranis to fix it. Work-around: forcefully install a version of perl module SQL::Abstract before 2.0

[markov2]

I have released 3.7.5 which fixes this issue. Thanks for your contribution. MarkOv