Решил попробовать прилепить авторизацию по ключам клиентов в PostgreSQL.
Код: Выделить всё
>uname -a
FreeBSD free9 9.0-PRERELEASE FreeBSD 9.0-PRERELEASE
Код: Выделить всё
pkg_info | grep postgre
postgresql-client-9.1.2 PostgreSQL database (client)
postgresql-server-9.1.2 The most advanced open-source database available anywhere
Скрипт создает корневой сертификат, подписывает ним сертификат сервера и создает сертификат пользователя pgsql.
Код: Выделить всё
#!/bin/sh
# Скрипт генерирует ключ для PostgreSQL server
# ----------------------------------------------------------------------------------------
# Создавать ли сертификат сервера yes/no
server_sert="yes"
# Создавать ли клиентские сертификаты yes/no
client_sert="yes"
# Использовать пароль при создании сертификатов
password="no"
srv="PostgreSQL_9.0"
user="pgsql"
user_server="pgsql"
user_server_group="pgsql"
email="root@localhost
valid_day_server="1200"
valid_day_client="1200"
bit_server=1024
bit_client=1024
server_data_folder="/usr/local/pgsql/data"
cert_root_dir="$server_data_folder/cert_server"
subject_server="/C=UA/ST=sity/L=sity/O=WorkGroup/OU=$srv/CN=$srv/emailAddress=$email"
subject_client="/C=UA/STsity/L=sity/O=WorkGroup/OU=$srv/CN=$user/emailAddress=$email"
# -----------------------------------------------------------------------------------------
# ------------------------ Server key -------------------------------------------------
if [ $server_sert = "yes" ]; then
rm $server_data_folder/server.* $server_data_folder/root.*
if [ $password = "yes" ]; then
openssl genrsa -des3 -rand /dev/random -out $server_data_folder/root.key $bit_server
else
openssl genrsa -rand /dev/random -out $server_data_folder/root.key $bit_server
fi
openssl rsa -in $server_data_folder/root.key -out $server_data_folder/root.key
openssl req -new -key $server_data_folder/root.key -days $valid_day_server -out $server_data_folder/root.crt \
-x509 -subj $subject_server
openssl req -new -newkey rsa:$bit_server -nodes -keyout $server_data_folder/server.key -subj $subject_server \
-out $server_data_folder/server.csr
openssl x509 -req -days $valid_day_server -in $server_data_folder/server.csr -signkey $server_data_folder/server.key \
-out $server_data_folder/server.crt -CA $server_data_folder/root.crt -CAkey $server_data_folder/root.key -CAcreateserial
chmod 400 $server_data_folder/server.*
chown $user_server:$user_server_group $server_data_folder/server.*
# ---
rm -rf $cert_root_dir
mkdir $cert_root_dir $cert_root_dir/certs $cert_root_dir/crl $cert_root_dir/newcerts
echo "01" > $cert_root_dir/crlnumber
echo "01" > $cert_root_dir/serial
touch $cert_root_dir/index.dat
echo "
[ ca ]
default_ca =CA_CLIENT
[ CA_CLIENT ]
dir = $cert_root_dir # Where everything is kept
certs = $cert_root_dir # Where the issued certs are kept
crl_dir = $cert_root_dir # Where the issued crl are kept
database = $cert_root_dir/index.dat # database index file.
#unique_subject = no # Set to 'no' to allow creation of
# several ctificates with same subject.
new_certs_dir = $cert_root_dir # default place for new certs.
certificate = $cert_root_dir/root.crt # The CA certificate
serial = $cert_root_dir/serial # The current serial number
crlnumber = $cert_root_dir/crlnumber # the current crl number
# must be commented out to leave a V1 CRL
crl = $cert_root_dir/root.crl # The current CRL
private_key = $cert_root_dir/root.key # The private key
RANDFILE = /dev/random # private random number file
x509_extensions = usr_cert # The extentions to add to the cert
default_days = 365 # how long to certify for
default_crl_days= 30 # how long before next CRL
default_md = sha1 # which md to use.
preserve = no # keep passed DN ordering" > $cert_root_dir/ca.cnf
cp $server_data_folder/root.crt $cert_root_dir/root.crt
cp $server_data_folder/root.key $cert_root_dir
openssl ca -config $cert_root_dir/ca.cnf -gencrl -out $cert_root_dir/root.crl > /dev/null 2>&1
cp $cert_root_dir/root.crl $server_data_folder
fi
# ------------------------ Klient ----------------------------------------------------------
if [ $client_sert = "yes" ]; then
rm $server_data_folder/$user.crt $server_data_folder/$user.csr $server_data_folder/$user.key
if [ $password = "yes" ]; then
openssl genrsa -des3 -rand /dev/random -out $server_data_folder/$user.key $bit_client
else
openssl genrsa -rand /dev/random -out $server_data_folder/$user.key $bit_client
fi
openssl rsa -in $server_data_folder/$user.key -out $server_data_folder/$user.key
openssl req -new -key $server_data_folder/$user.key -days $valid_day_client -out $server_data_folder/$user.csr -subj $subject_client
openssl x509 -req -days $valid_day_client -in $server_data_folder/$user.csr -signkey $server_data_folder/$user.key \
-out $server_data_folder/$user.crt -CA $server_data_folder/root.crt -CAkey $server_data_folder/root.key -CAcreateserial
chmod 400 $server_data_folder/$user.*
chown $user_server:$user_server_group $server_data_folder/$user.*
fi
После перезапуска postgrsqle в его логах нет ни какой ругани и сервер работает.
Далее проверяю работу ssl из Windows через Pgadmin 1.14
Если в файле pg_hba.conf
Указать так:
Код: Выделить всё
hostssl all all 192.168.0.0/24 md5
Pgadmin рапортует о ssl защищенном соединении с сервером.
Если в файле pg_hba.conf
Указать так:
Код: Выделить всё
hostssl all all 192.168.0.0/24 cert
Вопросы:
1. Подскажите что я делаю не так?
2. Как проверить в freebsd клиентский сертификат?
Спасибо.
