Terraform - Remove AWS security_group_rule from a PostgreSQL DB after upload of dump has been completed

I got a Terraform script in which I setup a PostgreSQL DB in AWS.

There my idea was to:

• Setup the DB with a security group which got port 5432 closed
• Add a rule to the security group which opens port 5432 to the internet
• Upload the DB schema and some initial data from a linux machine
• Close the port 5432 again - later I want to change this to be only reachable from a Lambda function...

So I got this Terraform code in one script which I execute with "terraform apply":

// Setup the DB with a security group which got port 5432 closed 1/2
resource "aws_security_group" "PostgreSQL-SecGrp" {
  vpc_id      = "${data.aws_vpc.default.id}"
  name        = "PostgreSQL-sec-group"
  description = "Lock traffic from outside"
}

// Setup the DB with a security group which got port 5432 closed 2/2
resource "aws_db_instance" "app_rds" {
  identifier             = "mypostgresqldb"
  db_name                = "PostgreSQLDB"
  instance_class         = "db.t2.micro"
  allocated_storage      = 5
  engine                 = "postgres"
  engine_version         = "12.7"
  skip_final_snapshot    = true
  publicly_accessible    = true
  vpc_security_group_ids = [aws_security_group.PostgreSQL-SecGrp.id]
  username               = "user"
  password               = "<secretPassword>"
}

// Add a rule to the security group which opens port 5432 to the internet
resource "aws_security_group_rule" "open_port5432" {
  security_group_id = aws_security_group.PostgreSQL-SecGrp.id
  
  type = "ingress"
  from_port   = 5432
  to_port     = 5432
  protocol    = "tcp"
  cidr_blocks = ["0.0.0.0/0"]
  
}
    
// Upload the DB schema and some initial data from a linux machine (1/2)
terraform {
  required_version = ">= 1.1.6"
 required_providers {
   ssh = {
      source = "loafoe/ssh"
    }
  }
}

// Upload the DB schema and some initial data from a linux machine (2/2)
resource "ssh_resource" "init" {

  host         = "<linuxMachine>"
  user         = "..."
  password     = "..."
  
  commands = [
  < command to upload a psql dump into postgresql >
  ]
}

// Close the port 5432 again
resource "aws_security_group_rule" "close_port5432" {
  security_group_id = aws_security_group.PostgreSQL-SecGrp.id 
  <????>
}

Everything works apart from the last point which shall remove the acessibility from the internet again - because it did not remove/change the security group again. My supposition was that I have maybe to import the created security group again and then modify its resource anyhow, but also there I didn't find anything usefull.

Does anybody got an idea how to do this? Or, since this also looks a bit like a standard problem to me (setup a DB, inject some initial data and then locking it from the internet), maybe someone could point me to a documentation, in which it is described to setup something like this?

One way to do this is to pass a variable to Terraform and use the value of that variable in your Terraform templates to determine what should be deployed. For example if you have an input variable defined like this:

variable "public_db" {
  type    = bool
  default = false

  description = "Expose the database to the public Internet"
}

Then in your template you could have:

resource "aws_security_group_rule" "open_port5432" {
  # We want 1 instance of this rule if public_db is true. 
  # Otherwise we want 0 instances of this rule.
  count = var.public_db ? 1 : 0

  security_group_id = aws_security_group.PostgreSQL-SecGrp.id
  
  type        = "ingress"
  from_port   = 5432
  to_port     = 5432
  protocol    = "tcp"
  cidr_blocks = ["0.0.0.0/0"]
}

Now when you run terraform apply -var="public_db=true" Terraform will create the security group rule, if it doesn't already exist. And if you run terraform apply -var="public_db=false" or just terraform apply" the security group rule will be deleted (if it exists).

You just need to remove the initial security group rule open_port5432, and perform a terraform plan and apply.

You could also use count as a feature flag to enable/disable the needed security groups.

count = local.enable_external_access ? 1 : 0

NB: it's a bad idea to expose your instance to 0.0.0.0/0 I would like to suggest if possible to use the specific external IP using /32 which will bootstrap the DB. Or use a lambda to do so, this way you only expose your DB in the private network.

Ok, based on the feedback of Mark and v-rosa, I want to come up with a solution which seems to work.

At first the overall security group of my code snipped (see the top of this thread):

resource "aws_security_group" "PostgreSQL-SecGrp" {
  vpc_id      = "${data.aws_vpc.default.id}"
  name        = "PostgreSQL-sec-group"
  description = "Lock traffic from outside"
}

stays in the top level main.tf file.

Then I use the hints from Mark and v-rosa and shift the security group rule into an own tf file, in an own folder:

resource "aws_security_group_rule" "everything_around_port_5432" {
  security_group_id = ...
  count = var.public_db ? 1 : 0
  type = "ingress"
  from_port   = 5432
  to_port     = 5432
  protocol    = "tcp"
  cidr_blocks = ["0.0.0.0/0"]
  
}

Then back in the "top level" main.tf I added two null_ressources:

resource "null_resource" "create_rule_with_open_port" {
  provisioner "local-exec" {
    working_dir = "./postgres_db"
    command     = "terraform init"
    interpreter = ["cmd", "/C"]
  }
  provisioner "local-exec" {
    working_dir = "./postgres_db"
    command     = "terraform apply -auto-approve -var=public_db=true"
    interpreter = ["cmd", "/C"]
  }
}

/*
At this point the database is running and the port is open. 
Do the dump upload with:
*/

resource "ssh_resource" "init" {

  host         = "<linuxMachine>"
  user         = "..."
  password     = "..."
  
  commands = [
  < command to upload a psql dump into postgresql >
  ]
}

# ... and then proceed with the removal of the port again.

resource "null_resource" "remove_rule_again" {
    provisioner "local-exec" {
    working_dir = "./postgres_db"
    command     = "terraform apply -auto-approve -var=public_db=false"
    interpreter = ["cmd", "/C"]
  }
  depends_on = [ null_resource.create_rule_with_open_port, ssh_resource.init ]
}

When I now run "terraform apply" on the top level file it calls this new tf file which is in charge for the security_group_rule and sets the port - and removes it again.

Probably the script can be tuned to e. g. not always do the "terraform init" in the first place.

And a small side note: This new tf file also gets an own state file and I was a bit wondering if this can get messed up when I call "terraform destroy" on the top level script. However when I call "terraform apply" on the top level script, in the specific state file is one resource added - and removed again. So this state is "empty" - that's my theory.