Postgres with separate-password-user setup and non-default schema

[lukestuts]

I am struggling to get Rodauth working with Postgres and a separate password user (referred to as ph in the documentation).

The error I am encountering when attempting to create a new user is:

Sequel::DatabaseError: PG::UndefinedTable: ERROR:  relation "account_password_hashes" does not exist

This happens when using the create_account plugin but I can reproduce it as follows:

  route do |r|
    r.rodauth

    RodauthLib = Rodauth.lib do
      enable :create_account
    end
    # XXX: This triggers the error:
    RodauthLib.create_account(login: "[email protected]", password: "demodemo")

I can see that functions have been created in the Postgres database (rodauth_valid_password_hash, rodauth_get_salt, etc) but my suspicion is that I have not correctly configured Rodauth to use a separate ph user and custom schemas. The section of the README that is unclear to me is this one:

You’ll also need to use the following Rodauth configuration methods so that the app account calls functions in a separate schema:

function_name do |name|
  "${DATABASE_NAME}_password.#{name}"
end
password_hash_table Sequel[:${DATABASE_NAME}_password][:account_password_hashes]

# if using the disallow_password_reuse feature:
previous_password_hash_table Sequel[:${DATABASE_NAME}_password][:account_previous_password_hashes]

I added some debugging output to the block that is passed to function_name and it appears to never get called.

I have added these sections in the following location in demo app:

class RodauthApp < Roda
  plugin :middleware
  plugin :rodauth do    
    enable :login
    enable :logout
    enable :remember
    enable :create_account
    enable :disallow_password_reuse

    function_name do |name|
      puts "XXXXX DEBUG XXXXXX"
      "#{DB.opts[:database]}_password.#{name}"
    end
    password_hash_table Sequel[:"#{DB.opts[:database]}_password"][:account_password_hashes]
    previous_password_hash_table Sequel[:"#{DB.opts[:database]}_password"][:account_previous_password_hashes]
end

I have also logged the following SQL from the create_account method:

SELECT NULL AS "nil" FROM "accounts" LIMIT 1
BEGIN
SAVEPOINT autopoint_1
SELECT "pg_attribute"."attname" AS "pk" FROM "pg_class", "pg_attribute", "pg_index", "pg_namespace" WHERE (("pg_class"."oid" = "pg_attribute"."attrelid") AND ("pg_class"."relnamespace" = "pg_namespace"."oid") AND ("pg_class"."oid" = "pg_index"."indrelid") AND ("pg_index"."indkey"[0] = "pg_attribute"."attnum") AND ("pg_index"."indisprimary" = 't') AND ("pg_class"."oid" = CAST(CAST('"accounts"' AS regclass) AS oid))) LIMIT 1
INSERT INTO "accounts" ("email") VALUES ('[email protected]') RETURNING "id"
RELEASE SAVEPOINT autopoint_1
UPDATE "account_password_hashes" SET "password_hash" = '$2a$12$xQvZk4LErdxb8Eh7VV9pkOprTQEv2d0qpDmHnYSMCFUjl2nOuE7mW' WHERE ("id" = 11)
ROLLBACK

I have looked around at the example Rodauth sites on Github but I have not found an example that uses separate Postgres users. I would be very grateful if anyone could point out the error of my ways or point me to an example project that uses a separate Postgres ph user and a non-default schema!

I must add that everything works fine with the default schema. This only happens when using a non-default schema.

[jeremyevans]

This happens when using the create_account plugin but I can reproduce it as follows:

  route do |r|
    r.rodauth

    RodauthLib = Rodauth.lib do
      enable :create_account
    end
    # XXX: This triggers the error:
    RodauthLib.create_account(login: "[email protected]", password: "demodemo")

Rodauth.lib creates a completely separate Rodauth configuration unrelated to the configuration you are using in your application. If you want to use Rodauth.lib, move the the Rodauth configuration you provided in your post into the Rodauth.lib block.

Note that you problem don't want to use Rodauth.lib, it's designed for usage outside of web applications. If you are designing a web application, just add the internal_request feature, then you can call the same methods. Rodauth.lib is literally just:

module Rodauth
  def self.lib(opts={}, &block) 
    require 'roda'
    c = Class.new(Roda)
    c.plugin(:rodauth, opts) do
      enable :internal_request
      instance_exec(&block)
    end 
    c.freeze
    c.rodauth
  end 
end

[lukestuts]

Thanks Jeremy! I was able to get the non-default schemas working like this:

    RodauthLib = Rodauth.lib do
      enable :disallow_password_reuse
      enable :create_account

      password_hash_table Sequel[:"#{DB.opts[:database]}_password"][:account_password_hashes]
      previous_password_hash_table Sequel[:"#{DB.opts[:database]}_password"][:account_previous_password_hashes]
      function_name do |name|
        "#{DB.opts[:database]}_password.#{name}"
      end
    end
    RodauthLib.create_account(login: "[email protected]", password: "demodemo")

where DB is defined earlier in my code as:

DB = Sequel.connect(db_url)

I ran into another problem where I was using Sequel.connect(db_url, test: false, keep_reference: false). This breaks with Rodauth because Rodauth looks at Sequel::DATABASES to find the database connection and when used these options, Sequel::DATABASES was not populated. I run these options by default because I use SequelTools to easily drop and recreate the database.

I can now create accounts, log in and log out with no problems. Thanks again.

[jeremyevans]

I ran into another problem where I was using Sequel.connect(db_url, test: false, keep_reference: false). This breaks with Rodauth because Rodauth looks at Sequel::DATABASES to find the database connection and when used these options, Sequel::DATABASES was not populated. I run these options by default because I use SequelTools to easily drop and recreate the database.

This is expected. I assume you used the Rodauth db configuration method to set the Database in this case?

I can now create accounts, log in and log out with no problems. Thanks again.

Great!

[lukestuts]

I've uploaded my weekend efforts here: rodauth_sinatra_demo. Hopefully this will be of use as a minimal "working" example of Sinatra+Rodauth with Postgres.