Hi all, I am thinking of installing pulumi-mysql. https://github.com/pulumi/pulumi-mysql I want to manage MySQL user permissions, but I don’t seem to be able to execute Grant statements at the column level.Is there a better way to use Pulumi and execute column level Grant statements?I would like to manage the following query using Pulumi.

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

You can create a Dynamic Provider that uses the

mysql

package under the hood. An example with a MySQL connection + custom SQL query can be found in the Automation API Examples repository - here.

Thank you!!!🙏 That’s the information I need. I will confirm it.

@incalculable-monkey-35668 does the user you're connecting to the mysql database have

GRANT

permission? what error are you getting?

@billowy-army-68599 Thanks for asking. I am running with root privileges. First run this pulumi program.

package main

import (
	"<http://github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql|github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql>"
	"<http://github.com/pulumi/pulumi/sdk/v3/go/pulumi|github.com/pulumi/pulumi/sdk/v3/go/pulumi>"
)

func main() {
	pulumi.Run(getInitLocalTestingDb())
}

func getInitLocalTestingDb() func(ctx *pulumi.Context) error {
	return func(ctx *pulumi.Context) error {
		_, err := mysql.NewDatabase(ctx, "pulumi_testing", nil)
		if err != nil {
			return err
		}
		_, err = mysql.NewUser(ctx, "testUser", &mysql.UserArgs{
			Host:              pulumi.String("127.0.0.1"),
			PlaintextPassword: pulumi.String("test"),
			User:              pulumi.String("test"),
		})
		if err != nil {
			return err
		}
		return nil
	}
}

Next, execute SQL.

create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

In addition, this pulumi program is executed.

package main

import (
	"<http://github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql|github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql>"
	"<http://github.com/pulumi/pulumi/sdk/v3/go/pulumi|github.com/pulumi/pulumi/sdk/v3/go/pulumi>"
)

func main() {
	pulumi.Run(getInitLocalTestingDb())
}

func getInitLocalTestingDb() func(ctx *pulumi.Context) error {
	return func(ctx *pulumi.Context) error {
		database, err := mysql.NewDatabase(ctx, "pulumi_testing", nil)
		if err != nil {
			return err
		}
		testUser, err := mysql.NewUser(ctx, "testUser", &mysql.UserArgs{
			Host:              pulumi.String("127.0.0.1"),
			PlaintextPassword: pulumi.String("test"),
			User:              pulumi.String("test"),
		})
		if err != nil {
			return err
		}
		_, err = mysql.NewGrant(ctx, "testGrant", &mysql.GrantArgs{
			Database: database.Name,
			Host:     testUser.Host,
			Privileges: pulumi.StringArray{
				pulumi.String("SELECT"),
			},
			User: testUser.User,
			Table: pulumi.String("tutorials_tbl"),
		})
		if err != nil {
			return err
		}
		return nil
	}
}

It succeeded. However, when I run the pulumi program with the following changes, it fails.

Table: pulumi.String("tutorials_tbl.tutorial_id"),

result

Diagnostics:
  mysql:index:Grant (testGrant):
    error: Error running SQL (GRANT SELECT ON `pulumi_testing-f0a806e`.`tutorials_tbl.tutorial_id` TO 'test'@'127.0.0.1' REQUIRE NONE): Error 1146: Table 'pulumi_testing-f0a806e.tutorials_tbl.tutorial_id' doesn't exist
 
  pulumi:pulumi:Stack (app-dev2):
    error: update failed

The reason for the failure is clear: the Grant statement is incorrect. If true, I would like to execute the following Grant statement.

GRANT SELECT (tutorial_id) ON `pulumi_testing-f0a806e`.`tutorials_tbl` TO 'test'@'127.0.0.1' REQUIRE NONE;

the reason seems to me because the table

pulumi_testing-f0a806e.tutorials_tbl.tutorial_id

doesn't exist? How are you running the

create table

statement?