Running migrations as superuser, then granting required privileges to app_role and ph_role

[MaTsou]

I am using rodauth as a middleware of my Hanami2 app. Discussing about rodauth specific migrations in Hanami forum leads to this advice : better responsibility separations using three roles. A superuser to migrate, app_role for application related database access and then, ph role for the account_password_hashes table.

I hope you agree with this..

I then created 'app_pg' and 'app_pg_password' roles with only LOGIN and PASSWORD options. Then run ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLES TO app_pg on my app database.

Hence, I run all my migrations as 'postgres' role. In the password specific migration, I tried to grant the minimal required privileged to 'app_pg' and 'app_pg_password' :

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

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

  # I am using 'user' instead of 'account'
  target_table = :user_password_hashes

  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 public" # probably uneeded since owner is postgres ?
    # it seems that next line is needed probably because of 'app_role' default privileges.
    run "REVOKE ALL PRIVILEGES ON #{target_table} FROM #{APP_ROLE}"
    RODAUTH_FUNCTIONS.each do |func|
      run "REVOKE ALL ON FUNCTION #{func} FROM public" # probably uneeded since owner is postgres ?
    end

    # granting needed privileges
    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}"
    end
  end

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

As you can see, I do not grant anything to 'app_pg_password'. This works because 'postgres' is the 'account_password_hashes' table owner.

But replacing 'app_pg_password' role by a superuser may be a security lack (?).
As my database skills are limited, I am not able to set the dedicated privileges to 'app_pg_password' to safely complete this migration.

I would appreciate any help on this.
Franck

[jeremyevans]

I think it's fine to have more database roles. Rodauth is designed to be flexible to work with different account and permission approaches. The split it documents (app user and ph user) is the minimum needed to enforce the desired security (no access to password hashes by app user), but you can certainly go further if you want.

It doesn't appear the ph user is used for anything in your approach. It appears you are using two accounts (app user and database superuser), not three.

Using the database superuser to define a SECURITY DEFINER function means callers of those functions run the function with the permissions of the database superuser. While I'm not sure that makes them vulnerable to anything, it seems like an unnecessary privilege escalation. You would at least want to use ALTER FUNCTION ... OWNER TO ph_user if you are running the migrations as a database superuser. This also implies granting the necessary permissions on the table to the ph user. That would at least actually use the three accounts.

FWIW, the README discusses how to use a separate table to store the schema version for the ph user. I'm not sure why that approach wouldn't work for you, but I'm guessing you aren't running the migrations in the way described by the README.

Note that you probably should revoke LOGIN/PASSWORD access from the ph user (or take another approach to prevent the ph user from being able to login to the database directly as described in the README), because the ph user should only be used via the SECURITY DEFINER functions.

[MaTsou]

Thanks for your reply.

You're right, I do not follow the README way to migrate my database. This is because Hanami framework not allowing multiple migration roles. So I have to properly set privileges after rodauth migration..

Following your advice, I remove LOGIN/PASSWORD access to ph user and rewrote the migration part concerned with privileges :

 # revoking privileges
    run "REVOKE ALL ON #{target_table} FROM #{APP_ROLE}"
    RODAUTH_FUNCTIONS.each do |func|
      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

Then,

1. Authentication works
2. select oid::regprocedure, proowner::regrole from pg_proc where pronamespace::regnamespace::text = 'public' and proowner::regrole::text = 'app_pg_password'; returns the expected rodauth functions..

I hope this lead to a database configuration as secure as the one I would get following the README (?).