In today’s rapidly evolving AI landscape, Amazon Bedrock is a quick, powerful, and easy to use platform for leveraging foundation models. This blog post will guide you through creating a simple web solution to demonstrate Stability AI’s image generation capabilities using Amazon Bedrock.

The goal is to build a simple website that accepts user prompts and generates corresponding images. This demo demonstrates the potential of AI-driven image creation and provides hands-on experience together with actual code. There is no reliance on PowerPoint decks 🙂 .

Infrastructure Setup
For this demonstration, I will be running the web server on an Amazon EC2 instance running Amazon Linux 2. The instance will be equipped with Python 3, Flask for web framework functionality, and boto3 for AWS SDK interactions.

A second windows EC2 instance located in the same VPC and subnet will be used to access the web page. The security group of the Linux server should allow connections from the windows server on port 5000.

Accessing Stability AI Model
Before executing the code, you must request access to the Stability AI model via the Amazon Bedrock console. This step allows the EC2 instance to execute the Stability AI model.

IAM Policy Configuration
To allow our EC2 instance to access the Stability AI model, we need to attach the below IAM policy:

        {
            "Effect": "Allow",
            "Action": [
                "bedrock:InvokeModel",
                "bedrock:InvokeModelWithResponseStream"
            ],
            "Resource": [
                "arn:aws:bedrock:us-east-1::foundation-model/stability.stable-diffusion-xl-v1"
            ]
        }

This can be added as an inline policy to the IAM role used by your EC2 instance.

Upload the python code (see download link at bottom of page) to a file named my_ai_web.py and start the web server with the command python ./my_ai_web.py as shown in the below image.

Sign on to the windows server and navigate to the second ip displayed in the above image. The following web page should be displayed:

Enter a prompt and click on the “Generate Image” button as shown below:

At this point the code makes a call to bedrock to process the prompt and generate the image:

After the image is generated, it is displayed as shown below:

Python/Flask code download link

Managing user access to your Amazon Redshift database can be a headache, especially when it comes to maintaining individual user accounts and passwords. However, there’s a better way – you can leverage your existing AWS Identity and Access Management (IAM) permissions to generate temporary database credentials for your users.

By configuring your system to authenticate users based on their IAM credentials, you can eliminate the need to create and manage individual database user accounts. Users can simply log in to your Redshift database using their IAM credentials, and your system will generate the necessary database credentials on-the-fly.

This approach offers several benefits. It streamlines user management, as you can leverage your existing IAM permissions structure to control access to the database. It also enhances security, as you don’t have to worry about users sharing or mishandling database passwords.

The official AWS documentation is at Using IAM authentication to generate database user credentials.

In my particular use case, I had to connect to a Redshift database with temporary IAM authentication and there is one small wrinkle that cost me a couple of hours. I am documenting this together with the code in the hope that I might save someone else some time in the future.

Below is my python code

import boto3
from botocore.exceptions import ClientError
import psycopg
psycopg._encodings._py_codecs["UNICODE"] = "utf-8"
psycopg._encodings.py_codecs.update(
    (k.encode(), v) for k, v in psycopg._encodings._py_codecs.items()
)
from psycopg import OperationalError

def connect_to_the_database(db_user, cluster_identifier, db_name, db_host, db_port):

    print('Original DB user     : ', db_user)

    redshift = boto3.client('redshift', 'us-west-1')
    try:
        credentials = redshift.get_cluster_credentials(
            DbUser = db_user,
            DbName = db_name,
            ClusterIdentifier = cluster_identifier,
            DurationSeconds = 900,
            AutoCreate = False,
        )
    except ClientError as e:
        print('Error getting cluster credentials')
    
    db_password = credentials.get("DbPassword")
    db_user     = credentials.get("DbUser")

    print('Modified IAM DB user : ', db_user)
    print('IAM DB password      : ', db_password)

    connection_args = 'host={} port={} dbname={} user={} password={}'.format(
        db_host,
        db_port,
        db_name,
        db_user,
        db_password
    )
    try:
        conn = psycopg.connect(connection_args)
    except psycopg.Error as e:
        print('Error connecting to the database')

    return conn

#
## main processing
#
if __name__ == '__main__':
    db_user='my_db_user'
    cluster_identifier='my-redshift-cluster'
    db_name='test'
    db_host='my-redshift-cluster.xxx.us-west-1.redshift.amazonaws.com'
    db_port=5439
    conn = connect_to_the_database(db_user, cluster_identifier, db_name, db_host, db_port)

Note that in the main processing the user name is “my_db_user” and this is the name passed to the function “connect_to_the_database”. This is confirmed by the print statement at the start of the function:

print('Original DB user     : ', db_user)

My expectation was that the call to BOTO3’s get_cluster_credentials would return a password that I would use to connect to the database with. However, you also have to extract the modified user name from the returned “credentials” dictionary object to get the modified user name. This modified user name and temporary credentials are used to connect to the database.

The output of the program’s print statements is below:

Original DB user     :  my_db_user
Modified IAM DB user :  IAM:my_db_user
IAM DB password      :  JmRCSmqxEyw2Seo89mWUg2H1PG9pONROZJBXA==

Note the difference between the original DB user and the modified IAM DB user. The user that you want to connect to the database has to be prefixed with the text “IAM:”. This, IAM:my_db_user, is the user name that will appear in the stv_sessions, stv_inflight etc. views.

In this blog I will demonstrate the creation of a Lambda written in Python to access a Postgres or Redshift database. As both these database engines are based on Postgres, the same process will work.

In order to access Postgres from Lambda we need to create and deploy a Lambda layer containing Psycopg, the most popular PostgreSQL database adapter for the Python programming language. I will be deploying version 3 of Psycopg.

Prerequisites
The first step is to create the software in a zip file required by the layer. A typical approach is to perform these steps on an EC2. This does require that an EC2 be deployed with the required permissions (IAM Role). Instead, I will be performing these commands in a Cloudshell environment. If you are connected to the AWS Management Console, either search for “Cloudshell” or click on the icon usually located in the top right corner of the web page.

Important note : This blog is using Python version 3.12 in all the commands. If you need to use a different version, change “3.12” to the version you need.

Install Psycopg
Make a directory to house the software. It is important that this directory structure be exactly as defined below as we will be zipping this directory to create the layer.

mkdir -p dc-layer/python

And then change directory to the above directory

cd dc-layer

Install the software with the command

pip3 install psycopg[binary] \
		--python-version 3.12 \
		--only-binary=:all: \
		--platform manylinux2014_x86_64 \
		-t dc-layer/python

The following directory structure and files should exist

/home/cloudshell-user $ pwd
/home/cloudshell-user/dc-layer/python
/home/cloudshell-user $ ls -l
total 160
drwxr-xr-x. 6 cloudshell-user cloudshell-user   4096 Jul  8 20:37 psycopg
drwxr-xr-x. 2 cloudshell-user cloudshell-user   4096 Jul  8 20:37 psycopg-3.2.1.dist-info
drwxr-xr-x. 3 cloudshell-user cloudshell-user   4096 Jul  8 20:37 psycopg_binary
drwxr-xr-x. 2 cloudshell-user cloudshell-user   4096 Jul  8 20:37 psycopg_binary-3.2.1.dist-info
drwxr-xr-x. 2 cloudshell-user cloudshell-user   4096 Jul  8 20:37 psycopg_binary.libs
drwxr-xr-x. 2 cloudshell-user cloudshell-user   4096 Jul  8 20:37 __pycache__
drwxr-xr-x. 2 cloudshell-user cloudshell-user   4096 Jul  8 20:37 typing_extensions-4.12.2.dist-info
-rw-r--r--. 1 cloudshell-user cloudshell-user 134451 Jul  8 20:37 typing_extensions.py

Zip the software for the lambda layer
Go up one level to the directory that contains the “python” directory and zip the software installed in the previous step with

zip -r layer.zip python

The directory should now contain a zip file

/home/cloudshell-user $ pwd; ls -l
/home/cloudshell-user/dc-layer
total 4668
-rw-r--r--. 1 cloudshell-user cloudshell-user 4772949 Jul  8 20:43 layer.zip
drwxr-xr-x. 9 cloudshell-user cloudshell-user    4096 Jul  8 20:37 python

Publish the layer
Publish the layer using AWS CLI with the below command

aws lambda publish-layer-version --layer-name dc-psycopg3-layer \
    --zip-file fileb://layer.zip \
    --compatible-runtimes python3.12 \
    --compatible-architectures "x86_64"

Confirm that the layer was published
From the AWS Management Console, navigate to the Lambda Layers page and confirm that the above layer is present.

Create a Lambda function
Create a new lambda function using Python 3.12

Add the layer previously uploaded to the function’s definition

Lambda function – sample code
Below is sample code that connects to a Redshift cluster and performs a select on a table

import logging
import psycopg
from psycopg import OperationalError
psycopg._encodings._py_codecs["UNICODE"] = "utf-8"
psycopg._encodings.py_codecs.update(
    (k.encode(), v) for k, v in psycopg._encodings._py_codecs.items()
)

logger = logging.getLogger()
logger.setLevel(logging.INFO)

def lambda_handler(event, context):

    print('**** Connecting to Redshift')
    my_hostname = 'my-cluster.us-east-1.redshift.amazonaws.com'
    my_database = 'my-databse'
    my_user     = 'my-database-user'
    my_password = 'my-db-user-password'
    my_port     =  5439

    connection_args = 'host={} port={} dbname={} user={} password={}'.format(
        my_hostname,
        my_port,
        my_database,
        my_user,
        my_password
    )

    try:
        logger.info("Establishing connection to Redshift")
        conn = psycopg.connect(connection_args)
        logger.info("SUCCESS: Connection to Redshift succeeded!")
    except psycopg.Error as e:
        logger.error("ERROR: Unexpected error: Could not connect to Redshift.")
        logger.error(e)


    sql_text = "select col_01, col_02 from my_schema.my_table where col_01 = 'dc-test'"
    print(sql_text)

    try:
        cur = conn.cursor()
        cur.execute(sql_text)
        for row in cur.fetchall():
            col_01 = row[0]
            col_02 = row[1]
            print(col_01, col_02)
        cur.close()
    except psycopg.Error as e:
        print('SQL failed. Failing text is ', sql_text)
        print(f"Error from database",e)

Recently, my focus has been on using Amazon Redshift for a data warehousing solution, and I wanted to share some insights gained through working with unload, copy, and super data types. Unloading data allows for efficient extraction from Redshift to various storage options, facilitating downstream analytics and archival processes. Similarly, the COPY command serves as a linchpin for ingesting data into Redshift, enabling seamless integration from diverse sources. Super data types are a flexible option for accommodating complex data structures without the need for continuous schema changes.

Unload and rename a subset of columns
If you need to unload a subset of columns from a source table for load into a target table with different column names, this can be accomplished as follows:

unload (' select   old_col_01     as new_col_01
                  ,old_col_23     as new_col_02
                  ,old_col_42     as new_col_03
           from    my_schema.my_table_name
          where    old_col_24     is not null
'
)
to 's3://my-s3-bucket/table_01_data_'
iam_role  'arn:aws:iam::93073150:role/my-redshift-role'
JSON
;

The column name old_col_01 from the source table is being renamed to new_col_01 for the target table.

The target S3 bucket should not contain any files with the prefix in the unload command. A quick way to clean up the files without the need to use the AWS Console, filter and select the files and type “permanently delete” is to use AWS Cloudshell and the command:

aws s3 rm s3://my-s3-bucket/ --recursive --exclude "*" --include "table_01_data_*"

Please make sure that you have populated the exclude and include options correctly. There is no warning or confirmation. All the files matching the exclude and include are deleted with no recovery.

Unload a subset of rows from a table
The Redshift unload command does not allow the use of a limit clause directly in the select. The work around is to use it in a sub select as shown below:

unload (' select * 
          from   my_schema.my_table_name
          where  col_01 in 
                 (    
                   select col_01 
                   from   my_schema.my_table_name 
                   limit 400
                 ) 
'
)
to 's3://my-s3-bucket/table_01_data_'
iam_role  'arn:aws:iam::93073150:role/my-redshift-role'
JSON
;

The sub select refers to the same table and applies the limit clause.

Copy a certain column(s) from a file that contains other columns
If you have a file in S3 the contains an unload from a table with a number of columns and you only want to populate 1 or more columns in a different table, you can do it with the below command:

copy       my_schema.my_table_name
           (col_01, col_02)
from      's3://my-s3-bucket/table_01_data_'
iam_role  'arn:aws:iam::93073150:role/my-redshift-role'
region    'us-east-1'
FORMAT     JSON 'auto ignorecase'
;

Columns named col_01 and col_02 are copied into table name my_schema.my_table name while other columns in the S3 file are ignored.

Copying data and Redshift super data type
If you have a file containing JSON data and you need to load it into a Redshift table containing a super data type, you can use the following command:

copy       my_schema.my_table_name
from      's3://my-s3-bucket/ table_01_data_'
iam_role  'arn:aws:iam::93073150:role/my-redshift-role'
region    'us-east-1'
FORMAT     JSON 'noshred'
;

All of the data in the S3 file will be loaded into the super data type column in JSON format.

The data in the super data type column containing JSON data can be queried as follows. Assume that the table name is my_schema.my_super_data_table containing a super data type column named my_json_data. This JSON data contains elements named create_time, account_id etc. You can refer to the individual elements as follows:

select   my_json_data."create_time"::timestamp::date as create_date
        ,my_json_data."account_id" as account_id
        ,my_json_data."cust_name"  as cust_name
from     my_schema.my_super_data_table
where    my_json_data."zip_code" = 12345
;

I have recently begun using Amazon CodeWhisperer and am continually impressed by its ability to save me time by automating typing, reducing syntax lookup, and error reduction. The tool is available for multiple IDEs, including Visual Studio Code and PyCharm, and supports multiple languages, such as Python, Java, and C#. I have been using it for Python in Visual Studio Code.

The first step is to add the AWS Toolkit extension to VS Code:

The next step is to sign up for CodeWhisperer, using a personal email address. The product is free for individual use with unlimited code suggestions. The extension exchanges a code with an Amazon website to enable the product to run. After this brief sign-up process, you are ready to go.

For example, to create a Python program to select data from a Postgres table, I provided the following comment line:

#connect to a postgres database and cust_id, cust_name, create_ts from public.dc_customer ordered by cust_name

and hit the enter key at the end of the command line. CodeWhisperer starts suggesting the required lines of code such as “import psycopg2”, “try:” etc.

Each suggestion shows up in grey text and if you want to accept the suggestion, use the tab key and it will be converted to code with the proper highlighting and indentation.

I continued to accept the suggestions in the next few screenshots:

After the “finally” block, all of the logic requested in the first comment line was in place. I made two changes to the code created by CodeWhisperer; added in the dbname, host, password etc. and added “FETCH FIRST 10 ROWS ONLY”.

The code executed without any errors:

Complete details on Amazon CodeWhisperer can be found on this page.

My client had a requirement to clone an Aurora database from the production account to a test account. In adherence to standard security practices, the production Aurora instance was configured with encryption utilizing a customer-managed Key Management Service (KMS) key. To enable the successful cloning of the database into the test account, a prerequisite step is to share the KMS key from the production account with the test account.

If the key was created via the console, we can navigate to the KMS page and filter for the key as shown below:

If you click on the Alias and then the Key Policy tab and scroll down

there is an option to add other AWS account

However, keys created via a cloud formation template such as below:

Resources:
  #
  ## Create a key
  #
  rCreateDBKMSCMK:
    Type: AWS::KMS::Key
    DeletionPolicy: Retain
    Properties:
      KeyPolicy:
        Version: '2012-10-17'
        Statement:
        - Effect: Allow
          Principal:
            AWS: 'arn:aws:iam::111111111121:root'
          Action: 'kms:*'
          Resource: '*'
      Tags:
      - Key: Name
        Value: dc-test-key-03
  #
  ## Create an alias for the key
  #
  rCreateDBKMSCMKAlias:
    Type: 'AWS::KMS::Alias'
    DeletionPolicy: Retain
    Properties:
      AliasName: 'alias/dc-test-key-03-alias'
      TargetKeyId: !Ref rCreateDBKMSCMK

lack the add other AWS account button:

In order to allow sharing, the below needs to be added to the key’s policy.

• In this example, account 101010101010 is the key owner and is sharing the key with account 707070707070.
• Typically the key policy will already contain permissions similar to the code in black. The code in red is needed to enable the share.
• In this example, I am sharing with the root account. This can be changed as per your security requirements.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::101010101010:root"
            },
            "Action": "kms:*",
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::707070707070:root"
            },
            "Action": [
                        "kms:Encrypt",
                        "kms:Decrypt",
                        "kms:ReEncrypt*",
                        "kms:GenerateDataKey*",
                        "kms:DescribeKey"
                      ],
            "Resource": "*"
        },
        {
            "Sid": "Allow attachment of persistent resources",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::707070707070:root"
            },
            "Action": [
                "kms:CreateGrant",
                "kms:ListGrants",
                "kms:RevokeGrant"
            ],
            "Resource": "*",
            "Condition": {
                "Bool": {
                    "kms:GrantIsForAWSResource": "true"
                }
            }
        }
    ]
}

After the above policy change has been made, the key will be shared with the other account. This can be verified by signing on to the 707070707070 account and issuing the below command to describe the key:

aws kms describe-key --key-id=arn:aws:kms:us-east-1:101010101010:key/6897

Using automated backups and snapshots we can restore an AWS RDS database
– to the most current point in time
– to a prior point in time
– use the restored database going forward
– or restore particular tables

In summary, we have multiple options depending on the nature and extent of the data lost.

In this example, I will demonstrate a method to restore certain rows in one table in the original database.

Create a test table

CREATE TABLE dc_schema.test_table (
                object_id numeric NOT NULL,
                "owner" varchar(128) NULL,
                object_name varchar(128) NULL,
                CONSTRAINT test_table_pk PRIMARY KEY (object_id)
);

and inserted some data. For demonstration purposes, let us assume that the rows with object_id between 30,000 and 40,000 are critical to the application. Further, assume that someone accidentally deleted this data with the following SQL:

select *
from   dc_schema.test_table daac 
where  object_id between 30000 and 40000
;

We now need this data restored as it is critical to the operation of the application.

On the AWS RDS Console page, select the DB cluster, click on “Actions” and then on “Restore to point in time”. On the next page, populate the custom date and time to a point before the data was deleted. Most of the options will be populated except for a few such as the DB instance identifier.

Click on the orange button at the bottom right of the page to begin the recovery. This will take some time to occur.

Some points to keep in mind with respect to the restored database:

– You can use the restored DB instance as soon as its status is available. The DB instance continues to load data in the background. This is known as lazy loading.

– If you access data that hasn’t been loaded yet, the DB instance immediately downloads the requested data from Amazon S3, and then continues loading the rest of the data in the background

– To help mitigate the effects of lazy loading on tables to which you require quick access, you can perform operations that involve full-table scans, such as SELECT *. This allows Amazon RDS to download all of the backed-up table data from S3.

The restore process will create a new instance. While the “restore” is occurring, you can continue to use the original database. To simulate this, I ran a Python program to add another 100,000 rows to the table from which the data was accidentally deleted. The situation of creating keys in the range of the data that was deleted has to be handled by the application staff. In this particular example, I am creating keys higher than the range of keys that were deleted to ensure that there is no primary key collision.

After the restore was completed, I connected to the restored database to check if my data is present. After this was confirmed, I switched back to the original database from where the critical data was deleted and created a link to the recovered database with the following commands:

Create an extension:

CREATE EXTENSION dblink;

Create a dblink:

SELECT dblink_connect
(
  'link_to_recovery’, 
  'dbname=clientdb 
   port=5432  
   host= red-recovery-cluster…….rds.amazonaws.com 
   user=dbadmin
   password=password_here'
);

Execute the following SQL to select the data from the recovered database and insert it into the original database:

insert
into    dc_schema.test_table
SELECT  * 
from    dblink
(
'link_to_recovery', 
'SELECT object_id, owner, object_name 
 FROM   dc_schema.test_table 
 where  object_id between 30000 and 40000'
)
AS 
p(object_id numeric, owner varchar(128), object_name varchar(128))
;

Assuming that you have recovered all the data that was accidentally deleted, you can drop the recovered database. I will do this via AWS CLI commands running in CloudShell.

First, we delete the instance created under the cluster with the command:

aws rds delete-db-instance \
  --db-instance-identifier red-recovery-cluster \
  --skip-final-snapshot \
  --delete-automated-backups

After the instance is deleted, I deleted the cluster with the following command:

aws rds delete-db-cluster \
  --db-cluster-identifier red-recovery-cluster \
  --skip-final-snapshot

Please note that the above commands delete the database along with automated backups and without a final snapshot.