Chapter 4 PostgreSQL

4.1 Version and Documentation

We currently run the latest version of PostgreSQL 12.

Documentation can be found here

4.2 SSL

You can create a key without a password using the following bash/zsh command:

openssl pkcs8 -topk8 -inform PEM -outform DER -in client-key.pem -out client-key.der -nocrypt

Use the resulting key in your DBeaver configuration.

4.3 User Management

A user can be created using the Google Cloud SQL user interface.

By default, users will be able to read schema, but perform no actions regarding tables. This behavior can be modified to fulfill the requirement of the project while adhering to the principle of least access.

/* Allow user to access a schema */
GRANT ALL ON SCHEMA "schema_name" TO "new_user";

/* Allow user read-only access to all tables in a schema */
GRANT SELECT ON ALL TABLES IN SCHEMA "schema_name" TO "new_user";

/* Allow user to perform all actions on all tables in a schema */
GRANT ALL ON ALL TABLES IN SCHEMA "schema_name" TO "new_user";

All user, table, and column names should be created with underscores (_) rather than dashes (-).

4.4 Dates and Times

Use CURRENT_TIMESTAMP or TRANSACTION_TIMESTAMP() to refer to the current time, as opposed to NOW().