PostgreSQL initiation

date posted: 2020-May-25, last edit date: 2020-Jun-01

/*
sudo su - postgres
psql
*/


-- STEP 0 -- revoke privileges for all unspecified users
\l+
revoke all privileges on database <db name> from public;
\l+


-- STEP 1 -- create new user
\du+
create user "<user name>" with encrypted password '<user password>';
\du+


-- STEP 2 -- create new database
\l+
create database "<DB name>";
revoke all privileges on database "<DB name>" from public;
\l+
\connect "<DB name>"
\dt+ *.*


-- STEP 3 -- grant privileges
\l+
grant all privileges on database "<DB name>" to "<user name>";
\l+


/*
exit
psql --host=localhost --username="<user name>" --dbname="<DB name>"
test that you are not be able to connect to other databases
*/

-- STEP 4 -- create new schema
select * from information_schema.schemata;
create schema <schema name>;
select * from information_schema.schemata;