I am writing an app that would run natively on some client machines that should connect to a database as a single DB user and later pretend to be more users (there's nothing new in this approach, I think). Now my problem is the following. Authentication is password based, that means that the app has to know it but Joe User must not (otherwise he could do arbitrary things with the DB). DB passwords change over time and I think recompiling the app every time the password changes is just silly.
So: how to store the DB access password so that Joe User doesn't see it but the admin can update it when it is necessary? Should I have an app on the server that the client would connect to or how?
I am using stored procedures for everything but selects (in fact - imitating object oriented programming on the PgSQL server), but I am not quite sure I could prevent anyone from using "delete" on a table who would use a stored function for that. How could I force people to use my stored functions for insert, update and delete operations instead of insert, update, delete commands in the DB?