Support for DDL Operations

[git-hub-abhishek]

Should we also open up DB2REST for DDL? That way it can be a potential safe replacement for flyway or partially liquibase. Consumers can push SQL to git and then with curl or httpie use DB2REST to apply changes.

[git-hub-abhishek]

My initial topics of discussions

• Typical application dev teams would not have the task to CRUD on entities like tables, sequences, table-spaces, grants, indexes etc. What entities should we consider? Or shall we consider a single API that takes a script and creates everything.
• Who are we targeting as users of the DDL APIs.? For pipeline etc, I am trying to compare the experience of running a DB script to setup the table vs having multiple roundtrips with APIs ( if we have to deal with CRUD on entities like tables etc.). Many test teams are even using DB images during setup phases in their pipelines.
• For security purposes is it safe to allow the same DB2REST API credentials for DDL and DMLs. If we allow the same creds the teams would loose the DBA vs APPDEV privilege separations!

[kdhrubo]

1. This is not a single API - its same tooling which can be used for DDL and DML execution.
2. Ones who are using DB CI/CD tools like Liquibase, Flyway, Atlas, Sqitch, DBT etc
3. Companies who work in that old way, probably dont use the DB CI/CD tools above, so they can do what they thing is best ;)

[thadguidry]

What is the real problem you are describing here? When you say Support for DDL Operations, do you really mean...
"Should we have a RESTful way to create data structures and schema?"

My take has been that creation of data structures and schema is done with traditional DB tools.
There are many things that go into planning a schema, relations, access control, partitioning, storage, etc.
"DB (existing DB) 2 REST" is what I thought we'd primarily focus on.
Creation and Writing are very different from Reading.
So my vote would be NO on supporting DDL.

[kdhrubo]

We won't get into planning and design
Execution of the scripts like Flyway does.
DB2Rest is a secure gateway for Databases as well besides the data access layer.

[git-hub-abhishek]

I am not fully convinced that, if the same DB2REST layer allows DDL and DML APIs with the same user credentials (e.g. API keys/ auth tokens) teams soon breach the authz constraints of DB! Which might lead to have a Authz module to be developed in DB2REST.

Moreover the Database User to be created for DB2REST layer to use, might be questioned by DB security teams (since it would require almost a DBA privilege to be assigned to it).

[git-hub-abhishek]

Thanks @thadguidry I agree to your perspective! That's why when the original idea came to us, I wanted to have everyone's opinion on this here. IMHO, DDL and DB design are for a different set of consumers (e.g DBAs) and there are already many software are available to create the physical DB from logical diagrams. That's why I flagged the question, Who are we targeting as users of the DDL APIs.?.
Expecting @kdhrubo to add more context to this please.

[kdhrubo]

DB design is not what DB2Rest does. It can get into DB CI/CD or DB evolution like Liquibase, Flyway or Sqitch or Atlas

This is very clean approach where you have your scripts in Git. Run pipeline and let DB2Rest safely allow the change.
The problem with LB or any other embedded tool is you need a connection to database from CI/CD platform if you have to run those scripts from build tools like maven or gradle in pipeline. This means your database credentials and connections are leaking over multiple places. Instead with DB2Rest action as the database gateway there is no need to open DB connection from CI/CD pipeline, you need a REST connection only.

Hope this gives more perspective.

[thadguidry]

Ah, well then if you want DB2REST to also be a "secure" gateway for CRUD operations against a DB, then sure, that makes sense.

[kdhrubo]

Yup just handle the DB schema evolution.
Not design, not permission management or user management. Leave those to the security and database admin.

Then once your DB is provisioned and user/password/role assigned and shared with the team (this could be per environment) DB2REST gateway stands up.

Now it can handle database CRUD and procedure executions. At the same time (if enabled via configuration?) can help to safely evolve DB schema(s) by executing DDL scripts hurled at it.

[souravroy]

@kdhrubo I agree with @thadguidry and @git-hub-abhishek. Let’s not mix DDL and DML in the same app.

[kdhrubo]

There are millions of Spring Boots apps (I am sure in other frameworks too) with embedded Liquibase or Flyway handling DB evolution.
This has been done in big and small companies in very regulated environments. These apps do not create database user.
They just execute database evolution scripts like table create, update or insert some seed data. Hence same app acts on ddl, dml.
Pretty much all microservices which should own their DB works in this way.

Supporting DDL opens up a new possibility where DB2REST can also support DB evolution along with data access.

[susanta-mukhopadhyay]

@kdhrubo, Technically DDL can be supported, but as you know API exposed are mainly business API which will be looking for CRUD operations. Creating tables using DDL through API may not be much useful features for the end users. Should we invest time on supporting DDL or better to invest time to make DML more robust and useful?

[kdhrubo]

Actually we are not building business API at this time.DB2REST is only concerned with DATA API. In future we might by providing before and after hooks extension with Python,Groovy,JavaScript or remote REST calls to actually become a complete API platform.

At present we will only support DML.
However opening up DDL also provides a viable and secure alternative to database CI/CD or database schema versioning or evolution tools.

Most of these tools run embedded in Java like Liquibase or Flyway making it challenging to synchronize in a multi container scenario often leading to race condition and stuck deployments.

Also running these tools using build from pipeline means you will expose your database from CI/CD tooling which is dangerous from infosec perspective. You don't want DB connections details and user credentials with dml permission in pipeline.

Liquibase solves these issues with a kind of detached server kind of installation which manages all different projects and it's database change sets.

DB2REST can similarly be the only gateway to database. It can then provide API to apply DB changes via DML execution.

Hope I am able to explain this future use case. I can further elaborate in a call.

[susanta-mukhopadhyay]

I used Liquibase earlier, so I got your point. Are we thinking of exposing DDL operations through APIs or it will be additional tool can be used with specific user role locally? As exposing API over HTTPs will need good security approach to prevent any hacking or unauthorized changes.

[kdhrubo]

So DB2REST APIs can be called from CI/CD pipeline via an API key mechanism (enterprise)
@rajivmca2004 is working on adding JWT security.

Alternatively - DB2Rest can listen for events from Github and execute SQL files in the changeset.

We have to think through this little more.