From a947a31ede27fdf995e0a63e766fcd68eb491426 Mon Sep 17 00:00:00 2001 From: Silvino Silva Date: Fri, 7 Feb 2020 03:41:45 +0000 Subject: System configuration update --- tools/postgresql.html | 252 ++++++++++++++++++++++++++++---------------------- 1 file changed, 140 insertions(+), 112 deletions(-) (limited to 'tools/postgresql.html') 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 -

2. Configure Server

+

1.1. Configure syslog-ng

-

2.1. Init script

- -

Change /etc/rc.d/postgresql;

+

Configure syslog-ng first, configuration example contains rules for postgresql as is configured in this document.

-        #
-        # /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");};
+
- # End of file +

Create /etc/logrotate.d/postgres;

+ + 
+        /var/log/pgsql {
+            weekly
+            compress
+            delaycompress
+            rotate 10
+            notifempty
+            create 660 postgres postgres
+        }
-

2.2. Certificates

+

1.2. Certificates

         $ sudo openssl genrsa -des3 -out /etc/ssl/keys/pg.key 2048
@@ -115,209 +121,231 @@
         $ sudo chmod 644 /etc/ssl/certs/pg.cert
-

2.3. Super user password

+

2. Configure Server

-

Create password for super user;

+

2.1. Init script

+ +

Change /etc/rc.d/postgresql;

-        # 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
-

2.4. Configure postgresql.conf

+

2.2. Configure postgresql.conf

Edit /srv/pgsql/data/postgresql.conf;

-        # - 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:
-

2.5. Configure pg_hba.conf

+

2.3. Super user password

-

Edit - /srv/pgsql/data/pg_hba.conf; -

+

Create password for the super user postgres, login to postgresql;

-        # 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
-

Start server and alter postgres password

+

Create password for postgres user;

-        # /etc/rc.d/postgresql start
-
- - 
-        postgres=# alter user postgres with password 'new_password';
+        postgres=# \password
+        Enter new password:
+        Enter it again:
+        postgres=#
-

2.6. Configure syslog-ng

+

Configure pg_hba.conf in the next step to enforce authentication.

-

Configure Syslog-ng, check Michael at otacoo article. Example;

+

2.4. Configure pg_hba.conf

-

Edit /pgsql/data/postgresql.conf;

+

Edit + /srv/pgsql/data/pg_hba.conf; + 

-        log_destination = 'syslog' # Can specify multiple destinations
-        syslog_facility='LOCAL0'
-        syslog_ident='postgres'
-        log_connections = on
-        log_disconnections = on
-        log_duration = on
-
- -

Create /etc/logrotate.d/postgres;

+ # TYPE DATABASE USER ADDRESS METHOD - 
-        /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
+

Restart server to enforce authentication from now on;

-        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
+

3. Manage users

-

3. Create User

+

3.1. Create user - create role

Create a new user with createuser command;

         $ 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:
-

4. Create Database

+

3.2. Remove user - drop role

-

Create a new database for new user with createdb command;

+

Deleting user with dropuser command;

-        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
-        --owner=user_example db_example
+        sudo -u postgres dropuser user_name
-

5. Drop Database

+

3.3. Change password

-

Deleting database with dropdb command;

+

Update password of a user;

-        sudo -u postgres dropdb db_example
+        $ sudo -u postgres psql
-

6. Drop User

+ 
+        postgres=#\password user_name;
+
-

Deleting user with dropuser command;

+

This will set password using hash / encryption method selected on postgresql.conf

+ +

3.4. List users - roles

+ + 
+        $ sudo -u postgres psql
+        
-        sudo -u postgres dropuser user_example
+        postgres=# \dg
-

7. Psql

+

4. Manage databases

-

Lets check with psql, login with user postgres;

+

4.1. Create database

+ +

Create a new database named db_name for user_name with createdb command;

-        $ sudo -u postgres psql
+        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
+        --owner=user_name db_name
-

First show help;

+

4.2. Drop database

+ +

Deleting database with dropdb command;

-        postgres=# \?
+        sudo -u postgres dropdb db_name
-

7.1. List Databases and Roles

+

4.3. List databases

List roles then list databases;

-        postgres=# \dg
         postgres=# \l
-

Connect to a datase;

+

4.4. Dump and restore

+ +

Dump all databases

-        postgres=# \c db_example
+        $ pg_dumpall -U postgres | gzip > cluster_dump.gz
-

List tables;

+

Restore dumpfile of all databases;

-        postgres=# \dt
+        $ gzip -c cluster_dump.gz | psql -U postgres
-

7.2. Create Database

+

Restore a database;

-        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
-

7.3. Drop All Tables

+

5. Psql

-

This example assumes that all tables, - are in public schema. First revoke previously granted privileges from one or more roles;

+

Lets check with psql, login with user postgres;

-        postgres=# revoke ALL PRIVILEGES on db_example from user_example;
+        $ sudo -u postgres psql
-

Drop all tables on public schema and recreate public schema;

+

First show help;

-        postgres=# \c db_example
-        db_example=# drop schema public cascade;
-        db_example=# create schema public;
+        postgres=# \?
-

7.4. Change user password

+

Connect to a db_name as user_name;

-

Update password of a user;

+ 
+        postgres=# \c db_name user_name
+
+ +

5.2. Create Database

-        postgres=# ALTER USER user_example WITH ENCRYPTED PASSWORD 'password';
+        postgres=# create database db_name owner user_name encoding 'UTF-8' template template0;
-

8. Backup and restore

+

5.3. Drop All Tables

-

8.1. Dump databases

+

This example assumes that all tables, + are in public schema. First revoke previously granted privileges from one or more roles;

-        $ pg_dumpall -U postgres | gzip > cluster_dump.gz
+        postgres=# revoke ALL PRIVILEGES on db_name from user_name;
-

8.2. Restore

+

Drop all tables on public schema and recreate public schema;

-        $ gzip -c cluster_dump.gz | psql -U postgres 
+        postgres=# \c db_name
+        db_name=# drop schema public cascade;
+        db_name=# create schema public;
Tools Index -- cgit 1.4.1-2-gfad0