Repository: supabase/cli
Pull request #890 (2023-02-28 02:38:12)

feat: support dumping and loading custom roles
## What kind of change does this PR introduce?

feature
closes https://github.com/supabase/cli/issues/877

## What is the new behavior?

- `supabase db dump --role-only` to dump custom roles from remote database
- `supabase db start` will load `supabase/roles.sql` if present

## Additional context

Add any other context or screenshots.


coveralls: ## Pull Request Test Coverage Report for [Build 4289522569](https://coveralls.io/builds/57353886)

* **21** of **30**   **(70.0%)**  changed or added relevant lines in **4** files are covered.
* No unchanged relevant lines lost coverage.
* Overall coverage increased (+**0.003%**) to **62.351%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [cmd/db.go](https://coveralls.io/builds/57353886/source?filename=cmd%2Fdb.go#L112) | 2 | 3 | 66.67%
| [internal/db/remote/commit/commit.go](https://coveralls.io/builds/57353886/source?filename=internal%2Fdb%2Fremote%2Fcommit%2Fcommit.go#L83) | 0 | 1 | 0.0%
| [internal/db/dump/dump.go](https://coveralls.io/builds/57353886/source?filename=internal%2Fdb%2Fdump%2Fdump.go#L43) | 17 | 19 | 89.47%
| [internal/db/start/start.go](https://coveralls.io/builds/57353886/source?filename=internal%2Fdb%2Fstart%2Fstart.go#L123) | 2 | 7 | 28.57%
<!-- | **Total:** | **21** | **30** | **70.0%** | -->


|  Totals | [![Coverage Status](https://coveralls.io/builds/57353886/badge)](https://coveralls.io/builds/57353886) |
| :-- | --: |
| Change from base [Build 4289162642](https://coveralls.io/builds/57352612): |  0.003% |
| Covered Lines: | 3723 |
| Relevant Lines: | 5971 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/cmd/db.go b/cmd/db.go
index 4fe1578e..8f6693c7 100644
--- a/cmd/db.go
+++ b/cmd/db.go
@@ -97,6 +97,7 @@ var (
 	}
 
 	dataOnly bool
+	roleOnly bool
 
 	dbDumpCmd = &cobra.Command{
 		Use:   "dump",
@@ -108,7 +109,7 @@ var (
 			}
 			host := utils.GetSupabaseDbHost(projectRef)
 			ctx, _ := signal.NotifyContext(cmd.Context(), os.Interrupt)
-			return dump.Run(ctx, file, username, dbPassword, database, host, dataOnly, fsys)
+			return dump.Run(ctx, file, username, dbPassword, database, host, dataOnly, roleOnly, fsys)
 		},
 	}
 
@@ -225,6 +226,8 @@ func init() {
 	// Build dump command
 	dumpFlags := dbDumpCmd.Flags()
 	dumpFlags.BoolVar(&dataOnly, "data-only", false, "Dumps only data records.")
+	dumpFlags.BoolVar(&roleOnly, "role-only", false, "Dumps only cluster roles.")
+	dbDumpCmd.MarkFlagsMutuallyExclusive("data-only", "role-only")
 	dumpFlags.StringVarP(&file, "file", "f", "", "File path to save the dumped contents.")
 	dumpFlags.StringVarP(&dbPassword, "password", "p", "", "Password to your remote Postgres database.")
 	cobra.CheckErr(viper.BindPFlag("DB_PASSWORD", dumpFlags.Lookup("password")))
diff --git a/internal/db/dump/dump.go b/internal/db/dump/dump.go
index dc53b02b..93fc9641 100644
--- a/internal/db/dump/dump.go
+++ b/internal/db/dump/dump.go
@@ -17,36 +17,48 @@ var (
 	dumpSchemaScript string
 	//go:embed templates/dump_data.sh
 	dumpDataScript string
+	//go:embed templates/dump_role.sh
+	dumpRoleScript string
 )
 
-func Run(ctx context.Context, path, username, password, database, host string, dataOnly bool, fsys afero.Fs) error {
+func Run(ctx context.Context, path, username, password, database, host string, dataOnly, roleOnly bool, fsys afero.Fs) error {
+	// Initialise output stream
+	var outStream afero.File
+	if len(path) > 0 {
+		f, err := fsys.OpenFile(path, os.O_WRONLY|os.O_CREATE|os.O_TRUNC, 0644)
+		if err != nil {
+			return err
+		}
+		defer f.Close()
+		outStream = f
+	} else {
+		outStream = os.Stdout
+	}
+	// Load the requested script
 	var script string
 	if dataOnly {
 		fmt.Fprintln(os.Stderr, "Dumping data from remote database...")
 		script = dumpDataScript
+	} else if roleOnly {
+		fmt.Fprintln(os.Stderr, "Dumping roles from remote database...")
+		script = dumpRoleScript
 	} else {
 		fmt.Fprintln(os.Stderr, "Dumping schemas from remote database...")
 		script = dumpSchemaScript
 	}
-	out, err := utils.DockerRunOnce(ctx, utils.Pg15Image, []string{
+	// Run script in docker
+	if err := utils.DockerRunOnceWithStream(ctx, utils.Pg15Image, []string{
 		"PGHOST=" + host,
 		"PGUSER=" + username,
 		"PGPASSWORD=" + password,
 		"EXCLUDED_SCHEMAS=" + strings.Join(utils.InternalSchemas, "|"),
+		"RESERVED_ROLES=" + strings.Join(utils.ReservedRoles, "|"),
 		"DB_URL=" + database,
-	}, []string{"bash", "-c", script})
-	if err != nil {
+	}, []string{"bash", "-c", script}, nil, "", outStream, os.Stderr); err != nil {
 		return errors.New("Error running pg_dump on remote database: " + err.Error())
 	}
-
 	if len(path) > 0 {
-		if err := afero.WriteFile(fsys, path, []byte(out), 0644); err != nil {
-			return err
-		}
 		fmt.Fprintln(os.Stderr, "Dumped schema to "+utils.Bold(path)+".")
-	} else {
-		fmt.Println(out)
 	}
-
 	return nil
 }
diff --git a/internal/db/dump/templates/dump_role.sh b/internal/db/dump/templates/dump_role.sh
new file mode 100755
index 00000000..6b8c4aa3
--- /dev/null
+++ b/internal/db/dump/templates/dump_role.sh
@@ -0,0 +1,18 @@
+#!/usr/bin/env bash
+set -euo pipefail
+
+# Explanation of pg_dumpall flags:
+#
+#   --roles-only     only include create, alter, and grant role statements
+pg_dumpall \
+    --roles-only \
+    --quote-all-identifier \
+    --no-role-passwords \
+    --no-comments \
+    --database "$DB_URL" \
+| sed -E "s/^CREATE ROLE \"($RESERVED_ROLES)\"/-- &/" \
+| sed -E "s/^ALTER ROLE \"postgres\" WITH NOSUPERUSER/-- &/" \
+| sed -E "s/^ALTER ROLE \"authenticator\" SET \"session_preload_libraries\" TO 'supautils'/-- &/"
+
+# Reset session config generated by pg_dump
+echo "RESET ALL;"
diff --git a/internal/db/remote/commit/commit.go b/internal/db/remote/commit/commit.go
index e7925081..ddb1b8b6 100644
--- a/internal/db/remote/commit/commit.go
+++ b/internal/db/remote/commit/commit.go
@@ -80,7 +80,7 @@ func fetchRemote(p utils.Program, ctx context.Context, schema []string, timestam
 		return err
 	} else if len(migrations) == 0 {
 		p.Send(utils.StatusMsg("Committing initial migration on remote database..."))
-		return dump.Run(ctx, path, username, password, database, host, false, fsys)
+		return dump.Run(ctx, path, username, password, database, host, false, false, fsys)
 	}
 
 	w := utils.StatusWriter{Program: p}
diff --git a/internal/db/start/start.go b/internal/db/start/start.go
index 9d4261b4..e68f2639 100644
--- a/internal/db/start/start.go
+++ b/internal/db/start/start.go
@@ -119,6 +119,13 @@ func initDatabase(ctx context.Context, fsys afero.Fs, w io.Writer, options ...fu
 	if err := diff.BatchExecDDL(ctx, conn, strings.NewReader(utils.GlobalsSql)); err != nil {
 		return err
 	}
+	if roles, err := fsys.Open(utils.CustomRolesPath); err == nil {
+		if err := diff.BatchExecDDL(ctx, conn, roles); err != nil {
+			return err
+		}
+	} else if !errors.Is(err, os.ErrNotExist) {
+		return err
+	}
 	fmt.Fprintln(w, "Setting up initial schema...")
 	return reset.InitialiseDatabase(ctx, conn, fsys)
 }
diff --git a/internal/utils/misc.go b/internal/utils/misc.go
index 5b3667ce..3b5669fe 100644
--- a/internal/utils/misc.go
+++ b/internal/utils/misc.go
@@ -135,6 +135,20 @@ var (
 		"supabase_functions",
 		"supabase_migrations",
 	}, SystemSchemas...)
+	ReservedRoles = []string{
+		"anon",
+		"authenticated",
+		"authenticator",
+		"dashboard_user",
+		"pgbouncer",
+		"postgres",
+		"service_role",
+		"supabase_admin",
+		"supabase_auth_admin",
+		"supabase_functions_admin",
+		"supabase_replication_admin",
+		"supabase_storage_admin",
+	}
 
 	SupabaseDirPath       = "supabase"
 	ConfigPath            = filepath.Join(SupabaseDirPath, "config.toml")
@@ -146,6 +160,7 @@ var (
 	FallbackImportMapPath = filepath.Join(FunctionsDir, "import_map.json")
 	DbTestsDir            = filepath.Join(SupabaseDirPath, "tests")
 	SeedDataPath          = filepath.Join(SupabaseDirPath, "seed.sql")
+	CustomRolesPath       = filepath.Join(SupabaseDirPath, "roles.sql")
 )
 
 // Used by unit tests
diff --git a/internal/utils/templates/globals.sql b/internal/utils/templates/globals.sql
index ae92534c..e3da1bf1 100644
--- a/internal/utils/templates/globals.sql
+++ b/internal/utils/templates/globals.sql
@@ -35,10 +35,12 @@ CREATE ROLE supabase_admin;
 ALTER ROLE supabase_admin WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'postgres';
 CREATE ROLE supabase_auth_admin;
 ALTER ROLE supabase_auth_admin WITH NOSUPERUSER NOINHERIT CREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'postgres';
-CREATE ROLE supabase_storage_admin;
-ALTER ROLE supabase_storage_admin WITH NOSUPERUSER NOINHERIT CREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'postgres';
 CREATE ROLE supabase_functions_admin;
 ALTER ROLE supabase_functions_admin WITH NOSUPERUSER NOINHERIT CREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'postgres';
+CREATE ROLE supabase_replication_admin;
+ALTER ROLE supabase_replication_admin WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION NOBYPASSRLS PASSWORD 'postgres';
+CREATE ROLE supabase_storage_admin;
+ALTER ROLE supabase_storage_admin WITH NOSUPERUSER NOINHERIT CREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'postgres';
 
 --
 -- User Configurations


Test patch:
diff --git a/internal/db/dump/dump_test.go b/internal/db/dump/dump_test.go
index ab1b55dc..40691456 100644
--- a/internal/db/dump/dump_test.go
+++ b/internal/db/dump/dump_test.go
@@ -33,7 +33,7 @@ func TestPullCommand(t *testing.T) {
 		apitest.MockDockerStart(utils.Docker, imageUrl, containerId)
 		require.NoError(t, apitest.MockDockerLogs(utils.Docker, containerId, "hello world"))
 		// Run test
-		err := Run(context.Background(), "schema.sql", username, password, database, host, false, fsys)
+		err := Run(context.Background(), "schema.sql", username, password, database, host, false, false, fsys)
 		// Check error
 		assert.NoError(t, err)
 		assert.Empty(t, apitest.ListUnmatchedRequests())
@@ -52,7 +52,7 @@ func TestPullCommand(t *testing.T) {
 		apitest.MockDockerStart(utils.Docker, imageUrl, containerId)
 		require.NoError(t, apitest.MockDockerLogs(utils.Docker, containerId, "hello world"))
 		// Run test
-		err := Run(context.Background(), "", username, password, database, host, false, fsys)
+		err := Run(context.Background(), "", username, password, database, host, false, false, fsys)
 		// Check error
 		assert.NoError(t, err)
 		assert.Empty(t, apitest.ListUnmatchedRequests())
@@ -68,7 +68,7 @@ func TestPullCommand(t *testing.T) {
 			Get("/v" + utils.Docker.ClientVersion() + "/images").
 			Reply(http.StatusServiceUnavailable)
 		// Run test
-		err := Run(context.Background(), "", username, password, database, host, false, fsys)
+		err := Run(context.Background(), "", username, password, database, host, false, false, fsys)
 		// Check error
 		assert.ErrorContains(t, err, "Error running pg_dump on remote database: request returned Service Unavailable")
 		assert.Empty(t, apitest.ListUnmatchedRequests())
@@ -83,7 +83,7 @@ func TestPullCommand(t *testing.T) {
 		apitest.MockDockerStart(utils.Docker, imageUrl, containerId)
 		require.NoError(t, apitest.MockDockerLogs(utils.Docker, containerId, "hello world"))
 		// Run test
-		err := Run(context.Background(), "schema.sql", username, password, database, host, false, fsys)
+		err := Run(context.Background(), "schema.sql", username, password, database, host, false, false, fsys)
 		// Check error
 		assert.ErrorContains(t, err, "operation not permitted")
 		assert.Empty(t, apitest.ListUnmatchedRequests())