https://pulumi.com logo
Title
i

incalculable-monkey-35668

06/19/2022, 3:59 AM
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';
n

nice-lizard-13594

06/20/2022, 12:43 AM
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.
i

incalculable-monkey-35668

06/20/2022, 1:07 AM
Thank you!!!🙏 That’s the information I need. I will confirm it.
b

billowy-army-68599

06/20/2022, 1:49 AM
@incalculable-monkey-35668 does the user you're connecting to the mysql database have
GRANT
permission? what error are you getting?
i

incalculable-monkey-35668

06/21/2022, 10:23 AM
@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;
b

billowy-army-68599

06/21/2022, 1:24 PM
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?