22 lines
992 B
SQL
22 lines
992 B
SQL
-- Dev database for Vash Esports, matching the personal-website pattern:
|
|
-- one login role owns one database on the central Postgres instance.
|
|
-- Run as the postgres superuser:
|
|
-- psql -h 127.0.0.1 -p 5440 -U postgres -d postgres -v password='choose-one' -f scripts/bootstrap-dev-database.sql
|
|
--
|
|
-- Production stays in database "vash" (owner role vash, app role vash_app).
|
|
-- Staging stays in database "vash_staging" (owner role vash_staging, app role vash_staging_app).
|
|
|
|
SELECT format('CREATE ROLE vash_dev LOGIN PASSWORD %L', :'password')
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'vash_dev') \gexec
|
|
|
|
SELECT 'CREATE DATABASE vash_dev OWNER vash_dev'
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'vash_dev') \gexec
|
|
|
|
REVOKE ALL ON DATABASE vash_dev FROM PUBLIC;
|
|
GRANT CONNECT, TEMPORARY ON DATABASE vash_dev TO vash_dev;
|
|
|
|
\connect vash_dev
|
|
REVOKE ALL ON SCHEMA public FROM PUBLIC;
|
|
GRANT ALL ON SCHEMA public TO vash_dev;
|
|
ALTER SCHEMA public OWNER TO vash_dev;
|