PostgreSQL 보안

1. PostgreSQL 보안 인증 수단

  trust, ident, password, GSSAPI, SSPI, LDAP, PAM 등등

2. 보안 인증 방법

    pg_hba.conf : 인증 제어 (host-based authentication)

    (설정이 변경되면 sighup 시그널을 보내서 적용)

    psql -U postgres -c "SELECT pg_reload_conf();"

   service postgresql reload

   /etc/init.d/postgresql reload

   kill -HUP process_id

    

   1) 항목 내용

      host_type database user [IP-address| address] [IP-mask] auth-method  [auth-options]

     - host_type 종류 

      Local : sock connection  사용

      Host : remote에서 ip address 사용하여 접속 (SSL 사용 또는 사용하지 않거나)

      Hostssl : SSL로 암호화된 연결

      Hostnossl : 암호화 되지 않은 연결

     - database user : all 또는 데이터베이스 이름

     - 사용예

       Local    all    all                      trust    -- socket으로 접속

  Host    all    all        127.0.0.1/32     trust    -- local loopback ip로 접속

  Host    all    all        ::1/128          trust

  Host    all    all        92.168.0.53/32    reject  -- 해당 ip 접속 거부

  Host    all    all        92.168.0.1/24     trust

3. Listen address 설정

   postgresql-conf 설정에 접속할 client에 대한 listen_addresses 설정

   psql   -c   "ALTER SYSTEM SET listen_addresses = '';"

   /etc/init.d/postgresql  restart

   psql -h localhost -c 'SELECT 1';

4. public에서 권한 revoke

   REVOKE ALL PRIVILEGES ON SCHEMA PUBLIC FROM public;

5. Role 부여로 보안설정

   CREATE ROLE web_app LOGIN NOINHERIT;

   CREATE ROLE public_user NOLOGIN;

   GRANT SELECT ON car_portal_app.advertisement_picture,    

   car_portal_app.advertisement_rating , car_portal_app.advertisement TO public_user;

   GRANT public_user TO web_app;

   GRANT USAGE ON SCHEMA car_portal_app TO web_app, public_user;

6. Postgresql Security level

  1) database base level

    CREATE DATABASE test;

    REVOKE ALL ON DATABASE test FROM public;

    GRANT CONNECT ON DATABASE car_portal TO web_app;

  2) schema base level

     GRANT USAGE ON SCHEMA test TO u1, u2;

  3) table base level

     GRANT ALL ON <table name> to <role>;

  4) column base level

      GRANT SELECT (f1) ON test1 TO test1_role; 

      GRANT USAGE ON SCHEMA public TO test_role;

      SET ROLE test_role;

      SELECT f1 FROM public.test1;

  

  5) row base level 

     9.5 버전부터 지원예정

7. encrypting data

  CREATE ROLE <role_name> WITH LOGIN PASWWORD 'role_password';

   SELECT usename, passwd FROM pg_shadow WHERE usename IN ('a');

  \password  -- password 변경

  2) 데이터 암호화

    CREATE EXTENSION pgcrypto;

    - one 방향 암호화 (md5, hash..) 

    INSERT INTO account VALUES (1, crypt ('my password', gen_salt('md5'))), (2, crypt ('my password', gen_salt('md5')));  -- md5 알고리즘으로 자동 생성 salt로 암호문 생성

    SELECT crypt('my password', gen_salt('bf',16));  -- blowfish 알고리즘으로 생성

   

    - 양방향 암호화 (encrypt, decrypt)

      \df encrypt

      \df decrypt

     SELECT encrypt ('Hello World', 'my key', 'aes');   -- aes 암호화

     SELECT decrypt ('\\xe3d6d1ddea318dbf88e34421fd095727', 'my key' , 'aes');

     SELECT convert_from('\\x48656c6c6f20576f726c64', 'utf-8');

     

    gpg --gen-key

    gpg --list-secret-key

    gpg -a --export 2ccerwe > /var/lib/postgresql/9.4/main/public.key

    gpg -a --export-secret-key  34234 > /var/lib/postgresql/9.4/main/secret.key

    chown postgres:postgres /var/lib/postgresql/9.4/main/public.key

   chown postgres:postgres /var/lib/postgresql/9.4/main/secret.key

   CREATE OR REPLACE FUNCTION encrypt (text) RETURNS bytea AS

   $$

   BEGIN

       RETURN pgp_pub_encrypt($1, dearmor(pg_read_file('public.key')));

   END;

   $$ Language plpgsql;

    CREATE OR REPLACE FUNCTION decrypt (bytea) RETURNS text AS

    $$

    BEGIN

       RETURN  pgp_pub_decrypt($1, dearmor(pg_read_file('secret.key')));

    END;

    $$ Language plpgsql;

   SELECT decrypt(encrypt('hello'));