How Do I Make Sure My Database Stays Intact?

| Step Number | Actions                   |
|-------------|---------------------------|
| 1           | SELECT * FROM database.db |
| 2           | INSERT INTO database.db   |
| 3           | SELECT * FROM database.db |
// The above set of queries is an example of a possible database transaction. Note that in some databases a transaction could be a single query.

| Step Number | Actions                   |
|-------------|---------------------------|
| 1           | SELECT * FROM database.db |
| 2           | INSERT INTO database.db   |
| 3           | SELECT * FROM database.db |
// In the above transaction, to demonstrate atomicity, should any one step fail then every other step in the transaction would fail. For instance, if steps 1 and 2 passed but step 3 failed, then the transaction would revert and fail steps 1 and 2.

| Database Rules                              |
|---------------------------------------------|
| All employee salaries must be an integer    |
| All employee IDs must be unique             |
| No store can have more than fifty employees |

// In the above set of rules for a database, any transaction wanting to maintain Consistency must follow each rule. Should any rule be broken, the transaction would no longer maintain ACID properties.

| Transaction 1        | Transaction 2        |
|----------------------|----------------------|
| SELECT * FROM table1 | SELECT * FROM table2 |
| INSERT INTO table2   | INSERT INTO table1   |
| INSERT INTO table 1  | INSERT INTO table1   |
// Notice how in these two transactions that both are set to interact with table1 and table2. Because of this, the transactions would occur sequentially to maintain the Isolation ACID property.

-- On a new database cluster you can check the existing role with either:

-- From a Query, using `pg_roles` table 
SELECT rolname, rolsuper 
FROM pg_roles 
WHERE rolsuper = true;

/*
+-----------------+----------+
|     rolname     | rolsuper |
+-----------------+----------+
| postgres        | t        |
+-----------------+----------+
*/

-- In PSQL
-- => \du

/*
List of roles
+--------------+------------------------------------------------------------+-----------+
|  Role name   |                         Attributes                         | Member of |
+--------------+------------------------------------------------------------+-----------+
| postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
+--------------+------------------------------------------------------------+-----------+
*/

-- Checking User Permissions (Results orientation to horizontal...), true/false for values like `rolsuper`, `rolcanlogin`, `rolcreaterole`, etc.
SELECT * 
FROM pg_catalog.pg_roles 
LIMIT 1

/*
+-[ RECORD 1 ]---+-------------------+
| rolname        | pg_signal_backend |
| rolsuper       | f                 |
| rolinherit     | t                 |
| rolcreaterole  | f                 |
| rolcreatedb    | f                 |
| rolcanlogin    | f                 |
| rolreplication | f                 |
| rolbypassrls   | f                 |
+----------------+-------------------+
*/

-- Checking Table Permissions

select
	grantor,
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.table_privileges 
WHERE table_name = 'sample';

/*
+--------------+--------------+--------------+------------+----------------+
|   grantor    |   grantee    | table_schema | table_name | privilege_type |
+--------------+--------------+--------------+------------+----------------+
| postgres     | user_1       | public       | sample     | INSERT         |
| postgres     | user_1       | public       | sample     | SELECT         |
| postgres     | user_1       | public       | sample     | UPDATE         |
| postgres     | user_1       | public       | sample     | DELETE         |
| postgres     | user_1       | public       | sample     | TRUNCATE       |
| postgres     | user_1       | public       | sample     | REFERENCES     |
| postgres     | user_1       | public       | sample     | TRIGGER        |
+--------------+--------------+--------------+------------+----------------+
*/

-- Step 1. 
SELECT current_user;

/*
+--------------+
| current_user |
+--------------+
| postgres     |
+--------------+
*/

-- Step 2. Using SET, change the current permissions to mimic another user's (if user has proper permissions)
SET ROLE user_1;


-- Step 3. Confirm change
SELECT current_user;

/*
+--------------+
| current_user |
+--------------+
| user_1       |
+--------------+
*/

-- Create a role named `miriam` with `NOSUPERUSER` (is not a superuser) and `LOGIN` (can log in to the DB)
CREATE ROLE miriam WITH NOSUPERUSER LOGIN;

-- To GRANT Permissions
GRANT SELECT, UPDATE 
ON finance.revenue 
TO analyst;

-- To REVOKE Permissions
REVOKE SELECT, UPDATE 
ON finance.revenue 
FROM analyst;

-- Alter the default permissions to a schema, combine `ALTER DEFAULT` with a `GRANT` statement
ALTER DEFAULT PRIVILEGES IN SCHEMA finance
GRANT SELECT ON TABLES TO analyst;

-- Create a group role named `marketing` with `alice` and `bob` as members.
CREATE ROLE marketing 
WITH NOLOGIN ROLE alice, bob;

-- Group roles can be created with CREATE ROLE
CREATE ROLE finance WITH NOLOGIN;

-- Then the permissionns of that role (`finance`) can be 
-- `GRANT`ed to another role
GRANT finance TO charlie;

-- GRANT SELECT on columns a, b, d. When user_1 queries sample table for a, b, c, d they'll revieve an error.
GRANT SELECT (a, b, d) 
ON sample_table to user_1;

-- Set role into user_1...
SET ROLE user_1;

-- Attempted query on sample_table
SELECT * FROM sample_table;

-- ERROR:  permission denied for table sample_table.

-- Create a RLS Policy, only allow user's to return rows where current_user is equal to the row's column `salesperson`
CREATE POLICY emp_rls_policy ON accounts 
FOR SELECT TO employees USING (salesperson=current_user);

-- Alter table to enable the new RLS policy
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE TABLE tablename (
    myNum integer,
    myString varchar(50)
);

CREATE TABLE table_name (
    column_1 integer,
    column_2 text,
    column_3 numeric CHECK (column_3 > 0),
    column_4 numeric CHECK (column_4 > 0),
    CHECK (column_3 > column_4)
);

CREATE TABLE table_name (
    column_1 integer NOT NULL CHECK (column_3 > 0),
    column_2 text UNIQUE NOT NULL,
    column_3 numeric
);

CREATE TABLE table_name (
    column_1 integer NOT NULL,
    column_2 text NOT NULL,
    column_3 numeric
);

CREATE TABLE table_name (
    column_1 integer UNIQUE,
    column_2 text UNIQUE,
    column_3 numeric,
    column_4 text,
    UNIQUE(column_3, column_4)
);

-- A primary key on one column
CREATE TABLE table_name (
    column_1 integer PRIMARY KEY,
    column_2 text,
);

-- A composite primary key
CREATE TABLE table_name (
    column_1 integer,
    column_2 text,
    column_2 integer,
    PRIMARY KEY (column_1, column_2),
);

CREATE TABLE table_1 (
    column_1 integer PRIMARY KEY,
    column_2 text,
    column_3 numeric
);
 
-- Option 1 
CREATE TABLE table_2 (
    column_a integer PRIMARY KEY,
    column_b integer REFERENCES table_1 (column_2),
    column_c integer
);

-- Option 2 - Creating columns b and c are a composite foreign key referencing columns c1 and c2 from other_table
CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric,
    sale_price numeric
);


--Assume some values in `sale_price` are missing, and we'd like to apply a `NOT NULL` constraint on `sale_price`. We must `UPDATE` the table before applying our constraint.

UPDATE TABLE products 
SET sale_price = 0 WHERE sale_price IS NULL