New postgres DB and user 的修订

javif89 修订了这个 Gist 1740724147. 转到此修订

1 file changed, 3 insertions, 20 deletions

newdb.sql

			@@ -1,20 +1,3 @@
1		-	CREATE DATABASE spacebin;
2		-	DO $$
3		-	DECLARE
4		-	db_name text default 'spacebin';
5		-	username text default 'spacebin';
6		-	user_password text default '/Yg6OS+BJMj5rHFGhsf00A==';
7		-	BEGIN
8		-	EXECUTE format('CREATE USER %I WITH PASSWORD %L', username, user_password);
9		-
10		-	EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', db_name, username);
11		-	SET search_path TO db_name, public;
12		-
13		-	EXECUTE format('GRANT ALL PRIVILEGES ON SCHEMA public TO %I', username);
14		-	EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %I', username);
15		-	EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I', username);
16		-	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I', username);
17		-	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I', username);
18		-	END $$;
19		-
20		-	grant all privileges on schema public to spacebin
	1	+	CREATE DATABASE nocodb;
	2	+	create user nocodb with password '';
	3	+	grant all privileges on schema public to nocodb

javif89 修订了这个 Gist 1740723959. 转到此修订

1 file changed, 7 insertions, 5 deletions

newdb.sql

			@@ -1,9 +1,9 @@
1		-	CREATE DATABASE [DB Name];
	1	+	CREATE DATABASE spacebin;
2	2		DO $$
3	3		DECLARE
4		-	db_name text default '[SAME ONE USED ABOVE]';
5		-	username text default '[APP NAME]';
6		-	user_password text default '[MAKE A PASSWORD];
	4	+	db_name text default 'spacebin';
	5	+	username text default 'spacebin';
	6	+	user_password text default '/Yg6OS+BJMj5rHFGhsf00A==';
7	7		BEGIN
8	8		EXECUTE format('CREATE USER %I WITH PASSWORD %L', username, user_password);
9	9
			@@ -15,4 +15,6 @@ BEGIN
15	15		EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I', username);
16	16		EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I', username);
17	17		EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I', username);
18		-	END $$;
	18	+	END $$;
	19	+
	20	+	grant all privileges on schema public to spacebin

javif89 修订了这个 Gist 1740707507. 转到此修订

1 file changed, 14 insertions, 25 deletions

newdb.sql

			@@ -1,29 +1,18 @@
1		-	DO $$
	1	+	CREATE DATABASE [DB Name];
	2	+	DO $$
2	3		DECLARE
3		-	username text := 'new_user'; -- Variable for the username
4		-	user_password text := 'new_password'; -- Variable for the user's password
5		-	db_name text := 'new_database'; -- Variable for the database name
	4	+	db_name text default '[SAME ONE USED ABOVE]';
	5	+	username text default '[APP NAME]';
	6	+	user_password text default '[MAKE A PASSWORD];
6	7		BEGIN
7		-	-- Step 1: Create the database
8		-	EXECUTE format('CREATE DATABASE %I;', db_name);
	8	+	EXECUTE format('CREATE USER %I WITH PASSWORD %L', username, user_password);
9	9
10		-	-- Step 2: Create the user with the password
11		-	EXECUTE format('CREATE USER %I WITH PASSWORD %L;', username, user_password);
	10	+	EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', db_name, username);
	11	+	SET search_path TO db_name, public;
12	12
13		-	-- Step 3: Connect to the created database
14		-	-- In this case, we can't connect from within the same query, but you can use this after running the block:
15		-	-- \c new_database;
16		-
17		-	-- Step 4: Grant the new user all privileges on the created database
18		-	EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I;', db_name, username);
19		-
20		-	-- Step 5: Grant the new user full privileges on the public schema of the created database
21		-	EXECUTE format('GRANT ALL PRIVILEGES ON SCHEMA public TO %I;', username);
22		-	EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %I;', username);
23		-	EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I;', username);
24		-	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I;', username);
25		-	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I;', username);
26		-
27		-	-- Optionally, raise a notice to confirm completion
28		-	RAISE NOTICE 'Database % created, user % granted privileges.', db_name, username;
29		-	END $$;
	13	+	EXECUTE format('GRANT ALL PRIVILEGES ON SCHEMA public TO %I', username);
	14	+	EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %I', username);
	15	+	EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I', username);
	16	+	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I', username);
	17	+	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I', username);
	18	+	END $$;

javif89 修订了这个 Gist 1740701406. 转到此修订

1 file changed, 29 insertions

newdb.sql(文件已创建)

		@@ -0,0 +1,29 @@
1	+	DO $$
2	+	DECLARE
3	+	username text := 'new_user'; -- Variable for the username
4	+	user_password text := 'new_password'; -- Variable for the user's password
5	+	db_name text := 'new_database'; -- Variable for the database name
6	+	BEGIN
7	+	-- Step 1: Create the database
8	+	EXECUTE format('CREATE DATABASE %I;', db_name);
9	+
10	+	-- Step 2: Create the user with the password
11	+	EXECUTE format('CREATE USER %I WITH PASSWORD %L;', username, user_password);
12	+
13	+	-- Step 3: Connect to the created database
14	+	-- In this case, we can't connect from within the same query, but you can use this after running the block:
15	+	-- \c new_database;
16	+
17	+	-- Step 4: Grant the new user all privileges on the created database
18	+	EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I;', db_name, username);
19	+
20	+	-- Step 5: Grant the new user full privileges on the public schema of the created database
21	+	EXECUTE format('GRANT ALL PRIVILEGES ON SCHEMA public TO %I;', username);
22	+	EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %I;', username);
23	+	EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I;', username);
24	+	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I;', username);
25	+	EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I;', username);
26	+
27	+	-- Optionally, raise a notice to confirm completion
28	+	RAISE NOTICE 'Database % created, user % granted privileges.', db_name, username;
29	+	END $$;