Thread: Using DSN Connection and knowing windows username

Using DSN Connection and knowing windows username

From
Łukasz Jarych
Date:
Hi,

i am using windows server 2012 and connecting from Access FE to Postgresql using DSN connection. 

When i am using "select current_user;" in postgresql i have always user "postgres" because it is my default user for database. 

How to know in postgresql which specific windows user is using database? 
How to solve this? 

I tried to use method from here:


but this is only to avoid inputing password by specific windows server user. 
(and this is not working also).

Please help,
Best,
Jacek 
Attachment

Re: Using DSN Connection and knowing windows username

From
"David G. Johnston"
Date:
On Wednesday, June 20, 2018, Łukasz Jarych <jaryszek@gmail.com> wrote:
How to know in postgresql which specific windows user is using database? 

You cannot.  All the server knows is the specific user credentials it is authenticating.

That said you can authenticate those credentials in such a way so that knowing the signed on user you would also know who they are in any environment that uses the same authentication source - and if that source supplies their Windows identity you are golden. The specific setups involved here are outside my experience, though.

David J.

Re: Using DSN Connection and knowing windows username

From
Łukasz Jarych
Date:
David G,

thank you.
Can you confirm if i am thinking correctly ?

So I can set up authetification to know which user is logged on and use this as postgresql user? 

But i think it will be not possible to use DSN connection with this.

Best ,
Luke

2018-06-20 15:34 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>:
On Wednesday, June 20, 2018, Łukasz Jarych <jaryszek@gmail.com> wrote:
How to know in postgresql which specific windows user is using database? 

You cannot.  All the server knows is the specific user credentials it is authenticating.

That said you can authenticate those credentials in such a way so that knowing the signed on user you would also know who they are in any environment that uses the same authentication source - and if that source supplies their Windows identity you are golden. The specific setups involved here are outside my experience, though.

David J.


Re: Using DSN Connection and knowing windows username

From
Adrian Klaver
Date:
On 06/20/2018 07:06 AM, Łukasz Jarych wrote:
> David G,
> 
> thank you.
> Can you confirm if i am thinking correctly ?
> 
> So I can set up authetification to know which user is logged on and use 
> this as postgresql user?

Only if the system user is a postgres user or can be mapped to one:

https://www.postgresql.org/docs/10/static/auth-username-maps.html

> 
> But i think it will be not possible to use DSN connection with this.

If you are talking about the ODBC DSN you use to create the linked table 
in Access then you are correct you are limited to whatever user is 
specified in the ODBC Manager.

It would help to know what you plan to use the user name for?

In the meantime just an idea, but could you capture the system user in a 
table in Access and use that to pass on to Postgres?


> 
> Best ,
> Luke
> 
> 2018-06-20 15:34 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com 
> <mailto:david.g.johnston@gmail.com>>:
> 
>     On Wednesday, June 20, 2018, Łukasz Jarych <jaryszek@gmail.com
>     <mailto:jaryszek@gmail.com>> wrote:
> 
>         How to know in postgresql which specific windows user is using
>         database? 
> 
> 
>     You cannot.  All the server knows is the specific user credentials
>     it is authenticating.
> 
>     That said you can authenticate those credentials in such a way so
>     that knowing the signed on user you would also know who they are in
>     any environment that uses the same authentication source - and if
>     that source supplies their Windows identity you are golden. The
>     specific setups involved here are outside my experience, though.
> 
>     David J.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Using DSN Connection and knowing windows username

From
Łukasz Jarych
Date:
Thank you Adrian,

In the meantime just an idea, but could you capture the system user in a table in Access and use that to pass on to Postgres?

Brilliant ! simple and genious!

The purpose of it is to have history log table with DML and DDL changes using triggers. 

Best,
Luke




2018-06-21 0:11 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 06/20/2018 07:06 AM, Łukasz Jarych wrote:
David G,

thank you.
Can you confirm if i am thinking correctly ?

So I can set up authetification to know which user is logged on and use this as postgresql user?

Only if the system user is a postgres user or can be mapped to one:

https://www.postgresql.org/docs/10/static/auth-username-maps.html


But i think it will be not possible to use DSN connection with this.

If you are talking about the ODBC DSN you use to create the linked table in Access then you are correct you are limited to whatever user is specified in the ODBC Manager.

It would help to know what you plan to use the user name for?

In the meantime just an idea, but could you capture the system user in a table in Access and use that to pass on to Postgres?



Best ,
Luke

2018-06-20 15:34 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:

    On Wednesday, June 20, 2018, Łukasz Jarych <jaryszek@gmail.com
    <mailto:jaryszek@gmail.com>> wrote:

        How to know in postgresql which specific windows user is using
        database?

    You cannot.  All the server knows is the specific user credentials
    it is authenticating.

    That said you can authenticate those credentials in such a way so
    that knowing the signed on user you would also know who they are in
    any environment that uses the same authentication source - and if
    that source supplies their Windows identity you are golden. The
    specific setups involved here are outside my experience, though.

    David J.




--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Using DSN Connection and knowing windows username

From
Łukasz Jarych
Date:
Hmm one problem. 

How to distinct value for specific user? 
I created new table in postgresql and when user is login into system New value is added to field "System_user". 
But how to show postgresql database that should use the specific value (for inputing data user, not for others?). 

Best,
Luke

2018-06-21 6:15 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:
Thank you Adrian,

In the meantime just an idea, but could you capture the system user in a table in Access and use that to pass on to Postgres?

Brilliant ! simple and genious!

The purpose of it is to have history log table with DML and DDL changes using triggers. 

Best,
Luke




2018-06-21 0:11 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 06/20/2018 07:06 AM, Łukasz Jarych wrote:
David G,

thank you.
Can you confirm if i am thinking correctly ?

So I can set up authetification to know which user is logged on and use this as postgresql user?

Only if the system user is a postgres user or can be mapped to one:

https://www.postgresql.org/docs/10/static/auth-username-maps.html


But i think it will be not possible to use DSN connection with this.

If you are talking about the ODBC DSN you use to create the linked table in Access then you are correct you are limited to whatever user is specified in the ODBC Manager.

It would help to know what you plan to use the user name for?

In the meantime just an idea, but could you capture the system user in a table in Access and use that to pass on to Postgres?



Best ,
Luke

2018-06-20 15:34 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:

    On Wednesday, June 20, 2018, Łukasz Jarych <jaryszek@gmail.com
    <mailto:jaryszek@gmail.com>> wrote:

        How to know in postgresql which specific windows user is using
        database?

    You cannot.  All the server knows is the specific user credentials
    it is authenticating.

    That said you can authenticate those credentials in such a way so
    that knowing the signed on user you would also know who they are in
    any environment that uses the same authentication source - and if
    that source supplies their Windows identity you are golden. The
    specific setups involved here are outside my experience, though.

    David J.




--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Using DSN Connection and knowing windows username

From
Łukasz Jarych
Date:
Hi Guys,

the best option here i think is to:

1. Create users login in Access
2. Use DSN-less odbc connection. 
3. When user is login to Access i am taking password and using it in postgresql BE.

Best,
Luke

2018-06-21 9:17 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:
Hmm one problem. 

How to distinct value for specific user? 
I created new table in postgresql and when user is login into system New value is added to field "System_user". 
But how to show postgresql database that should use the specific value (for inputing data user, not for others?). 

Best,
Luke

2018-06-21 6:15 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:
Thank you Adrian,

In the meantime just an idea, but could you capture the system user in a table in Access and use that to pass on to Postgres?

Brilliant ! simple and genious!

The purpose of it is to have history log table with DML and DDL changes using triggers. 

Best,
Luke




2018-06-21 0:11 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 06/20/2018 07:06 AM, Łukasz Jarych wrote:
David G,

thank you.
Can you confirm if i am thinking correctly ?

So I can set up authetification to know which user is logged on and use this as postgresql user?

Only if the system user is a postgres user or can be mapped to one:

https://www.postgresql.org/docs/10/static/auth-username-maps.html


But i think it will be not possible to use DSN connection with this.

If you are talking about the ODBC DSN you use to create the linked table in Access then you are correct you are limited to whatever user is specified in the ODBC Manager.

It would help to know what you plan to use the user name for?

In the meantime just an idea, but could you capture the system user in a table in Access and use that to pass on to Postgres?



Best ,
Luke

2018-06-20 15:34 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:

    On Wednesday, June 20, 2018, Łukasz Jarych <jaryszek@gmail.com
    <mailto:jaryszek@gmail.com>> wrote:

        How to know in postgresql which specific windows user is using
        database?

    You cannot.  All the server knows is the specific user credentials
    it is authenticating.

    That said you can authenticate those credentials in such a way so
    that knowing the signed on user you would also know who they are in
    any environment that uses the same authentication source - and if
    that source supplies their Windows identity you are golden. The
    specific setups involved here are outside my experience, though.

    David J.




--
Adrian Klaver
adrian.klaver@aklaver.com



Re: Using DSN Connection and knowing windows username

From
Adrian Klaver
Date:
On 06/21/2018 12:17 AM, Łukasz Jarych wrote:
> Hmm one problem.
> 
> How to distinct value for specific user?
> I created new table in postgresql and when user is login into system New 
> value is added to field "System_user".
> But how to show postgresql database that should use the specific value 
> (for inputing data user, not for others?).

That would depend on where you want to input the user name and for what 
actions?

> 
> Best,
> Luke
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Using DSN Connection and knowing windows username

From
Adrian Klaver
Date:
On 06/21/2018 01:33 AM, Łukasz Jarych wrote:
> Hi Guys,
> 
> the best option here i think is to:
> 
> 1. Create users login in Access
> 2. Use DSN-less odbc connection.
> 3. When user is login to Access i am taking password and using it in 
> postgresql BE.

That would work as long as the user name and password used to login into 
Access are also recognized by Postgres.

> 
> Best,
> Luke


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Using DSN Connection and knowing windows username

From
Łukasz Jarych
Date:
Hi ,

That would work as long as the user name and password used to login into Access are also recognized by Postgres.

I created users in postgresql and connecting to database using windows username and common password.

Thank you for help.

Best,
Luke 

2018-06-21 15:33 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 06/21/2018 01:33 AM, Łukasz Jarych wrote:
Hi Guys,

the best option here i think is to:

1. Create users login in Access
2. Use DSN-less odbc connection.
3. When user is login to Access i am taking password and using it in postgresql BE.

That would work as long as the user name and password used to login into Access are also recognized by Postgres.



Best,
Luke


--
Adrian Klaver
adrian.klaver@aklaver.com