Switch the connection parameters while sqlflow is running

[weiguoz]

Is your feature request related to a problem? Please describe.
In the production environment, we can't specify the data source connection parameters when SQLFlow starts. Instead, we know the connection parameters only after the user logs on.
Therefore, we should support this connection parameter change after the startup is complete.

[tonyyang-svail]

One possible solution: change the pysqlflow-sqlflow server proto

sqlflow/server/proto/sqlflow.proto

Lines 22 to 29 in b4e9965

	// SQL statements to run
	// e.g.
	// 1. `SELECT ...`
	// 2. `USE ...`, `DELETE ...`
	// 3. `SELECT ... TRAIN/PREDICT ...`
	message Request {
	string sql = 1; // The SQL statement to be executed.
	}

to

message Request {
    string credential = 1;      // e.g. mysql://root:root@localhost
    string sql = 2;		// The SQL statement to be executed.
}

[hungry1526]

@weiguoz I don't understand enough about the context, in which case user needs to change DB connection when SQLFlow server is running live?

[wangkuiyi]

It looks to me that we need a whole plan for authentication and authorization, with a constraint here -- we don't want 1-to-1 mapping between the Jupyter Notebook server instance (which runs the SQLFlow magic command plugin) and the SQLFlow server instance.

This constraint is essential because it allows us to run Jupyter Notebook servers and SQLFlow servers as Kubernetes services. It also allows us to run Jupyter Notebook server and the Web browser on users' laptop/desktop computers and SQLFlow server on the cloud.

To realize this constraint, we need to make sure that SQLFlow servers are stateless. However, a SQL program consists of SQL statements, and it is very like that there are some states with the running of a SQL program. If SQLFlow servers are stateless, where could we put these states?

An answer is in the databases that the SQLFlow users are operating. An alternative place is in the magic command class's data members. It looks to me that the user credentials are likely to be in the magic command.

In my mind, we might need a unique SQL statement, say,

%%sqlflow
SET CREDENTIAL username password

When a user inputs such a statement in her Jupyter Notebook, the SQLFlow magic command gets it. Instead of sending it to the SQLFlow server, the magic command saves the credential locally.

Then, when SQLFlow sends subsequent non-SET CREDENTIAL SQL statements to the SQLFlow service, it attaches the user credential information as part of the message Request. Of course, we need SSL between the magic command and the SQLFlow servers.

Is this a viable solution to authentication and authorization?

[typhoonzero]

There are several details to consider to implement the above design as @wangkuiyi commented:

The SQLFlow RPC server may never be stateless if we allow many-to-many client-server connections, for example, we have two clients A, B connected to one server, and they want to run SQL statements concurrently:

# A's SQL statements:
%%sqlflow
SET CREDENTIAL username password
USE mydb;
select * from mytable limit 10;

# B's SQL statements:
%%sqlflow
SET CREDENTIAL username2 password2
USE mydb2;
SELECT * FROM mytable2 limit 10;

We must implement server-side "Session" or the actual execution order on the server could be:

SET CREDENTIAL username password
SET CREDENTIAL username2 password2
USE mydb;  # error username2 have no permission on reading table mydb

And it seems that in talking about supporting credentials, we are talking about several independent features:

1. Supporting multiple clients in one SQLFlow server (supporting client sessions)
2. Use SSL for gRPC when communicating
3. Support SET CREDENTIAL username password for each client session
4. Whether to support connect to different database cluster for each session.

[Yancey0623]

I think the Session is a good idea, in addition to it:

Support SET CREDENTIAL username password for each client session

Maybe we can use SET DATA_SOURCE mysql://username:password@localhost:3306 instead of
SET CREDENTIAL username password, that we do not need to deploy the sqlflow-server for each DB server.

Whether to support connect to different database cluster for each session.

I agree with supporting different db cluster by SET DATASOURCE ...

[typhoonzero]

Some thoughts about supporting server-side session:

Requirements for the session feature:

1. One server should hold many sessions, each session stores the state of one connection come from the client.
2. One session stores only one connection to the database (with user credential information).
3. The session cannot be lost when the client is active, or the client's SQL statements will fail.
4. The session should be deleted when the client disconnects from the server, or there's no activity in a long time.
5. Client can switch to another database connection using SQL statements.

Possible session struct:

type Session struct {
	ClientAddr   string
	ClientPort   string
    DBConnStr    string
    DBCredential string
    // seconds passed from last activity
    LastActivity int64
}

Possible solutions are:

1. Store session directly in sqlflowserver process's memory
   • the session will be lost if one of the servers stops for any reason like horizontal scaling
   • a client must be routed to the same server if the servers are behind some reverse proxy or load balancer.
2. Store sessions in an etcd cluster
   • then sqlflowserver must depend on an etcd service

[Yancey0623]

@typhoonzero how about PR a design doc, it's easier to comment.

[wangkuiyi]

To make SQLFlow server stateless, should we follow the idea of HTTP COOKIEs to save states in the magic command side as something like COOKIEs?

[typhoonzero]

@wangkuiyi that would be cool, still, we must create one connection for each client and keep it on the server. If some server goes down, the client may send "COOKIEs" to the server to let the server recreate a connection to the database.

[wangkuiyi]

@typhoonzero I agree that SQLFlow server should try to reuse connections to SQL engines as much as possible. I think that in HTTP(S), each request has all COOKIEs attached. It is the server's flexibility to decide if to use them.

[Yancey0623]

@typhoonzero also need a unique sessionId in the Session struct, it can be generated when the client first connects by the server side.