sshkarupa commented 6 years ago

Steps to reproduce

I'm trying to enable postgis in a Apartment tenanted postgresql database and I'm bump the the following problem.

If I'm creating the database using postgresql adapter and then adding postgis configurations (adapter, schema_search_path, db_enhancements.rake) all works fine. But if I'm creating the new database using postgis adapter it doesn't work for creating a new tenant.

$ RAILS_ENV=test rake db:create
$ RAILS_ENV=test rake db:schema:load
$ RAILS_ENV=test rails console
irb> Apartment::Tenant.create 'testapp'

Expected behavior

Apartment::Tenant.create('my-tenant') won't raise errors.

Actual behavior

ActiveRecord::StatementInvalid: PG::UndefinedObject: ERROR:  type "geometry" does not exist
LINE 1: ...r, "geometricable_type" character varying, "data" geometry(G...
                                                             ^
: CREATE TABLE "geometries" ("id" serial primary key, "geometricable_id" integer, "geometricable_type" character varying, "data" geometry(GEOMETRY,0), "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL) 
from /usr/local/bundle/gems/activerecord-4.2.10/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'

In public tenant everything fine.

System configuration

Database: I'm using docker for development, so for postgresql i'm using the official image postgresql:9.6-alpine, for postgis - mdillon/postgis:9.6-alpine accordingly.

Database config (in database.yml):


development: &default
adapter: postgis
schema_search_path: "public,shared_extensions"
host: <%= ENV.fetch('DATABASE_HOST', 'localhost') %>
username: <%= ENV['DATABASE_USERNAME'] %>
password: <%= ENV['DATABASE_PASSWORD'] %>
database: <%= ENV.fetch('DATABASE_NAME', development_db') %>
encoding: utf8
min_messages: warning
pool: <%= ENV.fetch('DATABASE_POOL') { 5 } %>
timeout: 5000

test: <<: *default database: <%= ENV.fetch('TEST_DATABASE_NAME', 'test_db') %>

lib/task/db_enhancements.rake

namespace :db do desc 'Creates shared_extensions Schema and enables postgis extension' task extensions: :environment do

Create Schema

ActiveRecord::Base.connection.execute 'CREATE SCHEMA IF NOT EXISTS shared_extensions;'
# Enable PostGIS
ActiveRecord::Base.connection.execute 'CREATE EXTENSION IF NOT EXISTS postgis SCHEMA shared_extensions;'
# Grant usage to public
ActiveRecord::Base.connection.execute 'GRANT usage ON SCHEMA shared_extensions to public;'

end end

Rake::Task['db:create'].enhance do Rake::Task['db:extensions'].invoke end

Rake::Task['db:test:purge'].enhance do Rake::Task['db:extensions'].invoke end



* Apartment version: 2.1.0

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

  * `use_schemas`: `true`
  * `config.persistent_schemas` = `%w[shared_extensions]`

* Rails (or ActiveRecord) version: rails 4.2.10

* Ruby version: docker image `ruby:2.3.5-alpine3.4`

* Activerecord Postgis Adapter (`rgeo/activerecord-postgis-adapter`) version: 3.1.5

sshkarupa commented 6 years ago

I've found a solution for this problem. For some reason the postgis extension is install into public schema instead of shared_extensions. In my case all iI need is to add another one command into the rake task:

# lib/task/db_enhancements.rake
namespace :db do
  desc 'Creates shared_extensions Schema and enables postgis extension'
  task extensions: :environment do
    # drop PostGIS extension from public schema;
    ActiveRecord::Base.connection.execute 'DROP EXTENSION IF EXISTS postgis;'
    # Create Schema
    ActiveRecord::Base.connection.execute 'CREATE SCHEMA IF NOT EXISTS shared_extensions;'
    # Enable PostGIS
    ActiveRecord::Base.connection.execute 'CREATE EXTENSION IF NOT EXISTS postgis SCHEMA shared_extensions;'
    # Grant usage to public
    ActiveRecord::Base.connection.execute 'GRANT usage ON SCHEMA shared_extensions to public;'
  end
end

Rake::Task['db:create'].enhance do
  Rake::Task['db:extensions'].invoke
end

Rake::Task['db:test:purge'].enhance do
  Rake::Task['db:extensions'].invoke
end

Now all works correctly

asergiop21 commented 4 years ago

Hi @sshkarupa, A Question here, I Am having the same problem, I wrote your solution and I still have error . Are you help me, please?

millisami commented 3 years ago

@asergiop21 I've stumbled upon these many times on my servers. The fix is to drop your db and re-create it with rake db:create which works along with the aparment's db_enhancement task.

influitive / apartment