System configuration update

1 files changed, 140 insertions, 112 deletions

diff --git a/tools/postgresql.html b/tools/postgresql.html
index 1fb48c7..141d6c2 100644
--- a/tools/postgresql.html
+++ b/tools/postgresql.html
@@ -26,32 +26,38 @@
         # sudo -u postgres initdb -D /srv/pgsql/data
         </pre>
 
-        <h2 id="config">2. Configure Server</h2>
+        <h3 id="syslog-ng">1.1. Configure syslog-ng</h3>
 
-        <h3>2.1. Init script</h3>
-
-        <p>Change <a href="conf/etc/rc.d/postgresql">/etc/rc.d/postgresql</a>;</p>
+        <p><a href="syslog-ng.html">Configure syslog-ng</a> first, configuration example contains rules for postgresql as is configured in this document.</p>
 
         <pre>
-        #
-        # /etc/rc.d/postgresql: start, stop or restart PostgreSQL server postmaster
-        #
+        destination d_postgres  { file("/var/log/pgsql"); };
+        filter f_postgres { facility(local0) and program("postgresql)"; };
+        log { source(s_src); filter(f_postgres); destination(d_postgres);};
 
-        PG_DATA=/srv/pgsql/data
+        filter f_messages { level(info,notice,warn)
+                    and not facility(auth,authpriv,cron,daemon,mail,news,local0); };
 
-        case "$1" in
-            start|stop|status|restart|reload)
-                sudo -u postgres pg_ctl -D "$PG_DATA" -l /var/log/postgresql "$1"
-                ;;
-            *)
-                echo "usage: $0 start|stop|restart|reload|status"
-                ;;
-        esac
+        filter f_daemon { facility(daemon, local0)
+        and not filter(f_debug)
+        and not program("vh_tribu")
+        and not program("vh_tribu_error");};
+        </pre>
 
-        # End of file
+        <p>Create /etc/logrotate.d/postgres;</p>
+
+        <pre>
+        /var/log/pgsql {
+            weekly
+            compress
+            delaycompress
+            rotate 10
+            notifempty
+            create 660 postgres postgres
+        }
         </pre>
 
-        <h3>2.2. Certificates</h3>
+        <h3 id="gencert">1.2. Certificates</h3>
 
         <pre>
         $ sudo openssl genrsa -des3 -out /etc/ssl/keys/pg.key 2048
@@ -115,209 +121,231 @@
         $ sudo chmod 644 /etc/ssl/certs/pg.cert
         </pre>
 
-        <h3>2.3. Super user password</h3>
+        <h2 id="server">2. Configure Server</h2>
 
-        <p>Create password for super user;</p>
+        <h3 id="init">2.1. Init script</h3>
+
+        <p>Change <a href="conf/etc/rc.d/postgresql">/etc/rc.d/postgresql</a>;</p>
 
         <pre>
-        # su postgres
-        $ psql -U postgres
+        #
+        # /etc/rc.d/postgresql: start, stop or restart PostgreSQL server postmaster
+        #
+
+        PG_DATA=/srv/pgsql/data
+
+        case "$1" in
+            start|stop|status|restart|reload)
+                sudo -u postgres pg_ctl -D "$PG_DATA" -l /var/log/postgresql "$1"
+                ;;
+            *)
+                echo "usage: $0 start|stop|restart|reload|status"
+                ;;
+        esac
+
+        # End of file
         </pre>
 
-        <h3>2.4. Configure postgresql.conf</h3>
+        <h3 id="config">2.2. 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 = scram-sha-256
+        jit = off
+        log_destination = 'syslog'
+        syslog_facility = 'LOCAL0'
+        log_connections = on
+        log_disconnections = on
+        log_duration = on
+        log_hostname = on
+        log_line_prefix = 'd=$d u=% %m [%p] '  # special values:
         </pre>
 
-        <h3>2.5. Configure pg_hba.conf</h3>
+        <h3 id="pass">2.3. Super user password</h3>
 
-        <p>Edit
-        <a href="conf/srv/pgsql/data/pg_hba.conf">/srv/pgsql/data/pg_hba.conf</a>;
-        </p>
+        <p>Create password for the super user postgres, login to postgresql;</p>
 
         <pre>
-        # TYPE  DATABASE        USER            ADDRESS                 METHOD
-        local   postgres        all             trust
-        host    postgres        all             127.0.0.1/32            trust
-        host    all             all             127.0.0.1/32            scram-sha-256
-        host all all 0.0.0.0/0 reject
+        $ sudo -u postgres psql -U postgres
         </pre>
 
-        <p>Start server and alter postgres password</p>
+        <p>Create password for postgres user;</p>
 
         <pre>
-        # /etc/rc.d/postgresql start
-        </pre>
-
-        <pre>
-        postgres=# alter user postgres with password 'new_password';
+        postgres=# \password
+        Enter new password:
+        Enter it again:
+        postgres=#
         </pre>
 
-        <h3 id="syslog">2.6. Configure syslog-ng</h3>
+        <p>Configure pg_hba.conf in the next step to enforce authentication.</p>
 
-        <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>
+        <h3 id="pg_hba">2.4. Configure pg_hba.conf</h3>
 
-        <p>Edit /pgsql/data/<a href="../conf/srv/pgsql/data/postgresql.conf">postgresql.conf</a>;</p>
+        <p>Edit
+        <a href="conf/srv/pgsql/data/pg_hba.conf">/srv/pgsql/data/pg_hba.conf</a>;
+        </p>
 
         <pre>
-        log_destination = 'syslog' # Can specify multiple destinations
-        syslog_facility='LOCAL0'
-        syslog_ident='postgres'
-        log_connections = on
-        log_disconnections = on
-        log_duration = on
-        </pre>
-
-        <p>Create /etc/logrotate.d/postgres;</p>
+        # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
-        <pre>
-        /var/log/pgsql {
-            weekly
-            compress
-            delaycompress
-            rotate 10
-            notifempty
-            create 660 postgres postgres
-        }
+        # "local" is for Unix domain socket connections only
+        local   all             postgres                                scram-sha-256
+        #local   all             postgres                                trust
+        # IPv4 local connections:
+        host    all             postgres        127.0.0.1/32            scram-sha-256
+        # IPv6 local connections:
+        host    all             postgres        ::1/128                 scram-sha-256
+        # Allow replication connections from localhost, by a user with the
+        # replication privilege.
+        local   replication     postgres                                 scram-sha-256
+        host    replication     postgres         127.0.0.1/32            scram-sha-256
+        host    replication     postgres         ::1/128                 scram-sha-256
         </pre>
 
+        <p>Restart server to enforce authentication from now on;</p>
 
         <pre>
-        destination postgres { file("/var/log/pgsql"); };
-        filter f_postgres { facility(local0); };
-        log { source(s_log); filter(f_postgres); destination(postgres); };
+        # /etc/rc.d/postgresql start
         </pre>
 
+        <h2 id="users">3. Manage users</h2>
 
-        <h2 id="createuser">3. Create User</h2>
+        <h3 id="createuser">3.1. Create user - create role</h3>
 
         <p>Create a new user with createuser command;</p>
 
         <pre>
         $ sudo -u postgres createuser --pwprompt --encrypted \
-        --no-createrole --no-createdb user_example
+        --no-createrole --no-createdb user_name
         Enter password for new user:
         Enter it again:
         </pre>
 
-        <h2 id="createdb">4. Create Database</h2>
+        <h3 id="dropuser">3.2. Remove user - drop role</h3>
 
-        <p>Create a new database for new user with createdb command;</p>
+        <p>Deleting user with dropuser command;</p>
 
         <pre>
-        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
-        --owner=user_example db_example
+        sudo -u postgres dropuser user_name
         </pre>
 
-        <h2 id="dropdb">5. Drop Database</h2>
+        <h3 id="userpass">3.3. Change password</h3>
 
-        <p>Deleting database with dropdb command;</p>
+        <p>Update password of a user;</p>
 
         <pre>
-        sudo -u postgres dropdb db_example
+        $ sudo -u postgres psql
         </pre>
 
-        <h2 id="dropuser">6. Drop User</h2>
+        <pre>
+        postgres=#\password user_name;
+        </pre>
 
-        <p>Deleting user with dropuser command;</p>
+        <p>This will set password using hash / encryption method selected on postgresql.conf</p>
+
+        <h3 id="listuser">3.4. List users - roles</h3>
+
+        <pre>
+        $ sudo -u postgres psql
+        </pre>
 
         <pre>
-        sudo -u postgres dropuser user_example
+        postgres=# \dg
         </pre>
 
-        <h2 id="psql">7. Psql</h2>
+        <h2 id="databases">4. Manage  databases</h2>
 
-        <p>Lets check with psql, login with user postgres;</p>
+        <h3 id="createdb">4.1. Create database</h3>
+
+        <p>Create a new database named db_name for user_name with createdb command;</p>
 
         <pre>
-        $ sudo -u postgres psql
+        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
+        --owner=user_name db_name
         </pre>
 
-        <p>First show help;</p>
+        <h3 id="dropdb">4.2. Drop database</h3>
+
+        <p>Deleting database with dropdb command;</p>
 
         <pre>
-        postgres=# \?
+        sudo -u postgres dropdb db_name
         </pre>
 
-        <h3 id="listdb">7.1. List Databases and Roles</h3>
+        <h3 id="listdb">4.3. List databases</h3>
 
         <p>List roles then list databases;</p>
 
         <pre>
-        postgres=# \dg
         postgres=# \l
         </pre>
 
-        <p>Connect to a datase;</p>
+        <h3 id="backup">4.4. Dump and restore</h3>
+
+        <p>Dump all databases</p>
 
         <pre>
-        postgres=# \c db_example
+        $ pg_dumpall -U postgres | gzip > cluster_dump.gz
         </pre>
 
-        <p>List tables;</p>
+        <p>Restore dumpfile of all databases;</p>
 
         <pre>
-        postgres=# \dt
+        $ gzip -c cluster_dump.gz | psql -U postgres 
         </pre>
 
-        <h3 id="psqldb">7.2. Create Database</h3>
+        <p>Restore a database;</p>
 
         <pre>
-        postgres=# create database db_example_ext owner user_example encoding 'UTF-8' template template0;
+        $ cat db_name_dump | psql -U user_name -d db_name
         </pre>
 
-        <h3 id="droptables">7.3. Drop All Tables</h3>
+        <h2 id="psql">5. Psql</h2>
 
-        <p>This example assumes that all tables,
-        are in public schema. First revoke previously granted privileges from one or more roles;</p>
+        <p>Lets check with psql, login with user postgres;</p>
 
         <pre>
-        postgres=# revoke ALL PRIVILEGES on db_example from user_example;
+        $ sudo -u postgres psql
         </pre>
 
-        <p>Drop all tables on public schema and recreate public schema;</p>
+        <p>First show help;</p>
 
         <pre>
-        postgres=# \c db_example
-        db_example=# drop schema public cascade;
-        db_example=# create schema public;
+        postgres=# \?
         </pre>
 
-        <h3 id="userpass">7.4. Change user password</h3>
+        <p>Connect to a db_name as user_name;</p>
 
-        <p>Update password of a user;</p>
+        <pre>
+        postgres=# \c db_name user_name
+        </pre>
+
+        <h3 id="psqldb">5.2. Create Database</h3>
 
         <pre>
-        postgres=# ALTER USER user_example WITH ENCRYPTED PASSWORD 'password';
+        postgres=# create database db_name owner user_name encoding 'UTF-8' template template0;
         </pre>
 
-        <h2 id="backup">8. Backup and restore</h3>
+        <h3 id="droptables">5.3. Drop All Tables</h3>
 
-        <h3>8.1. Dump databases</h3>
+        <p>This example assumes that all tables,
+        are in public schema. First revoke previously granted privileges from one or more roles;</p>
 
         <pre>
-        $ pg_dumpall -U postgres | gzip > cluster_dump.gz
+        postgres=# revoke ALL PRIVILEGES on db_name from user_name;
         </pre>
 
-        <h3>8.2. Restore</h3>
+        <p>Drop all tables on public schema and recreate public schema;</p>
 
         <pre>
-        $ gzip -c cluster_dump.gz | psql -U postgres 
+        postgres=# \c db_name
+        db_name=# drop schema public cascade;
+        db_name=# create schema public;
         </pre>
 
         <a href="index.html">Tools Index</a>