#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    69
    Rep Power
    11

    Unhappy Getting UserID from Web Application


    Scenario:

    Have a Web Application written in Java and the backend is developed in Postgres. I am currently creating history tables which are updated everytime a UPDATE,INSERT,DELETE occurs to the orginal table. The history table is a mirror of the original with 3 extra fields one of which is app_user_id who is responsible for the changes.

    Problem Description:

    I would like to write a function that will be able to access the Web Application side and retrieve the current app_user_id so that the value can then be stored in the history table. The details of the user are stored in my app_user table (such as name, surname, password etc) in the database which is used for validating user access when the user logs into the system. Please note I need the web application user as opposed to the Database user.

    Can anyone please let me know what the best possible option is for this kind of procedure or possibly refer me to some sites. I will really appreciated the assistance.

    Regards,
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    make a table called 'users' with id and name, password. Have a page where they 'LOGIN' to the page. I think there is an option in postgresql.conf near the middle/towards the bottom that says #Enable encrypted passwords. The stuff I found in the manual kind of looks like it says you should make a true database user for each client if you want secure authentication. BUT you can restrict them to accessing tables individually, those that you name.

    Then when the user logs on, just have that passed as a parameter to each web page they visit. When you run the java app, you can just check that parameter. Then, to avoid normalization problems, just put the user's ID into your "mirror" table (I think that is 3NF, could be BCNF, depends on the rest of your mirror table). If you created another table to keep track of who modified the record last, you could either use their system name (which you could get out of the system table with users, maybe pg_user?) or the name that you have in your name/ID table.

    6.2.2. Password authentication

    Password-based authentication methods include md5, crypt, and password. These methods operate similarly except for the way that the password is sent across the connection. If you are at all concerned about password "sniffing" attacks then md5 is preferred, with crypt a second choice if you must support pre-7.2 clients. Plain password should especially be avoided for connections over the open Internet (unless you use SSL, SSH, or other communications security wrappers around the connection).

    PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_shadow system catalog table. Passwords can be managed with the query language commands CREATE USER and ALTER USER, e.g., CREATE USER foo WITH PASSWORD 'secret';. By default, that is, if no password has been set up, the stored password is null and password authentication will always fail for that user.

    To restrict the set of users that are allowed to connect to certain databases, list the users separated by commas, or in a separate file. The file should contain user names separated by commas or one user name per line, and be in the same directory as pg_hba.conf. Mention the (base) name of the file preceded with @ in the user column. The database column can similarly accept a list of values or a file name. You can also specify group names by preceding the group name with +.
    I found this quote

    here

IMN logo majestic logo threadwatch logo seochat tools logo