postgres command¶
The postgres command can be used to connect to a cloudsql postgres database with your personal user. It includes subcommands for granting personal access to an instance, setting up a cloudsql proxy, and connecting to the database using a psql shell.
Warning
Run the following command first before running any of the other commands:
prepare¶
Prepare will prepare the postgres instance by connecting using the application credentials and modify the permissions on the public schema. All IAM users in your GCP project will be able to connect to the instance.
This operation is only required to run once for each postgresql instance.
| Argument | Required | Description |
|---|---|---|
| appname | Yes | Name of application owning the database |
| Flag | Required | Short | Default | Description |
|---|---|---|---|---|
| namespace | No | -n | namespace set in kubeconfig | Kubernetes namespace where app is deployed |
| cluster | No | -c | context set in kubeconfig | Kubernetes context where app is deployed |
| all-privs | No | false | If true ALL is granted, else only SELECT is granted |
grant¶
Grant yourself access to a Postgres database.
This is done by temporarily adding your user to the list of users that can administrate Cloud SQL instances and creating a database user with your email.
This operation is only required to run once for each postgresql database.
| Argument | Required | Description |
|---|---|---|
| appname | Yes | Name of application owning the database |
| Flag | Required | Short | Default | Description |
|---|---|---|---|---|
| namespace | No | -n | namespace set in kubeconfig | Kubernetes namespace where app is deployed |
| cluster | No | -c | context set in kubeconfig | Kubernetes context where app is deployed |
proxy¶
Update IAM policies by giving your user a timed sql.cloudsql.instanceUser role, then start a proxy to the instance.
| Argument | Required | Description |
|---|---|---|
| appname | Yes | Name of application owning the database |
| Flag | Required | Short | Default | Description |
|---|---|---|---|---|
| namespace | No | -n | namespace set in kubeconfig | Kubernetes namespace where app is deployed |
| cluster | No | -c | context set in kubeconfig | Kubernetes context where app is deployed |
| port | No | -p | 5432 | Local port for cloudsql proxy to listen on |
| host | No | -H | localhost | Host for the proxy |
Note When using proxy to connect to the database, the auth method is username and password. The username is your full Google account email: e.g.
ola.bruker@nais.io, and password is blank.
psql¶
Create a shell to the postgres instance by opening a proxy on a random port (see the proxy command for more info) and opening a psql shell.
| Argument | Required | Description |
|---|---|---|
| appname | Yes | Name of application owning the database |
| Flag | Required | Short | Default | Description |
|---|---|---|---|---|
| namespace | No | -n | namespace set in kubeconfig | Kubernetes namespace where app is deployed |
| cluster | No | -c | context set in kubeconfig | Kubernetes context where app is deployed |
| verbose | No | -V | false | Verbose will print proxy log |
users add¶
Adds a user to the database. By default the user is granted select privileges to the database public schema. The privilege level can be altered with the --privilege flag.
| Argument | Required | Description |
|---|---|---|
| username | Yes | Name of the new database user |
| password | Yes | Password for the new database user |
| appname | Yes | Name of application owning the database |
| Flag | Required | Short | Default | Description |
|---|---|---|---|---|
| namespace | No | -n | namespace set in kubeconfig | Kubernetes namespace where app is deployed |
| cluster | No | -c | context set in kubeconfig | Kubernetes context where app is deployed |
| privilege | No | select | The privilege level the user is granted |
users list¶
Lists all users in a database.
| Argument | Required | Description |
|---|---|---|
| appname | Yes | Name of application owning the database |
| Flag | Required | Short | Default | Description |
|---|---|---|---|---|
| namespace | No | -n | namespace set in kubeconfig | Kubernetes namespace where app is deployed |
| cluster | No | -c | context set in kubeconfig | Kubernetes context where app is deployed |
Created: 2022-01-31