supabase の Postgres に直接接続する

• あるWebサービスの開発メモ・目次ページに戻る

やりたいこと

supabase におけるデータベース操作は app.supabase.com の　SQLEditor を使っていました。これはこれでよいのですが、psql でデータベースを直接操作した方が、圧倒的に生産性が向上しそうなので、この psql によるデータベース接続を目指してみます

方式検討

• DockerHub の PotgreSQL 公式イメージから、ローカルPCの　psql 実行環境を構築する
• ローカルPC から PostgreSQL 間は SSL 接続する

公式の情報

• 接続

docker-compose

docker-compose.yml

% pwd
/Users/c22/Dev/psql
% ls
docker-compose.yml
% cat docker-compose.yml
version: '3'

services:
  db:
    image: postgres:14
    container_name: 'psql14'
    hostname: 'psql14'
    ports:
      - '5432:5432'
    stdin_open: true
    tty: true
    volumes:
      - ./db-store:/var/lib/postgresql/data
      - ./Download:/var/lib/postgresql/Download
    environment:
      - POSTGRES_PASSWORD=hogehoge
volumes:
  db-store:

%

• ./db-store でデータを永続化しようとしています
• ./Download　に サーバールート証明書を配置するつもりです

docker-compose up

% docker-compose up
[+] Running 1/1
 ⠿ Container psql14  Created                                                                                                                                                                                                           0.1s
Attaching to psql14
psql14  | The files belonging to this database system will be owned by user "postgres".

(snip)

コンテナ接続

c22@c22noMac-mini psql % docker container ls -a
CONTAINER ID   IMAGE            COMMAND                  CREATED        STATUS                    PORTS                    NAMES
3ab2d28beb96   ruby:3.1.3       "irb"                    8 days ago     Exited (1) 31 hours ago                            ruby313
b37079e2e92c   centos:centos7   "/bin/bash"              8 days ago     Exited (137) 6 days ago                            centos7
2631edc32829   node:latest      "docker-entrypoint.s…"   11 days ago    Up 7 days                 0.0.0.0:3000->3000/tcp   unrwebpagemui
09e86eb0f09b   node:latest      "docker-entrypoint.s…"   3 months ago   Up 3 weeks                0.0.0.0:3006->3000/tcp   sirokuro-dev6
c22@c22noMac-mini psql % docker container ls
CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS          PORTS                    NAMES
f499111d41f4   postgres:14   "docker-entrypoint.s…"   29 seconds ago   Up 29 seconds   0.0.0.0:5432->5432/tcp   psql14
2631edc32829   node:latest   "docker-entrypoint.s…"   11 days ago      Up 7 days       0.0.0.0:3000->3000/tcp   unrwebpagemui
09e86eb0f09b   node:latest   "docker-entrypoint.s…"   3 months ago     Up 3 weeks      0.0.0.0:3006->3000/tcp   sirokuro-dev6
c22@c22noMac-mini psql % docker container exec -it psql14 /bin/bash
root@psql14:/# pwd
/
root@psql14:/# ls
bin  boot  dev	docker-entrypoint-initdb.d  etc  home  lib  media  mnt	opt  proc  root  run  sbin  srv  sys  tmp  usr	var
root@psql14:/#

vim インストール

• Linux ディストリビューション確認

root@psql14:~# cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
NAME="Debian GNU/Linux"
VERSION_ID="11"
VERSION="11 (bullseye)"
VERSION_CODENAME=bullseye
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"
root@psql14:~#

• インストール (スーパーユーザにて)
  • apt-get update
  • apt-get install vim

接続情報とサーバールート証明書を取得

• app.supabase.com
• (左下) [ProjectSettings] > [Database]
  • Connection info　の Host が接続文字列になるのでメモしておく
• SSL Connection の [Download Certificate]
  • 証明書がダウンロードされる

クライアント接続

証明書を配置する

(ローカルPCのターミナル)

% pwd
/Users/c22/Dev/psql/Download
%

ここにダウンロードした証明書を prod-ca-2021.cer というファイル名で配置する

DB接続

(コンテナの /bin/bash で su - postgres しています)

postgres@psql14:~/Download$ pwd
/var/lib/postgresql/Download
postgres@psql14:~/Download$ psql "sslmode=verify-full sslrootcert=prod-ca-2021.cer host=db.cyznbgxxxxxxxxxxxxxx.supabase.co dbname=postgres user=postgres"
Password for user postgres:
psql (14.6 (Debian 14.6-1.pgdg110+1), server 14.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

DB操作

DB一覧

postgres=> \l
                                 List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |      Access privileges
-----------+----------+----------+---------+---------+-----------------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres               +
           |          |          |         |         | postgres=CTc/postgres      +
           |          |          |         |         | dashboard_user=CTc/postgres
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres                +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres                +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)

postgres=> 

DB切替

postgres=> \c postgres
psql (14.6 (Debian 14.6-1.pgdg110+1), server 14.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "postgres".
postgres=> 

テーブル一覧

postgres=> \dt;
              List of relations
 Schema |   Name    | Type  |     Owner
--------+-----------+-------+----------------
 public | cats      | table | supabase_admin
 public | comments  | table | supabase_admin
 public | commentsg | table | supabase_admin
 public | posts     | table | supabase_admin
 public | profiles  | table | supabase_admin
 public | replies   | table | supabase_admin
 public | votes     | table | supabase_admin
(7 rows)

postgres=>

テーブル構造の表示

select

まとめ

DockerHub の PostgreSQL 公式イメージのおかげで、macOS 上で容易に supabase.com の Postgres にリモート接続できるようになった。