-
-
Save msalahat/1d4501dbc863c901a2ce590b81c84391 to your computer and use it in GitHub Desktop.
Create new postgres db user with ownership on database and objects
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| read -p "Enter database super user: " PG_USER | |
| read -p "Enter database host: " PG_HOST | |
| read -p "Enter NEW database user: " DB_USER | |
| read -s -p "Enter password for '$DB_USER': " DB_PASS | |
| echo | |
| read -p "Enter TARGET database name: " DB_NAME | |
| echo "Creating user '$DB_USER' if not exists..." | |
| # Create user if not exists | |
| psql -U "$PG_USER" -h "$PG_HOST" -d postgres <<EOF | |
| DO \$\$ | |
| BEGIN | |
| IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${DB_USER}') THEN | |
| CREATE USER ${DB_USER} WITH PASSWORD '${DB_PASS}'; | |
| END IF; | |
| END | |
| \$\$; | |
| EOF | |
| echo "Changing ownership of database '$DB_NAME'..." | |
| psql -U "$PG_USER" -h "$PG_HOST" -d postgres -c "ALTER DATABASE \"${DB_NAME}\" OWNER TO ${DB_USER};" | |
| echo "Granting privileges on schema & future objects..." | |
| psql -U "$PG_USER" -h "$PG_HOST" -d "$DB_NAME" <<EOF | |
| GRANT CONNECT, TEMPORARY ON DATABASE "$DB_NAME" TO $DB_USER; | |
| GRANT USAGE, CREATE ON SCHEMA public TO $DB_USER; | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $DB_USER; | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $DB_USER; | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO $DB_USER; | |
| EOF | |
| echo "Transferring ownership of existing objects using fast \gexec..." | |
| psql -U "$PG_USER" -h "$PG_HOST" -d "$DB_NAME" <<EOF | |
| -- Tables | |
| SELECT 'ALTER TABLE ' || quote_ident(schemaname) || '.' || quote_ident(tablename) | |
| || ' OWNER TO ${DB_USER};' | |
| FROM pg_tables | |
| WHERE schemaname = 'public' | |
| \gexec | |
| -- Sequences | |
| SELECT 'ALTER SEQUENCE ' || quote_ident(schemaname) || '.' || quote_ident(sequencename) | |
| || ' OWNER TO ${DB_USER};' | |
| FROM pg_sequences | |
| WHERE schemaname = 'public' | |
| \gexec | |
| -- Functions | |
| SELECT 'ALTER FUNCTION ' || n.nspname || '.' || p.proname || '(' | |
| || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ${DB_USER};' | |
| FROM pg_proc p | |
| JOIN pg_namespace n ON p.pronamespace = n.oid | |
| WHERE n.nspname = 'public' | |
| \gexec | |
| EOF | |
| echo "✅ Done: '$DB_USER' fully owns '$DB_NAME' and all its objects." |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment