could not create unique index "pg_statistic_relid_att_inh_index" on postgresql 9.3 - FIXED

I have a newly restored database via symantec backup but got a problem.

Scenario:

• Postgresql version: 9.3 - Centos
• Able to access the database, via command line and pgadmin4 only, pgadmin 3 does not work.
• When creating any table, I cannot create, it will not proceed.
• When doing command reindex system DBNAME, i am stuck with the error below.
  
  DBNAME=# reindex system DBNAME;
  NOTICE: table "pg_catalog.pg_class" was reindexed
  ERROR: could not create unique index "pg_statistic_relid_att_inh_index"
  DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated.
  
  LOG:
  Apr 26 14:50:44 stationname postgres[5452]: [10-2] 2017-04-26 14:50:44 PHT postgres DBNAME 127.0.0.1 DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated.
  
  
  Got a tip from one blog I found to check if there were really a duplicate by sql command below: (link of blog: http://www.pateldenish.com/2016/09/recovering-corrupted-postgres-database.html)
  
  select starelid, staattnum, stainherit from pg_catalog.pg_statistic where starelid=2610 order by 2;
  
  
  DBNAME=# select starelid, staattnum, stainherit from pg_catalog.pg_statistic where starelid=2610 order by 2;
  starelid | staattnum | stainherit
  ----------+-----------+------------
  2610 | 1 | f
  2610 | 2 | f
  2610 | 3 | f
  2610 | 4 | f
  2610 | 5 | f
  2610 | 6 | f
  2610 | 7 | f
  2610 | 8 | f
  2610 | 9 | f
  2610 | 10 | f
  2610 | 11 | f
  2610 | 12 | f
  2610 | 13 | f
  2610 | 14 | f
  2610 | 15 | f
  2610 | 16 | f
  2610 | 17 | f
  2610 | 18 | f
  (18 rows)
  
  
  but there wee no duplicates found.
  
  So since I dont have a choice because I cannot seem to create any additional table, then I may need to restore another one from backup, and since I still have a running backup, so before doing another restore, I tried deleting the reported duplicate by command below:
  
  DBNAME=# delete from pg_catalog.pg_statistic where starelid=2610 and staattnum=15;
  DELETE 1
  
  DBNAME=# reindex database DBNAME;
  NOTICE: table "pg_catalog.pg_class" was reindexed
  ERROR: could not create unique index "pg_statistic_relid_att_inh_index"
  DETAIL: Key (starelid, staattnum, stainherit)=(2610, 13, f) is duplicated.
  
  DBNAME=# delete from pg_catalog.pg_statistic where starelid=2610 and staattnum=13;
  DELETE 1
  
  
  After the last deletion of duplicate, I able to reindex system and reindex database and seems those indexes were recreated and my issue on creating any table was fixed.
  
  
  
  

Small bash script to create or reset nginx reverse proxy protected password

Create a file /etc/nginx/listofuser

say it contains username

user1
user2
user3

Then, create a hidden file /etc/nginx/.users


Create a bash script and put it at cron, say daily so once run, it will reset the proxy password to daily and email the result to users on the list with CC on admin.


##############################################################################
#!/bin/bash


for USERNAME in `cat /etc/nginx/listofusers`
do
#Random Pass generation using openssl
RANPASS=`openssl rand -base64 32 | sha256sum | base64 | head -c 8 ; echo`

#htpasswd command
/usr/bin/htpasswd -b /etc/nginx/.users $USERNAME $RANPASS

#email the results to users
echo -e "USER: $USERNAME \nPASSWORD: $RANPASS \n \nPlease login at https://myproxy.example.com \n \nNote: Please expect email notification that it has been changed at the end of the day" | mail -s "password changed at myproxy on `date`" -r noreply@example.com $USERNAME@example.com,admin@example.com

done
###############################################################################

or if you want to load it at command line only and output the user and pass and check the file  /etc/nginx/.users, see below


#################################################################
#!/bin/bash
for USERNAME in `cat /etc/nginx/listofusers`
do

#Random pass generation using openssl (found it by googling)
RANPASS=`openssl rand -base64 32 | sha256sum | base64 | head -c 8 ; echo`

#htpasswd command
/usr/bin/htpasswd -b /etc/nginx/.users $USERNAME $RANPASS

#display the output after execution
echo "USER: $USERNAME"
echo "PASSWORD: $RANPASS"

done
#################################################################

[root@localhost ~]# /usr/local/bin/test.sh
Updating password for user user1
USER: user1
PASSWORD: ZTkxMTQ0
Updating password for user user2
USER: user2
PASSWORD: ZDc4MTMz
Updating password for user user3
USER: user3
PASSWORD: NTIwOGUx

Below are encrypted password file per htpasswd default encryption (CRYPT)

[root@localhost ~]# cat /etc/nginx/.users
user1:$apr1$HJ7/Q/qG$UTO3KujLj3rkANcNpHked1
user2:$apr1$uk59ZMD4$c4zjvMXarVK9zI7nKthz60
user3:$apr1$iIOsYmbh$GdKdkMjwfi4VEHfwTnER60

Below is the portion where you will set on your nginx config under location parameter

    auth_basic "Protected";
    auth_basic_user_file /etc/nginx/.users;

How to import multiple filters of a zimbra users.

To extract the filter of a single user at zimbra cli.


User the command below: example user is testsharedmbox@example.com
as zimbra user at cli, execute:

zmprov ga testsharedmbox@example.com zimbraMailSieveScript

Output below:


# name testsharedmbox@example.com
zimbraMailSieveScript: require ["fileinto", "reject", "tag", "flag"];

# Redirect
if anyof (header :matches ["subject"] "*") {
    redirect "user1@yahoo.com";
    redirect "user2@gmail.com";
    redirect "user3@linux.com";
    keep;
    stop;
}

So just imagine if you have 1000 redirect, so just use a little bash. Convert the above output to become like below as bash executable.

####

zmprov ma testsharedmbox@example.com zimbraMailSieveScript 'require ["fileinto", "reject", "tag", "flag"];

# Redirect
if anyof (header :matches ["subject"] "*") {
    redirect "user1@yahoo.com";
    redirect "user2@gmail.com";
    redirect "user3@linux.com";
    keep;
    stop;
}'

####

Tunnel RDP via ssh

I have a ssh server serve as proxy and i want to tunnel the rdp on it


SSH Server (example only)
Public: 11.11.22.22
Internal: 10.10.1.1

Windows server that I need to RDP via SSH Server
Internal IP: 10.10.1.2

IP of my workstation:
Public: 9.9.9.9 (example only)


So I am going to ssh with below parameters:

ssh -L 12389:10.10.1.2:3389 user@11.11.22.22

Once connected, I can now RDP to 127.0.0.1:12389

So as long as you are connected to 11.11.22.22, RDP is tunneled.

Enabling Transparent Data Encryption on Postgresql 9.3

Well, this is actually copied from this link.

https://translate.google.com/translate?sl=auto&tl=en&js=y&prev=_t&hl=en&ie=UTF-8&u=http%3A%2F%2Fpgsqldeepdive.blogspot.com%2F2015%2F06%2Fpostgresql-nec-tde.html&edit-text=&act=url

But then, below are my steps.


Specs: Current posstgresql server is 9.3.10

yum install --enablerepo=pgdg93 postgresql93-devel gmake make gcc gcc-c++ postgresql93-test yum-utils rpm-build bison flex perl-ExtUtils-Embed "perl(ExtUtils::MakeMaker)" python-devel tcl-devel readline-devel zlib-devel openssl-devel krb5-devel e2fsprogs-devel gettext libxml2-devel libxslt-devel pam-devel uuid-devel openldap-devel openjade opensp docbook-dtds docbook-style-dsssl rpm-build git


It will update the current postgres-9.3 so be sure you have a backup of you current database.


cd /root
mkdir src
cd src
yumdownloader --source postgresql93
rpm -ivh postgresql93-9.3.11-1PGDG.rhel6.src.rpm
cd /root/rpmbuild
rpmbuild -bp SPECS/postgresql-9.3.spec

mkdir /usr/local/src/
cd /usr/local/src/
git clone https://github.com/nec-postgres/tdeforpg.git
ll /root/rpmbuild/BUILD/postgresql-9.3.11/
export PGSRC=/root/rpmbuild/BUILD/postgresql-9.3.11/
export PGHOME=/usr/pgsql-9.3
export TDEHOME=/usr/local/src/tdeforpg
export PATH=/usr/pgsql-9.3/bin/:$PATH

ln -s /usr/pgsql-9.3/lib/pgcrypto.so /usr/lib64/libpgcrypto.so

cd $PGSRC
./configure

cd /usr/local/src/tdeforpg/SOURCES/data_encryption/

ln -s /root/rpmbuild/BUILD/postgresql-9.3.11/src/backend/utils/errcodes.h /root/rpmbuild/BUILD/postgresql-9.3.11/src/include/utils/errcodes.h

sh makedencryption.sh 93 $PGSRC

ln -s /usr/local/src/tdeforpg/SOURCES/data_encryption/93/data_encryption93.so.1.1.1.1 /usr/lib64/data_encryption.so
chown postgres:postgres -R -v /usr/local/src/tdeforpg

Apped the below line at current postgresql.conf

shared_preload_libraries='/usr/lib64/data_encryption.so'
encrypt.enable = on

Then connect to the database that you want the encryption to be enabled.

as super user.

psql -U pgadminuser -h 127.0.0.1 mycompanydb

then execute

create extension pgcrypto;

then exit.


Follow the ff:

cd $TDEHOME/SOURCES
sh bin/cipher_setup.sh $PGHOME


Transparent data encryption feature setup script
Please select from the setup menu below
Transparent data encryption feature setup menu
1: activate  the transparent data encryption feature
2: inactivate the transparent data encryption feature
select menu [1 - 2] > 1
Please enter database server port to connect : 5432
Please enter database user name to connect : pgadminuser
Please enter password for authentication :
Please enter database name to connect : mycompanydb


CREATE LANGUAGE
INFO: Transparent data encryption feature has been activated



cd $TDEHOME/SOURCES
sh bin/cipher_key_regist.sh /usr/pgsql-9.3
=== Database connection information ===
Please enter database server port to connect : 5432
Please enter database user name to connect : pgadminuser
Please enter password for authentication :
Please enter database name to connect : mycompanydb
=== Regist new cipher key ===
Please enter the new cipher key :  "make sure you remember this"
Please retype the new cipher key :
Please enter the algorithm for new cipher key : aes

Are you sure to register new cipher key(y/n) : y

Try to check cipherkey table


[root@MyServer SOURCES]# psql -U pgadminuser -h 127.0.0.1 mycompanydb
Password for user pgadminuser:
psql (9.3.11)
Type "help" for help.

mycompanydb=# select * from cipher_key_table;
                                                                                                 key                                                        
                                         | algorithm
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------+-----------
 \xc38f3e4f1305863e66e2ac3e4eaffe7617e09c7c6f8f3e4f1305863e66e
2ac3e4eaffe7617e09c7c6f8f3e4f1305863e66e2ac3e4eaffe7617e
09c7c6f8f3e4f1305863e66e2ac3e4eaffe7617e09c7c6f

7dacc2290d1ce491c620e0b43702a1f8509656c6 | aes
(1 row)

mycompanydb=#



Then developers will be the one to continue.

Deleting a message per subject via CLI on zimbra

Deleting a message per subject via CLI on zimbra

The scenario:

You want to delete a message that has been accidentally sent to a bunch of zimbra users and you need to delete those message with out accessing the mailbox of those who received one by one on the web. So you might be ending doing it on a CLI mode.

Assuming that you able to open one of the message that you need to delete. If you view the full headers, you will find that it contain inside the header a msgid:

sample:

msgid:609947668.476624.1447370486564.JavaMail.zimbra@my.company.com

Assuming the the senders zimbra domain is my.company.company

the contents of the message ID is common to all that received the common message.

assuming that you have a list file at /tmp/userlists.txt containing

user1@my.company.com
user2@my.company.com
user3@my.company.com
user4@my.company.com
user5@my.company.com


I used this command to generate first the user with message ID for deletion. Make sure to run this under zimbra user.

for i in `cat /tmp/userlists.txt`; do echo $i,`zmmailbox -z -m $i s -t message -l 1 "msgid:609947668.476624.1447370486564.JavaMail.zimbra@my.company.com" |grep mess |awk '{print $2,$3,$4,$5}'`; done |tee -a /tmp/outputlist.txt

I pipe the output at /tmp/outputlist.txt


Sample output that will fill the file is below:


user1@my.company.com,57 mess Userx MYSPAM:
user2@my.company.com,1705 mess Userx MYSPAM:
user3@my.company.com,505 mess Userx MYSPAM:
user4@my.company.com,4705 mess Userx MYSPAM:
user5@my.company.com,9715 mess Userx MYSPAM:


The Numbers are the MSG ID
UserX is the sender
MYSPAM is the Subject.

I intended to do it to display the Sender and Subject so to double check that the message ID is the exact ID i need to delete.

The awk part is the one that will display the tab needed.

Now, I need to generate the email and the message ID.

cat /tmp/outputlist.txt |awk '{priont $1}' > /tmp/fordeletelist.txt

Outbut is now a csv.

user1@my.company.com,57
user2@my.company.com,1705
user3@my.company.com,505
user4@my.company.com,4705
user5@my.company.com,9715


Then use bash IFS for the little script.

while IFS=, read USER ID; do echo deleting message id $ID from $USER; zmmailbox -z -m $USER dm $ID; done < /tmp/fordeletelist.txt

It will search the user and the message ID which is the number on the file and will delete it. If you have a thousand to delete, it will be helpfull.

By the way, google and there are also scripts provided already, just modified it for my need.

Joining CentOS and authenticate to ACtive Directory using winbind

Joining CentOS and authenticate to ACtive Directory using winbind


- Make sure that you have a working DNS that can resolve the domain you are going to join and authenticate the CentOS server. Check /etc/resolv.conf


Install the ff:
yum install authconfig krb5-workstation pam_krb5 samba-common

Execute the command:

Assuming the domain is MYCOMPANY.COM




authconfig --disablecache --enablewinbind --enablewinbindauth --smbsecurity=ads --smbworkgroup=MYCOMPANY --smbrealm=MYCOMPANY.COM --enablewinbindusedefaultdomain --enablekrb5 --krb5realm=MYCOMPANY.COM --enablekrb5kdcdns --enablekrb5realmdns --enablelocauthorize --enablepamaccess --smbidmapuid=16777216-16777300 --krb5kdc=srv001.mycompany.com --krb5adminserver=srv001.mycompany.com --winbindtemplateshell=/bin/bash --updateall


The command above will change the /etc/samba/smb.conf and /etc/krb5.conf

Once done on the authconfig command, issue the command below:

kinit admin.user@MYCOMPANY.COM #This will ask for your password that you use on your AD domain to login, and will tell you if the server was joined successfully.

Once accepted, you may join to domain.

net join -w MYCOMPANY.COM -U admin.user #Again will ask for a password.
/etc/init.d/winbind restart
chkconfig winbind on