Can't access right schema while running migrations in production

[GabrielaGuedes]

Hi! I've created a new sequent migration and runned it in development environment without issues. However, after deploying the code and trying to run bundle exec rake sequent:migrate:online in production, it seems like sequent is accessing the public schema of my database instead of the view_schema one, throwing me the following error:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "sequent_versions" does not exist (ActiveRecord::StatementInvalid)
LINE 1: SELECT "sequent_versions".* FROM "sequent_versions" ORDER BY...

The eager_loading is already set as true.

In the database.yml, the only difference between these two environments is that on development, we have the database connection set with the fields database, username, password, and host, and on production, we have set the url. Since we already had this problem in the past, when we had our fields set individually instead of grouped on the url, we tried different variations of config for the database (as listed below), but none of them was successful.

What was tried

1. Setting just the url field on database.yml

Like mentioned above

2. Setting database, username, password, and host instead of using the url

Then, we run bundle exec rake sequent:migrate:online. However, it throws the same error as item 1 (missing sequent_versions)

3. Setting both the url and database + username + password fields on database.yml

After running bundle exec rake sequent:migrate:online, it throws the same error as item 1 (missing sequent_versions)

4. Running the migration inside the rails console with loaded db_config

To do so, a code like this was run:

ENV['SEQUENT_ENV'] = ENV['RAILS_ENV']
db_config = Sequent::Support::Database.read_config(ENV['SEQUENT_ENV'])
view_schema = Sequent::Migrations::ViewSchema.new(db_config: db_config)

view_schema.send(:in_view_schema) do
  pp view_schema.exec_sql("SELECT current_schema();").to_a
end # => [{"current_schema"=>"public"}]

view_schema.migrate_online

But, when migrating, it throws an error saying that event_records does not exist:

I, [2024-05-15T14:05:16.418775 #2136]  INFO -- : group_exponent: 3
I, [2024-05-15T14:05:16.418926 #2136]  INFO -- : Start replaying events
I, [2024-05-15T14:05:16.420386 #2136]  INFO -- : Number of groups 4096
E, [2024-05-15T14:05:16.543767 #5083] ERROR -- : Replaying failed for ids: ^002 - 002
E, [2024-05-15T14:05:16.543837 #5083] ERROR -- : +++++++++++++++ ERROR +++++++++++++++
E, [2024-05-15T14:05:16.543922 #5083] ERROR -- : PG::UndefinedTable: ERROR:  relation "event_records" does not exist
LINE 10:  WHERE a.attrelid = '"event_records"'::regclass

5. Running the migration inside the rails console with manual set db_config

Very similar to the previous approach, but with the difference that now we are manually defining what needs to be the db_config

db_config = {"primary"=>
{"adapter"=>"postgresql",
 "encoding"=>"unicode",
 "pool"=>15,
 "schema_search_path"=>"public,sequent_schema, view_schema",
 "variables"=>{"statement_timeout"=>"500s", "lock_timeout"=>"100s"},
 "database"=>"<our_db_name>",
 "username"=>"<our_db_user>",
 "password"=>"<our_db_password>", 
 "host"=>"<our_db_host>"},
"primary_replica"=>
{"replica"=>true,
 "adapter"=>"postgresql",
 "encoding"=>"unicode",
 "pool"=>15,
 "schema_search_path"=>"public,sequent_schema, view_schema",
 "variables"=>{"statement_timeout"=>"500s", "lock_timeout"=>"100s"},
 "database"=>"<our_replica_name>",
 "username"=>"<our_replica_user>",
 "password"=>"<our_replica_password>", 
 "host"=>"<our_replica_host>"}}

This keeps accessing the public schema and throwing the same error as item 4.

6. Running the migration inside the rails console with manual set db_config and without the public schema

The db_config is almost the same, being the only difference on the schema_search_path where we've put only sequent_schema, view_schema. However, this throws the same error as item 4.

Versions

Rails 7.1.3.2 Sequent 7.0

Files

database.yml

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  schema_search_path: "public, sequent_schema, view_schema"
  variables:
    statement_timeout: "500s"
    lock_timeout: "100s"

development:
  primary:
    <<: *default
    database: <our_db>
    username: <our_user>
    password: <our_password>
    host: <our_host>
    gssencmode: disable
  primary_replica:
    replica: true
    <<: *default
    database: <our_db>
    username: <our_user>
    password: <our_password>
    host: <our_host>
    gssencmode: disable

test:
  primary:
    <<: *default
    database: <our_db>
    username: <our_user>
    password: <our_password>
    host: <our_host>
    gssencmode: disable
  primary_replica:
    replica: true
    <<: *default
    database: <our_db>
    username: <our_user>
    password: <our_password>
    host: <our_host>
    gssencmode: disable

production:
  primary:
    <<: *default
    url: <our_url>
  primary_replica:
    replica: true
    <<: *default
    url: <our_url>

References

• https://github.com/zilverline/sequent/issues/283
• https://github.com/zilverline/sequent/issues/205

[lvonk]

Hi thanks for reaching out. First I want to verify a few things:

• Are you using the released version 7.0.0?
• Which database version are you running?
• How does your Rakefile look like? (check https://www.sequent.io/docs/rails-sequent.html bullet 5)
• Can you share your sequent.rb initializer?
• Next to the sequent migrations, do you have 'normal' Rails migrations?
• When running bundle exec rake sequent:migrate:online does it run alone in a single command?
• Does the table sequent_versions exist in the view_schema?
• If your run this in your rails console:

ENV['SEQUENT_ENV'] = ENV['RAILS_ENV']
db_config = Sequent::Support::Database.read_config(ENV['SEQUENT_ENV'])
view_schema = Sequent::Migrations::ViewSchema.new(db_config: db_config)

pp ActiveRecord::Base.connection.schema_search_path
view_schema.send(:in_view_schema) do
  ActiveRecord::Base.connection.schema_search_path
end

What is the output (if you can, can you check both development and production)?

[lvonk]

One other thing that can be happening is that your user does not have access to the other schema's in production. For instance if I create a user in postgres without access to schema like view_schema but set the search_path to view_schema,public then executing SELECT current_schema() will just return public without warnings.

User without access to schema foos

vonk=> set search_path to foos,public;
SET
vonk=> SELECT current_schema();
 current_schema
----------------
 public

User with access

vonk=# set search_path to foos,public;
SET
vonk=# SELECT current_schema();
 current_schema
----------------
 foos

Perhaps this helps 🤞🏼

[GabrielaGuedes]

Are you using the released version 7.0.0?

Yes

Which database version are you running?

14.10

How does your Rakefile look like? (check https://www.sequent.io/docs/rails-sequent.html bullet 5)

ENV["SEQUENT_ENV"] = ENV["RAILS_ENV"] ||= "development"

require "sequent/rake/migration_tasks"

require_relative "../../config/initializers/sequent"
Sequent::Rake::MigrationTasks.new.register_tasks!

task "sequent:migrate:init" => ["sf:sequent:db_connect"]

desc "Connect sequent to the database"
task "sf:sequent:db_connect" => :environment do
  Sequent::Support::Database.connect!(ENV.fetch("SEQUENT_ENV", nil))
end

desc "Setup Sequent to be used in CI"
task "sf:sequent:setup" => [:environment, "sf:sequent:db_connect"] do
  unless %w[development test].include?(ENV.fetch("SEQUENT_ENV", nil))
    raise ArgumentError, "Can only be run on Dev, Test, CI envs"
  end

  begin
    Rake::Task["sequent:db:create_event_store"].execute
  rescue StandardError
    Sequent::Support::Database.drop_schema!(Sequent.configuration.event_store_schema_name)
    Sequent::Support::Database.drop_schema!(Sequent.configuration.view_schema_name)

    Rake::Task["sequent:db:create_event_store"].execute
  end

  Rake::Task["sequent:db:create_view_schema"].execute

  Rake::Task["sequent:migrate:online"].execute
  Rake::Task["sequent:migrate:offline"].execute

  `bin/rails db:schema:dump`
end

Can you share your sequent.rb initializer?

require_relative "../../db/sequent_migrations"

Rails.application.config.to_prepare do
  Sequent.configure do |config|
    config.migrations_class_name = "SequentMigrations"

    config.command_handlers = [
      # ... some of ours command handlers
    ].map(&:new)

    config.event_handlers = [
      # ... our projects and workflows (including the one that manages the new table)
    ].map(&:new)

    config.database_config_directory = "config"
    config.enable_multiple_database_support = true

    config.migration_sql_files_directory = "db/sequent"
  end
end

Next to the sequent migrations, do you have 'normal' Rails migrations?

No

When running bundle exec rake sequent:migrate:online does it run alone in a single command?

Not sure if I got the question, but this runs alone only with this command on my development environment for example

Does the table sequent_versions exist in the view_schema?

Yes

If your run this in your rails console, what is the output (if you can, can you check both development and production)?

On development:

#...
pp ActiveRecord::Base.connection.schema_search_path
# =>  "public, sequent_schema, view_schema"
view_schema.send(:in_view_schema) do
  ActiveRecord::Base.connection.schema_search_path
end
# => "view_schema"

On production:

pp ActiveRecord::Base.connection.schema_search_path
# =>  "public, sequent_schema, view_schema"
view_schema.send(:in_view_schema) do
  ActiveRecord::Base.connection.schema_search_path
end
# => "\"$user\", public" 

Is this the expected result on production? I've manually granted permissions to our db user to the view schema using ActiveRecord::Base.connection.execute("GRANT USAGE ON SCHEMA view_schema TO postgres;") (to ensure the user has access to the schema) and even after it, the result on production keeps the same "\"$user\", public"

[lvonk]

Your sequent config looks good.

In your Rakefile can you change:

task "sequent:migrate:init" => ["sf:sequent:db_connect"]

desc "Connect sequent to the database"
task "sf:sequent:db_connect" => :environment do
  Sequent::Support::Database.connect!(ENV.fetch("SEQUENT_ENV", nil))
end

To and try running the migration again?

Rake::Task['sequent:init'].enhance([:environment])
task 'sequent:migrate:init' => [:sf:sequent:db_connect]

desc "Connect sequent to the database"
task "sf:sequent:db_connect" do
  Sequent::Support::Database.connect!(ENV.fetch("SEQUENT_ENV", nil))
end

[lvonk]

Is this the expected result on production?

No, when I execute the same commands in our production env it returns the correct results.

I've manually granted permissions to our db user to the view schema using ActiveRecord::Base.connection.execute("GRANT USAGE ON SCHEMA view_schema TO postgres;")

Can you connect to the database with psql with the user specified in database.yml and verify it has access to the schema and table (select count(*) from view_schema.sequent_versions) just to rule out it is a privileges thing?

[GabrielaGuedes]

Can you connect to the database with psql with the user specified in database.yml and verify it has access to the schema and table (select count(*) from view_schema.sequent_versions) just to rule out it is a privileges thing?

Sure!! Does connecting through active record is enough? It uses the user specified on the yml, which is the same we've tried to run the migrations with It does have access to the schema

[lvonk]

You mentioned you also tried running the migration without specifying the database url but with all attributes specified individually like in development. Does executing this:

pp ActiveRecord::Base.connection.schema_search_path
# =>  "public, sequent_schema, view_schema"
view_schema.send(:in_view_schema) do
  ActiveRecord::Base.connection.schema_search_path
end

Then also yield to the same result?

[GabrielaGuedes]

hey @lvonk thanks so much for the quick responses!! I have good news, we've managed to run the migration 🎉

But answering your questions and explained what happened just to have it documented here and help future visitors: We tried updating our Rakefile with the snippet below as you suggested:

Rake::Task['sequent:init'].enhance([:environment])
task 'sequent:migrate:init' => [:sf:sequent:db_connect]

desc "Connect sequent to the database"
task "sf:sequent:db_connect" do
  Sequent::Support::Database.connect!(ENV.fetch("SEQUENT_ENV", nil))
end

But we kept not being able to access the view_schema.

And when executing:

pp ActiveRecord::Base.connection.schema_search_path
# =>  "public, sequent_schema, view_schema"
view_schema.send(:in_view_schema) do
  ActiveRecord::Base.connection.schema_search_path
end

on the production environment, it also returned the same result (=> "\"$user\", public").

But, until then, we were running all these things in a Heroku machine, which contains a secondary machine that points to our app. Our main instance is deployed on Fly.io. So we decided to try running the migration directly on Fly.io and it worked!! To be honest, we don't know yet exactly why it worked on Fly, but what we know is that the environment there is more similar to the one we have on our development end, since it uses the same Dockerfile. If you want, here is our Dockerfile:

# https://github.com/evilmartians/fullstaq-ruby-docker
FROM quay.io/evl.ms/fullstaq-ruby:3.3.0-jemalloc-bullseye-slim as base

# General configs
WORKDIR /home/web
SHELL ["/bin/bash", "-c"]
ENV EDITOR=vim
# Can read Sidekiq Pro config from `.bundle/config`.
ENV BUNDLE_APP_CONFIG=/home/web/.bundle
# Save gems to a specifc folder, that we can create a Volume for.
ENV BUNDLE_PATH=/bundle

COPY ./.bundle ./.bundle
RUN if ! grep -q "CONTRIBSYS" .bundle/config; then echo "Sidekiq Pro '.bundle/config' missing" && exit 1; fi

RUN apt-get update && \
  apt-get install --no-install-recommends --yes \
  build-essential \
  git \
  vim \
  # Image processing:
  # libvips \
  # Ruby openssl extension dependency:
  libssl-dev \
  # Ruby zlib extension dependency:
  # zlib1g-dev \
  # Gem `pg` dependency:
  libpq-dev \
  # Gem `psych` dependency:
  libyaml-dev

# 🐳 END OF `base` STAGE

FROM base as ci

# On CI we avoid `.bundle` getting overridden by the docker-compose volume that brings in the latest code
ENV BUNDLE_APP_CONFIG=/bundle/.bundle
RUN mkdir /bundle && mv /home/web/.bundle /bundle/.bundle

COPY ./Gemfile* ./
RUN bundle config set --global without production
RUN bundle install --jobs 4 && rm -rf $GEM_HOME/cache

# 🐳 END OF `ci` STAGE

FROM base as production
COPY ./Gemfile* ./
RUN bundle config set --global without development:test
RUN bundle install --jobs 4 && rm -rf $GEM_HOME/cache

COPY . .

# 🐳 END OF `production` STAGE

I'm closing this issue since it seems like the problem was on our end, but thanks again for all your support 🙇‍♀️