So I got an postgresql rds.Instance up and running, now I want to define an extra database inside this instance, so I configure a pg.Provider

const pgProvider = (db: rds.Instance, pw: string) => {
        return new pg.Provider(`${name}-db-provider`, {
            port: db.port,
            host: db.address,
            password: pw,
            username: db.username,
            superuser: true
        });
}

but that causes pulumi to try to connect to the rds.Instance private ip. and I end up with

error: error detecting capabilities: error PostgreSQL version: dial tcp: lookup <privateip>

when trying to define a role. Is there a way to use aws.rds to create roles and databases in addition to the initialDatabase created when using

aws.rds.Instance('name', config)

From the docs it's not clear to me how to do this. Which is the reason why I moved to the postgresql package, and tried to define a provider to tell it about my rds instance. I found https://www.pulumi.com/blog/managing-your-mysql-databases-with-pulumi/ for mysql, but the pg.Provider does not accept an endpoint, I tried using endpoint for the host property, but that causes a double

:PORT:PORT

if you have created an rds instance in a private subnet then the only way to connect is having a network connection I guess.

Agree with Jan, can you share the code you used to define your DB instance?

Sorry for the late answer, but yes; this is the database code

const dbSubnets = new aws.rds.SubnetGroup(
    `unleash-iaac-private-subnets`,
    {
        subnetIds: vpc.privateSubnetIds,
    }
);
export const unleashDb = new aws.rds.Instance(name, {
    instanceClass: 'db.t3.micro',
    dbSubnetGroupName: dbSubnets.id,
    vpcSecurityGroupIds: [sg.id],
    name: 'unleash',
    allocatedStorage: 20,
    maxAllocatedStorage: 100,
    engine: 'postgres',
    engineVersion: "13.3",
    username: 'unleash',
    publiclyAccessible: false,
    skipFinalSnapshot: true,
    deletionProtection: false,
    password: randomPassword("database").result,
    allowMajorVersionUpgrade: true
});

So, I might want to expand

dbSubnet

to include the public subnet, and turn publiclyAccessible to

true

?

well you might, but there's security implications. you probably wnt to set up a bastion host

Will have a look at that. Thanks so much for taking the time to reply.

Another option instead of Bastion host is any ec2 instance with access to the Database network With installed Session Manager client - this way even ec2 instance can be in the private subnet https://www.element7.io/2021/01/aws-ssm-session-manager-port-forwarding-to-rds-without-ssh/ then you can port forward only the database port to your local machine I do this with Cloud9 instance - as a bonus you get IDE. However, for the production and CI/CD I'd use a private running in my VPC to eliminate all network problems. I postgresql provider could connect to the DB on your localhost - then you could develop all the code there and then CI/CD deployment would run in VPC. And ass jaxx says I would not recommend opening your DB to the public network.

Thank you Jan. Great option.