new_pguser.sql
· 855 B · MySQL
Raw
-- Create a new user (role)
CREATE USER myappuser WITH
PASSWORD 'your_secure_password'
NOSUPERUSER
CREATEDB
NOCREATEROLE
INHERIT
LOGIN;
-- Create a new database owned by the user
CREATE DATABASE myappdb WITH
OWNER = myappuser
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;
-- Connect to the new database
\c myappdb
-- Grant all privileges on the database to the user
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser;
-- Create schema and grant privileges
CREATE SCHEMA IF NOT EXISTS myappschema AUTHORIZATION myappuser;
-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR USER myappuser IN SCHEMA myappschema
GRANT ALL ON TABLES TO myappuser;
-- Set the search path
ALTER DATABASE myappdb SET search_path TO myappschema, public;
| 1 | -- Create a new user (role) |
| 2 | CREATE USER myappuser WITH |
| 3 | PASSWORD 'your_secure_password' |
| 4 | NOSUPERUSER |
| 5 | CREATEDB |
| 6 | NOCREATEROLE |
| 7 | INHERIT |
| 8 | LOGIN; |
| 9 | |
| 10 | -- Create a new database owned by the user |
| 11 | CREATE DATABASE myappdb WITH |
| 12 | OWNER = myappuser |
| 13 | ENCODING = 'UTF8' |
| 14 | LC_COLLATE = 'en_US.UTF-8' |
| 15 | LC_CTYPE = 'en_US.UTF-8' |
| 16 | TEMPLATE = template0; |
| 17 | |
| 18 | -- Connect to the new database |
| 19 | \c myappdb |
| 20 | |
| 21 | -- Grant all privileges on the database to the user |
| 22 | GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser; |
| 23 | |
| 24 | -- Create schema and grant privileges |
| 25 | CREATE SCHEMA IF NOT EXISTS myappschema AUTHORIZATION myappuser; |
| 26 | |
| 27 | -- Set default privileges for future tables |
| 28 | ALTER DEFAULT PRIVILEGES FOR USER myappuser IN SCHEMA myappschema |
| 29 | GRANT ALL ON TABLES TO myappuser; |
| 30 | |
| 31 | -- Set the search path |
| 32 | ALTER DATABASE myappdb SET search_path TO myappschema, public; |