no password supplied in GH Action docker CI

[havok2063]

What action do you want to perform

I'm trying to run some postgres tests in a docker CI github action, and I am keeping "no password sent" error. It seems fairly similar to this issue, #294, but I'm using the latest package version, 4.1, and I think that bug is fixed.

What is the correct syntax to get the docker db to accept the password? This use to work on Travis with pytest-postgresql==2.3 but I'm trying to migrate to Github Actions and update to the latest code.

When I set a debug point in the action and ssh into the container, I can manually run the DatabaseJanitor code and it works. So maybe it's this specific use of it?

What are the results

psycopg.OperationalError: connection failed: fe_sendauth: no password supplied. Full traceback is at the bottom.

Here is my Github Action service setup:

    services:
      # Label used to access the service container
      postgres:
        # Docker Hub image
        image: postgres:14
        # Provide the password for postgres
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: test
          POSTGRES_PORT: 5432
        ports:
          - 5432:5432
        # Set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

and here is my pytest fixture.

@pytest.fixture(scope='module')
def database(request, postgresql_noproc):
    ''' Module fixture to initialize a real database or a test postgresql database '''
    if hasattr(request, 'param'):
        # yield a real database
        yield request.param
    else:
        # check if request is coming from a sqla db or peewee db
        issqla = 'sqladbs' in request.module.__name__ or 'sqlalchemy' in request.module.__name__
        # initialize the test database
        # uses https://github.com/ClearcodeHQ/pytest-postgresql
        janitor = DatabaseJanitor('postgres', 'localhost', 5432, 'test', '14',  password="test")
        janitor.init()
        db = sqla_prepdb() if issqla else pw_prepdb()
        yield db
        db = None
        janitor.drop()

I've also tried using DatabaseJanitor as a context manager, and I've tried using postgresql_noproc to set the config parameters

        janitor = DatabaseJanitor(postgresql_noproc.user, postgresql_noproc.host,
                                                  postgresql_noproc.port, 'test', postgresql_noproc.version, password="test")

What are the expected results

My tests run locally successfully. It only fails when run on CI.

Traceback

/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/psycopg/connection.py:572: OperationalError
_______________ ERROR at setup of TestFactory.test_a_transaction _______________

request = <SubRequest 'postgresql_noproc' for <Function test_db_connected>>

    @pytest.fixture(scope="session")
    def postgresql_noproc_fixture(request: FixtureRequest) -> Iterator[NoopExecutor]:
        """
        Noop Process fixture for PostgreSQL.
    
        :param request: fixture request object
        :returns: tcp executor-like object
        """
        config = get_config(request)
        pg_host = host or config["host"]
        pg_port = port or config["port"] or 5432
        pg_user = user or config["user"]
        pg_password = password or config["password"]
        pg_dbname = xdistify_dbname(dbname or config["dbname"])
        pg_options = options or config["options"]
        pg_load = load or config["load"]
    
        noop_exec = NoopExecutor(
            host=pg_host,
            port=pg_port,
            user=pg_user,
            ***
            dbname=pg_dbname,
            options=pg_options,
        )
        template_dbname = f"{noop_exec.dbname}_tmpl"
        with DatabaseJanitor(
            user=noop_exec.user,
            host=noop_exec.host,
            port=noop_exec.port,
            dbname=template_dbname,
>           version=noop_exec.version,
            ***
        ) as janitor:

/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/pytest_postgresql/factories/noprocess.py:91: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/pytest_postgresql/executor_noop.py:75: in version
    options=self.options,
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

cls = <class 'psycopg.Connection'>
conninfo = "dbname=postgres user=postgres host=127.0.0.1 port=5432 password='' options=''"
autocommit = False, row_factory = None, context = None
kwargs = {'dbname': 'postgres', 'host': '127.0.0.1', 'options': '', 'password': '', ...}
params = {'connect_timeout': None, 'dbname': 'postgres', 'host': '127.0.0.1', 'options': '', ...}

    @classmethod  # type: ignore[misc] # https://github.com/python/mypy/issues/11004
    def connect(
        cls,
        conninfo: str = "",
        *,
        autocommit: bool = False,
        row_factory: Optional[RowFactory[Row]] = None,
        context: Optional[AdaptContext] = None,
        **kwargs: Any,
    ) -> "Connection[Any]":
        """
        Connect to a database server and return a new `Connection` instance.
        """
        params = cls._get_connection_params(conninfo, **kwargs)
        conninfo = make_conninfo(**params)
    
        try:
            rv = cls._wait_conn(
                cls._connect_gen(conninfo, autocommit=autocommit),
                timeout=params["connect_timeout"],
            )
        except e.Error as ex:
>           raise ex.with_traceback(None)
E           psycopg.OperationalError: connection failed: fe_sendauth: no password supplied

[havok2063]

Although, looking at the traceback more closely, that code does not match the code in 4.1.1, https://github.com/ClearcodeHQ/pytest-postgresql/blob/v4.1.1/src/pytest_postgresql/factories/noprocess.py. Unless Github scrubbed that line from the code display? But I can confirm that 4.1.1 is being installed in the CI during the dependency installation step.

pytest-7.1.1 pytest-cov-3.0.0 pytest-factoryboy-2.1.0 pytest-postgresql-4.1.1

[fizyk]

the line seems to be scrubbed, how do you call pytest in github actions? What's the pytest configuration?

[havok2063]

Sorry for the long delay. I was away from my computer for the past week. It's the default pytest configuration and I call it straight with pytest, no options. Here is the actions file.

name: Test

on: [push, pull_request]

jobs:
  build:
    runs-on: ubuntu-latest

    # Service containers to run
    services:
      # Label used to access the service container
      postgres:
        # Docker Hub image
        image: postgres:14
        # Provide the password for postgres
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: test
          POSTGRES_PORT: 5432
        ports:
          - 5432:5432
        # Set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python 3.7
        uses: actions/setup-python@v2
        with:
          python-version: 3.7

      - name: Install dependencies
        run: |
          python -m pip install --upgrade pip
          pip install ".[dev]"

      - name: Test with pytest
        run: |
          pip install pytest
          pytest

[fizyk]

@havok2063 see this line:
https://github.com/ClearcodeHQ/pytest-postgresql/blob/main/.github/workflows/tests.yml#L82

It's the github action test where I do connect the tests to the running docker service with postgresql

[havok2063]

Ok, so I updated the pytest command in the github action to pytest --postgresql-host=localhost --postgresql-port 5432 --postgresql-password=test --postgresql-dbname=test and I no longer get the password error, but tests are still failing.

On the very first test that runs, I get a cursor/connection error with a warning like the postgres instance is not running.

[WARNING]: failed to connect to database 'test': connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

Subsequent tests all fail with psycopg.errors.DuplicateDatabase: database "test" already exists. Is there something I'm missing on how to implement the DatabaseJanitor correctly? All the tests successfully run locally.

My database fixture for all my tests is

@pytest.fixture(scope='module')
def database(request, postgresql_noproc):
    ''' Module fixture to initialize a real database or a test postgresql database '''
    if hasattr(request, 'param'):
        # yield a real database
        yield request.param
    else:
        # check if request is coming from a sqla db or peewee db
        issqla = 'sqladbs' in request.module.__name__ or 'sqlalchemy' in request.module.__name__
        # initialize the test database
        janitor = DatabaseJanitor(postgresql_noproc.user, postgresql_noproc.host,
                                  postgresql_noproc.port, 'test', postgresql_noproc.version, password="test")
        janitor.init()
        db = sqla_prepdb() if issqla else pw_prepdb()
        yield db
        db = None
        janitor.drop()

[fizyk]

@havok2063 this fixture is supposed to create a test database for all tests in a module?

Do you have tracebacks to the first error?

[havok2063]

@fizyk Yeah that's the idea. The first test that runs is in a module test_connection.py in my top level tests directory that tests that we have successfully connected to the test database. And as mentioned before this used to work on Travis-CI with an older version of this package, 2.3, and I'm trying to update to the latest version and migrate to GH Actions. Looking at the release logs, it doesn't seem like anything drastic has changed between version 2.3, and 4.1.

def assert_testdb(database):
    assert database.dbname == 'test'
    assert database.connected is True
    assert database.profile == 'local'
    assert database.dbversion is None        


class TestGenericDatabaseConnection(object):

    def test_db_connected(self, database):
        ''' test connection to testdb '''
        assert_testdb(database)

Here is that traceback

==================================== ERRORS ====================================
______ ERROR at setup of TestGenericDatabaseConnection.test_db_connected _______
request = <SubRequest 'database' for <Function test_db_connected>>
postgresql_noproc = <pytest_postgresql.executor_noop.NoopExecutor object at 0x7f225ccc8450>
    @pytest.fixture(scope='module')
    def database(request, postgresql_noproc):
        ''' Module fixture to initialize a real database or a test postgresql database '''
        if hasattr(request, 'param'):
            # yield a real database
            yield request.param
        else:
            # check if request is coming from a sqla db or peewee db
            issqla = 'sqladbs' in request.module.__name__ or 'sqlalchemy' in request.module.__name__
            # initialize the test database
            # uses https://github.com/ClearcodeHQ/pytest-postgresql
            #janitor = DatabaseJanitor('postgres', 'localhost', 5432, 'test', '14', ***)
            janitor = DatabaseJanitor(postgresql_noproc.user, postgresql_noproc.host,
                                      postgresql_noproc.port, 'test', postgresql_noproc.version, ***)
            janitor.init()
>           db = sqla_prepdb() if issqla else pw_prepdb()
tests/conftest.py:95: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
tests/pwdbs/__init__.py:39: in prepare_testdb
    database.create_tables(models)
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/peewee.py:3369: in create_tables
    model.create_table(**options)
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/peewee.py:6754: in create_table
    and cls.table_exists():
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/peewee.py:6744: in table_exists
    return cls._schema.database.table_exists(M.table.__name__, M.schema)
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/peewee.py:3347: in table_exists
    return table_name in self.get_tables(schema=schema)
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/peewee.py:3856: in get_tables
    cursor = self.execute_sql(query, (schema or 'public',))
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/peewee.py:3175: in execute_sql
    cursor = self.cursor(commit)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
self = <TmpDatabaseConnection (dbname='test', profile='local', connected=False)>
commit = True
    def cursor(self, commit=None):
        if self.is_closed():
            if self.autoconnect:
                self.connect()
            else:
                raise InterfaceError('Error, database connection not opened.')
>       return self._state.conn.cursor()
E       AttributeError: 'NoneType' object has no attribute 'cursor'
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/peewee.py:3162: AttributeError
---------------------------- Captured stderr setup -----------------------------
[WARNING]: failed to connect to database 'test': connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
[WARNING]: failed to connect to database 'test': connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
------------------------------ Captured log setup ------------------------------
WARNING  sdssdb:connection.py:397 failed to connect to database 'test': connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
WARNING  sdssdb:connection.py:397 failed to connect to database 'test': connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

[fizyk]

Still... two years....

Could you migrate and update separately? I mean do one first, and once working, take care of the other? (ie gh actions first, update later or the other way around?

But looking at traceback, the issue.... well... Janitor did connect to the database and did what it had to do, now it seems that i's peewee that can not connect 🤔

[havok2063]

I can't go back to test on Travis since they removed their free tier, so I'm trying to test the package versions separately. I went back to 2.6.1 but couldn't test it. I think because of #294. Same with version 3.0.0. I get the fe_sendauth: no password supplied error. I'm now trying version 3.0.2 which has that fix in place. I'm getting the same error now as before.

I don't think it's specifically a peewee issue. I tried disabling peewee and using sqlalchemy instead. And restricting the tests run to 3 generic tests, just to see if I can get a valid database connection. And I get a similar error with sqlalchemy about not being able to connect to the database test. Full traceback below. This limited test runs OK locally as well.

These GH Action tests were run with pytest --postgresql-host=localhost --postgresql-port 5432 --postgresql-password=test --postgresql-dbname=test tests/test_connection.py

Full traceback

tests/test_connection.py EEE                                             [100%]

==================================== ERRORS ====================================
______ ERROR at setup of TestGenericDatabaseConnection.test_db_connected _______

request = <SubRequest 'database' for <Function test_db_connected>>
postgresql_noproc = <pytest_postgresql.executor_noop.NoopExecutor object at 0x7fcbf795f490>

    @pytest.fixture(scope='module')
    def database(request, postgresql_noproc):
        ''' Module fixture to initialize a real database or a test postgresql database '''
        if hasattr(request, 'param'):
            # yield a real database
            yield request.param
        else:
            # check if request is coming from a sqla db or peewee db
            issqla = 'sqladbs' in request.module.__name__ or 'sqlalchemy' in request.module.__name__
            # initialize the test database
            # uses https://github.com/ClearcodeHQ/pytest-postgresql
            janitor = DatabaseJanitor(postgresql_noproc.user, postgresql_noproc.host,
                                      postgresql_noproc.port, 'test', postgresql_noproc.version, ***)
    
            janitor.init()
>           db = sqla_prepdb() 

tests/conftest.py:96: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
tests/sqladbs/__init__.py:39: in prepare_testdb
    database.base.metadata.create_all(database.engine)
/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/sqlalchemy/sql/schema.py:4888: in create_all
    bind = _bind_or_error(self)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

schemaitem = MetaData()
msg = 'MetaData object is not bound to an Engine or Connection.  Execution can not proceed without a database to execute against.'

    def _bind_or_error(schemaitem, msg=None):
    
        util.warn_deprecated_20(
            "The ``bind`` argument for schema methods that invoke SQL "
            "against an engine or connection will be required in SQLAlchemy 2.0."
        )
        bind = schemaitem.bind
        if not bind:
            name = schemaitem.__class__.__name__
            label = getattr(
                schemaitem, "fullname", getattr(schemaitem, "name", None)
            )
            if label:
                item = "%s object %r" % (name, label)
            else:
                item = "%s object" % name
            if msg is None:
                msg = (
                    "%s is not bound to an Engine or Connection.  "
                    "Execution can not proceed without a database to execute "
                    "against." % item
                )
>           raise exc.UnboundExecutionError(msg)
E           sqlalchemy.exc.UnboundExecutionError: MetaData object is not bound to an Engine or Connection.  Execution can not proceed without a database to execute against.

/opt/hostedtoolcache/Python/3.7.12/x64/lib/python3.7/site-packages/sqlalchemy/sql/base.py:1658: UnboundExecutionError
---------------------------- Captured stderr setup -----------------------------
[WARNING]: Failed to connect to database test
------------------------------ Captured log setup ------------------------------
WARNING  sdssdb:connection.py:585 Failed to connect to database test

[fizyk]

@havok2063 sorry for the sporadic responses, do you have that as open-source? Could I take a look?

[havok2063]

@fizyk Yeah this particular repo in our org is. It's https://github.com/sdss/sdssdb/tree/oldtests. The branch I'm trying to get this working in is oldtests. This branch I was incrementing the versions of pytest-postgresql, currently pinned to 3.x. The branch ghtests has version 4.1. You should be able to just run pip install -e ".[dev]" to get all the dependencies and run the tests.

[benoitLebreton-perso]

Hello,
it would be very cool to leverage this package in CI pipeline. Has anybody managed to do it with gitlab-ci ?
I have unsuccesfully tried with an existing postgres service : https://repository.prace-ri.eu/git/help/ci/services/postgres.md
And with docker:dind : https://stackoverflow.com/questions/71459760/pghost-for-gitlab-pipeline-with-dockerdind-for-postgres-created-by-docker-compo

maybe it doesn't work with pytest-postgresql because the postgres service is not in localhost ?

Maybe it is easy with docker-compose I haven't tried yet

I will also try with inspiration from your github action https://github.com/ClearcodeHQ/pytest-postgresql/blob/main/.github/workflows/tests.yml#L82