running two queries on the same migration fails

[lcjury]

I have a migration with the following code:

def up
    execute("UPDATE #{Apartment::Tenant.current}.job SET x_date = start_date")
    execute("UPDATE #{Apartment::Tenant.current}.job SET end_date = start_date")
end

and it fails with the following error:

PG::InsufficientPrivilege: ERROR: permission denied for schema first_client_schema LINE 1: SELECT 1 FROM ONLY "first_client_schema"."employees" x ... QUERY: SELECT 1 FROM ONLY "first_client_schema"."employees" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

Notice that, job and employee (from the error) are not the same table!

if I change the migration to have only one executestatement it works without problems, I have issues when I have more than one.

System configuration

• Database: Postgres 9.6

• Apartment version: 2.2.0

• Apartment config (in config/initializers/apartment.rb or so):

  Apartment.configure do |config|
  config.excluded_models = %w{Tenant}
  config.tenant_names = lambda { Tenant.pluck :tenant_name }
  config.use_schemas = true
  config.persistent_schemas = %w{ shared_extensions }
  config.prepend_environment = !Rails.env.production?
  end
  Rails.application.config.middleware.use Apartment::Elevators::Subdomain

  • use_schemas: true by default

• Rails (or ActiveRecord) version: rails version 5.0.7.2 (activerecord-5.0.7.2)

• Ruby version: 2.5.3p105 (2018-10-18 revision 65156) [x86_64-darwin18]

[lcjury]

Additional information:

I'm able to reproduce this through the rails cli running a migration after switching to another tenant.

Apartment::Tenant.switch!("some_schema")
ActiveRecord::Migrator.run(:up, ActiveRecord::Migrator.migrations_paths, 20190108141736)

That code fails with the same error shown before. But the following code work correctly:

ActiveRecord::Migrator.run(:up, ActiveRecord::Migrator.migrations_paths, 20190108141736)

[lcjury]

Apparently, this issue is related to dumps/restore.

I was moving my no-apartment databases using the following script to dump the data:

psql -h $HOST -U $USER -c "alter schema public rename to $SCHEMA_NAME;" $DB_NAME
pg_dump -F plain -h $HOST -U $USER -d $DB_NAME -n $SCHEMA_NAME --file $DUMP_FILE
psql -h $HOST -U $USER -c "alter schema $SCHEMA_NAME rename to public;" $DB_NAME

Then I just restored then using running them with psql.

What I'm doing now: Instead of dumping the whole database, I'm dumping only the data, excluding the ar_internal_metadata and the schema_migrations. I only need to be carefull that the schema version on the recently created schema matchs the dump versions.

[mikecmpbll]

am i right in assuming it's not apartment related, or?