Creating postgres schemas (schema_prefix)

Hi! I’m trying to figure out if I want to use @schema_prefix, and I have gathered that one is responsible to put their own CREATE SCHEMA commands inside an execute block in a migration if they do this. But I am wondering, what is the recommended way to create the postgres schema if you use a migration_default_prefix?

Have you run into an issue trying to execute a command to create a schema? I would try it first, because I am assuming that just executing a CREATE SCHEMA command will not conflict with any migration_default_prefix.

execute only takes a single command arg, so if migration_default_prefix is literally applying a default value for prefix in other migration function options, that doesn’t apply in this case, where there is no prefix option being defaulted.

Yep you are right, its only an issue once the schema is referenced, even if its default, so putting it in an execute block of a migration before any other usage of the schema works fine. Thanks.

I use schema prefixes extensively and have been very satisfied with the flexibility and modularity that can be formed. Ecto doesn’t (or didn’t, haven’t checked in a while) like single queries that involve multiple schemas. My solution was alter database X set search_path …

Any idea how would I set the schema to use on the migration_source table? Using "#{prefix}.schema_migrations" doesn’t work

To answer my own question, looking at the source it seems this is not possible, and that the migration_source table must be in public.

From the Little Ecto Cookbook:

query_args = ["SET search_path TO connection_prefix", []]

config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  database: "demo_dev",
  hostname: "localhost",
  pool_size: 10,
  after_connect: {Postgrex, :query!, query_args}

(The connection_prefix being your postgres schema)

However, unless the Postgres schema is part of the design (such as if each user has their own schema, or different parts of the app require different schemas), I personally think that the app shouldn’t care about the schema itself and it should be done at the database level.

I use schemas for all my projects and I do it when setting up the database, I always set up a schema that matches the DB, so I can lock it down:

CREATE SCHEMA IF NOT EXISTS myapp_dev;

REVOKE ALL ON DATABASE myapp_dev FROM public;
REVOKE ALL ON SCHEMA public FROM myapp_dev;
GRANT ALL ON SCHEMA myapp_dev TO myapp_dev;

ALTER ROLE myapp_dev SET search_path TO myapp_dev;

Once the role only has permission to use the schema and the search_path is set, connecting to the schema is invisible. Any connection to the db by the user will automatically use the schema and you don’t have to write any specific configuration in your app.

Yeah the prefix works, and setting the search path isn’t necessary in my case. But I don’t think you can set the schema on the migration_source: https://github.com/elixir-ecto/ecto_sql/blob/28d16c3265b7da103cbfecb3fa5026593923e1eb/lib/ecto/adapters/postgres.ex#L183 and https://github.com/elixir-ecto/ecto_sql/blob/master/lib/ecto/adapters/postgres.ex#L209