DEV Community

Vincent Tommi
Vincent Tommi

Posted on

Fixing PostgreSQL Permission Errors in Laravel PHPUnit Tests

#laravel #php #postgressql #testing

Introduction

When writing automated tests in Laravel, configuring the testing environment correctly is just as important as writing the tests themselves.

While setting up a feature test for a menu-category API in my Laravel POS project, I encountered a PostgreSQL permission error that prevented Laravel from creating the migrations table.

The error was:

SQLSTATE[42501]: Insufficient privilege: 7
ERROR: permission denied for schema public

SQL: create table "migrations" (...)
Enter fullscreen mode Exit fullscreen mode

In this article, I'll explain why this error occurs, how to configure a dedicated PostgreSQL test database, and how to use Laravel's RefreshDatabase trait to prepare the database for automated tests.

1. Understanding the Problem

My initial goal was to test whether the menu_categories table existed.

I created a feature test using Laravel's Artisan command:

php artisan make:test MenuCategoryApiTest
Enter fullscreen mode Exit fullscreen mode

However, when I ran the test, Laravel failed to create its migration tracking table.

The root cause was PostgreSQL permissions.

Even though my samu_user role had access to the samu_test database, the database was owned by postgres. The application user did not have the necessary permissions to create tables in the public schema.

Why does this happen?

PostgreSQL manages permissions at different levels:

  • Database privileges: Control access to the database.
  • Schema privileges: Control access to objects and operations within a schema.
  • Table privileges: Control operations such as reading, inserting, updating, and deleting rows.

Granting access to a database does not automatically guarantee permission to create tables inside its schemas.

2. Creating a Dedicated Test Database

Automated tests can create, modify, or roll back database structures. Therefore, I used a separate test database rather than running tests against my development database.

First, I listed the available PostgreSQL databases:

sudo -u postgres psql -l
Enter fullscreen mode Exit fullscreen mode

My database list included:

Name       | Owner
-----------+----------
postgres   | postgres
samu       | samu_user
samu_test  | postgres
Enter fullscreen mode Exit fullscreen mode

The samu_test database existed, but its owner was postgres.

To avoid accidentally modifying the main samu database, I kept samu_test dedicated to automated testing.

3. Fixing PostgreSQL Permissions

I changed the owner of the test database to samu_user:

sudo -u postgres psql -c \
"ALTER DATABASE samu_test OWNER TO samu_user;"
Enter fullscreen mode Exit fullscreen mode

Next, I changed ownership of the public schema:

sudo -u postgres psql -d samu_test -c \
"ALTER SCHEMA public OWNER TO samu_user;"
Enter fullscreen mode Exit fullscreen mode

These commands ensure that the application user can create the tables required by Laravel's migrations in the test database.

I verified the schema permissions with:

sudo -u postgres psql -d samu_test -c "\dn+ public"
Enter fullscreen mode Exit fullscreen mode

Important: These commands should be run only when you intend to grant the application role ownership of the dedicated test database and schema.

4. Configuring PHPUnit for PostgreSQL

Laravel reads its testing environment from PHPUnit configuration and, when applicable, a .env.testing file.

I configured the database-related environment variables in phpunit.xml:

<env name="APP_ENV" value="testing"/>
<env name="DB_CONNECTION" value="pgsql"/>
<env name="DB_HOST" value="127.0.0.1"/>
<env name="DB_PORT" value="5432"/>
<env name="DB_DATABASE" value="samu_test"/>
<env name="DB_USERNAME" value="samu_user"/>
Enter fullscreen mode Exit fullscreen mode

If PostgreSQL requires password authentication, the appropriate password must also be configured securely.

The important settings are:

Setting Purpose
APP_ENV Activates the testing environment
DB_CONNECTION Selects PostgreSQL
DB_HOST Specifies the database server
DB_PORT Specifies the PostgreSQL port
DB_DATABASE Selects the test database
DB_USERNAME Specifies the PostgreSQL role

Make sure the database name is consistent across your configuration files. In my case, the intended test database was samu_test.

Also check .env.testing for conflicting values, since Laravel may load that file when running tests.

5. Using Laravel's RefreshDatabase Trait

Laravel provides the RefreshDatabase trait to prepare the database for tests.

I added it to tests/Feature/MenuCategoryApiTest.php:

<?php

namespace Tests\Feature;

use Illuminate\Foundation\Testing\RefreshDatabase;
use Tests\TestCase;

class MenuCategoryApiTest extends TestCase
{
    use RefreshDatabase;

    public function test_menu_categories_table_exists(): void
    {
        $this->assertDatabaseHas('migrations', [
            'migration' =>
                '2026_09_18_034452_create_menu_categories_table',
        ]);
    }
}
Enter fullscreen mode Exit fullscreen mode

This example checks whether a particular migration is recorded in Laravel's migration history. It assumes that the migration has run and that its filename matches the value shown.

RefreshDatabase prepares the database using Laravel's migration and transaction mechanisms as appropriate.

It is useful when tests need a predictable database state, but the test database must be configured correctly before using it.

6. Running the Test

After updating the configuration and permissions, I ran:

php artisan config:clear
php artisan test --filter=MenuCategoryApiTest
Enter fullscreen mode Exit fullscreen mode

Initially, the test failed because PostgreSQL could not create the migrations table.

After correcting the database and schema ownership, the test passed:

PASS  Tests\Feature\MenuCategoryApiTest
✓ menu categories table exists

Tests: 1 passed
Time: 3.69s
Enter fullscreen mode Exit fullscreen mode

This confirmed that Laravel could connect to the configured PostgreSQL test database and that the assertion succeeded.

It did not yet prove that all menu-category API operations worked correctly. Those require additional feature tests.

7. Understanding PHP Deprecation Warnings

Even after the test passed, PHP displayed warnings from the nunomaduro/collision package:

PHP Deprecated:
Implicitly marking parameter $throwable as nullable
is deprecated
Enter fullscreen mode Exit fullscreen mode

These warnings were separate from the PostgreSQL permissions problem.

They indicate that the installed package uses parameter declarations that newer PHP versions deprecate.

To investigate compatibility, I checked the installed versions:

php -v
composer show nunomaduro/collision
composer show laravel/framework
composer show phpunit/phpunit
Enter fullscreen mode Exit fullscreen mode

Before upgrading dependencies, check which Collision version is compatible with the installed Laravel and PHPUnit versions.

A passing test can still produce deprecation warnings, so it is worth addressing these separately.

8. What Should Be Tested Next?

Checking whether a database table exists is a useful first step, but a complete API test suite should cover application behavior.

For a menu-category API, I would add the following tests:

Test Expected behavior
List categories Returns the expected response
Create category Saves valid data
Validate input Rejects invalid data
Show category Returns the requested category
Update category Persists changes
Delete category Removes the record
Prevent duplicates Enforces the intended uniqueness rules
Validate relationships Handles warehouse and kitchen-section references correctly

Migration tests can also verify the expected columns, foreign keys, and constraints.

Once the feature tests are in place, run the complete suite:

php artisan test
Enter fullscreen mode Exit fullscreen mode

Conclusion

This troubleshooting experience reinforced an important lesson: automated testing depends on both application code and a correctly configured database environment.

When using Laravel with PostgreSQL, remember to:

  1. Create a dedicated test database.
  2. Configure PHPUnit to use the correct database and user.
  3. Grant the necessary database and schema permissions.
  4. Use RefreshDatabase when appropriate.
  5. Separate database failures from PHP deprecation warnings.
  6. Test API behavior, not just the existence of database tables.

Fixing these fundamentals makes Laravel applications easier to test, debug, and maintain.

Top comments (0)