josh revised this gist . Go to revision
1 file changed, 2 insertions, 5 deletions
new_pguser.sql
| @@ -1,9 +1,6 @@ | |||
| 1 | - | CREATE USER newuser WITH | |
| 2 | - | PASSWORD 'your_secure_password' | |
| 3 | - | LOGIN; | |
| 1 | + | CREATE USER newuser WITH PASSWORD 'your_secure_password' LOGIN; | |
| 4 | 2 | ||
| 5 | - | CREATE DATABASE newdatabase WITH | |
| 6 | - | OWNER = newuser; | |
| 3 | + | CREATE DATABASE newdatabase WITH OWNER = newuser; | |
| 7 | 4 | ||
| 8 | 5 | \c newdatabase | |
| 9 | 6 | ||
josh revised this gist . Go to revision
1 file changed, 7 insertions, 27 deletions
new_pguser.sql
| @@ -1,32 +1,12 @@ | |||
| 1 | - | -- Create a new user (role) | |
| 2 | - | CREATE USER myappuser WITH | |
| 1 | + | CREATE USER newuser WITH | |
| 3 | 2 | PASSWORD 'your_secure_password' | |
| 4 | - | NOSUPERUSER | |
| 5 | - | CREATEDB | |
| 6 | - | NOCREATEROLE | |
| 7 | - | INHERIT | |
| 8 | 3 | LOGIN; | |
| 9 | 4 | ||
| 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; | |
| 5 | + | CREATE DATABASE newdatabase WITH | |
| 6 | + | OWNER = newuser; | |
| 17 | 7 | ||
| 18 | - | -- Connect to the new database | |
| 19 | - | \c myappdb | |
| 8 | + | \c newdatabase | |
| 20 | 9 | ||
| 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; | |
| 10 | + | GRANT ALL PRIVILEGES ON DATABASE newdatabase TO newuser; | |
| 11 | + | GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO newuser; | |
| 12 | + | GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO newuser; | |
josh revised this gist . Go to revision
1 file changed, 32 insertions
new_pguser.sql(file created)
| @@ -0,0 +1,32 @@ | |||
| 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; | |