Laravel 9, Postgres can't change search_path to valid schema

[xorock]

• Laravel Version: 9.1.0
• PHP Version: 8.1.2
• Database Driver & Version: PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Description:

I am trying to change the search_path to the current schema and unfortunately neither queries nor migrations work. Laravel fails to connect to the database on the defined schema.

Steps To Reproduce:

• create new laravel 9 project
• change driver to pgsql
• create test-db schema
• change database.php, postgres setting to 'search_path' => 'test-db',
• try to run migration or select query from database

dd(DB::getPdo()->query('select current_schemas(true)')->fetchAll());

^ array:1 [[▼]()
  0 => array:2 [[▼]()
    "current_schemas" => "{pg_catalog}"
    0 => "{pg_catalog}"
  ]
]

php artisan migrate

   Illuminate\Database\QueryException

  SQLSTATE[3F000]: Invalid schema name: 7 ERROR:  no schema has been selected to create in at character 14 (SQL: create table "migrations" ("id" serial primary key not null, "migration" varchar(255) not null, "batch" integer not null))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +37 vendor frames
  38  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

[derekmd]

Character - in test-db is the issue.

framework/src/Illuminate/Database/Connectors/PostgresConnector.php

Lines 127 to 133 in 171f65d

	protected function parseSearchPath($searchPath)
	{
	if (is_string($searchPath)) {
	preg_match_all('/[a-zA-z0-9$]{1,}/i', $searchPath, $matches);
	$searchPath = $matches[0];
	}

It looks PostgresConnector's parsing of config('database.connections.pgsql.search_path') doesn't cover every accepted Postgres symbol character.

preg_match_all('/[a-zA-z0-9$]{1,}/i', 'test-db', $matches);
$matches;
// [
//     [
//         "test",
//         "db",
//     ],
// ]

which should be [["test-db"]].

---

Funnily enough, 'test_db' is (correctly) parsed only because the regex mistakenly (?) contains range [A-z] instead of [A-Z]:

preg_match_all('/[a-zA-z0-9$]{1,}/i', 'test_db', $matches);
$matches;
// [
//     [
//         "test_db",
//     ],
// ]

Regex '/[a-z0-9$_\-]{1,}/i' might fix it but I have to find an exact definition in the Postgres docs. e.g., whether extended ASCII or multibyte characters are accepted too.

---

Edit: Yup, ¡tëst-db! is an acceptable schema name too.

According the originating PR's examples, a naive negating regex might be the easiest solution for parsing the config string:

preg_match_all('/[^\s,"\']+/', $searchPath, $matches);

[xorock]

This is also funny because, according to this code, when I pass search_path as array it will work - which looks like a nice workaround temporarily.

But.... also looking at method below

framework/src/Illuminate/Database/Connectors/PostgresConnector.php

Line 150 in 171f65d

protected function quoteSearchPath($searchPath)

It looks potentially buggy. It uses count($searchPath) and according to phpdoc @param array|string $searchPath. So, if string is passed it will throw Fatal error: Uncaught TypeError: count(): Argument #1 ($var) must be of type Countable|array, string given. I see that configureSearchPath first uses parseSearchPath, but if so then $searchPath should be typehinted to array.

[driesvints]

@derekmd can you maybe PR that?