javif89 / New postgres DB and user

DO $$ DECLARE username text := 'new_user'; -- Variable for the username user_password text := 'new_password'; -- Variable for the user's password db_name text := 'new_database'; -- Variable for the database name BEGIN -- Step 1: Create the database EXECUTE format('CREATE DATABASE %I;', db_name); -- Step 2: Create the user with the password EXECUTE format('CREATE USER %I WITH PASSWORD %L;', username, user_password); -- Step 3: Connect to the created database -- In this case, we can't connect from within the same query, but you can use this after running the block: -- \c new_database; -- Step 4: Grant the new user all privileges on the created database EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I;', db_name, username); -- Step 5: Grant the new user full privileges on the public schema of the created database EXECUTE format('GRANT ALL PRIVILEGES ON SCHEMA public TO %I;', username); EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %I;', username); EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I;', username); EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I;', username); EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I;', username); -- Optionally, raise a notice to confirm completion RAISE NOTICE 'Database % created, user % granted privileges.', db_name, username; END $$;