[SOLVED] GitHubCI+PostgreSQL: after DB creation script is run, somehow the DB user is still unable to log-in

Issue

I need to create a PostgreSQL DB in a GitHubActions workflow so I came up with the following yml fragment:

jobs:
  build:
    name: DB
    runs-on: ubuntu-20.04

    steps:
      - uses: actions/checkout@v2

      - name: Create DB
        run: |
          sudo apt update --yes
          sudo apt install postgresql --yes
          sudo service postgresql start
          sudo -u postgres createdb somedb
          sudo -u postgres psql -d somedb -a -q -f db/createDBCommands.sql
          sudo -u postgres psql -c "CREATE USER someDbUser WITH PASSWORD 'localDevPassword';"
          sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE somedb to someDbUser;"

The above works ok (no error is thrown). However, when I want to run a test within the same CI job that accesses the database, I get the following error:

28P01: password authentication failed for user "someDbUser"

The connection string used in the code is: "Server=localhost;Port=5432;Database=somedb;User Id=someDbUser;Password=localDevPassword".

I've tried adding double-quotes to the password in the connection string (escaping them properly but it still doesn't work).

Is is that I have to grant the user access from within 127.0.0.1 host? If so, how to do it with a command instead of modifying config files? (Given that I'm using CI.)

Solution

If you don't need necessarily need a new user you can alter the superuser password and connect using that user instead.

- name: Create DB
    run: |
      sudo apt update --yes
      sudo apt install postgresql --yes
      sudo service postgresql start
      sudo -u postgres createdb somedb
      sudo -u postgres psql -d somedb -a -q -f db/createDBCommands.sql
      sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'localDevPassword';"

You can connect to it using the connection string below:

"Server=localhost;Port=5432;Database=somedb;User Id=postgres;Password=localDevPassword"

Answered By - Afshin Arani

Answer Checked By - Dawn Plyler (WPSolving Volunteer)