PostgreSQL 13のデータベースの作成

スポンサーリンク

CentOS8.2に、PostgreSQLにデータベースを作成し、アプリケーションから操作できるまでの一連の操作をまとめます。

データベースの作成

データベースの作成 – デフォルト設定

PostgreSQLでデータベースを作成するには、OSからcreatedbコマンドを実行するか、postgresqlにログインしCREATE DABASE文を実行するかのいずれの方法を使用します。

createdbコマンドを使用する例は以下の通りです。

[postgres@centos82 ~]$ createdb newdb1
[postgres@centos82 ~]$ 

CREATE DATABASE文を使う例は以下の通りです。

[postgres@centos82 ~]$ psql
psql (13.1)
Type "help" for help.

postgres=# CREATE DATABASE newdb2 ;
CREATE DATABASE
postgres=# 

データベースが作成された事を確認します。データベース作成時にencodeを指定しない場合は、template1の値がコピーされます。template1と同じencoding, collate, ctypeが設定されている事を確認します。

[postgres@centos82 ~]$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 newdb1    | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 newdb2    | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 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
(5 rows)

[postgres@centos82 ~]$ 

データベースの作成 – encode指定

データベース作成時にencodeなどを明示的に指定する事もできます。createdbコマンドを使用する例は以下の通りです。

[postgres@centos82 ~]$ dropdb newdb1
[postgres@centos82 ~]$ createdb newdb1 \
> --encoding=EUC_JP \
> --lc-collate=C \
> --lc-ctype=C \
> --template=template0
[postgres@centos82 ~]$ 

CREATE DATABASE文を使う例は以下の通りです。

postgres=# DROP DATABASE newdb2;
DROP DATABASE
postgres=# CREATE DATABASE newdb2 
postgres-#   TEMPLATE = 'template0'
postgres-#   ENCODING = 'SQL_ASCII';
CREATE DATABASE
postgres=# 

想定通りのデータベースが作成された事を確認します。

[postgres@centos82 ~]$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding  |   Collate   |    Ctype    |   Access privileges   
-----------+----------+-----------+-------------+-------------+-----------------------
 newdb1    | postgres | EUC_JP    | C           | C           | 
 newdb2    | postgres | SQL_ASCII | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 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
(5 rows)

[postgres@centos82 ~]$

データベースの作成 – template1の最作成

特段の指定をしない場合はtemplate1のencodingの設定がコピーされます。この設定を変更したい場合は、template1の再作成が必要です。

template1の再作成の操作例を以下に記します。

ALTER DATABASE template1 is_template false;
DROP DATABASE template1 ;
CREATE DATABASE template1 
  TEMPLATE = template0
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8';
ALTER DATABASE template1 is_template true;

サンプルデータベースのインポート

動作確認のためにPostgreSQLチュートリアルとして使用されるデータベースをインポートします。

まずはデータベースを作成します。

createdb dvdrental

postgresユーザで以下コマンドを実行し、サンプルデータベースをインポートします。なお、URLがリンク切れになっていましたら、ブラウザでPostgreSQLチュートリアルのサイトを開き、最新のURLを確認してください。

wget https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
unzip dvdrental.zip
pg_restore -d dvdrental dvdrental.tar
rm dvdrental.tar dvdrental.zip 

テーブル群が作成された事を確認します。

[postgres@centos82 ~]$ psql -d dvdrental 
psql (13.1)
Type "help" for help.

dvdrental=# \d
                     List of relations
 Schema |            Name            |   Type   |  Owner   
--------+----------------------------+----------+----------
 public | actor                      | table    | postgres
 public | actor_actor_id_seq         | sequence | postgres
 public | actor_info                 | view     | postgres
 public | address                    | table    | postgres
 public | address_address_id_seq     | sequence | postgres
 public | category                   | table    | postgres
 public | category_category_id_seq   | sequence | postgres
 public | city                       | table    | postgres

ユーザの作成

一般ユーザの作成

ユーザを作成するにはOSからcreateuserコマンドを使用するか、postgresqlにログインしCREATE USER文を実行するかのいずれの方法を使用します。

createuserコマンドの使用例を以下に示します。-S(–no-superuser), -D(–no-createdb), -R(–no-createrole) -P(–pwprompt)は文字通りの意味ですが、man createuserで詳細情報を得る事もできます。

[postgres@centos82 ~]$ createuser -S -D -R -P user01
Enter password for new role: 
Enter it again: 
[postgres@centos82 ~]$

CREATE USER文を使う例は以下の通りです。また、\passwordでパスワードを設定する事もできます。

[postgres@centos82 ~]$ psql
psql (13.1)
Type "help" for help.

postgres=# CREATE USER user02;
CREATE ROLE
postgres=# \password user02
Enter new password: 
Enter it again: 
postgres=# 

権限の付与

user01に全権限を、user02にSELECT権限を与えます。

PostgreSQLはデフォルトでpublicという名前のスキーマに属し、以下DDLはpublicという名前のスキーマに所属する全てのテーブル, シーケンス, ファンクションに対して権限を与える設定例です。

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user01;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user01;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO user01;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user02;

user01とuser02に権限が付与された事を確認します。

[postgres@centos82 ~]$ psql dvdrental;
psql (13.1)
Type "help" for help.

dvdrental=# \dp
                                             Access privileges
 Schema |            Name            |   Type   |     Access privileges     | Column privileges | Policies 
--------+----------------------------+----------+---------------------------+-------------------+----------
 public | actor                      | table    | postgres=arwdDxt/postgres+|                   | 
        |                            |          | user01=arwdDxt/postgres  +|                   | 
        |                            |          | user02=r/postgres         |                   | 
 public | actor_actor_id_seq         | sequence |                           |                   | 
 public | actor_info                 | view     | postgres=arwdDxt/postgres+|                   | 
        |                            |          | user01=arwdDxt/postgres  +|                   | 
        |                            |          | user02=r/postgres         |                   | 
 public | address                    | table    | postgres=arwdDxt/postgres+|                   | 
        |                            |          | user01=arwdDxt/postgres  +|                   | 
        |                            |          | user02=r/postgres         |                   | 

 <omitted>

ログイン確認および権限の確認

さきほどの手順で作成したユーザでPostgreSQLへログインできるかどうかを確認します。

psqlコマンドのシンタックスは以下の通りです。ユーザ名を指定してログインするには、データベース名の後にユーザ名を指定します。

psql [option...] [dbname [username]]

ログイン動作確認をするには、–host=127.0.0.1を指定してください。これは後述のpg_hba.confのデフォルト設定が、–hostを指定した場合に接続不可になっているためです。

user01の場合はSELECTとINSERTが可能である事を確認します。

INSERTを実行するにはINSERTの権限付与だけではなくテーブルに付随するシーケンスも権限付与が必要です。

[postgres@centos82 ~]$ psql --host=127.0.0.1 dvdrental user01
Password for user user01: 
psql (13.1)
Type "help" for help.

dvdrental=> SELECT * FROM actor LIMIT 3;
 actor_id | first_name | last_name |      last_update       
----------+------------+-----------+------------------------
        1 | Penelope   | Guiness   | 2013-05-26 14:47:57.62
        2 | Nick       | Wahlberg  | 2013-05-26 14:47:57.62
        3 | Ed         | Chase     | 2013-05-26 14:47:57.62
(3 rows)

dvdrental=> 
dvdrental=> INSERT INTO actor(first_name, last_name) values ('taro', 'yamada');
INSERT 0 1
dvdrental=> 

user02の場合はSELECTのみ可能である事を確認します。

[postgres@centos82 ~]$ psql --host=127.0.0.1 dvdrental user02
Password for user user02: 
psql (13.1)
Type "help" for help.

dvdrental=> SELECT * FROM actor LIMIT 3;
 actor_id | first_name | last_name |      last_update       
----------+------------+-----------+------------------------
        1 | Penelope   | Guiness   | 2013-05-26 14:47:57.62
        2 | Nick       | Wahlberg  | 2013-05-26 14:47:57.62
        3 | Ed         | Chase     | 2013-05-26 14:47:57.62
(3 rows)

dvdrental=> 
dvdrental=> INSERT INTO actor(first_name, last_name) values ('jiro', 'yamada');
ERROR:  テーブル actor へのアクセスが拒否されました
dvdrental=> 

リモート接続の許可

listen_addresses

PostgreSQLはデフォルトでリモートからの接続を許可していません。デフォルト設定ではローカルホスト(127.0.0.1)
に対してのみlistenしている状態です。

[root@centos82 ~]# ss -ano | grep 5432
u_str              LISTEN              0                    128                                                     /tmp/.s.PGSQL.5432 29618                  * 0                                                                               
u_str              LISTEN              0                    128                                      /var/run/postgresql/.s.PGSQL.5432 29616                  * 0                                                                               
tcp                LISTEN              0                    128                                                              127.0.0.1:5432             0.0.0.0:*                                                                               
tcp                LISTEN              0                    128                                                                  [::1]:5432                [::]:*                                                                               
[root@centos82 ~]# 

postgresql.confを以下のように編集します。

$ vi ${PGDATA}/postgresql.conf

 <omitted>

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
listen_addresses = '0.0.0.0'

再起動で設定を反映させます。

systemctl restart postgresql-13.service

全てのIPアドレス(0.0.0.0)に対してlitsten状態になった事を確認します。

[root@centos82 ~]# ss -ano | grep 5432
u_str              LISTEN              0                    128                                                     /tmp/.s.PGSQL.5432 41022                  * 0                                                                               
u_str              LISTEN              0                    128                                      /var/run/postgresql/.s.PGSQL.5432 41020                  * 0                                                                               
tcp                LISTEN              0                    128                                                                0.0.0.0:5432             0.0.0.0:* 

pg_hba.conf

リモートからのアクセスを許可するには、postgresql.confのlisten_addressesを編集するだけでなく、pg_hba.confの編集も必要です。

hbaはost-based authenticationの略称で、どのデータベースへ、誰が、どのネットワークから接続する事を許可するかの定義です。METHOD列は認証方法の定義で、peer, password, md5, scram-sha-256などを指定する事ができますが、パスワードが暗号化されるmd5またはscram-sha-256が推奨です。

設定例は以下の通りです。

$ vi ${PGDATA}/postgresql.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv4 remote connections:
host   dvdrental        user01          192.168.0.0/16          scram-sha-256 
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

再起動によって設定を反映させます。

systemctl restart postgresql-13.service 

リモートのホストからの接続が可能である事を確認します。

[root@centos80 ~]# psql --host=192.168.63.82 dvdrental user01
ユーザ user01 のパスワード: 
psql (13.1)
"help"でヘルプを表示します。

dvdrental=> 

クライアント側の文字コード設定

デフォルトでは文字コードはデータベースに対して設定されたencodingの値が使用されますが、クライアント側で上書きする事もできます。以下に上書き方法を3通り示します。

いずれの設定方法もコネクションが維持されている間のみ有効でコネクションが切断されればリセットされます。

\encoding

\encodingコマンドで文字コードを上書きする事ができます。

[postgres@centos82 ~]$ psql
psql (13.1)
Type "help" for help.

postgres=# \encoding
UTF8
postgres=# \encoding sjis
postgres=# \encoding
SJIS
postgres=# 

CLIENT_ENCODING

変数CLIENT_ENCODINGを設定する事で文字コードを上書く事ができます。

[postgres@centos82 ~]$ psql
psql (13.1)
Type "help" for help.

postgres=# \encoding 
UTF8
postgres=# SET CLIENT_ENCODING TO 'SJIS';
SET
postgres=# \encoding 
SJIS
postgres=# 

libpg

PostgreSQLに接続する時に使用するC言語ライブラリlibpgに与える変数PQclientEncodingで文字コードを指定する事ができます。高級言語を使用する場合はlibpgを直接操作する事はなく、間接的にPQclientEncodingを指定することになります。例えば、pythonを使った場合の実装例は以下のようになります。

import psycopg2

connection = psycopg2.connect(\ 
    host='192.168.63.82',\ 
    user='user01',\ 
    password='P@ssw0rd',\ 
    database='dvdrental') 
connection.set_client_encoding('sjis') 
タイトルとURLをコピーしました