Installing database failling

[VLucet]

Hi! I am trying to set up AIDE on my local machine but I am having issues with setting up the database. Everything seems to work fine until I have to run python setup/setupDB.py. I seem to have the .ini file set up properly, and the postgres database seems to have been set up correctly as well. But I am quite new to databases, so my ability to debug on my own is a little limited.

The error I get when I run python setup/setupDB.py is the following:

Traceback (most recent call last):
  File "setup/setupDB.py", line 125, in <module>
    setupDB()
  File "setup/setupDB.py", line 97, in setupDB
    dbConn.execute(sql, None, None)
  File "/home/vlucet/Documents/WILDLab/repos/AIDE/aerial_wildlife_detection/modules/Database/app.py", line 99, in execute
    with self._get_connection() as conn:
  File "/usr/lib/python3.8/contextlib.py", line 113, in __enter__
    return next(self.gen)
  File "/home/vlucet/Documents/WILDLab/repos/AIDE/aerial_wildlife_detection/modules/Database/app.py", line 89, in _get_connection
    conn = self.connectionPool.getconn()
  File "/home/vlucet/Documents/WILDLab/repos/AIDE/AIDEenv/lib/python3.8/site-packages/psycopg2/pool.py", line 169, in getconn
    return self._getconn(key)
  File "/home/vlucet/Documents/WILDLab/repos/AIDE/AIDEenv/lib/python3.8/site-packages/psycopg2/pool.py", line 93, in _getconn
    return self._connect(key)
  File "/home/vlucet/Documents/WILDLab/repos/AIDE/AIDEenv/lib/python3.8/site-packages/psycopg2/pool.py", line 63, in _connect
    conn = psycopg2.connect(*self._args, **self._kwargs)
  File "/home/vlucet/Documents/WILDLab/repos/AIDE/AIDEenv/lib/python3.8/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "aide_pop_os_34JT"
connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "aide_pop_os_34JT"

[bkellenb]

Hi! Thanks a lot for your message. I have been informed about problems with database authentication, mostly due to special characters like underscores and hyphens in account names (as in your case). These have mostly been resolved, but chances are some special environments still require optimization.

Could you please check the contents of Postgres' authentication file? You can do so with the following command:

version=12    # replace this with the current PostgreSQL version you have installed; check with "psql -V"
sudo cat /etc/postgresql/$version/main/pg_hba.conf

You should see this line in there, among others:

host    all             all             0.0.0.0/0               md5

(for the latest versions of Postgres "md5" might be replaced with "scram-sha-256")

If you cannot find it, you can add it manually:

version=12    # see above
echo "host    all            all            0.0.0.0/0               scram-sha-256" | sudo tee -a /etc/postgresql/$version/main/pg_hba.conf > /dev/null

# restart server
sudo service postgresql restart

Then try again.

If the line is there and you still get this error: can you execute the following statements from within the AIDE root directory?

export AIDE_CONFIG_PATH=config/settings.ini    # modify to point to your custom settings.ini file if needed
export AIDE_MODULES=FileServer
export PYTHONPATH=.

dbName=$(python util/configDef.py --section=Database --parameter=name)
dbUser=$(python util/configDef.py --section=Database --parameter=user)
dbPassword=$(python util/configDef.py --section=Database --parameter=password)
dbPort=$(python util/configDef.py --section=Database --parameter=port --fallback=5432)

sudo -u postgres psql -c "CREATE USER \"$dbUser\" WITH PASSWORD '$dbPassword';"
sudo -u postgres psql -c "CREATE DATABASE \"$dbName\" WITH OWNER \"$dbUser\" CONNECTION LIMIT -1;"
sudo -u postgres psql -d $dbName -c "GRANT CREATE, CONNECT ON DATABASE \"$dbName\" TO \"$dbUser\";"
sudo -u postgres psql -d $dbName -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"
sudo -u postgres psql -d $dbName -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"$dbUser\";"

# then try again
python setup/setupDB.py

Please let me know if that worked; otherwise we'd have to dig a little deeper.

[VLucet]

Hi, thanks for the reply!

I do see the line, but it is repeated 4 times. I edited the conf file so that it is only there once (must have rerun the same command multiple times I guess?) Just restarting the service didn't work. But your second set of instructions seems to have worked (the python script ran no problem). My understanding is that it manually created the proper user with the proper username and granted access database access to it? Just trying to understand. I will let you know if I run into more issues. But that did it, thanks!