今だから確認したDBセキュリティの基本のSQLサンプル

bytea.sql

create table fruit2(id serial,name bytea); --bytea型はバイナリ型

/* データを fruit から fruit2 にコピーして、表示してみます。*/

select * from fruit2;
 id |      name
----+----------------
  1 | \x6170706c65
  2 | \x6f7261676e65
  3 | \x7065616368
(3 行)

disp_console2.txt

2018-12-17 01:39:12.082 GMT [1976] FATAL:  password authentication failed for user "postgres"
2018-12-17 01:39:12.082 GMT [1976] DETAIL:  Password does not match for user "postgres".
	Connection matched pg_hba.conf line 81: "host    all             all             ::1/128                 md5"

display_console.sh

# OSユーザー名と同じDBユーザーを作成
createuser -U postgres -d -P -I -E [OS username]

# データベースを作成
createdb userdb

# データベース一覧の確認
psql -l

encrypt.sql

/* 有効化 */
create extension pgcrypto;

/* 暗号化してデータを格納 */
update fruit2 SET name = encrypt(name, 'pass'::bytea, 'AES');

/* 復号化して表示 */
select convert_from(decrypt(name, 'pass'::bytea,'AES'),'UTF8') as name from fruit2;

grant.sql

create user user01 with password 'pass';
grant connect on database userdb to user01; --user01 で接続できることを確認

/* データベースに誰も接続できないようにしてみます */
revoke all on database userdb from PUBLIC;

hba.conf

# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.

role.sql

/* group01 という役割を作ってユーザを紐づけ */
create role group01 login;

create role user02 login --ユーザ2を割当
connection limit 1
password 'pass' in role group01;