[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5782:
---------------------------------------
Hello Steven, I think the answer is already partly covered in my response in the other jira issue. So the problem in the past was, that I had to send a dummy value for the primary key in order to get the create request via odata accepted from Teiid. I did not sent NULL but "0" by the way. The response than contained the correct …
[View More]autogenerated key.
To my opinion you are right, it should not be necessary to send a dummy value. Sometimes less is more :) In case you decide to change this, please let me know, as the change like you described, to refuse a create request with a given value for an autoincrement property, would break my code in some places.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5763:
---------------------------------------
> Hence if I would sent "1"as dummy, I receive "1" back instead of "0" like in the example above. However, the real autogenerated key which I would expect is a different one.
See the other jira issue. In general you should not rely on getting auto increment values by sending a dummy value - that relies upon source specific / non-…
[View More]standard behavior.
If you need this scenario to work that way, then please follow up on the other issue.
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 12.3
>
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5780) Support certificate based authentication into Teiid pg
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5780?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5780:
---------------------------------------
The relationship is bi-directional. The pg database fdw needs to point to the Teiid pg instance so that it can perform the materialization loads by reading directly from the Teiid views (imported to it as foreign tables).
Given our metadata and permission model, the load query cannot simply be an anonymous connection or a regular …
[View More]user. Already with our materialization logic we escalate loads to "admin" status. This means that the load query gets to see everything and bypass authorization checks - otherwise a non-privileged user could not implicitly load a materialized view that was used transitively, or even the materialization management would lack authorization to perform loads.
Teiid needs a connection to pg so that on a materialized view query it can redirect to the pg instance instead. This is far simpler as there will be a pre-configured service account.
> What is the process that Teiid uses to register/enlist PG for the materialization purpose, maybe we can add/build a relationship (secure backdoor) during that perhaps?
I don't quite follow this. Anything we do over the pg transport will not be a backdoor.
> 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)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5763:
---------------------------------------
Hello Steven, here is the first example:
{code:java}
CREATE VIEW QuicklyAddedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
product_name string(20) NOT NULL,
energy_100g double NOT NULL,
carbohydrates_100g double NOT NULL,
proteins_100g double NOT NULL,
fat_100g double NOT NULL,
CONSTRAINT "…
[View More]PRIMARY" PRIMARY KEY(fkProduct),
CONSTRAINT fkQuicklyAddedProductsToProducts FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fkQuicklyAddedProductsToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkQuicklyAddedProductsToAccount_idx INDEX(fkProfile)
)
OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
qap.fkProduct as fkProduct,
qap.fkProfile as fkProfile,
qap.product_name as product_name,
qap.energy_100g as energy_100g,
qap.carbohydrates_100g as carbohydrates_100g,
qap.proteins_100g as proteins_100g,
qap.fat_100g as fat_100g
FROM QuicklyAddedProducts_SRC as qap;
-- ///////////
CREATE TRIGGER ON QuicklyAddedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
INSERT INTO
Products(fkDatabaseKey)
VALUES
(2);
vIdProduct = cast(generated_key('idProduct') as long);
key.fkProduct = vIdProduct;
-- create a new record for the quickly added product
INSERT INTO
QuicklyAddedProducts_SRC(fkProfile, product_name, energy_100g, carbohydrates_100g, proteins_100g, fat_100g)
VALUES
(new.fkProfile, new.product_name, new.energy_100g, new.carbohydrates_100g, new.proteins_100g, new.fat_100g);
END;
{code}
When I sent the following request to add a new record
{"fkProduct":"0","fkProfile":"1","product_name":"This is a test","energy_100g":1255,"carbohydrates_100g":60.08,"proteins_100g":24.02,"fat_100g":15.89}
{"@odata.context":"https://morpheus.fritz.box/odata4/svc/my_nutri_diary/$metadata#QuicklyAdd...","{color:red}fkProduct":"0"{color},"fkProfile":"1","product_name":"This is a test","energy_100g":1255.0,"carbohydrates_100g":60.08,"proteins_100g":24.02,"fat_100g":15.89}
I get this object back. As a matter of principle in the past I always had to send a dummy for the primary key. Here fkProduct is the foreign key. Note, without defining a INSERT Trigger, the response correctly contains the key of the newly created item. In case of having a custom insert, simply the default dummy value is sent back. Hence if I would sent "1"as dummy, I receive "1" back instead of "0" like in the example above. However, the real autogenerated key which I would expect is a different one.
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 12.3
>
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5780) Support certificate based authentication into Teiid pg
by Ramesh Reddy (Jira)
[ https://issues.jboss.org/browse/TEIID-5780?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5780:
-------------------------------------
Giving the connection from PG into Teiid for materialization purpose with the same type of security layers as typical user seems to be complicated as shown above. What is the process that Teiid uses to register/enlist PG for the materialization purpose, maybe we can add/build a relationship (secure backdoor) during that perhaps?
> …
[View More]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)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5780) Support certificate based authentication into Teiid pg
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5780?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5780:
---------------------------------------
> Is there an option we can explore with ServiceAccount that has permissions on both containers?
That gets complicated as well. Presumably the Teiid pod will be configured to use keycloak, where as the pg container will have it's own user store with an admin account generated at deployment time.
- We simply glean the pg admin …
[View More]credentials and put special handling in our username/password authentication to grant that user admin access. This is very similar to the ssl cert approach in that the principal we're authenticating will not exist in the Teiid realm. As with the ssl case there's a chance that this username will collide with a user in the realm which would be very confusing from a logging perspective. Note that it is possible to assume that this user belongs to a different security-domain / realm, but most of our logic no longer reports that as part of the username...
- We require the creation of an admin account in the realm, and then also configure pg to use keycloak or some other identity assertion mechanism. This doesn't seem that easy, and it could be a manual task to add the admin user. It that account gets disabled for any reason all materialization for any vdb using that realm will be broken.
Do you see another option?
> 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)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5763:
---------------------------------------
> I have one more note regarding the removal of the NOT NULL constraint on the autoincrement key, as I just added a further Jira issue to a similar topic.
I have followed up on that issue. There may be more we could to add safe guards or optional behavior, but it's unfortunately not well defined behavior.
> Do you have an …
[View More]idea what is going wrong?
It's a little difficult to say just from what you have above as I'll have to try to recreate the full ddl.
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 12.3
>
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5780) Support certificate based authentication into Teiid pg
by Ramesh Reddy (Jira)
[ https://issues.jboss.org/browse/TEIID-5780?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5780:
-------------------------------------
Is there an option we can explore with ServiceAccount that has permissions on both containers? I understand apart from the connection you are also looking to encrypt the connection channel.
> Support certificate based authentication into Teiid pg
> ------------------------------------------------------
>
> …
[View More]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)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5782:
----------------------------------
Issue Type: Quality Risk (was: Bug)
Summary: When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value (was: When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly …
[View More]transferred back in the response of the odata layer.)
Priority: Major (was: Critical)
This unfortunately is not a well defined situation.
Core Teiid does not support the behavior you are describing. If a column is not null, then null cannot be explicitly inserted even if the column is marked as auto increment. This is common source behavior, but to my knowledge not guaranteed.
The zero value is even more troublesome as there are typically source flags that dictate the expected behavior - https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_auto_val...
See also identity behavior for postgresql - https://www.postgresql.org/docs/10/sql-insert.html if the column is marked as generated always, then you cannot specify a insert value without a special overriding clause.
The best practice would be to not rely on the implicit source behavior, and instead omit the value from the insert when you want to utilize the auto increment value.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
[View Less]
5 years, 9 months
[JBoss JIRA] (TEIIDSB-110) Support kerberos over pg/jdbc
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIIDSB-110?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIIDSB-110:
----------------------------------------
> I am not sure how much useful this will be at this time, as all applications in the OpenShift will be OAuth2 based AFAIK.
I'm not assuming that it is immediately useful - it's unassigned and bucketed to a later release. It's a bit of an overstatement that all openshift apps will use oath2, but yes it will be dominant. At …
[View More]the very least this issue can capture issues blocking this.
> Support kerberos over pg/jdbc
> -----------------------------
>
> Key: TEIIDSB-110
> URL: https://issues.jboss.org/browse/TEIIDSB-110
> Project: Teiid Spring Boot
> Issue Type: Enhancement
> Reporter: Steven Hawkins
> Priority: Major
> Fix For: 1.3.0
>
>
> Keycloak has support for spengo/kerberos, but at first glance it's only for browser/http challenge flows. It seems like using it in a direct flow is not possible out of the box: https://issues.jboss.org/browse/KEYCLOAK-1751
> Note that even if just kerberos password authentication were supported that would allow for secure logins without ssl, which can be useful for our pg transport.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
[View Less]
5 years, 9 months