I'm thinking about how to hide as many attributes of relations from a user as possible & if functions can help with that.
- The user "dummy_user" should have as little insight as possible.
- The table "user" contains user data incl. password. The password should not be accessible for the dummy_user.
- The function "does_password_match(user_id, password)" checks if the password matches the user.
My question now: I read in the PostgreSQL doc: "SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it.". So that means I can create the function does_password_match as admin and it can access the password that way and the user is allowed to execute it with the privileges of the admin?
But the dummy_user can still see the body of the function. So you can't really hide an internal structure this way, right?
And if within this function the password is searched via a SELECT, can the dummy_user then potentially still see the value of the field? And so the password of a user can potentially still be read?