This article is a continuation of 2 below.
Create a database to access from the web module. Click SQL in the side menu. When the details screen appears, click "Create Instance".
Then click "PostgreSQL".
On the details screen, enter "sample-app-ist" for the instance ID and "123456" for the default user password.
Then click Show Configuration Options.
Check the private IP.
If the details screen is displayed, click "Enable API".
After a while, the database instance was created.
Then click the database instance and then click the database on the details screen. Then click "Create Database".
Enter "sample-app-db" as the database name and click "Create".
The database has been created.
Immediately connect to the database instance. Enter "123456" when prompted for a password.
[userid]@cloudshell:~ ([project_id])$ sudo gcloud sql connect sample-app-ist --user=postgres Whitelisting your IP for incoming connection for 5 minutes...done. Connecting to database with SQL user [postgres].Password for user postgres: psql (9.6.11, server 9.6.10) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off) Type "help" for help.
Since the database to be operated is postgres, switch to the sample-app-db created earlier. Enter "123456" when prompted for a password.
postgres=> \connect sample-app-db Password for user postgres: psql (9.6.11, server 9.6.10) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off) You are now connected to database "sample-app-db" as user "postgres".
Then create a proxyuser for later use and give it connection and permissions to the database. After switching to proxyuser (enter "123456" when prompted for password), create a t_sample table and insert one test data.
sample-app-db=> CREATE ROLE proxyuser WITH LOGIN PASSWORD '123456'; CREATE ROLE sample-app-db=> GRANT CONNECT ON DATABASE "sample-app-db" TO proxyuser; sample-app-db=> \connect - proxyuser Password for user proxyuser: psql (9.6.11, server 9.6.10) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off) You are now connected to database "sample-app-db" as user "proxyuser". sample-app-db=> CREATE TABLE t_sample (order_no BIGINT NOT NULL, cre_dt TIMESTAMP NOT NULL default CURRENT_TIMESTAMP, nickname VARCHAR(30), order_id VARCHAR(5000)); CREATE TABLE sample-app-db=> INSERT INTO t_sample (order_no, cre_dt, nickname, order_id) VALUES (1, '1999-01-08 04:05:06', 'test', 'ABC'); INSERT 0 1
Check the created table.
sample-app-db=> \dt List of relations Schema | Name | Type | Owner --------+----------+-------+----------- public | t_sample | table | proxyuser (1 row)
Make the necessary settings to connect GKE to the database instance. First, click Enable Cloud SQL Administration API (https://console.cloud.google.com/flows/enableapi?apiid=sqladmin&hl=ja&_ga=2.250044038.-1219564708.1545700047).
Enter "Select a project to register the application" and click "Continue".
The Cloud SQL Administration API is now enabled. Click Cancel to add credentials to the project.
Then click IAM & Administration-> Service Accounts.
Click Create Service Account.
Enter "sample-app-db-client" as the service account name and click "Create".
Enter Cloud SQL Client for the role and click Continue.
Finally, click "Create Key".
Make sure "JSON" is selected on the details screen and click "Create".
The JSON file will be downloaded to your browser.
Upload the downloaded JSON file to your browser.
Check if the file has been uploaded.
[userid]@cloudshell:~ ([project_id])$ ls -Fal -rw-r--r-- 1 [userid] xxxxxxx 2361 Mar 10 09:25 [project_id]-xxxxxxxxxxxx.json
Create a secret by specifying the JSON you uploaded earlier.
[userid]@cloudshell:~ ([project_id])$ kubectl create secret generic cloudsql-instance-credentials --from-file=credentials.json=[project_id]-xxxxxxxxxxxx.json secret "cloudsql-instance-credentials" created
Create a secret by specifying the user and password that Cloud Sql Proxy will use to access the database.
[userid]@cloudshell:~ ([project_id])$ kubectl create secret generic cloudsql-db-credentials --from-literal=username=proxyuser --from-literal=password=123456 secret "cloudsql-db-credentials" created