Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Passing login variable from Access FE into postgresql BE

  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    563

    Passing login variable from Access FE into postgresql BE

    Hi Guys,

    i have Access FE tables linked with DSN connection to Postgresql on windows system.

    IT is ok but i am adding now new, history table where i want to know which user inserted or deleted data.

    I am creating history table like here:
    https://www.cybertec-postgresql.com/...in-postgresql/

    And now how to check which user was inputing data?

    I can imagine that i can create log form in Access and have login variable to pass into postgresql BE
    but i do not know if it would be simple
    Maybe i should write Stored procedure to invoke trigger?

    In BE in postgresql i will have trigger like that:

    Code:
    CREATE FUNCTION change_trigger() RETURNS trigger AS $$
            BEGIN
    
                    IF      TG_OP = 'INSERT'
    
                    THEN
    
                            INSERT INTO logging.t_history (tabname, schemaname, operation, new_val)
    
                                    VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
    
                            RETURN NEW;
    
                    ELSIF   TG_OP = 'UPDATE'
    
                    THEN
    
                            INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val)
    
                                    VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
    
                                            row_to_json(NEW), row_to_json(OLD));
    
                            RETURN NEW;
    
                    ELSIF   TG_OP = 'DELETE'
    
                    THEN
    
                            INSERT INTO logging.t_history (tabname, schemaname, operation, old_val)
    
                                    VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
    
                            RETURN OLD;
    
                    END IF;
    
            END;
     $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
    so i have to pass variable to postgresql somehow and use this within trigger.



    Please help,
    any tips will be welcome.

    Best
    Jacek

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    645
    Why use this custom log instead of the Postgresql Transaction Log that does most of it for you?
    Patience serves as a protection against wrongs as clothes do against cold.

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    563
    Hi Perceptus,

    can you write a little more details about this postgresql Transaction log?
    How to get into it in mode details? And If this log is caughting both DDL and DML changes?

    Best,
    Jacek

  4. #4
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Hi Jacek,

    You could try to edit your table and trigger to get the IP of the computer doing the change using inet_client_addr() (see https://stackoverflow.com/questions/...d-machine-name) and add that to your user info table (new column to hold the user's computer name or IP). That way you don't need to pass anything to Postgresql.

    Cheers,
    Vlad

  5. #5
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    645
    I am not familiar with what version you are using. I mention it because I know I have used a similar use of the MySQL Transaction log, and that Postgres has one as well. in Postgres its called WAL.

    https://www.postgresql.org/docs/9.3/...wal-intro.html
    Patience serves as a protection against wrongs as clothes do against cold.

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    563
    Hi Guys,

    to catch variable i am using pass through query:

    Code:
    set username.username = 'bober';
    And this variable i can read within trigger.

    thank you for helping,

    Best,
    Jacek

  7. #7
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Hi Jacek,

    I take it you don't have concurrent users, only one at the time?

    Cheers,
    Vlad

  8. #8
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    563
    Hi Vlad,

    I have multiuser environment.
    Aa it can be a problem here.

    So i have to get windows autethinfication users login, or create access user login form.
    Or use your IP solution.

    Best,
    Jacek

  9. #9
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Hi Jacek,

    I think the Windows authentication (using Postgresql trust authentication method) is the best approach, I have used it successfully in the past within a custom trigger-based auditing module in SQL server. Storing the variable in a back-end table may cause data integrity and performance problems in multiuser environment.

    Cheers,
    Vlad

  10. #10
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    563
    thank you Vlad,

    i will try this. You mean users login into windows server remote machine and i am checking who is inputing data with windows authentification?

    Best,
    Jacek

  11. #11
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Jacek,

    How do your users authenticate in Postgresql? Do you use SSPI authentication (I think is similar to Windows authentication in SQL server) where you give then access rights to the back-end based on their Windows login, or are you using password authentication where they basically share a user id and password? https://www.postgresql.org/docs/9.6/...h-methods.html

    Cheers,
    Vlad

  12. #12
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    563
    Vlad,

    i have first item from lost i think

    Trust Authentication

    because i have only one user and password for database. (user is posgres, password is "1234").

    Access FE is linked with DNS with postgresql where i provided user and password for connection string...

    Hmm maybe i should add accesses for windows users in postgresql?

    Best,
    Jacek

  13. #13
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    That would allow you to capture the user from within postgresql. I am not sure if the IP method I first recommended would work in your case as it seems like they all would login into the same remote server to access the front-end, so the IP will be the same.

    Cheers,
    Vlad

  14. #14
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    563
    So postgresql can allow to catch windows server users ?

    Which method is it from link which you provided?

    This is Trust Authentication?

    Best,
    Jacek

  15. #15
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Hi Jacek, it is SSPI. Here are some links:

    https://www.cafe-encounter.net/p2034...s-on-localhost
    https://stackoverflow.com/questions/...authentication
    https://stackoverflow.com/questions/...l-version-10-4

    Please note that I have not used postgresql other than some test connection, but that looks similar to SQL's Windows Authentication method.

    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-23-2018, 06:54 AM
  2. Replies: 1
    Last Post: 10-29-2015, 06:47 AM
  3. Replies: 3
    Last Post: 10-27-2015, 01:37 PM
  4. Variable Not passing to query
    By chris.williams in forum Queries
    Replies: 2
    Last Post: 10-14-2011, 07:30 AM
  5. Passing SQL result into variable
    By jonny in forum Access
    Replies: 3
    Last Post: 10-18-2009, 06:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums