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.
2 Likes
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
1 Like
Hello! 
I’m resurrecting this thead because I find it kind of a bummer (actually: a bummer 
) that Ecto doesn’t allow the schema_migrations table to be placed in any schema other than public. There seems to be no way to do it yet.
In my use case, I’m building a service that shares a Postgres DB with other services. All my service’s tables live in their own private schema, but the service needs also to access the public schema (it doesn’t create any new tables there though).
I can’t place schema_migrations in the public schema because it will conflict with the other services that use their own Ecto migrations. I want schema_migrations to be in the service’s private schema.
The only workaround I could find is to enable an after_connect option in my repo configuration that sets the default schema for each new DB connection. it works, but it’s kind of a hack and ideally I would like to leave the default schema and search path unchanged.
My opinion is that Ecto needs a configuration option to set the schema for the schema_migrations table.
What do you guys think?
