Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create function where_did_that_table_go(tablename text)returns text return
(select (pg_identify_object(a.classid, a.objid, a.objsubid)).schema
 from pg_get_object_address('table', array[$1],'{}')as a);

CREATE FUNCTION

create role user1;
grant all on schema public to user1;
set role user1;
reset search_path;

select current_schema()
     , current_schemas(true)
     , current_setting('search_path');
create table my_table1(i int);
select where_did_that_table_go('my_table1');

CREATE ROLE

GRANT

SET

RESET

current_schema
current_schemas
current_setting

public
{pg_catalog,public}
"$user", public

SELECT 1

CREATE TABLE

where_did_that_table_go

public

SELECT 1

select set_config('search_path',concat_ws(', ','"$user"',current_setting('search_path')), false);
show search_path;
select current_schema();--it knows "$user" doesn't exist (yet), returns public instead, even though it's the 2nd entry
create table my_table2(i int);
select where_did_that_table_go('my_table2');

set_config

"$user", "$user", public

SELECT 1

search_path

"$user", "$user", public

SHOW

current_schema

public

SELECT 1

CREATE TABLE

where_did_that_table_go

public

SELECT 1

set role postgres;
create schema anotherschema authorization user1;
revoke all on schema public from user1;
set role user1;
set search_path to "$user", public, anotherschema;

create table my_table3(i int);
select where_did_that_table_go('my_table3');--lack of permissions isn't enough to skip `public` and jump to `anotherschema`

SET

CREATE SCHEMA

REVOKE

SET

SET

ERROR:  permission denied for schema public
LINE 7: create table my_table3(i int);
                     ^

set role postgres;
create schema user1 authorization user1;
set role user1;
reset search_path;
show search_path;

create table my_table4(i int);
select where_did_that_table_go('my_table4');--finally using user's schema

SET

CREATE SCHEMA

SET

RESET

search_path

"$user", public

SHOW

CREATE TABLE

where_did_that_table_go

user1

SELECT 1

set search_path to 'this_schema_does_not_exist';
select current_schema();
create table my_table4(i int);

SET

current_schema

null

SELECT 1

ERROR:  no schema has been selected to create in
LINE 3: create table my_table4(i int);
                     ^