POSTGRES queries logging

Decription

To provide security standart compliance like PCI DSS 10.2.2 (All actions taken by any
individual with root or administrative privilege), HIPAA and others all administrators actions must be logged.

To provide this requirement Postgres has mutliple options. Most company will be satisfacted with the folowing solution

Solution

This methon will sufficient if your application communicates with PostgreSQL  with dedicated user and you don’t need to log such requests.

Add the folowing parameters to your postgres.conf

log_statement = 'all'  #Log any requests
log_destination = 'stderr,syslog'  #log to file(if you need) and log to syslog
syslog_facility = 'LOCAL0' #default value

Edit your rsyslog configuration:

$EscapeControlCharactersOnReceive off # Remove special characters from log #011
*.*   @192.168.0.10:514  #send to remote rsyslog server

#### RULES ####
local0.* ~ #Don't log local, we'll send it to remote server. Should be the first rule

Alter appplication postgres user to ignore it all queries log:

Alter role APPUSER set log_statement to 'none';

 

Read log

Multiple line entries may be read with my script

cat /var/log/sql/requests.log|awk 'BEGIN{sqlid=""};{idcur=substr($6,2,match($6,"-")-2)};sqlid==idcur{for(i=7;i<=NF;++i)buf=buf " " $i};sqlid!=idcur{buf=buf "\n\n";print buf;sqlid=idcur;buf=$0;}'|grep --color user

It groups line with the same connection ID.

 

 

List all schema’s function code:

 psql -U postgres my_db  -c '\df+ partman.*'| \
cut -d '|' -f2,10|sed -s 's/                               //g'|less

View global avtovacuum settings:

select name, setting, unit
from pg_settings 
where name like 'autovacuum%';

Show single table avtovacuum configuration

select relname, reloptions
from pg_class where reloptions::TEXT like '%autovacuum%';

Set vacuum and analyse scale factor and threshold for a table:

ALTER TABLE piblic.log SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE piblic.log SET (autovacuum_vacuum_threshold = 1000000);
ALTER TABLE piblic.log SET (autovacuum_analyze_scale_factor = 0.1);
ALTER TABLE piblic.log SET (autovacuum_analyze_threshold = 1000000);

List Postgres tables and schemas in tablespaces

select relname, nspname as schema, spcname as tablespace from 
  pg_class cl, 
  pg_namespace ns, 
  pg_tablespace ts 
where 
  cl.relnamespace=ns.oid and 
  cl.reltablespace=ts.oid and 
  relkind='r'; 

Example output:

      relname       |   schema   | tablespace 
--------------------+------------+------------
 pg_authid          | pg_catalog | pg_global
 pg_database        | pg_catalog | pg_global
 pg_db_role_setting | pg_catalog | pg_global
 pg_tablespace      | pg_catalog | pg_global
 pg_pltemplate      | pg_catalog | pg_global
 pg_auth_members    | pg_catalog | pg_global
 pg_shdepend        | pg_catalog | pg_global
 pg_shdescription   | pg_catalog | pg_global
 pg_shseclabel      | pg_catalog | pg_global

Elasticsearch 2.0 update by query

Unfortunately ES 2.0  doesn’t have _update_by_query function support even via plugins. So lest’s bash it!

Get data

First of all fetch all data (I expect not more 9999) need to be deleted: host is «UA-web1», message contains «Response from bank» and not contans «censored». I’ll put all data in pretty (field by line) mode to the file raw_list.
curl -XGET http://localhost:9200/logstash-2016.03*/_search?pretty=true -d {'
"query": {
"bool": {
"must": [
{ "match": { "host": "UA-web1"}},
{ "match": { "message": "Response"}},
{ "match": { "message": "from"}},
{ "match": { "message": "bank"}}
],
"must_not": {
"match": { "message": "censored" }
}}},
"fields": "message",
"size": 9999
'} > raw_list

Format list to update

Then parse raw_list to format «id index»:
AVON8Q5Hv9AoJbRnorhQ logstash-2016.03.19
AVON-P9Av9AoJbRnot7r logstash-2016.03.19
AVOOIA5hv9AoJbRno6Nu logstash-2016.03.19
AVOOFnFVv9AoJbRno2wT logstash-2016.03.19
AVOOKeW1v9AoJbRno9SO logstash-2016.03.19
Here is my AWK script:
cat raw_list |awk -F'"' '/_id/{id=$4} /_index/{ind=$4} /_score/{print id,ind}' > list_to_delete

Update records

Now file list_to_delete contains lines with IDs of records to update.

CURL query will update a record with _id=$ID and _index=$INDEX:

curl -XPOST http://localhost:9200/$INDEX/esyslog/$ID/_update?pretty -d '{
doc: {"message": "This message was fixed" }
}'

So read list_to_delete in cycle and update records:

cat list_to_delete|while read line; do arr=($line);curl -XPOST http://localhost:9200/${arr[1]}/esyslog/${arr[0]}/_update?pretty -d '{
doc: {"message": "This message was fixed" }
}'; done

That’s all!

Mysql Replication Cluster

Master-Slave

node1 — master

Edit mysqld config

[mysqld]
server-id = 1
log-bin = /var/lib/mysql/mysql-bin 
replicate-do-db = somedb

Grand replicate permissoin to user replication on database somedb:

mysql@master>  GRANT replication slave ON "somedb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";

Then restart daemon

service mysqld restart

Set ReadOnly mode:

mysql@master> SET GLOBAL read_only = ON;

Make dump:

 /usr/bin/mysqldump --single-transaction --flush-logs --routines --events somedb > master.dump

View binlog inforamation:

mysql@master> SHOW MASTER STATUS\G
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:

Remove ReadOnly mode:

mysql@master> SET GLOBAL read_only = OFF;

node2 — slave

Edit mysqld config:

server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = somedb

Then restart daemon

service mysqld restart

Restore from master dump:

mysql somedb < master.dump

Configure slave replication:

mysql@replica> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password", MASTER_LOG_FILE = "mysql-bin.000003", MASTER_LOG_POS = 98;

Start replication:

mysql@replica> start slave;

View status

mysql@replica> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.001152
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb,testdb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 5

Стандартные задачи по обслуживанию базы на MS SQL

Обрезаем лог-файл

BACKUP LOG <dbname> WITH NO_LOG DBCC shrinkdatabase(N'<dbname>’)

upd. Для MS SQL 2008 следует использовать следующую конструкцию:
USE <имя базы >
GO
BACKUP LOG <имя базы &gt; TO DISK=’NUL:’
go
DBCC SHRINKFILE (N'<Логическое имя логфайла базы >’ , 1)
GO

Реиндексация базы данных

CREATE PROCEDURE [dbo].[sp_ReindexDatabase] AS
BEGIN
SET NOCOUNT ON
DECLARE @ID int
INSERT INTO _Reindexes(StartTime, HostName, UserName) VALUES(GETDATE(), HOST_NAME(), USER_NAME())
SET @ID = SCOPE_IDENTITY()
DECLARE reindex_cursor CURSOR
FOR SELECT name FROM sysobjects WHERE type = ‘U’
OPEN reindex_cursor
DECLARE @tablename sysname
FETCH NEXT FROM reindex_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXECUTE (‘DBCC DBREINDEX (‘ +@tablename + ‘)’)
FETCH NEXT FROM reindex_cursor INTO @tablename
END
CLOSE reindex_cursor
DEALLOCATE reindex_cursor
EXEC sp_updatestats @resample=’resample’
UPDATE _Reindexes SET EndTime=GETDATE() WHERE ReindexID=@ID
END
GO

Резервное копирование базы

CREATE PROCEDURE sp_AutoBackup AS
DECLARE @FileName varchar(50), @FilePath varchar(200), @ArhPath varchar(200),  @Date datetime, @CMD varchar(200)
DECLARE @D int, @M int, @Y int, @H int, @N int
SET @Date = GETDATE()
SET @D=DATEPART(dd, @Date)
SET @M=DATEPART(mm, @Date)
SET @Y=DATEPART(yy, @Date)
SET @H=DATEPART(hh, @Date)
SET @N=DATEPART(mi, @Date)
SET @FileName=’DB_backup’
IF @Y<10 SET @FileName=@FileName+’_0’+LTRIM(STR(@Y)) ELSE SET @FileName=@FileName+’_’+LTRIM(STR(@Y))
IF @M<10 SET @FileName=@FileName+’_0’+LTRIM(STR(@M)) ELSE SET @FileName=@FileName+’_’+LTRIM(STR(@M))
IF @D<10 SET @FileName=@FileName+’_0’+LTRIM(STR(@D)) ELSE SET @FileName=@FileName+’_’+LTRIM(STR(@D))
IF @H<10 SET @FileName=@FileName+’_0’+LTRIM(STR(@H)) ELSE SET @FileName=@FileName+’_’+LTRIM(STR(@H))
IF @N<10 SET @FileName=@FileName+’0’+LTRIM(STR(@N)) ELSE SET @FileName=@FileName+LTRIM(STR(@N))
SET @FilePath=’C:\’+@FileName+’.bak’
SET @ArhPath=’U:\backup\’+@FileName+’.rar’
DECLARE @BAKFile varchar(200)
BACKUP DATABASE [<db_name>] TO DISK = @FilePath WITH  NOINIT ,  NOUNLOAD ,  NAME = N'<db_name> backup’,  NOSKIP ,  STATS = 10,  NOFORMAT
SET @Cmd = ‘rar.exe a ‘+@ArhPath+»+@FilePath+’ -p<пароль> -m3  -df -ep’
PRINT @cmd
EXEC master..xp_cmdshell @CMD
GO