Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using Token (JWT) authentication mechanism in Postgres

Using Token (JWT) authentication mechanism in Postgres

From
Julio Cesar Tenganan Daza
Date:

Hello,

 

I would like to know if is possible to use Token (JWT) authentication mechanism in Postgres? In order to authenticate users and also authorize access to specific tables, This is in a multi-tenant application context where users can create their own tables and share it if they want.

 

Is it possible this authentication mechanism or is there any plugin to achieve it?

 

Thank You for your help!

 

Regards,

 

Cesar

Re: Using Token (JWT) authentication mechanism in Postgres

From
James Keener
Date:
I believe postgrest can, and you can always use jwt in your application, but postgresql doesn't natively support them.

On January 24, 2018 4:27:23 PM EST, Julio Cesar Tenganan Daza <ctenganand@psl.com.co> wrote:

Hello,

 

I would like to know if is possible to use Token (JWT) authentication mechanism in Postgres? In order to authenticate users and also authorize access to specific tables, This is in a multi-tenant application context where users can create their own tables and share it if they want.

 

Is it possible this authentication mechanism or is there any plugin to achieve it?

 

Thank You for your help!

 

Regards,

 

Cesar


--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: Using Token (JWT) authentication mechanism in Postgres

From
Alexander Kukushkin
Date:
Hi,


2018-01-24 22:27 GMT+01:00 Julio Cesar Tenganan Daza <ctenganand@psl.com.co>:

Hello,

 

I would like to know if is possible to use Token (JWT) authentication mechanism in Postgres? In order to authenticate users and also authorize access to specific tables, This is in a multi-tenant application context where users can create their own tables and share it if they want.

 

Is it possible this authentication mechanism or is there any plugin to achieve it?


Postgres can use pam for authentication.
I am not sure that such plugin already exists, but it shouldn't be very hard to implement it.

There are a few problems though:
1. JWT token already contains information about username, but you still have to provide it (username) when opening connection.
2. Token has to be send as a connection password. Therefor connection must be encrypted.
3. Usually JWT tokens are quite big in size, but for example when psql is asking you for a password, it thinks that password can't be longer than 100 characters. And this value is hard-coded. It's possible to overcome this issue if you specify your token in PGPASSWORD env variable.

We at Zalando are using JWT tokens to authenticate employees when they are accessing postgres databases, but we are not dealing with JWT directly.
We have some OAuth infrastructure in-place, which can validate JWT tokens.
At the end it boiled down to sending http request to tokeninfo service and validating its answer.

Source code of PAM module is here: https://github.com/CyberDem0n/pam-oauth2

Basically you can do something similar. Either take pam-oauth2 as a reference and add possibility to validate JWT tokens or implement your tokeninfo service.

 

 

Thank You for your help!

 

Regards,

 

Cesar


Regards,
--
Alexander Kukushkin

RE: Using Token (JWT) authentication mechanism in Postgres

From
Julio Cesar Tenganan Daza
Date:

Hi Alexander,

 

I think we can use PAM authentication with something similar as you did is a good mechanism.

 

And also I have an additional question, can the implemented PAM authentication module be used from JDBC connections? Or they works totally apart?

 

Thank you so much!

 

 

Regards,

 

 

Cesar

 

From: Alexander Kukushkin [mailto:cyberdemn@gmail.com]
Sent: Thursday, January 25, 2018 3:43 AM
To: Julio Cesar Tenganan Daza <ctenganand@psl.com.co>
Cc: pgsql-general@postgresql.org
Subject: Re: Using Token (JWT) authentication mechanism in Postgres

 

Hi,

 

 

2018-01-24 22:27 GMT+01:00 Julio Cesar Tenganan Daza <ctenganand@psl.com.co>:

Hello,

 

I would like to know if is possible to use Token (JWT) authentication mechanism in Postgres? In order to authenticate users and also authorize access to specific tables, This is in a multi-tenant application context where users can create their own tables and share it if they want.

 

Is it possible this authentication mechanism or is there any plugin to achieve it?

 

Postgres can use pam for authentication.

I am not sure that such plugin already exists, but it shouldn't be very hard to implement it.

 

There are a few problems though:

1. JWT token already contains information about username, but you still have to provide it (username) when opening connection.

2. Token has to be send as a connection password. Therefor connection must be encrypted.

3. Usually JWT tokens are quite big in size, but for example when psql is asking you for a password, it thinks that password can't be longer than 100 characters. And this value is hard-coded. It's possible to overcome this issue if you specify your token in PGPASSWORD env variable.

 

We at Zalando are using JWT tokens to authenticate employees when they are accessing postgres databases, but we are not dealing with JWT directly.

We have some OAuth infrastructure in-place, which can validate JWT tokens.

At the end it boiled down to sending http request to tokeninfo service and validating its answer.

 

Source code of PAM module is here: https://github.com/CyberDem0n/pam-oauth2

 

Basically you can do something similar. Either take pam-oauth2 as a reference and add possibility to validate JWT tokens or implement your tokeninfo service.

 

 

 

Thank You for your help!

 

Regards,

 

Cesar

 

Regards,

--

Alexander Kukushkin

Re: Using Token (JWT) authentication mechanism in Postgres

From
Alexander Kukushkin
Date:
Hi,

 

And also I have an additional question, can the implemented PAM authentication module be used from JDBC connections? Or they works totally apart?


I think it should work from JDBC without any additional effort. And basically pam module itself doesn't even know that it is used from postgres.

Regards,
--
Alexander Kukushkin