Owner for password tables

[i-h8-github]

Hello,

In a previous flirtation with Rodauth (postgres 14), the migrations in the readme resulted in the user app_password owning all the password-related tables. However, running the migrations now (postgres 16), the migrations result in all tables being owned by the app user. The migrations won't run unless the app_password role exists, but does not seem to require granting schema rights as per the readme.

I have tested account creation and login, and it certainly looks like it's working, but I would like some clarification on the correctness of this setup since it's markedly different from my previous run and appears to be in conflict with the readme.

Thank you.

[jeremyevans]

PostgreSQL 15 changed things so only the repo owner has access to the public schema by default (a good change). Rodauth updated the documentation to reflect the changes needed: https://rodauth.jeremyevans.net/rdoc/files/README_rdoc.html#label-Grant+schema+rights+-28PostgreSQL+15-2B-29 . You mentioned it isn't requiring granting schema rights, so my only guess is you ran both migrations as the app user. You still need to run one migration as the app user, and the other migration as the app_password user.

If you are having problems, I recommend starting from scratch on a new PostgreSQL database cluster following the documentation, record every command you are running, and if you still have the same result, posting the commands you are running for review.

[i-h8-github]

It seems my ability to migrate up after revoking privileges was thanks to an existing schema_info_password table left over from a previous migration. I think? That's what my testing seems to indicate, anyway. I feel like the last time I got it right, it was because things were a bit louder about what I was doing wrong. Or maybe I've just gotten dumber.

I now have the migrations running with two roles as per the docs, but it doesn't change the fact that things appeared to have been working before with everything owned by the one role. What is the "gotcha" of that setup?

[jeremyevans]

The "gotcha" of that setup (app user owning everything) is that it doesn't add security, as the app user can then read the password hashes.

[i-h8-github]

I see, so the app_password user is there for extra security, but not strictly required? Is there any functionality that is unavailable without that setup?

[jeremyevans]

No loss of functionality, the use of two database users is purely for additional security.

[i-h8-github]

Thank you for your time.