Clients don't seem to respect existing search path

[zachsa]

Using Postgres 12, I can create a user and set the search path of that user:

create user "${username}" with encrypted password '${password}';
create schema "${schema}" authorization "${username}";
alter user "${username}" set search_path = "${schema},public";
grant usage on schema public to "${username}";
grant select on all tables in schema public to "${username}";

Using a pg Client, I can see that the search path is set correctly:

SHOW search_path;

// returns "<schema>,public"

Running SELECT queries I see that the custom schema IS in the search path. However, when trying to create a table the search path can't be found:

CREATE TABLE test (
     id    integer PRIMARY KEY,
     name   varchar(40)
);

// returns "no schema has been selected to create in"

If I adjust the SQL to specify the search path first, then it works as expected:

SET search_path = "${schema},public";
CREATE TABLE test (
     id    integer PRIMARY KEY,
     name   varchar(40)
);

If I use Postgres via DBeaver with the same client credentials (and just login with the user, without updating the search path), then this works as expected.

I am creating a client like this:

const client = new Client({
    host,
    user,
    database,
    password,
    port,
  })
client.connect()
await client.query(...)
client.end()

[zachsa]

Actually, it looks like queries don't use the search_path either if the table is not on the first schema in the search path list.

[zachsa]

Apologies.

Changing the alter user statement fixed this

ALTER ROLE "<user name>" SET search_path = "<schema name>", public;