If both tables - user and user_profile - have as primary key the column user_id, and there is a one-to-one mapping between rows in these tables, what you have there is a vertical partitioning of a single logical entity.
In the logical data model this should be represented as a single entity type. In the physical data model that single entity type is implemented as two tables. There are reasons why you would want to do this. Security is one, as you have mentioned, though that could be handled just as well with views.
I have not come across this implementation nor seen articles advocating it. I'm not convinced this paradigm is that common.
If there could be many rows in user_profile for each row in user then you have a simple parent-child pattern. Other tables will follow the usual pattern, too.
For other tables which have obvious one-to-many relationships to user your options are not a simple binary. In the logical model the situation is simple - entity type Payments references entity type User. In the physical implementation with vertically partitioned user table you have options. If either of user or user_profile is optional the foreign key must be declared as referencing the mandatory one. If both are required then it simply doesn't matter.
To be explicit, it is OK to join in columns which are not explicitly declared as foreign keys. So you could have the DRI written as
.. payments.user_id references user.user_id and legitimately write
.. from user_profile up inner join payments p on up.user_id = p.user_id. That is OK; it will work.
If you really wanted to, and your DBMS didn't object, you could even declare two foreign keys for the child table. One FK will link Payment to User. The second will link Payment to User_Profile. My feeling is that this is excessive and adds little, but it is not "wrong".
To answer the stated question, I would follow the principle of least surprise. If table Payment has a column user_id, and there exists a table called User I would expect Payment.user_id to reference User.user_id.