Customize 'schema_migrations' table name on a gateway.

issues
1

Hello there,
I try to have rodauth working with my Hanami2.3 web app. One of rodauth nice feature is delegating password hash checking to a dedicated db user (the ‘password’ user). The app user can’t read these hashes.

This needs a specific migration to be done as the ‘password’ user. This migration can be done by hand as specified in rodauth readme.rdoc. But I would like to run it as other migrations.

So, I created an Authentication slice, with a config/db/migrate folder and sets an AUTHENTICATION__DATABASE_URL=postgres:://password_user_name:password_user_pwd@localhost/app_db. Checking Authentication::Slice['db.gateway'] leads to a well defined gateway for my ‘password’ user, targetting the same database as my app_user.

The issue is when running migration, I get this error :
ERROR: relation "schema_migrations" already exists (PG::DuplicateTable) . Indeed, my slice migrator try to create (and use) the same migration table that my app uses (and creates). After a few hours searching in ROM::SQL code, it seems I can’t specify a specific migration table (rodauth manual migration proposes ‘schema_info_password’).

Is there a way to do this customization ? Or another way (other than the ‘by hand’ one) ?

2

From the Rodauth docs:

While the app account is not be able to read password hashes, it is still be able to insert password hashes, update passwords hashes, and delete password hashes, so the additional security is not that painful.

So, this is role separation within a single database. Using a separate gateway config is the wrong approach here, because that assumes that the gateways are connecting to different DBs. There is only one config dir with one set of migrations per database in the ROM integration.

My recommendation would be to create three roles in your database: a migration role with superuser access to everything, an app role, and a ph role.

3

@alassek’s advice is good :slight_smile:

(In addition, I wanted to share that the name of the schema migrations table is not easily customisable right now anyway. We’d need to make changes to hanami-cli. If anyone is interested, I’d be open to us supporting this, since it could be useful for migration pathways from other Ruby apps.)

4

Thank you very much for you reply. I’ll try to follow your advice.

As I understand what you propose, I should have a connection to migrate the db and a different connection to access it via relations (all this in a single gateway). But, for now, it seems that hanami uses the gateway connection as the migrator connection. So I can’t see how to separate those roles since hanami does not give (writing) access to migrator config.

Am I wrong concluding this cannot be achieved with hanami current features ?

5

You’re right in that we don’t provide any specific support for running migrations with a different role, but I wonder if you might be able to tackle this by setting a different DATABASE_URL env var (with your migration role specified) just when you run the db migrate commands?

6

This appears so obvious now. And better than any way I thought about.

Great thanks !

7

I agree with @timriley’s advice, since this is a security hardening you don’t want SET ROLE to be possible from the less-privileged role, so you need to use separate credentials.

8

Thank you both for improving my understanding. Below my current database configuration (it may interest someone ?)

So I run all db related CLI command (create, migrate) preceded by DATABASE_URL=postgres://postgres:{postgres_pwd}@{host}:{port}/app_pg

I created two roles with minimal privileges :

psql -U postgres -c "CREATE ROLE app_pg_password;"
psql -U postgres -c "CREATE ROLE app_pg WITH LOGIN PASSWORD 'my-strong-pwd';"
psql -U postgres -d app_pg -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLES TO app_pg;"

My ph role do not have any LOGIN capabilities (advice from Jeremy Evans)

My app role has only the required privileges to run queries…

With this configuration, I needed to adapt rodauth password specific migration to correctly grant/revoke privileges. I ended with :

# frozen_string_literal: true

require 'rodauth/migrations'

ROM::SQL.migration do
  # ||= because of migration running twice (app db, then test db)
  APP_ROLE ||= 'app_pg'
  PWD_ROLE ||= 'app_pg_password'

  RODAUTH_FUNCTIONS ||= [
    'rodauth_get_salt(int8)',
    'rodauth_valid_password_hash(int8, text)'
  ]

  target_table = :user_password_hashes # I use 'user' rather than 'account'

  up do
    create_table target_table do
      foreign_key :id, :users, primary_key: true, type: :Bignum
      column :password_hash, String, null: false
    end

    Rodauth.create_database_authentication_functions self, table_name: target_table

    # revoking privileges
    run "REVOKE ALL ON #{target_table} FROM #{APP_ROLE}"
    RODAUTH_FUNCTIONS.each do |func|
      # probably not necessary (?)
      run "REVOKE ALL ON FUNCTION #{func} FROM public"
    end

    # granting needed privileges
    run "ALTER TABLE #{target_table} OWNER TO #{PWD_ROLE}"
    run "GRANT INSERT, UPDATE, DELETE ON #{target_table} TO #{APP_ROLE}"
    run "GRANT SELECT(id) ON #{target_table} TO #{APP_ROLE}"
    RODAUTH_FUNCTIONS.each do |func|
      run "GRANT EXECUTE ON FUNCTION #{func} TO #{APP_ROLE}"
      run "ALTER FUNCTION #{func} OWNER TO #{PWD_ROLE}"
    end
  end

  down do
    Rodauth.drop_database_authentication_functions self, table_name: target_table
    drop_table target_table
  end
end