can't create table on new db/schema/user

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

can't create table on new db/schema/user

psql-contact
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


Reply | Threaded
Open this post in threaded view
|

Re: can't create table on new db/schema/user

hubert depesz lubaczewski-2
On Fri, Aug 28, 2020 at 12:32:02PM +0200, [hidden email] wrote:

> 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?

Schema s_chris is in database "postgres", but you try to make the table
in "db_chris".

So you have to make the schema in db_chris database.

Best regards,

depesz



Reply | Threaded
Open this post in threaded view
|

Re: can't create table on new db/schema/user

psql-contact
On Fri, Aug 28, 2020 at 12:56:12PM +0200, hubert depesz lubaczewski wrote:
> Schema s_chris is in database "postgres", but you try to make the table
> in "db_chris".
>
> So you have to make the schema in db_chris database.

How would I do that?

I ran
        create schema     s_chris    authorization "chris";

How do I specify the database?

According to
        https://www.postgresql.org/docs/11/sql-createschema.html

there seems to be no such parameter.




Reply | Threaded
Open this post in threaded view
|

SOLVED: can't create table on new db/schema/user

pgsql-novice
On Fri, Aug 28, 2020 at 01:28:35PM +0200, [hidden email] wrote:
> On Fri, Aug 28, 2020 at 12:56:12PM +0200, hubert depesz lubaczewski wrote:
> > Schema s_chris is in database "postgres", but you try to make the table
> > in "db_chris".
> >
> > So you have to make the schema in db_chris database.
>
> How would I do that?

You have to actually _connnect_ to the database the schema is created for:

        \connect db_chris
        create schema     s_chris    authorization "chris";
        \connect postgres

Yeah, that's why it's called pgsql-novice@...


Reply | Threaded
Open this post in threaded view
|

Re: can't create table on new db/schema/user

Didier Gasser-Morlay-2
In reply to this post by psql-contact
You must be connected to the database in psql first

\c db_chris
Then create schema ....


Le ven. 28 août 2020 à 13:28, <[hidden email]> a écrit :
On Fri, Aug 28, 2020 at 12:56:12PM +0200, hubert depesz lubaczewski wrote:
> Schema s_chris is in database "postgres", but you try to make the table
> in "db_chris".
>
> So you have to make the schema in db_chris database.

How would I do that?

I ran
        create schema     s_chris    authorization "chris";

How do I specify the database?

According to
        https://www.postgresql.org/docs/11/sql-createschema.html

there seems to be no such parameter.




Reply | Threaded
Open this post in threaded view
|

[SOLVED] Re: can't create table on new db/schema/user

psql-contact
On Fri, Aug 28, 2020 at 01:47:56PM +0200, Didier Gasser-Morlay wrote:
> You must be connected to the database in psql first
>
> \c db_chris
> Then create schema ....

Thanx :-)