Skip to main content

PostgreSQL Onboarding

How to connect to my PostgreSQL instance ?​

Once Postgres single or Flexible server is deployed, you are able to connect with either your personal Thales account or the group name for multiple administrators. those account are used for administration purpose, we recommend to not use them for regular database operations.

In order to connect your application to PostgreSQL, we need to create an internal PostgreSQL account

Step 1 - Create an internal account using psql command line​

In this part, we :

  • Connect to database using your Azure identity
  • Create a user
  • Create a database
  • Grant the user to the database

Prerequisites :

First, you need to retrieve an unique token corresponding to your Azure identity.

There's the corresponding authentication scenario :

img

Log in to azure:

az login

Set your tenant name and pg host:

export TENANT_NAME=<YOUR_TENANT_NAME> # ex: yourproject-sandbox
export FLEXIBLE_INDEX=1 # Updatewith you flexible database name
export PG_HOST="dbaas-${TENANT_NAME}-psql-flex${FLEXIBLE_INDEX}"

echo "$PG_HOST"
# dbaas-yourproject-sandbox-psql-flex1

Connect with psql:

# PostgreSQL host as specified in mail"
#FLEX
export PG_URL="${PG_HOST}.postgres.database.azure.com"

#Use the group name as user
export PG_USER="dbaas-${TENANT_NAME}-psq-flex${FLEXIBLE_INDEX}-aadadmins"
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)

psql "host=${PG_URL} \
      user=${PG_USER} \
      dbname=postgres sslmode=require"

A psql shell should open, connected on database

With psql open:

Create a database named "mydatabase":

CREATE DATABASE mydatabase;

Create an user

CREATE USER mydatabaseuser WITH ENCRYPTED PASSWORD 'acomplexpassword';

Grant access to database

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO mydatabaseuser;
note

You now have a new database with a user granted on it !!!

Exit your psql shell:

exit

Next steps :

Step 2 - Connect an application to your database​

In this step, we'll start a REALLY simple application connected to your PostgreSQL database created in Step 1

This example is based on this nodeJS application : TomFern/dockerizing-nodejs It uses node postgresql driver and Sequelize ORM

The application creates a People table and expose a REST api

Without Docker​

Prerequisites :

  • npm

git clone  https://github.com/TomFern/dockerizing-nodejs.git
cd dockerizing-nodejs/src
export PG_HOST="dbaas-${TENANT_NAME}-psql-flex${FLEXIBLE_INDEX}" ### As delivered by mail
export DB_HOST="${PG_HOST}.postgres.database.azure.com"
export DB_SCHEMA="mydatabase"         # Created in step 1
## Note: Please notice the user@host user
export DB_USER="mydatabaseuser"
export DB_PASSWORD="acomplexpassword" # Create in step 1
## This parameter is not documented in the github, but mandatory for us
export DB_SSL="true"

## install dependencies
yarn install

## Now we can launch the migration task which will create People table
npm run migrate

#Executing (default): CREATE TABLE IF NOT EXISTS "People" ("id"   SERIAL , "firstName" VARCHAR(255) NOT NULL, "lastName" VARCHAR(255)
# ...
note

You can validate the table creation with psql as configured in step 1

postgres-> \c mydatabase You are now connected to database "mydatabase" as user "dbaas-project-sandbox-psq-flex1-aadadmins". mydatabase-> \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------------- public | People | table | mydatabaseuser (1 row)

Then we can start the application in the same window where you define your variables:

npm start
## The application expose an api on localhost:3000
info

Your application is now running, don't close the shell !

In another shell, start adding a user using the api :

curl -w "\n" \
       -X PUT \
       -d "firstName=Tom&lastName=Sawyer" \
       localhost:3000/persons

Tom's contact should have been created :

curl -w "\n" localhost:3000/persons/all
#[
#    {
#        "id": 3,
#        "firstName": "Tom",
#        "lastName": "Sawyer",
#        "createdAt": "2022-07-05T22:54:00.448Z",
#        "updatedAt": "2022-07-05T23:54:00.448Z"
#    }
#]

Using psql , we can see entries in Persons table :

#FLEX
export PG_URL="${PG_HOST}.postgres.database.azure.com"

#Use the group name as user
export PG_USER="mydatabaseuser"
export PGPASSWORD="acomplexpassword"

psql "host=${PG_URL} \
      user=${PG_USER} \
      dbname=postgres sslmode=require"

postgres=> \c mydatabase
psql (15.3, server 13.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "mydatabase" as user "mydatabaseuser".


mydatabase=> select * from "People"
mydatabase-> ;
 id | firstName | lastName |         createdAt          |         updatedAt
----+-----------+----------+----------------------------+----------------------------
  1 | Bobbie    | Draper   | 2022-07-05 22:55:42.381+00 | 2022-07-05 22:55:42.381+00
  2 | Bobbie    | Draper   | 2022-07-05 22:57:32.061+00 | 2022-07-05 22:57:32.061+00
  3 | Tom       | Sawyer   | 2022-07-05 23:00:54.113+00 | 2022-07-05 23:00:54.113+00

With Docker​

git clone  https://github.com/TomFern/dockerizing-nodejs.git
cd applications
 docker build -t addressbook .
docker run -it -p 3000:3000 addressbook

All others steps remains the same as without Docker

Step 3 - Use pgAdmin to operate your database​

pgAdmin is a popular administration and development tool for postgreSQL.

At DBaas, we host a pgAdmin instance (on a Kaas Cluster ;) )

First , you need to fetch an unique token (as already seen in part 1)

Retrieve token​


az login
az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv
## Please copy your token to your clipboard - we'll use it in the next steps

Connect to pgAdmin :

PROD : https://pgadmin.prod.dbaas.thalesdigital.io/browser/

Then Login with Thales Azure AD authentication

img

Then right click on Servers > Register > Server

img

In General tab :

img

Name your server in Name: field (Ex: Dbaas test)

Disable Connect now option

tip

IMPORTANT ! You have to turn off Connect now Option

In Connection tab:

Note down your hostname using the env variable:

echo "$PG_URL"
# dbaas-<TENANT_NAME-ENV>-psql-flex<FLEXIBLE_INDEX>.postgres.database.azure.com
echo "$PG_USER"
# dbaas-<TENANT_NAME-ENV>-psq-flex<FLEXIBLE_INDEX>-aadadmins
warning

To be sure your Group exist you can use these command from the az cli:

az ad group show --group dbaas-<TENANT_NAME-ENV>-**psq**-flex1-aadadmins

e.g. : az ad group show --group dbaas-jiraavs-sandbox-psq-flex1-aadadmins

If the group doesn't exist you should get this error message:

No group matches the name of 'dbaas-jiraavs-sandbox-psql-flex1-aadadmins'

Use these variables to register in the "Connection Tab":

img

In SSL Tab

img

SSL option MUST be "Required"

Then save your Server configuration

Now, right click on the server and select "Connect". you will be prompted for your token . How to get it

img

Enter the token as your password:

 az account get-access-token --resource <https://ossrdbms-aad.database.windows.net>

img

And now, you're connected !

tip

Tip ! If you connect with user mydatabaseuser and password acomplexpassword you can see the table "People" and users created before:

img

Appendix 1 : Install AZ Client​

Please find documentation here

Appendix 2 : Install Postgres Client​

Before you start​

Before you start, you should confirm that you don’t already have psql installed. In fact, if you’ve ever installed Postgres or TimescaleDB before, you likely already have psql installed.

psql --version

Install on MacOS using Homebrew​

First, install the Brew Package Manager

Second, update brew. From your command line, run the following commands:

brew doctor
brew update
brew install libpq

Finally, symlink psql (and other libpq tools) into /usr/local/bin:

brew link --force libpq

Install on Ubuntu 16.04,18.04 and Debian 9,10​

Install on Ubuntu and Debian using the apt package manager:

sudo apt-get update
sudo apt-get install postgresql-client

Note: This only installs the psql client and not the PostgreSQL database.

Install Windows 10​

We recommend using the installer from PostgreSQL.org

Last step: Connect to your PostgreSQL server​

Let’s confirm that psql is installed:

psql --version