1

I have installed postgresql on ubuntu20.04, then I need to perform a series of actions in the script:

#!/bin/bash


main()
{
        local randpass=$(date +%s | sha256sum | base64 | head -c 32)

        #Add PostGres password file for modifying the database
        newlines=$(printf ":5432::$USER:$randpass\nlocalhost:5432:*:$USER:$randpass\n127.0.0.1:5432:*:$USER:$randpass\n" )
        echo -e "$newlines$(cat $HOME/.pgpass >/dev/null)" > $HOME/.pgpass
        chmod og-rw $HOME/.pgpass

        #Create Database User and Table
        echo "CREATE ROLE \"$USER\" WITH CREATEDB LOGIN NOSUPERUSER NOCREATEROLE PASSWORD '$randpass'" |  sudo -u postgres psql
        echo "ALTER ROLE \"$USER\" WITH PASSWORD '$randpass'" |  sudo -u postgres psql
        dropdb peasoup_$USER
        createdb peasoup_$USER

        #Setup the Database to store PEASOUP info
        source set_env_vars
        $PEASOUP_HOME/tools/db/pdb_setup.sh
}

main

I set USER to test, but after running the script, I get an error:

root@xxx:/xxx# ./xxxx_setup.sh 
CREATE ROLE
ALTER ROLE
dropdb: error: could not connect to database template1: FATAL:  role "root" does not exist
createdb: error: could not connect to database template1: FATAL:  role "root" does not exist
psql: error: FATAL:  database "peasoup_test" does not exist
psql: error: FATAL:  database "peasoup_test" does not exist

But there's a switch in the script, sudo -u postgres psql Why is the user reporting the error still root?

gazile
  • 105
  • 6
  • can you try `sudo pg_ctlcluster status 14 main`? or just `sudo service postgresql restart` – jian Jun 23 '22 at 06:45
  • Restart does work, I can implement the server link after following the method in this link: https://stackoverflow.com/questions/32439167/psql-could-not-connect-to-server-connection-refused-error-when-connecting-to/41161674#41161674. But a new error appears: dropdb: error: could not connect to database template1: FATAL: role "root" does not exist . I know root is my system account, which is different from my PostgresQL account, but I don't know what to do next. – gazile Jun 23 '22 at 06:57
  • then try `psql -h localhost postgres postgres --cluster 14/main` the first "postgres is the database, the second refer to the postgres user. – jian Jun 23 '22 at 07:02
  • But it seems like this is the way to link to a database? What if I need to create a database and a user? To be honest, I was running an existing script, which I updated and posted. If so, please help me see if I should change this script? – gazile Jun 23 '22 at 07:21
  • The issue is this `dropdb peasoup_$USER createdb peasoup_$USER` where `$USER` is `root`. You should not and need not run these as the `root` system user. You need to connect using a database user where the general default superuser is `postgres`. You do not need to create a database or user that is handled by the `initdb` process when the database cluster was created. The error shows that: `.could not connect to database template1 ...`. – Adrian Klaver Jun 23 '22 at 15:11
  • Spend some time here [Ubuntu Postgres](https://ubuntu.com/server/docs/databases-postgresql). – Adrian Klaver Jun 23 '22 at 15:12

0 Answers0