Skip to content

Database Creation, Ownership, and Permissions Automation Guide

Decision

After reviewing the documentation, it's been decided that we will keep using best practices when it comes down to assign db owner.

Two scripts have been developed to automate the process of db creation. It is important to highlight that the second executed script, creates a group called devops_group which will have full administrative rights over the freshly created database. By doing this, it's not necessary to go user by user granting access to the freshly created database, since now all we have to do is make the user member of the devops_group and it will have full rights over the db.

How it works

Four files are involved in the creation process: db.conf, provision_new_admin.sh, postgres_create_db_user.sh.

  1. connect-cloudsql.sh This is your proxy file, as it can be seen, the only enabled connection it is the one we one to use to connect to our project and instance. This example was used for testing purposes, that is why the current projects are commented. If the future those will have to be enabled to carry out real-life creation scenarios.

# Create tunnels for Cloud SQL instances using query params for ports. # This is the correct, modern syntax for the v2 proxy. cloud_sql_proxy \ --auto-iam-authn \ --debug \ "db-creation-test-project:europe-north1:testing?port=5432" # "attracs-backups:europe-north1:attracs-postgre-test?port=55434" \ # "att-nightly:europe-north1:attracs-sql-nightly?port=33307" \ # "pg-databases-nightly:europe-north1:pg-1-nightly?port=55435" \ # "pg-databases-stable:europe-north1:pg-1-stable?port=55439" \ # "attracs-logistics-nightly-6519:europe-north1:pg-road-nightly?port=5432"

I, recommend to run this script as follow: bash connect-cloudsql.sh so the flags get read properly. Now another choice is to make executable by running this command chmod 755 connect-cloudsql.sh . This command will change the properties. Now, if you want to make it even easier you can move this file to /usr/local/bin/ and you can call it from anywhere in the console.

  1. db.conf Here, just make sure variables are set to point to the right project and instance, otherwise it will not work. This file does need to be executed. It is called by the upcoming scripts because they just need its values.

# Your GCP Project SET_PROJECT="project_name"

# Your Cloud SQL Instance INSTANCE_NAME="bd_instance_name" INSTANCE_PROJECT="project_name" LOCAL_PORT="5432" # Most likely this does not have to be changed, but look at the proxy file to be sure.

# --- This is new, for the admin script --- # The 'postgres' superuser for your instance. DB_ADMIN_USER="postgres"

3. provision_new_admin.sh

This file caused some source of confusion, because it is hard to understand at the beginning why it's needed. Let's break it down. The Goodle credential (firstName.lastName@aholadigital.com) and the PostgreSQL user (firstName.lastName@aholadigital,com) are two separate entities in two different permission systems.

    • Google Cloud IAM: controls who can access the Google Could product (the SQL instance itself). The gcloud login and the roles/cloudsql.instanceUser role are part of this system. This help us to identify ourselves and acquire permission to connect to the instance. This is authentication.
    • PosgreSQL Internal Roles: control what we can do inside the db after we've connected. By default, a new IAM user DOES NOT have any permissions in the system. Therefore, it CANNOT CREATE ROLE, CREATE DATABASE, OR GRANT privileges. This is authorization.

Our main script, postgres_create_db_user.sh , tries to run commands like CREATE ROLE grafana_monitoring .

When we run this, the following was the outcome. 1. 1. Authentication (Pass): Our Google credentials allowed us to connect to the db. 2. Authorization (Failure): The scrips sends the CREATE ROLE command. The PostgreSQL db checks its internal permissions for the user firstName.lastName@aholadigital.com, finds no CREATE ROLE privilege, and returns permission denied failure. We could not use the script to grant ourselves these permissions, because we need permissions to run the script. This is a "chicken and the egg" issue or catch-22 per se.

Two-script solution To solve this problem, it is necessary to use an existing superuser (like postgres) to give out IAM user the necessary database-level permissions.

    1. provision_new_admin.sh ("Setup script") * What it is: A one-time setup script (per project) * Who runs it: An existing super user (the postgres user). * How it works: it runs the ALTER ROLE "firstName.lastName@aholadigital.com" WITH CREATEROLE, CREATEDB, INHERET; commands. This gives our separate IAM use the internal database permissions it was missing.

Once this script is run we can proceed to run upcoming script.

How to run the script In the console, navigate to the folder where the file is located (attracs-linux-playbooks-1/scripts/db-scripts/creating_bd/). The command to type has the following structure: file-name configuration-file "IAM-user", so the command would look something like this: ./provision_new_admin.sh db.conf "``first_name.last_name@aholadigital.com.

The output you'll see most likely will be something like this: This script will log in as 'postgres' to grant permissions. Enter password for 'postgres': Step 1/3: Granting 'roles/cloudsql.instanceUser' to``firstName.lastName@aholadigital.com``... Updated IAM policy for project [db-creation-test-project]. bindings: - members: - user:firstName.lastName@aholadigital.com role: roles/cloudsql.instanceUser - members: - serviceAccount:service-112458474883@compute-system.iam.gserviceaccount.com role: roles/compute.serviceAgent - members: - serviceAccount:112458474883-compute@developer.gserviceaccount.com - serviceAccount:112458474883@cloudservices.gserviceaccount.com role: roles/editor - members: - user:firstName.lastName@aholadigital.com role: roles/owner etag: BwZB0728Rhg= version: 1 Step 2/3: Creating Cloud SQL user``firstName.lastName@aholadigital.com``... Creating Cloud SQL user...done. Created user [``firstName.lastName@aholadigital.com``]. Step 3/3: Setting database-level permissions (CREATEROLE, CREATEDB, INHERIT)... ALTER ROLE ALTER ROLE ALTER ROLE GRANT ROLE

--- Success! --- User firstName.lastName@aholadigital.com is now provisioned. They must run 'gcloud auth login' and 'gcloud config set project db-creation-test-project'.

This part They must run 'gcloud auth login' and 'gcloud config set project project-name'. can be done prior running the script.

4. postgres_create_db_user.sh

This is our second script that needs to join to the party. * * What it is: Our main automation script. * Who runs it: You (the newly provisioned admin), using your passwordless Google credential. * How it works: When you run this script, it connects as you. * What it does: When it sends the CREATE ROLE command, the database checks your permissions. This time, it sees that postgres has granted you the CREATEROLE privilege, and the command succeeds. Both scripts are needed to separate the one-time, privileged task of granting admin rights from the repeatable, daily task of using those rights.

How to run the script In the console, navigate to the folder where the file is located (attracs-linux-playbooks-1/scripts/db-scripts/creating_bd/).

The command to type has the following structure: file-name configuration-file "dataBase-name", so the command would look something like this: ./provision_new_admin.sh db.conf "dataBase-name" The owner of the db will have the same name as the db itself.

In this example we are creating a db with the name test-db-one-henri-2.0 . ./postgres_create_db_user.sh db.conf "test-db-one-henri-2.0" Getting current authenticated GCP account... Authenticated as: first_name.last_name@aholadigital.com Connected as first_name.last_name@aholadigital.com to postgres db. Ensuring prerequisite roles exist... ERROR: role "grafana_monitoring" already exists ERROR: role "devops_group" already exists ERROR: role "readonly" already exists ERROR: role "readwrite" already exists ERROR: role "postgres_exporter" already exists Ensuring 'devops_group' has default admin on new databases... GRANT Creating standard user 'test-db-one-henri-2.0'... format ------------------------------------------------------------------------------------------------------------------------------------------- CREATE USER "test-db-one-henri-2.0" WITH NOCREATEDB NOINHERIT NOCREATEROLE PASSWORD 'etYwfFki^Lio^qjDae3sws2T@mt8p9X9*!yjcM&EKbjxFYoami'; (1 row)

CREATE ROLE Granting membership of 'test-db-one-henri-2.0' to 'alejandro.monje@aholadigital.com'... format ---------------------------------------------------------------------- GRANT "test-db-one-henri-2.0" TO "``first_name.last_name@aholadigital.com``"; (1 row)

GRANT ROLE Creating database 'test-db-one-henri-2.0'... format ------------------------------------------------------------------------ CREATE DATABASE "test-db-one-henri-2.0" OWNER "test-db-one-henri-2.0"; (1 row)

CREATE DATABASE Connecting to new database 'test-db-one-henri-2.0' to set permissions... -> Hardening database permissions... REVOKE GRANT GRANT -> Setting schema permissions... REVOKE format ------------------------------------------------------------------ GRANT CREATE, USAGE ON SCHEMA public TO "test-db-one-henri-2.0"; (1 row)

GRANT format --------------------------------------------- GRANT readwrite TO "test-db-one-henri-2.0"; (1 row)

GRANT ROLE ALTER DEFAULT PRIVILEGES -> Enabling 'pg_stat_statements'... CREATE EXTENSION

--- Playbook execution finished --- Password for test-db-one-henri-2.0: etYwfFki^Lio^qjDae3sws2T@mt8p9X9*!yjcM&EKbjxFYoami

In a existing, you will get most likely the same errors. These error occur because it is trying to create stuff that already exists. More than error should be warnings.