Search code examples
sqlpostgresqlprivileges

Can't access to schema in postgres

Lets say that I made this "simple" implementation of a DB in Postgres

postgres=# CREATE ROLE my_role;
           CREATE DATABASE my_db;
           GRANT ALL ON DATABASE my_db TO my_role;
           CREATE SCHEMA my_schm AUTHORIZATION my_role;

And then I want to make a table: 

postgres=#CREATE TABLE IF NOT EXIST my_db.my_schm.table(...);

And got the following error: cross-database references are not implemented: "my_db.my_schm.table"

After this, I tried to create the table connected to the db (i.e. \c my_db) and got the following error: 

schema "my_schm"does not exist

So, I don't understand the behavior from these errors, It is supposed that the role have all permissions to the db (and yes, I also tried using SET ROLE my_role;) but when I ask to show the schemas in my_db indeed my_schm doesn't exist, but in Postgres it does. Can someone explain to me please why is this happening? And also how can group the tables in my_schm?

Solution

  • Schemas exist only in one single database and are created in the current database: you have created your schema in postgres database not in mydb.

    You need to connect first to mydb database in order to create the schema in mydb database.