Laravel connection with postgres doesn't work properly

user_2800956 0
Viewed 195

I'm just setting up a new Laravel installation with a postgressql server using a role with NO superuser privileges (test). I'm using Manjaro to test and I've installed php 8 and enabled/installed php-pgsql with pgsql and pdo_pgsql extensions uncommented at /etc/php/php.ini

Laravel does seem to detect the table but it can't run migrations. These are the commands I'm using:

php artisan migrate:install (this one works)

php artisan migrate:status (this one doesn't work, it can't find the migrations table)

Also this is my .env (the relevant piece):

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=test
DB_USERNAME=test
DB_PASSWORD=

This is what I get from the test user within laravel:

test=> \l
 postgres  | postgres | UTF8         | es_CL.utf8 | es_CL.utf8 | 
 template0 | postgres | UTF8         | es_CL.utf8 | es_CL.utf8 | =c/postgres          +
           |          |              |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8         | es_CL.utf8 | es_CL.utf8 | =c/postgres          +
           |          |              |            |            | postgres=CTc/postgres
 test      | test     | UTF8         | es_CL.utf8 | es_CL.utf8 |
laravel postgresql
1 Answers

For command migrate:status, Laravel calls this method in internal class PostgresGrammar in namespace Illuminate\Database\Schema\Grammars.

/**
 * Compile the query to determine if a table exists.
 *
 * @return string
 */
public function compileTableExists()
{
    return "select * from information_schema.tables where table_schema = ? and table_name = ? and table_type = 'BASE TABLE'";
}

If you see it select records from information_schema which needs proper permissions to work. The user, in your case test, needs to be the owner of the schema, which is test in your case, to get any rows from information_schema.tables which are related to your schema.

run this command to change the owner of the schema:

alter schema `test` owner to `test`;

Or the user needs to be a member of a group that owns the schema.

Note: Usage grant is not sufficient. The user does not need to be a superuser. The user can own a table in a schema, but that is not sufficient either.

Refs:

user_164308840
Related