DBA Tasks for Silencer¶

Introduction¶

The database tasks for database instance inno-silencer

Database Engine: Postgres version 12

Provider: Google Cloud Platform

Connection name: <project_id>:<project_region>:inno-silencer

Registered Database:

Schema	Description
beacon	Supplies marts data for clients

Schema	Description
beacon	Supplies marts data for clients level production
beacon_dev	Supplies marts data for clients level staging to test for member in data team

Initiation Setting¶

Roles IAM¶

# Ref: https://cloud.google.com/sql/docs/mysql/connect-functions
"roles/cloudsql.client",
"roles/cloudsql.instanceUser"

Connect to Cloud SQL for PostgreSQL¶

We can connect in one of the two methods below:

Connect with Cloud Shell: Quickstart: Connect to Cloud SQL for PostgreSQL from Cloud Shell | Google Cloud
Connect with PdAdmin 4: Connecting to GCP’s Cloud SQL (PostgresSQL) from PgAdmin — 3 simple steps | by Shivakumar Chandrashekar | Medium

Policies¶

The following rules are implemented for the database:

No	Rules	Affected Identifiers
1	Everyone can have access to the database and will be assigned the role `group_all_privileges`	+ Schema public
2	Only engineers who do ETL and the orchestration application belong to the `group_all_privileges`
3	Data members will go to the team_data group, included runners that run data pipelines.	+ Group team_data
4	Alway has revoke plan on any policies.Required to E.g: Drop user when create

Set database access¶

1. For users: create, add group, drop.¶

[0] For the metadata a) Show list user existed

SELECT * FROM pg_user;

b) Show perm of user

SELECT *
FROM information_schema.role_table_grants ;

[1] Create User

CREATE USER <user_name> WITH PASSWORD '<secret-password>' VALID UNTIL '2022-12-30';

Reff: Password Generator - LastPass

[2] Set Permissions

Set role and perm for user sieu_huynh

-- Set role read-only
GRANT group_read_only TO "sieu_huynh";
-- set perm to process all table in schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema name> TO "sieu_huynh";
-- Grant to team data priviledges;
ALTER GROUP team_data ADD USER "sieu_huynh";
-- or as an engineer:
-- GRANT group_all_privileges TO "sieu_huynh";

[3] Drop users, required deleted users when:

Team members leave
Revoke for securities purpose

Examples: Drop user sieu_huynh

Remove user from team data

ALTER GROUP team_data DROP USER "sieu_huynh"

Then drop user

DROP USER "sieu_huynh"

-- In case the user still holds ownership of the schema or table, use the CASCADE command, or revoke all rights of that user and then re-execute the `DROP USER` command.

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> FROM "sieu_huynh";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> FROM "sieu_huynh";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema_name> FROM "sieu_huynh";

2. Process for a new schema¶

[1] Script creates a new schema:

-- Create a new schema
CREATE SCHEMA <new schema name>;

[2] Grant access for groups and roles existed. If no groups or roles exist yet, do the step in the For group section and the For roles, groups section.

-- Change schema owner to group_all_privileges
ALTER SCHEMA <new schema name> OWNER TO group_all_privileges;
-- Grant access for group_all_privileges
GRANT ALL PRIVILEGES ON SCHEMA <new schema name> TO group_all_privileges;
-- Change all tables owner to group_all_privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA <new schema name> GRANT ALL PRIVILEGES ON TABLES TO group_all_privileges;
-- Grant access for group_read_only
GRANT USAGE ON SCHEMA <new schema name> TO group_read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA <new schema name> TO group_read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA <new schema name> GRANT SELECT ON TABLES TO group_read_only;

3. For group¶

Show list group existed

SELECT * FROM pg_group;

CREATE GROUP -- define a new user group CREATE GROUP

[1] Scripts to generate group team_data

-- Define first stage of group
CREATE GROUP team_data
WITH USER
"bao.truong@innotech.vn",
"sieu.huynh@innotech.vn",
"thu.nguyen@innotech.vn",
"sa-streaming-slardar",
"bao_truong",
"thu_nguyen",
"sieu_huynh";

[2] Rename group name, required admin

ALTER GROUP groupname RENAME TO newname;

[3] Drop group: removes the specified group. The users in the group are not removed. DROP GROUP

DROP GROUP team_data;

4. For Roles, Group¶

Replace <schema name> with a schema name existed

Role: group_read_only

[1] Scripts:

CREATE ROLE group_read_only;
GRANT CONNECT ON DATABASE lighthouse TO group_read_only;
GRANT USAGE ON SCHEMA <schema name> TO group_read_only;
-- grant access to existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO group_read_only;
-- grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO group_read_only;
-- Alter for search path
ALTER ROLE group_read_only
SET search_path
TO "$user", <schema name>;

Role: group_all_privileges

[1] Scripts:

CREATE ROLE group_all_privileges;
GRANT ALL PRIVILEGES ON DATABASE lighthouse TO group_all_privileges;
-- schema public
GRANT ALL PRIVILEGES ON SCHEMA <schema name> TO group_all_privileges;
-- grant access to existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema name> TO group_all_privileges;
-- grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT ALL PRIVILEGES ON TABLES TO group_all_privileges;

Group: group_all_privileges

At Postgres 12, This is the same with CREATE ROLE: CREATE GROUP, CREATE USER

Set search path default Permanently Set Postgresql Schema Path

Role: team_data

-- Connect to database
GRANT CONNECT ON DATABASE lighthouse TO team_data;
-- Each user can create perms
GRANT CREATE ON DATABASE lighthouse TO team_data;
-- List of shchemas controlled by team_data:
-- [
--    "<schema name>"
-- ]
-- Special Components
GRANT CREATE, USAGE ON SCHEMA <schema name> TO team_data;
-- Grant Usage
GRANT USAGE
  ON SCHEMA <schema name>
    TO team_data;
-- Grant access on exists table in target schemas
GRANT SELECT
  ON ALL TABLES IN SCHEMA <schema name>
    TO team_data;
-- Grant access on exists table in target schemas
ALTER DEFAULT PRIVILEGES
  IN SCHEMA <schema name>
  GRANT SELECT ON TABLES TO team_data;
-- Set group to role `group_read_only`
GRANT group_read_only TO team_data;

Role: innotech_group_runner

Description: Using for the runners in CI, database deployment

Valid through: Dec 31, 2022

[1] Scripts:

CREATE GROUP innotech_group_runner WITH VALID UNTIL '2022-12-31';
-- Basic: Connect only
GRANT CONNECT ON DATABASE lighthouse TO innotech_group_runner;

[2] Drop group

REVOKE CONNECT ON DATABASE lighthouse FROM innotech_group_runner;
DROP ROLE innotech_group_runner;

5. Process for a new table¶

Change owner to group_all_privileges and grant select for group_read_only

[1] Scripts:

ALTER TABLE todos OWNER TO group_all_privileges;
GRANT SELECT ON todos TO group_read_only;

6. DBA task for perms¶

a) Show list user existed

SELECT * FROM pg_user;

b) Show perm of user

SELECT *
FROM information_schema.role_table_grants;

# Shortcut helpful commands >>>
#
# ---- Create
# CLS.__table__.create(ENGINE)
#
# ---- Drop
# CLS.__table__.drop(ENGINE)
#
# ---- Truncate
# ENGINE.connect().execute(TABLE.__table__.delete())
#
# ---- Validate
# ENGINE.connect().execute(f"SELECT * FROM {TABLE.__table__} LIMIT 0").fetchall()

Source Reference¶

[1] [Database] Database Access Policy