Date: Fri, 28 Aug 2020 12:32:02 +0200
From: psql-contact@citecs.de
To: pgsql-novice@lists.postgresql.org
Cc: psql-contact@citecs.de
Subject: can't create table on new db/schema/user
Message-ID: <20200828103202.GA6864@app.citecs>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Organization: private
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk

What I have:

	debian 10 
	postgresql 11.7-0+deb10u1

What I did:

	sudo -s
    mkdir           /var/lib/postgresql/11/ts_chris
    chown postgres: /var/lib/postgresql/11/ts_chris
    chmod 700       /var/lib/postgresql/11/ts_chris

    su - postgres -c psql
    create user "chris" with login password '***' connection limit -1;
    create tablespace "ts_chris" owner    chris location '/var/lib/postgresql/11/ts_chris';
    alter  tablespace ts_chris   owner to chris;
    create database   db_chris   owner =  automicuser template = template0 encoding = "UTF-8" tablespace = ts_chris lc_collate = "C" lc_ctype = "C" connection limit = -1 ;
    create schema     s_chris    authorization "chris";
    alter  role       chris      in database db_chris set search_path to s_chris;
    grant usage  on schema   s_chris  to chris;
    grant create on schema   s_chris  to chris;

What I tried:

	psql -h lxc05 db_chris chris

	db_chris=> create table t1 (i int);
	ERROR:  no schema has been selected to create in
	LINE 1: create table t1 (i int);
	                     ^
Debugging attempts:

	postgres=# \dn+
	                               List of schemas
	  Name   |    Owner    |     Access privileges      |      Description       
	---------+-------------+----------------------------+------------------------
	 public  | postgres    | postgres=UC/postgres      +| standard public schema
	         |             | =UC/postgres               | 
	 s_chris | chris       | chris=UC/chris             | 
	(3 rows)
	
	postgres=# \dg+
	                                           List of roles
	  Role name  |                         Attributes                         | Member of | Description 
	-------------+------------------------------------------------------------+-----------+-------------
	 chris       |                                                            | {}        | 
	 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 

	db_chris=>     create table t1 (i int);
	ERROR:  no schema has been selected to create in
	LINE 1: create table t1 (i int);
	                     ^
	db_chris=> \dn+
	                          List of schemas
	  Name  |  Owner   |  Access privileges   |      Description       
	--------+----------+----------------------+------------------------
	 public | postgres | postgres=UC/postgres+| standard public schema
	        |          | =UC/postgres         | 
	(1 row)
	
	db_chris=> \dg+
	                                           List of roles
	  Role name  |                         Attributes                         | Member of | Description 
	-------------+------------------------------------------------------------+-----------+-------------
	 chris       |                                                            | {}        | 
	 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 

	db_chris=> show search_path;
	 search_path 
	-------------
	 s_chris
	(1 row)


So, what did I miss?

Kind regards, Chris