Module6_Creating_and_managing_databases

Question 1

what is the order of creation of the default databases in a new instance.

Answer 1

First database is template1 and then a copy of that is template 0 and then a copy of that to POSTGRES. every new database is a copy of template1. all configuration happend in template will be alos configured in NEW database and not the existing once.

Question 2

what are the 3 ways to create a database?

Answer 2

from OS by using CREATEDB command
from PSQL bij using CREATE DATABASE command
from GUI

Question 3

who can by default login in a new database?

Answer 3

everyone with PUBLIC permission which is everyone. Best practice is to revoke connection to new database for users with only public priviliges.

Question 4

what are the ways to create a new database user?

Answer 4

1) with CREATE USER from psql

2) with createuser from OS (postgres BIN Directory)

Question 5

what are ROLES?

Answer 5

Role is a collection of priviliges. You can create a role with CREATE ROLE in PSQL. You can then grant priviliges to that role. Then you can grant this ROLE to a user and it will get all the needed priviliges at once.

Question 6

when to user GRANT or ALTER USER options to grant priviliges?

Answer 6

GRANT can be used to grant object level priviligens to a user. Always IN the database.
ALTER USER grant are on instance level.

Question 7

create a new user with name “MINA” and make it read a table in the public schema

Answer 7

Create user MINA with password “Mina123”;
grant connect on database postgres to Mina;
Grant usage on schema public to mina;
\c postgres MINA
select * from test;

Question 8

how to check current schem you are working in?

Answer 8

select current_schema();

Question 9

what are these short cuts:
\dt
\dn
\q
\L
\du
\c
\s
\e
\e FILENAME
\w FILENAME
\di
\ds
\dv
\db
\dS

Answer 9

\dt : show all tables in DB
\dn: list schema's in DB
\q: exit
\L: list databases
\du: show current user's permissions
\c: connect DATABASENAME USERNAME
\s show command history
\e will edit the query buffer and then exectute it
\e FILENAME will edit filename and then execute it
\w FILENAME will save the query buffer to filename
\di : list of indexes
\ds: list of sequences
\dv: list of views
\db: list of tablespaces
\dS: list of dictionaries