diff options
Diffstat (limited to 'tools/postgresql.html')
| -rw-r--r-- | tools/postgresql.html | 95 |
1 files changed, 92 insertions, 3 deletions
diff --git a/tools/postgresql.html b/tools/postgresql.html index b8790e2..ca31a4d 100644 --- a/tools/postgresql.html +++ b/tools/postgresql.html @@ -26,6 +26,10 @@ # sudo -u postgres initdb -D /srv/pgsql/data </pre> + <h2 id="config">2. Configure Server</h2> + + <h3>2.1. Init script</h3> + <p>Change <a href="conf/etc/rc.d/postgresql">/etc/rc.d/postgresql</a>;</p> <pre> @@ -47,7 +51,71 @@ # End of file </pre> - <h2 id="config">2. Configure Server</h2> + <h3>2.2. Certificates</h3> + + <pre> + $ sudo openssl genrsa -des3 -out /etc/ssl/keys/pg.key 2048 + Password: + Generating RSA private key, 2048 bit long modulus + ..............................+++ + ............+++ + e is 65537 (0x10001) + Enter pass phrase for /etc/ssl/keys/pg.key: + Verifying - Enter pass phrase for /etc/ssl/keys/pg.key: + </pre> + + <p>Create ceritificate signing request. For "Common Name" + provide domain name or ip address, leave challange password + and optional company name blank;</p> + + <pre> + $ sudo openssl req -x509 -in server.req -text -key /etc/ssl/keys/pg.key -out /etc/ssl/certs/pg.crt + + Enter pass phrase for /etc/ssl/keys/pg.key: + You are about to be asked to enter information that will be incorporated + into your certificate request. + What you are about to enter is what is called a Distinguished Name or a DN. + There are quite a few fields but you can leave some blank + For some fields there will be a default value, + If you enter '.', the field will be left blank. + ----- + Country Name (2 letter code) [AU]:PT + State or Province Name (full name) [Some-State]: + Locality Name (eg, city) []: + Organization Name (eg, company) [Internet Widgits Pty Ltd]: + Organizational Unit Name (eg, section) []: + Common Name (e.g. server FQDN or YOUR name) []:core.privat-network.net + Email Address []: + + Please enter the following 'extra' attributes + to be sent with your certificate request + A challenge password []: + An optional company name []: + $ + </pre> + + <p>Having password is a good idea, but requires it every + time pg is restarted. To remove;</p> + + <pre> + $ sudo cp /etc/ssl/keys/pg.key /etc/ssl/keys/pg.key.pass + $ sudo openssl rsa \ + -in /etc/ssl/keys/pg.key.pass \ + -out /etc/ssl/keys/pg.key + </pre> + + <pre> + Enter pass phrase for /etc/ssl/keys/pg.key.pass: + writing RSA key + </pre> + + <pre> + $ sudo chown postgres:postgres /etc/ssl/keys/pg.key* + $ sudo chmod 0600 /etc/ssl/keys/pg.key* + $ sudo chmod 644 /etc/ssl/certs/pg.cert + </pre> + + <h3>2.3. Super user password</h3> <p>Create password for super user;</p> @@ -56,6 +124,28 @@ $ psql -U postgres </pre> + <h3>2.4. Configure postgresql.conf</h3> + + <p>Edit <a href="conf/srv/pgsql/data/postgresql.conf">/srv/pgsql/data/postgresql.conf</a>;</p> + + <pre> + # - Security and Authentication - + + #authentication_timeout = 1min # 1s-600s + ssl = on # (change requires restart) + #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers + # (change requires restart) + #ssl_prefer_server_ciphers = on # (change requires restart) + #ssl_ecdh_curve = 'prime256v1' # (change requires restart) + ssl_cert_file = '/etc/ssl/certs/pg.crt' # (change requires restart) + ssl_key_file = '/etc/ssl/keys/pg.key' # (change requires restart) + #ssl_ca_file = '' # (change requires restart) + #ssl_crl_file = '' # (change requires restart) + password_encryption = on + </pre> + + <h3>2.5. Configure pg_hba.conf</h3> + <p>Edit <a href="conf/srv/pgsql/data/pg_hba.conf">/srv/pgsql/data/pg_hba.conf</a>; </p> @@ -87,7 +177,7 @@ postgres=# alter user postgres with password 'new_password'; </pre> - <h3 id="syslog">2.1. Configure syslog-ng</h3> + <h3 id="syslog">2.6. Configure syslog-ng</h3> <p><a href="syslog-ng.html">Configure Syslog-ng</a>, check <a href="http://michael.otacoo.com/postgresql-2/postgres-settings-simple-syslog-configuration-with-syslog-ng/">Michael at otacoo</a> article. Example;</p> @@ -98,7 +188,6 @@ syslog_facility='LOCAL0' syslog_ident='postgres' log_connections = on - password_encryption=on </pre> |