Ansible ssh connection closed error

Problem:
Ansible returns the error "Failed to connect to the host via ssh: Shared connection to host closed"


Solution:
Modify ansible.cfg to send a SSH keep-alive ping every 2 minutes.

ssh_args = -C -o ControlMaster=auto -o ControlPersist=60s -o ServerAliveInterval=120

AWS terraform: Use DynamoDB locking

Please see the previous post on how to set up terraform to use a remote AWS S3 bucket to store the terraform.tfstate file (http://www.javajirawat.com/2017/07/aws-terraform-use-s3-remote-tfstate-file.html) . This example continues from the S3 bucket article.

Remote terraform state file allows multiple terraform servers to manage the same resources. However if two people try to modify the same terraform state at the same time, it may lead to corruption and errors. Terraform can be configured to use AWS DynamoDB to lock the state file and prevent concurrent edits.

AWS Dynamo DB is a cloud NoSQL key-value database.

Setup:

Create an AWS DynamoDB with terraform to lock the terraform.tfstate.

1.) Create terraform main.tf for AWS DynamoDB
     See https://github.com/juttayaya/devops/blob/master/hashicorp/terraform/s3-tfstate-example/dynamodb/main.tf

provider "aws" {
  region = "us-east-1"
}

resource "aws_dynamodb_table" "dynamodb-terraform-lock-example" {
  name = "terraform-lock-example"
  hash_key = "LockID"
  read_capacity = 5
  write_capacity = 5

  attribute {
    name = "LockID"
    type = "S"
  }

  tags {
    Name = "Terraform Lock Table Example"
    Org = "JavaJirawat"
  }
}

You can name the DynamoDB table to anything you wish. The hash_key must be a String attribute named LockID


2.) Execute main.tf to create the DynamoDB table on AWS
      Run the command

      terraform apply

      The AWS account that executes terraform needs AmazonDynamoDBFullAccess permission in the region you are creating the database table
https://console.aws.amazon.com/iam/home?region=us-east-1#/policies/arn:aws:iam::aws:policy/AmazonDynamoDBFullAccess

Usage:

Here is an example of using the DynamoDB table we just created to lock a terraform.tfstate for a AWS EC2 resource.

1.) Create terraform main.tf for AWS EC2 server with a S3 backend to store the terraform.tfstate file and a DynamoDB table to lock it.

      See https://github.com/juttayaya/devops/blob/master/hashicorp/terraform/s3-tfstate-example/ec2-with-locking/main.tf

terraform {
  backend "s3" {
    bucket = "terraform-s3-tfstate-example"
    region = "us-east-1"
    key = "example/ec2-with-locking/terraform.tfstate"
    dynamodb_table = "terraform-lock-example"
    encrypt = true
  }
}

provider "aws" {
  region = "us-east-1"
}

# Amazon Linux AMI
resource "aws_instance" "ec2-with-locking-example" {
    count = 1
    ami = "ami-a4c7edb2"
    instance_type = "t2.micro"
    
    lifecycle {
      create_before_destroy = true
    }

    tags {
      Name = "Example for DynamoDB lock"
      Org = "JavaJirawat"
    }      
}

The dynamodb_table value must match the name of the DynamoDB table we created.

2.) Initialize the terraform S3 backend
     Run the command

     terraform init

     Type in "yes" for any prompt.

3.) Execute main.tf to create the EC2 server on AWS
      Run the command

      terraform apply

      The AWS account that executes terraform needs AmazonEC2FullAccess permission in the region you are creating the EC2 server
https://console.aws.amazon.com/iam/home?region=us-east-1#/policies/arn:aws:iam::aws:policy/AmazonEC2FullAccess

AWS Terraform: Use S3 remote tfstate file

Terraform uses an text file called terraform.tfstate to store the state of the infrastructure it manages.  (https://www.terraform.io/docs/state/) . If multiple terraform servers manage the same resources, this file needs to be remotely accessible. To prevent accidental deletion or corruption, terraform.tfstate should be versioned.

Amazon S3 (Simple Storage Service) fulfills the above requirements. S3 is a cloud file storage service; basically the AWS version of Dropbox.

Setup:

Create an AWS S3 bucket with terraform to store terraform.tfstate
1.) Create terraform main.tf for AWS S3 bucket.
     See https://github.com/juttayaya/devops/blob/master/hashicorp/terraform/s3-tfstate-example/s3/main.tf

provider "aws" {
  region = "us-east-1"
}

resource "aws_s3_bucket" "s3-tfstate-example" {
    bucket = "terraform-s3-tfstate-example"
    acl = "private"

    versioning {
      enabled = true
    }

    lifecycle {
      prevent_destroy = true
    }

    tags {
      Name = "Terraform S3 tfstate Example"
      Org = "JavaJirawat"
    }      
}

The above configuration turns on S3 versioning so you can query for the history of infrastructure changes. The prevent_destroy = true guards against accidental deletion.

2.) Execute main.tf to create the S3 bucket on AWS
      Run the command

      terraform apply

      The AWS account that executes terraform needs AmazonS3FullAccess permission in the region you are creating the S3 bucket
https://console.aws.amazon.com/iam/home?region=us-east-1#/policies/arn:aws:iam::aws:policy/AmazonS3FullAccess

Usage:

Here is an example of using the S3 bucket we just created to store a terraform.tfstate for a AWS EC2 resource.

1.) Create terraform main.tf for AWS EC2 server with a S3 backend to store the terraform.tfstate file.

     See https://github.com/juttayaya/devops/blob/master/hashicorp/terraform/s3-tfstate-example/ec2/main.tf

terraform {
  backend "s3" {
    bucket = "terraform-s3-tfstate-example"
    region = "us-east-1"
    key = "example/ec2/terraform.tfstate"
    encrypt = true
  }
}

provider "aws" {
  region = "us-east-1"
}

# Amazon Linux AMI
resource "aws_instance" "ec2-example" {
    count = 1
    ami = "ami-a4c7edb2"
    instance_type = "t2.micro"
    
    lifecycle {
      create_before_destroy = true
    }

    tags {
      Name = "Example for S3 tfstate"
      Org = "JavaJirawat"
    }      
}

The terraform backend bucket name and region must match the S3 bucket name and region we created. The key is the full folder path and filename to store the terraform.tfstate file

2.) Initialize the terraform S3 backend
     Run the command

     terraform init

     Type in "yes" for any prompt.

3.) Execute main.tf to create the EC2 server on AWS
      Run the command

      terraform apply

      The AWS account that executes terraform needs AmazonEC2FullAccess permission in the region you are creating the EC2 server
https://console.aws.amazon.com/iam/home?region=us-east-1#/policies/arn:aws:iam::aws:policy/AmazonEC2FullAccess

Ansible Online Notes

Online notes on technical issues I encountered using Ansible and the resolution. Posting online in case I need to refer to it again

1.)    The remote machine needs to have python simplejson or json module

Resolution: Run command to remote install module
ansible hostname -i inventory/hosts -m raw -a "sudo yum install -y python-simplejson"  -k  -u root -vvvv

2.) authorized_keys does not work on target ssh server
Symptom: When ssh from Ansible server to target server, it ask for a password even when .ssh/authorized_keys are set

http://unix.stackexchange.com/questi...authentication 

Make sure the permissions on the ~/.ssh directory and its contents are proper. When I first set up my ssh key auth, I didn't have the ~/.ssh folder properly set up, and it yelled at me. Your home directory ~, your ~/.ssh directory and the ~/.ssh/authorized_keys file on the remote machine must be writable only by you: rwx------ and rwxr-xr-x are fine, but rwxrwx--- is no good¹, even if you are the only user in your group (if you prefer numeric modes: 700 or 755, not 775). If ~/.ssh or authorized_keys is a symbolic link, the canonical path (with symbolic links expanded) is checked. Your ~/.ssh/authorized_keys file (on the remote machine) must be readable (at least 400), but you'll need it to be also writable (600) if you will add any more keys to it. Your private key file (on the local machine) must be readable and writable only by you: rw-------, i.e. 600. Also, if SELinux is set to enforcing, you may need to run restorecon -R -v ~/.ssh (see e.g. Ubuntu bug 965663 and Debian bug report #658675; this is patched in CentOS 6).

 If that does not work, on the target server

sudo su -
service sshd stop   (Note: this will not kill your current session)
/use/sbin/sshd -d   (Note: debug mode)

service sshd start  (Note: do this when finished debugging or else no one can ssh into the VM. Try ssh from a new terminal before exiting the main root terminal)

In debug mode, you will see what sshd is doing when it is trying to read the authorized_keys file

In my case, sshd was reading the wrong file. To fix I had to

Edit /etc/ssh/sshd_config, and uncomment

RSAAuthentication yes

PubkeyAuthentication yes

AuthorizedKeysFile      .ssh/authorized_keys

Then restart sshd

service sshd stop
service sshd start

3.)    Issue: The remote user needs to be able to “sudo su –“ without password. Needed to  configure iptables firewall and other super-admin commands

Resolution: Have user add entry in /etc/sudoers file

Backlog Enhancement: Have precondition check for root access. Or find a way to make root access unnecessary

4.)    Ansible 2.2.0 had a bug
https://github.com/ansible/ansible/issues/16128

Resolution: Updated Ansible from Git with the latest version

git pull --rebase

git submodule update --init –recursive

5.) Ansible has issues transfering files to target server.

Ansible uses sftp to transfer files behind the scenes. Try

sftp user@target-server

to see if you can sftp without a password.

If you cannot, sftp sometimes has issues with echo in .bashrc . Comment out the echo in .bashrc and try again.

If that does not work, force Ansible to use scp instead of sftp.

In /etc/ansible/ansible.cfg, add the line

scp_if_ssh = True

or if you cannot edit the ansible.cfg file, then from shell type

export ANSIBLE_SCP_IF_SSH=y

Oracle foreign key analysis

Find Oracle foreign key by name
For Oracle error

SQL Error: ORA-02291: integrity constraint (SCHEMA_OWNER.FOREIGN_KEY_NAME) violated - parent key not found

Find the source and reference Oracle table of the foreign key name using the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where uc.OWNER='SCHEMA_OWNER' and uc.CONSTRAINT_NAME='FOREIGN_KEY_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys on a table
To find all the foreign keys that a Oracle table has, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where src_ucc.OWNER='SCHEMA_OWNER' and src_ucc.TABLE_NAME='SOURCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys referenced to a table
To find all the Oracle tables that references a table via foreign key, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where dest_ucc.OWNER='SCHEMA_OWNER' and dest_ucc.TABLE_NAME='REFERENCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Column meaning

CONSTRAINT_NAME	The foreign key name
SOURCE_SCHEMA	The schema of the foreign key source table
SOURCE_TABLE	The table that has the foreign key
SOURCE_COLUMN	The column in the source table enforced by the foreign key
POSITION	For foreign keys with multiple columns, the order of the columns in the foreign key
REFERENCE_TABLE	The table referenced by the foreign key
REFERENCE_COLUMN	The column of the table referenced by the foreign key
REFERENCE_POSITION	For foreign keys with multiple columns, the order of the referenced columns in the foreign key
DELETE_RULE	The action Oracle performs with the row associated with the foreign key in the reference table is deleted NO ACTION: Oracle respond with a referential integrity error and will not allow the row to be removed CASCADE: Oracle automatically deletes the row in the tables with the associated foreign key SET NULL: Oracle automatically sets the foreign key column value to NULL
STATUS	Is the foreign key enforced ENABLED: The foreign key is enforced and active DISABLED: The foreign key is not enforced and inactive
DEFERRABLE	If a transaction can use SET CONSTRAINT[S] to dynamically defer a foreign key referential integrity check DEFERRABLE: Yes you can use SET CONSTRAINTS NOT DEFERRABLE: No you cannot use SET CONSTRAINTS
DEFERRED	If a foreign key is DEFERRABLE, when in the transaction will Oracle check for foreign key referential integrity violations by default. NOT DEFERRABLE is always IMMEDIATE. DEFERRED: Oracle will check when after a commit has been issued IMMEDIATELY Oracle will immediately check when the SQL is executed.
VALIDATED	When adding foreign keys to a Oracle table with existing data, checks to see if the existing data violates foreign key referential integrity. VALIDATED: Oracle will check to see if the existing data violates integrity. If it does then the foreign key will not be created NOT VALIDATED: Oracle will not check to see if the existing data violates integrity. The foreign key will be created even if there is existing bad data

See the Oracle language reference on constraints for more details.