Not sure about a topic concerning android and postgresql-database.
Following situation:
We have a postgresql-database hosted by google cloud (gcp). On this database, only a specific network is allowed to connect with (authorized networks). In this network we will have some android-phones for some qr-code scanning and write to database. It should also be possible to query some data on the phone. The app is not public and will only run on some specific devices from the company. The users (employees) will not be allowed to install other apps or doing other things.
I have read a lot that it is much better to build a webservice for the devices to connect to, and the webservice will talk to the database. But i think it would be much more work to solve it with a web service.
Question 1: Is for this scenario a direct database-connection really so bad or is it ok, because of the restrictions (authorized network, not public app etc.).
Question 2 (for direct database connection): I am able to establish a connection from the app without using SSL:
import android.Manifest;
import android.content.pm.PackageManager;
import android.os.StrictMode;
import android.support.v4.app.ActivityCompat;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MainActivity extends AppCompatActivity {
private static String ip = "HOST_IP";
private static String port = "5432";
private static String Classes = "org.postgresql.Driver";
private static String database = "DATABASE_NAME";
private static String username = "USERNAME";
private static String password = "PASSWORD";
private static String url = "jdbc:postgresql://"+ip+":"+port+"/"+database;
private Connection connection = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
public void start(View view) {
ActivityCompat.requestPermissions(this,new String[]{Manifest.permission.INTERNET}, PackageManager.PERMISSION_GRANTED);
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);
try {
Class.forName(Classes);
connection = DriverManager.getConnection(url, username,password);
Toast.makeText(this, "Connected", Toast.LENGTH_SHORT).show();
} catch (ClassNotFoundException e) {
e.printStackTrace();
Toast.makeText(this, "Class fail", Toast.LENGTH_SHORT).show();
} catch (SQLException e) {
e.printStackTrace();
Toast.makeText(this, "Connected no", Toast.LENGTH_SHORT).show();
}
}
}
From the gcp i have now 3 certificates. I am able to connect using SSL with a python application using the 3 certificates and verify-ca: server-ca.pem, client-ca.pem, client-key.pem.
How can i do this with the jdbc in the android app and where to store the certificates? Can i handle it using Network security configuration (res/xml/network_security_config.xml)? Unfortunately, i don't have a lot of experience with this.
Question 3 (The postgREST way): I read about postgrest (https://postgrest.org/en/stable/) for the other solution. But i think i don't really understand the concept of authentication very well. How would a request look like when i want to query some data as a user with the name 'user' and the password 'password'? Will i send the user and password directly with the JWT?
