[teiid-issues] [JBoss JIRA] (TEIID-5780) Support certificate based authentication into Teiid pg

[Steven Hawkins (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5780?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13750444#comment-13750444 ] 

Steven Hawkins commented on TEIID-5780:
---------------------------------------

> The SSL certificate is the one from the transport level certificate that is created as part of route creation? 

There would be service signing certificate created both for the teiid pg service, and one create for the pg database.

> Where does the Teiid server get this certificate?

It would one for the Teiid pg service as shown in https://github.com/teiid/teiid-openshift-examples/blob/master/keycloak/keycloak-db-security.adoc 

Note that the deployment config converts the service signing certificate to a jks file: https://github.com/teiid/teiid-openshift-examples/blob/master/keycloak/src/main/fabric8/deploymentconfig.yml to use with a Teiid transport.

This needs to occur for the pg instance as well.  It will have to have a signing certificate created, which an init container would turn into the the appropriate formats (pem and der) for consumption by the pg fdw connection to present to Teiid as the client certificate.

Each side will also need additional changes for another init container that creates or adds to a trust store on each side the public key from the other side.

At the end of the day that means that the teiid pg transport will support mutual authentication such that connections from the pg server are trusted.  

On top of that there is the additional configuration on the Teiid session service to set a specific certificate principal as the admin account.  This would need to match whatever openshift populates for the service signing certificate created for the pg database service.

> Is this for general authentication for any client for PG, or you looking only at materialization case?

It is primarily so that a pg client to Teiid can assume an "admin" role for materialization.  You could extrapolate this is several ways to make it a more general mechanism.

Essentially we need the connection coming in from pg to read Teiid to be a super user.  At a minimum it has to see all tables (which marking the dqpworkcontext as admin does).  It will probably need to be granted all roles as well - as it needs to be able to directly read from any view that is marked as materialized, which is not required of the normal materialization logic because it performs loads via procedures.

The other approach would be to have the user designate or create an admin user in the realm used with each vdb, and the provide the credentials if materialization is being used.

> Maybe a flow diagram will help me.

A thousand words is worth one picture right?



> Support certificate based authentication into Teiid pg
> ------------------------------------------------------
>
>                 Key: TEIID-5780
>                 URL: https://issues.jboss.org/browse/TEIID-5780
>             Project: Teiid
>          Issue Type: Sub-task
>          Components: ODBC
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 12.3
>
>
> To support the pg connection into Teiid we will do something like:
> - require a pg secure port using the service signing certificate: TEIIDSB-90 TEIIDSB-92
> -- one clarification is that we must document how to make the pg cert dominant if both pg and jdbc secure are used
> TODO:
> - configure the pg instance to have a service signing certificate and trust the Teiid service signing certificate.  If that trust seems too difficult we can just configure the connection to trust all.
> - configure the pg connection to Teiid to use the pg service signing certificate as the client certificate
> - trust the pg service signing certificate at the teiid service - we need hostname validation to be enabled and the Teiid server to map the service host name to an authenticated user (this could possibly be generalized via keycloak support to more users).



--
This message was sent by Atlassian Jira
(v7.12.1#712002)