search

pulumi / pulumi-postgresql

A Postgresql Pulumi resource package
Apache License 2.0
28 stars 9 forks source link

Error granting role pg_database_owner to postgres #206

Open dimas-cyriaco opened 1 year ago

dimas-cyriaco commented 1 year ago

What happened?

When trying to give privileges to an user, Pulumi throws an error:

* Error granting role pg_database_owner to postgres: pq: role "pg_database_owner" cannot have explicit members

I'm trying to replicate this statement GRANT ALL ON SCHEMA public TO db-user; with the code bellow.

Expected Behavior

Expect the grant works normally.

Steps to reproduce

Create a Pulumi program with something like this. And pulumi up.

const rdsInstance = new aws.rds.Instance(/** ... */)

const provider = new postgresql.Provider('postgres-provider', {
  host: rdsInstance.address.apply(address => address),
  password: rdsInstance.password,
  port: 5432,
  sslmode: 'require',
  superuser: false,
  username: rdsInstance.username,
})

const dbUser = `db-user`
const dbPassword = 'db-password'
const dbName = 'db-name'

new postgresql.Role(
  'postgres-role',
  {
    name: dbUser,
    password: dbPassword,
    login: true,
    connectionLimit: -1,
  },
  { provider }
)

new postgresql.Database(
  'postgres-database',
  {
    name: dbName,
  },
  { provider }
)

new postgresql.Grant(
  'postgres-grant',
  {
    role: dbUser,
    database: dbName,
    schema: 'public',
    objectType: 'schema',
    privileges: ['CREATE', 'USAGE'],
  },
  { provider }
)

Output of pulumi about

CLI
Version 3.65.1 Go Version go1.20.3 Go Compiler gc

Plugins NAME VERSION nodejs unknown

Host
OS arch Version "rolling" Arch x86_64

This project is written in nodejs: executable='/home/dimas/.local/share/rtx/installs/node/16.20.0/bin/node' version='v16.20.0'

Current Stack: motrix/api/dev

TYPE URN pulumi:pulumi:Stack urn:pulumi:dev::api::pulumi:pulumi:Stack::api-dev engine:api:ec2Instance urn:pulumi:dev::api::engine:api:ec2Instance::api-instance engine:api:ci urn:pulumi:dev::api::engine:api:ci::engine-api-dev-ci engine:api:subnet urn:pulumi:dev::api::engine:api:subnet::api-vpc engine:api:api-gateway urn:pulumi:dev::api::engine:api:api-gateway::api-apigateway engine:api:rds urn:pulumi:dev::api::engine:api:rds::engine-api-dev-rds pulumi:providers:aws urn:pulumi:dev::api::pulumi:providers:aws::default_5_34_0 engine:api:codeDeploy urn:pulumi:dev::api::engine:api:ci$engine:api:codeDeploy::engine-api-dev-ci-codedeploy engine:api:ci:codebuild urn:pulumi:dev::api::engine:api:ci$engine:api:ci:codebuild::engine-api-dev-ci-codebuild engine:api:codePipeline urn:pulumi:dev::api::engine:api:ci$engine:api:codePipeline::engine-api-dev-ci-codepipeline aws:ec2/keyPair:KeyPair urn:pulumi:dev::api::engine:api:ec2Instance$aws:ec2/keyPair:KeyPair::engine-api-dev-ec2-keypair aws:iam/role:Role urn:pulumi:dev::api::engine:api:ec2Instance$aws:iam/role:Role::engine-api-dev-ec2-codedeploy-role aws:s3/bucket:Bucket urn:pulumi:dev::api::engine:api:ci$aws:s3/bucket:Bucket::engine-api-dev-ci-artifacts-bucket aws:ec2/vpc:Vpc urn:pulumi:dev::api::engine:api:subnet$aws:ec2/vpc:Vpc::engine-api-dev-vpc aws:ssm/parameter:Parameter urn:pulumi:dev::api::engine:api:rds$aws:ssm/parameter:Parameter::engine-api-dev-rds-user-parameter aws:secretsmanager/secret:Secret urn:pulumi:dev::api::engine:api:rds$aws:secretsmanager/secret:Secret::engine-api-dev-rds-dbpassword-secret aws:secretsmanager/secret:Secret urn:pulumi:dev::api::engine:api:rds$aws:secretsmanager/secret:Secret::engine-api-dev-rds-root-dbpassword-secret aws:ssm/parameter:Parameter urn:pulumi:dev::api::engine:api:rds$aws:ssm/parameter:Parameter::engine-api-dev-rds-name-parameter aws:codedeploy/application:Application urn:pulumi:dev::api::engine:api:ci$engine:api:codeDeploy$aws:codedeploy/application:Application::engine-api-dev-ci-codedeploy-application aws:iam/role:Role urn:pulumi:dev::api::engine:api:ci$engine:api:codeDeploy$aws:iam/role:Role::engine-api-dev-ci-codedeploy-role aws:iam/role:Role urn:pulumi:dev::api::engine:api:ci$engine:api:ci:codebuild$aws:iam/role:Role::engine-api-dev-ci-codebuild-role aws:iam/role:Role urn:pulumi:dev::api::engine:api:ci$engine:api:codePipeline$aws:iam/role:Role::engine-api-dev-ci-codepipeline-role aws:iam/instanceProfile:InstanceProfile urn:pulumi:dev::api::engine:api:ec2Instance$aws:iam/instanceProfile:InstanceProfile::engine-api-dev-ec2-instance-profile aws:iam/rolePolicyAttachment:RolePolicyAttachment urn:pulumi:dev::api::engine:api:ec2Instance$aws:iam/rolePolicyAttachment:RolePolicyAttachment::engine-api-dev-ec2-codedeploy-role-attachment aws:ec2/subnet:Subnet urn:pulumi:dev::api::engine:api:rds$aws:ec2/subnet:Subnet::engine-api-dev-rds-public-subnet2 aws:ec2/internetGateway:InternetGateway urn:pulumi:dev::api::engine:api:subnet$aws:ec2/internetGateway:InternetGateway::engine-api-dev-internet-gateway aws:ec2/securityGroup:SecurityGroup urn:pulumi:dev::api::engine:api:rds$aws:ec2/securityGroup:SecurityGroup::engine-api-dev-rds-security-group aws:ec2/subnet:Subnet urn:pulumi:dev::api::engine:api:subnet$aws:ec2/subnet:Subnet::engine-api-dev-subnet aws:ec2/subnet:Subnet urn:pulumi:dev::api::engine:api:rds$aws:ec2/subnet:Subnet::engine-api-dev-rds-public-subnet1 aws:ec2/securityGroup:SecurityGroup urn:pulumi:dev::api::engine:api:subnet$aws:ec2/securityGroup:SecurityGroup::engine-api-dev-security-group aws:secretsmanager/secretVersion:SecretVersion urn:pulumi:dev::api::engine:api:rds$aws:secretsmanager/secretVersion:SecretVersion::engine-api-dev-rds-dbpassword-secret-version aws:secretsmanager/secretVersion:SecretVersion urn:pulumi:dev::api::engine:api:rds$aws:secretsmanager/secretVersion:SecretVersion::engine-api-dev-rds-root-dbpassword-secret-version aws:iam/rolePolicy:RolePolicy urn:pulumi:dev::api::engine:api:ci$engine:api:codeDeploy$aws:iam/rolePolicy:RolePolicy::engine-api-dev-ci-codedeploy-role-policy-attachment aws:iam/rolePolicyAttachment:RolePolicyAttachment urn:pulumi:dev::api::engine:api:ci$engine:api:codeDeploy$aws:iam/rolePolicyAttachment:RolePolicyAttachment::engine-api-dev-ci-codedeploy-role-attachment aws:codedeploy/deploymentGroup:DeploymentGroup urn:pulumi:dev::api::engine:api:ci$engine:api:codeDeploy$aws:codedeploy/deploymentGroup:DeploymentGroup::engine-api-dev-ci-codedeploy-deployment-group aws:iam/rolePolicyAttachment:RolePolicyAttachment urn:pulumi:dev::api::engine:api:ci$engine:api:ci:codebuild$aws:iam/rolePolicyAttachment:RolePolicyAttachment::engine-api-dev-ci-codebuild-role-policy-attachment aws:iam/rolePolicyAttachment:RolePolicyAttachment urn:pulumi:dev::api::engine:api:ci$engine:api:codePipeline$aws:iam/rolePolicyAttachment:RolePolicyAttachment::engine-api-dev-ci-codepipeline-role-attachment aws:ec2/routeTable:RouteTable urn:pulumi:dev::api::engine:api:subnet$aws:ec2/routeTable:RouteTable::engine-api-dev-route-table aws:rds/subnetGroup:SubnetGroup urn:pulumi:dev::api::engine:api:rds$aws:rds/subnetGroup:SubnetGroup::engine-api-dev-rds-public-subnet-group aws:ec2/instance:Instance urn:pulumi:dev::api::engine:api:ec2Instance$aws:ec2/instance:Instance::engine-api-dev-ec2-instance aws:ec2/routeTableAssociation:RouteTableAssociation urn:pulumi:dev::api::engine:api:subnet$aws:ec2/routeTableAssociation:RouteTableAssociation::engine-api-dev-route-table-association aws:ec2/routeTableAssociation:RouteTableAssociation urn:pulumi:dev::api::engine:api:rds$aws:ec2/routeTableAssociation:RouteTableAssociation::engine-api-dev-rds-subnet1-route-table-association aws:ec2/routeTableAssociation:RouteTableAssociation urn:pulumi:dev::api::engine:api:rds$aws:ec2/routeTableAssociation:RouteTableAssociation::engine-api-dev-rds-subnet2-route-table-association aws:rds/instance:Instance urn:pulumi:dev::api::engine:api:rds$aws:rds/instance:Instance::engine-api-dev-rds-instance aws:apigatewayv2/api:Api urn:pulumi:dev::api::engine:api:api-gateway$aws:apigatewayv2/api:Api::engine-api-dev-apigateway aws:ssm/parameter:Parameter urn:pulumi:dev::api::engine:api:rds$aws:ssm/parameter:Parameter::engine-api-dev-rds-host-parameter aws:iam/rolePolicy:RolePolicy urn:pulumi:dev::api::engine:api:ci$engine:api:ci:codebuild$aws:iam/rolePolicy:RolePolicy::engine-api-dev-ci-codebuild-role-policy-attachment pulumi:providers:postgresql urn:pulumi:dev::api::engine:api:rds$pulumi:providers:postgresql::engine-api-dev-rds-postgres-provider aws:codebuild/project:Project urn:pulumi:dev::api::engine:api:ci$engine:api:ci:codebuild$aws:codebuild/project:Project::engine-api-dev-ci-codebuild-project aws:codepipeline/pipeline:Pipeline urn:pulumi:dev::api::engine:api:ci$engine:api:codePipeline$aws:codepipeline/pipeline:Pipeline::engine-api-dev-codepipeline postgresql:index/role:Role urn:pulumi:dev::api::engine:api:rds$postgresql:index/role:Role::engine-api-dev-rds-content-planning-user postgresql:index/database:Database urn:pulumi:dev::api::engine:api:rds$postgresql:index/database:Database::engine-api-dev-rds-database postgresql:index/grant:Grant urn:pulumi:dev::api::engine:api:rds$postgresql:index/grant:Grant::engine-api-dev-rds-database-root-privileges

Found no pending operations associated with motrix/dev

Backend
Name pulumi.com URL https://app.pulumi.com/dimas-motrix User dimas-motrix Organizations dimas-motrix, motrix

Pulumi locates its logs in /tmp by default warning: Failed to get information about the Pulumi program's dependencies: could not find either /home/dimas/code/api/infra/yarn.lock or /home/dimas/code/api/infra/package-lock.json

Additional context

I could found the same problem in the Terraform provider issues: here and here.

But I wasn't able to make the solutions presented to work with Pulumi.

Contributing

Vote on this issue by adding a 👍 reaction. To contribute a fix for this issue, leave a comment (and link to your pull request, if you've opened one already).

aq17 commented 1 year ago

Hi @dimas-cyriaco , thanks for opening this issue. You are correct, it is an upstream issue – we'll do an upgrade once the fix is released upstream.

rtuin commented 1 year ago

Just dropping in to mention this issue is still in the way.

What are the options to fix this issue? "it is an upstream issue" is a very passive way of handling things.

dimas-cyriaco commented 1 year ago

@rtuin I was able to make it work by 'overriding' the public schema:

const role = new postgresql.Role('role', {
  name: dbUser,
  password: dbPassword,
  login: true,
  connectionLimit: -1,
})

const database = new postgresql.Database('database', {
  name: dbName,
  owner: role.name,
})

// This was the key.
const schema = new postgresql.Schema(
  'schema',
  {
    name: 'public',
    owner: role.name,
    database: dbName,
    dropCascade: true,
  },
  {
    dependsOn: [database, role],
    provider,
  }
)

// Now this works.
new postgresql.Grant(
  'grant',
  {
    role: role.name,
    database: dbName,
    schema: 'public',
    objectType: 'schema',
    privileges: ['CREATE', 'USAGE'],
  },
  { provider, dependsOn: [schema] }
)

I don't know exactly why this works.

asf-loft commented 7 months ago

That seems to be already fixed upstream (https://github.com/cyrilgdn/terraform-provider-postgresql/commit/e3b877e09eaf5d3b4c7b8fff4dd8685880c6c291 / https://github.com/cyrilgdn/terraform-provider-postgresql/issues/301)

asf-loft commented 7 months ago

This bug can be closed. It's already fixed with recent pulumi and pulumi/postgresql versions.