Ubuntu 20.04 に Posgtgres12 をインストール後 C# + Npgsql でアクセスしてみる

#目的
Ubuntu 20.04 に Posgtgres をインストールする
VsCode + C\# + Npgsql + .NET Core SDK 3.1 でアクセスしてみる

##インストールの準備
・必要なパッケージをインストールする

<pre>
$ sudo apt-get install curl ca-certificates gnupg
</pre>
・鍵を入れる

<pre>
$ sudo curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
</pre>
・リポジトリの参照リストに追記する。
aptのリポジトリを順にみていくとv13まであるようだ
※http://apt.postgresql.org/pub/repos/apt/dists/focal-pgdg/13/

<pre>
$ sudo apt-add-repository 'deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main'
</pre>
/etc/apt/sources.list (の最後の行)に 

<pre>
deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main
&#35; deb-src http://apt.postgresql.org/pub/repos/apt focal-pgdg main
</pre>
が追加されるが apt update の時に以下のエラーが発生する

<pre>
N: リポジトリ 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' がアーキテクチャ 
'i386' をサポートしないため設定ファイル 'main/binary-i386/Packages' の取得をスキップ
</pre>
これを避けるために以下の様に [arch=amd64] を追加する

<pre>
deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt focal-pgdg main
&#35; deb-src [arch=amd64] http://apt.postgresql.org/pub/repos/apt focal-pgdg main
</pre>

##リポジトリを更新後にPostgresをインストールする
※postgresql-13 のレポジトリは今日現在では使用不可のようだ

<pre>
$ sudo apt update
$ sudo apt install postgresql-12 pgadmin4
</pre>
※RDT + Virtual Box 環境では pgadmin4 は起動しない
※エラーが出た事だけ確認

##インストールログでメモったほうが良さげな項目
設定ファイルの位置 -> /etc/postgresql/12/main/
ログの位置 -> /var/log/postgresql/
pg_ctlcluster 12 main start
※ pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster
デフォルトのmax_connectionsを選択しています ... 100
デフォルトの shared_buffers を選択しています ... 128MB
selecting default time zone ... Asia/Tokyo

##設定ファイルの修正
<pre>
/etc/postgresql/12/main/postgresql.conf の修正
&#35;コメントアウト
&#35;listen_addresses = 'localhost' # what IP address(es) to listen on;

&#35;以下に修正
listen_addresses = '*'           # what IP address(es) to listen on;
</pre>
※IPアドレスは環境に合わせてください・・・

<pre>
/etc/postgresql/12/main/pg_hba.conf を以下に修正
&#35; "local" is for Unix domain socket connections only
&#35;local   all             all                                     peer
local   all             all                                       md5
&#35; IPv4 local connections:
&#35;host    all             all             127.0.0.1/32            md5
host    all             all             192.168.5.0/24            md5
&#35; IPv6 local connections:
&#35;host    all             all             ::1/128                 md5
&#35; Allow replication connections from localhost, by a user with the
&#35;replication privilege.
&#35;local   replication     all                                     peer
&#35;host    replication     all             127.0.0.1/32            md5
&#35;host    replication     all             ::1/128                 md5
</pre>
上記２ファイルを修正後pgsqlを再起動する

<pre>
$ sudo pg_ctlcluster 12 main restart
※ man pg_ctlcluster より
※ pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster
</pre>

##サンプルDBの作成
[PostgreSQL Sample Database](https://www.postgresqltutorial.com/postgresql-sample-database/)より
PostgreSQL DVD Rental sample databaseをDL後
<pre>
$ su - postgres
$ unzip dvdrental.zip
$ createdb --locale=C --encoding=UTF-8 --template=template0 -U postgres dvdrental
$ pg_restore -U postgres -d dvdrental dvdrental.tar
$ psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
 dvdrental | postgres | UTF8             | C           | C                 |
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres

$ psql -U postgres
ユーザ postgres のパスワード:
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
"help"でヘルプを表示します。

postgres=# \c dvdrental
データベース"dvdrental"にユーザ"postgres"として接続しました。
dvdrental=# \d
                       リレーション一覧
 スキーマ |            名前            |     型     |  所有者
----------+----------------------------+------------+----------
 public   | actor                      | テーブル   | postgres
 public   | actor_actor_id_seq         | シーケンス | postgres
 public   | actor_info                 | ビュー     | postgresｑ
 public   | address                    | テーブル   | postgres
 public   | address_address_id_seq     | シーケンス | postgres
 public   | category                   | テーブル   | postgres
 ～
</pre>

##アクセス環境の設定
<pre>
$ su - postgres
$ psql -U postgres
ユーザ postgres のパスワード:
postgres=# create role demo with login password 'passwd';
postgres=# grant connect on database dvdrental to demo;
postgres=# \c dvdrental
※サンプル用のために、全権限を全テーブルに許可する
postgres=# grant all on all tables in schema public to demo;
postgres=# grant all on all sequences in schema public to demo;
postgres=# grant all on all functions in schema public to demo;

postgres=# grant select on actor to demo;
postgres=# grant all on database dvdrental to demo;
</pre>

##サンプルを試してみる
※ Ubuntu2004、Win10 共に同一コードで動作を確認
・VSCode を立ちあげて適当なフォルダを選択する
・コンソールから作成したフォルダ以下で dotnet new console 
~/code/pg0001$ ls
Program.cs  bin  pg0001.csproj  obj
##System.Data.SqlClient&Npgsqlを追加する
表示メニュー
　-> コマンドパレット
　　-> nuget
　　　-> Nuget package Manager:Add Pckage
　　　　-> System.Data.SqlClient を入力後 enter
　　　　　-> System.Data.SqlClient を選択
　　　　　　-> 4.8.1 を選択

　　　　-> Npgsqlを入力後 enter
　　　　　-> Npgsql を選択
　　　　　　-> 4.1.3.1 を選択

pg0001.csproj に以下を追加する(上記で追加後 restore　ボタン で追加される)
<pre>
\<ItemGroup\>
\<PackageReference Include="System.Data.SqlClient" Version="4.8.1"/\>
\<PackageReference Include="Npgsql" Version="4.1.3.1"/\>
\</ItemGroup\>
</pre>
※launch.json
実行->デバックなしで実行　を選択したときに作成される

```cs
using System;
using Npgsql;
using System.Data;
using System.Data.SqlClient;
namespace pg0001
{
    class Program
    {
        static void Main(string[] args)
        {
            var constr = @"Server=192.168.5.44;Port=5432;User Id=demo;Password=passwd;Database=dvdrental";
            NpgsqlConnection con = new NpgsqlConnection(constr);
            con.Open();

            try {
                    NpgsqlDataAdapter da = new NpgsqlDataAdapter("select count(*) as cnt  from actor;", con);
                    DataSet ds = new DataSet();
                    da.Fill(ds, "actor");
                    DataTable tbl = ds.Tables["actor"];           

                    foreach(DataRow row in tbl.Rows)
                    {
                        Console.WriteLine(row[0].ToString());
                    }
            }
            finally {
                con.Close();
            }
        }
    }
}
```
#参考にしたサイトはこちら
[Ubuntu 12.10 > Repository](https://sites.google.com/site/teyasn001/ubuntu-12-10/ripojitori)
[PostgreSQL packages for Debian and Ubuntu](https://wiki.postgresql.org/wiki/Apt)
[PostgreSQL データベース接続ユーザーの作成](https://www.kakiro-web.com/postgresql/postgresql-create-user.html)
[[PostgreSQL] よく使うコマンドまとめ](https://dev.classmethod.jp/articles/postgresql-organize-command/)
[ロールにテーブルやビューなどに対する権限を追加する(GRANT)](https://www.dbonline.jp/postgresql/role/index3.html)
[18．アクセス権の付与](http://www.cgis.biz/others/postgresql/18/)
[PostgreSQLで全てのテーブルにGRANT ALLする方法](https://blog.longkey1.net/2013/02/13/how-to-grant-access-to-all-tables-of-a-database-in-postgres/)
[Npgsql 4.1.3.1](https://www.nuget.org/packages/Npgsql/)
[Npgsql/Installation](https://www.npgsql.org/doc/installation.html)
[.NETライブラリ「Npgsql」によるPostgreSQLの活用](https://www.buildinsider.net/small/npgsql/01)
[Ubuntu 18.04 に .NET Core SDK 3.1 をインストールする](https://qiita.com/tabizou/items/f47983d1d327e6c5d5e1)