Forem: whchi

Managing Object Ownership in PostgreSQL Migrations

whchi — Sun, 03 Aug 2025 13:27:46 +0000

Migrating a PostgreSQL database involves more than just copying data and schema—it also requires careful handling of ownership and privileges. Failure to properly manage these aspects can lead to permission errors, particularly when roles need to ALTER, TRUNCATE, or DROP objects. This guide outlines solutions to ensure a smooth migration process with proper object ownership.

"Owner" in PostgreSQL

In PostgreSQL, every database object—such as tables, views, sequences, functions, and even schemas—is owned by a role. The owner is the only role that can perform certain high-level operations on the object unless privileges are explicitly reassigned.

PostgreSQL roles are essentially accounts, and they can either be:

Superusers: These are roles with unrestricted access. They can bypass ownership rules but should be used cautiously.
Non-superuser roles: These require explicit ownership or privileges to modify objects.

For production environments, it’s a best practice to delegate ownership to a specific application role and avoid using the default superuser for schema management or migrations.

Why Ownership Matters in Migration

When you dump and restore a database (e.g., using pg_dump and pg_restore), all objects retain their original ownership unless explicitly changed. If the original owner doesn't exist or isn't appropriate in the target environment, you'll encounter permission issues.

Common symptoms of incorrect ownership:

ERROR: must be owner of table
Inability to run schema-altering migrations (e.g., ALTER TABLE, DROP, etc.)
Unexpected behavior in CI/CD pipelines or database initialization scripts

Solutions For Handling Ownership in Migration

1. use `pg_dump` & `pg_restore` correctly

Recommended when migrating to a different environment or when role structures differ between source and target databases.

# Export the database without OWNER and GRANT statements
pg_dump --no-owner --no-privileges -Fc -f db.dump your_db
# Restore into a new database as the desired application role
pg_restore --role=your_app_role -d your_new_db db.dump

This ensures that all objects are created under your_app_role without retaining the original roles or grants from the source database.

2. Set Owner During Database Creation

When provisioning a new database, you can set the owner from the beginning:

CREATE DATABASE your_new_db OWNER your_owner;
\c your_new_db
SET ROLE your_owner;
CREATE SCHEMA my_schema;

This ensures all subsequently created objects inherit the correct owner.

3. Use a One-Time Ownership Migration Script

If ownership needs to be updated after initialization, use a PL/pgSQL block to update all object types in one go:

-- The following script automates ownership reassignment for all tables in a database, ensuring consistency across schemas."

DO $$
DECLARE
    rec RECORD;
BEGIN
    -- Alter tables
    FOR rec IN 
        SELECT n.nspname, c.relname 
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE c.relkind = 'r'
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of table %.% to your_new_owner', rec.nspname, rec.relname;
    END LOOP;

    -- Repeat for sequences, views, enums, domains, functions, schemas, and other objects you want
END $$;

This approach ensures all relevant objects are properly reassigned to the desired role, preventing future permission issues.

4. Use `GRANT` When Ownership Change Isn't Required

You can delegate permissions via GRANT, but this does not allow operations like DROP, ALTER, or TRUNCATE. It’s a more limited but safer approach for read/write access

-- schema
GRANT ALL ON SCHEMA my_schema TO new_role;
-- table
GRANT ALL ON TABLE my_schema.my_table TO new_role;
GRANT ALL ON ALL TABLES IN SCHEMA my_schema TO new_role;
-- sequence
GRANT ALL ON SEQUENCE my_schema.my_sequence TO new_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA my_schema TO new_role;
-- view
GRANT ALL ON my_schema.my_view TO new_role;
-- enum
GRANT ALL ON TYPE my_schema.my_enum TO new_role;
-- function
GRANT ALL ON FUNCTION my_schema.my_function() TO new_role;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA my_schema TO new_role;

Use GRANT when you want multiple roles to collaborate without transferring ownership.

Final Advice

Plan ownership early: It’s easier to assign correct owners during initialization than to fix them later.
Avoid superuser for app logic: Delegate to application-specific roles with scoped permissions.
Automate ownership reassignment: Use PL/pgSQL to enforce consistency in your CI/CD pipeline or database bootstrap scripts.
Audit before migration: Run queries to list current owners and identify mismatches before migrating.

By handling ownership carefully, you ensure your PostgreSQL migrations remain predictable, secure, and maintainable.

Introduction to access control model: ACL, RBAC, ABAC

whchi — Tue, 06 May 2025 02:43:00 +0000

In system design, access control is a critical mechanism to ensure data security and correct authorization of functionalities. Depending on the complexity of the system, the variety of user roles, and the flexibility needed in resource management, there are three common access control models: ACL (Access Control List), RBAC (Role-Based Access Control), and ABAC (Attribute-Based Access Control). Below is a brief comparison of these three models, outlining their design logic, characteristics, and application scenarios to help clarify the selection and implementation considerations.

ACL

Direct authorization, fine-grained, but difficult to manage

Design Logic: User → Resource + Operation

If a user has read and update permissions for the development department's file list, they can view all the data under that department and make changes to it.

RBAC

Simplified management, clear structure, but limited flexibility

Design Logic: Role → Resource + Operation; User is assigned to a role

If a user’s role is a department manager, they can perform CRUD operations on all resources within that department.

ABAC

Highest flexibility, dynamic condition evaluation based on the user's, resource's, and environment's attributes to determine authorization.
Some variants include ReBAC (Relationship-Based Access Control), LBAC (Label-Based Access Control).

Design Logic: Access is determined based on attributes.

If a user’s department attribute is "HR Department," the resource's attribute is "personnel data," and the current time is within working hours, access is granted.

Comparison Table

Item	ACL (Access Control List)	RBAC (Role-Based Access Control)	ABAC (Attribute-Based Access Control)
📘 Authorization Target	User → Resource	Role → Resource	User attributes, resource attributes, environment attributes
👤 User Binding Method	User is directly granted access	User is assigned a role	User attributes are used to check access
🔄 Authorization Method	Static (each rule is defined clearly)	Static (role-based)	Dynamic condition-based (using multiple attributes)
🧱 Database Design Simplicity	Simple (but hard to scale)	Moderate	Complex (requires extra logic for attributes and rules)
⚙️ Flexibility / Detail Control	High (per user and resource)	Medium (role-level)	Very high (can use any attribute condition)
📈 Maintenance Cost (Large Systems)	High (hard to manage when users grow)	Low to medium (if roles are well designed)	High (complex rules and attributes need engine support)
🔐 Permission Sharing / Inheritance	Low (granted per user)	High (shared roles, hierarchical roles)	High (attributes are reusable)
🛡️ Suitable Resource Scale	Small (few users and resources)	Medium to large (with stable role structure)	Large and complex (many rules, multi-tenant)
🧠 Learning / Implementation Difficulty	Low	Low to medium	High (requires abstract thinking, policy language like XACML or engines like OPA)
🧭 Suitable Use Cases	Personal systems, sensitive data with fine control	Enterprise backends, SaaS, shared platforms	Banks, government, insurance, dynamic access control, zero-trust
🧩 Typical Examples	Dropbox, Google Drive “share with someone”	Internal company systems (e.g., Admin, Manager)	Data classification access, policy-driven API Gateway
👮🏻‍♂️ Security	High (fine control, but human error risk)	Medium (depends on good role design)	High (granular control, but needs accurate rules/attributes)
🏎️ Performance	Decreases with scale (each access is checked)	High (simple role lookup)	Low (many attributes and rules to evaluate)
🎲 Standard Support	None	NIST RBAC Model	XACML

Setting up TLS connection for containerized PostgreSQL database

whchi — Mon, 24 Feb 2025 06:52:40 +0000

When connecting your database to external services, especially SaaS platforms, you often can't restrict access by IP address or domain. In these cases, enabling TLS (Transport Layer Security) encryption helps keep your data safe.

PostgreSQL version: 16

Understanding PostgreSQL Client Connection Types

PostgreSQL clients can connect in six different ways

disabled: No encryption at all. Only safe for local networks.
allow: Prefers unencrypted connections but will use encryption if the server requires it.
prefer: (Default for most clients) Tries to use encryption first but accepts unencrypted connections if necessary.
require: Must use encryption. Won't connect without it but doesn't verify certificates.
verify_ca: Uses encryption and checks if the server's certificate is signed by a trusted authority.
verify_full: The most secure option. Checks encryption, certificates, and ensures the server name matches the certificate.

The most secure options are verify_ca and verify_full, but they require more setup in development:

one-way verification: You need the root CA certificate
two-way(mTLS) verification: You need both the client certificate and key in your connection string, the certificate/key MUST signed by root CA and key.

Setting Up the Server(One-Way Verification)

1. Create Certificate and Key

# Set to 100 years for development use
openssl req -x509 -newkey rsa:4096 -sha256 -nodes -keyout key.pem -out cert.pem -days 36500

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/cert.pem'
ssl_key_file = '/var/lib/postgresql/key.pem'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all scram-sha-256
hostnossl all all all reject

4. Set Up Docker Compose

services:
  postgres:
    image: postgres:16.3
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./.docker/postgres/config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./.docker/postgres/config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./.docker/postgres/certs/cert.pem:/var/lib/postgresql/cert.pem:ro
      - ./.docker/postgres/certs/key.pem:/var/lib/postgresql/key.pem:ro
      - postgres:/var/lib/postgresql/data
    command:
        - postgres
        - -c
        - config_file=/etc/postgresql/postgresql.conf
        - -c
        - hba_file=/etc/postgresql/pg_hba.conf

Connecting to Your Database

After setting everything up, just add sslmode=require to your connection string:

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=require"

That's all you need to establish an encrypted connection to your PostgreSQL database.

Setting Up the Server(mTLS)

For mTLS (mutual TLS), both the server and client need valid certificates signed by a trusted Certificate Authority (CA). Here's how to set it up

1. Create certifications for server and client

Your server.csr CN MUST be your postgreSQL hostname

# 1. Create Root CA
openssl genrsa -out root.key 4096
openssl req -x509 -new -nodes -key root.key -sha256 -days 36500 -out root.crt \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=IT/CN=PostgreSQL Root CA"

# 2. create server cert
openssl genrsa -out server.key 4096
openssl req -new -key server.key -out server.csr \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Database/CN=postgres"

# This is important that SAN MUST contains all your possible DNS/IP 
openssl x509 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt -days 36500 -sha256 \
  -extfile <(printf "subjectAltName=DNS:localhost,DNS:postgres,IP:127.0.0.1,IP:192.168.0.23")

# 3. create client cert - CN here MUST same as the postgres user you want
openssl genrsa -out client.key 4096
openssl req -new -key client.key -out client.csr \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Developers/CN=postgres"

openssl x509 -req -in client.csr -CA root.crt -CAkey root.key -CAcreateserial \
  -out client.crt -days 36500 -sha256

If you want your client CN using custom map, you should use pg_ident.conf and adjust setting in pg_hba.conf to archive that

# pg_hba.conf
hostssl all all 0.0.0.0/0 cert map=my_map
# pg_ident.conf
my_map /CN=client postgres

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/server.crt'
ssl_key_file = '/var/lib/postgresql/server.key'
ssl_ca_file = '/var/lib/postgresql/root.crt'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all cert clientcert=verify-full
hostnossl all all all reject

4. Update docker-compose.yml

Update volume bindings

...
  volumes:
      - ./.docker/postgres/config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./.docker/postgres/config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./.docker/postgres/certs/server.crt:/var/lib/postgresql/server.crt:ro
      - ./.docker/postgres/certs/server.key:/var/lib/postgresql/server.key:ro
      - ./.docker/postgres/certs/root.crt:/var/lib/postgresql/root.crt:ro
...

Connecting to Your Database

After setting everything up, add the following options to your connection string

sslmode=verify-full
sslcert=client.crt
sslkey=client.key
sslrootcert=root.crt

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=verify-full&sslcert=client.crt&sslkey=client.key&sslrootcert=root.crt"

Setting up TLS connection for containerized PostgreSQL database

whchi — Sat, 22 Feb 2025 08:27:43 +0000

When you need to allow external connections to your database while keeping costs low, encrypting the connection is essential. This guide shows you the simplest way to set up encrypted connections for your development environment.

Why Do We Need Encryption?

PostgreSQL version: 16

Understanding PostgreSQL Client Connection Types

PostgreSQL clients can connect in six different ways

disabled: No encryption at all. Only safe for local networks.
allow: Prefers unencrypted connections but will use encryption if the server requires it.
prefer: (Default for most clients) Tries to use encryption first but accepts unencrypted connections if necessary.
require: Must use encryption. Won't connect without it but doesn't verify certificates.
verify_ca: Uses encryption and checks if the server's certificate is signed by a trusted authority.
verify_full: The most secure option. Checks encryption, certificates, and ensures the server name matches the certificate.

The most secure options are verify_ca and verify_full, but they require more setup in development:

one-way verification: You need the root CA certificate
two-way(mTLS) verification: You need both the client certificate and key in your connection string, the certificate/key MUST signed by root CA and key.

This guide focuses on setting up one-way verification for development environments since it's simpler while still providing good security.

Setting Up the Server(One-Way Verification)

1. Create Certificate and Key

# Set to 100 years for development use
openssl req -x509 -newkey rsa:4096 -sha256 -nodes -keyout key.pem -out cert.pem -days 36500

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/cert.pem'
ssl_key_file = '/var/lib/postgresql/key.pem'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all scram-sha-256
hostnossl all all all reject

4. Set Up Docker Compose

services:
  postgres:
    image: postgres:16.3
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./.docker/postgres/config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./.docker/postgres/config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./.docker/postgres/certs/cert.pem:/var/lib/postgresql/cert.pem:ro
      - ./.docker/postgres/certs/key.pem:/var/lib/postgresql/key.pem:ro
      - postgres:/var/lib/postgresql/data
    command:
        - postgres
        - -c
        - config_file=/etc/postgresql/postgresql.conf
        - -c
        - hba_file=/etc/postgresql/pg_hba.conf

Connecting to Your Database

After setting everything up, just add sslmode=require to your connection string:

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=require"

That's all you need to establish an encrypted connection to your PostgreSQL database.

Setting Up the Server(mTLS)

For mTLS (mutual TLS), both the server and client need valid certificates signed by a trusted Certificate Authority (CA). Here's how to set it up

1. Create certifications for server and client

Your server.csr CN MUST be your postgreSQL hostname

# 1. Create Root CA
openssl genrsa -out root.key 4096
openssl req -x509 -new -nodes -key root.key -sha256 -days 36500 -out root.crt \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=IT/CN=PostgreSQL Root CA"

# 2. create server cert
openssl genrsa -out server.key 4096
openssl req -new -key server.key -out server.csr \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Database/CN=postgres"

# This is important that SAN MUST contains all your possible DNS/IP 
openssl x509 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt -days 36500 -sha256 \
  -extfile <(printf "subjectAltName=DNS:localhost,DNS:postgres,IP:127.0.0.1,IP:192.168.0.23")

# 3. create client cert - CN here MUST same as the postgres user you want
openssl genrsa -out client.key 4096
openssl req -new -key client.key -out client.csr \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Developers/CN=postgres"

openssl x509 -req -in client.csr -CA root.crt -CAkey root.key -CAcreateserial \
  -out client.crt -days 36500 -sha256

If you want your client CN using custom map, you should use pg_ident.conf and adjust setting in pg_hba.conf to archive that

# pg_hba.conf
hostssl all all 0.0.0.0/0 cert map=my_map
# pg_ident.conf
my_map /CN=client postgres

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/server.crt'
ssl_key_file = '/var/lib/postgresql/server.key'
ssl_ca_file = '/var/lib/postgresql/root.crt'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all cert clientcert=verify-full
hostnossl all all all reject

4. Update docker-compose.yml

Update volume bindings

...
  volumes:
      - ./.docker/postgres/config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./.docker/postgres/config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./.docker/postgres/certs/server.crt:/var/lib/postgresql/server.crt:ro
      - ./.docker/postgres/certs/server.key:/var/lib/postgresql/server.key:ro
      - ./.docker/postgres/certs/root.crt:/var/lib/postgresql/root.crt:ro
...

Connecting to Your Database

After setting everything up, add the following options to your connection string

sslmode=verify-full
sslcert=client.crt
sslkey=client.key
sslrootcert=root.crt

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=verify-full&sslcert=client.crt&sslkey=client.key&sslrootcert=root.crt"

Integrate Self-host Infisical into your NestJS project

whchi — Wed, 11 Sep 2024 15:26:34 +0000

Why centralized secret management is necessary

In modern software development, especially in containerized and collaborative environments, centralized secret management has become increasingly important. Here are

Flexibility in containerized deployment

Real-time environment variable updates: In containerized deployments, centralized secret management allows us to easily update environment variables without rebuilding or redeploying containers. This greatly improves system flexibility and security.
Environment consistency: It ensures all container instances use the same up-to-date secrets, reducing problems caused by environment inconsistencies.

Convenience in multi-developer scenarios

Avoiding .env file transfers: Traditionally, developers might need to send .env files via email or messaging apps, which is not only insecure but can also lead to version confusion.
Permission management: Centralized management allows us to set different access permissions for different team members, enhancing security.
Version control: You can track the change history of secrets, making audits and rollbacks easier. two main reasons:

A little about Infisical

Infisical is a secret management service similar to HashiCorp Vault, but it focuses more on the developer experience.

Advantages of Infisical

User-friendly: Offers an intuitive web interface and CLI tools, making secret management simple.
Integration with development workflows: Provides SDKs in multiple languages, making it easy to integrate into existing projects.
Team collaboration: Supports secure sharing and management of secrets among team members.

Paid features

Advanced audit logs
Custom roles and more granular permission controls
SAML single sign-on
Advanced key rotation strategies

Writing a NestJS Module to integrate Infisical

First, install the necessary dependency:

npm install @infisical/sdk

Then, create a new infisical.module.ts

import { DynamicModule, Global, Module } from '@nestjs/common';
import { InfisicalClient } from '@infisical/sdk';
import { InfisicalService } from './infisical.service';
import { InfisicalModuleOptions } from './infisical-module-options.type';
import { ConfigModule, ConfigService } from '@nestjs/config';

@Global()
@Module({})
export class InfisicalModule {
  static forRoot(options: InfisicalModuleOptions): DynamicModule {
    return {
      imports: [
        // fallback to dotenv
        ConfigModule.forRoot({
          envFilePath: options.fallbackFile,
        }),
      ],
      module: InfisicalModule,
      providers: [
        {
          provide: 'INFISICAL_OPTIONS',
          useValue: { ...options },
        },
        {
          provide: InfisicalClient,
          useFactory: (config: ConfigService) => {
            return new InfisicalClient({
              siteUrl: config.get<string>('INFISICAL_SITE_URL'),
              auth: {
                universalAuth: {
                  clientId: config.get<string>('INFISICAL_CLIENT_ID', ''),
                  clientSecret: config.get<string>('INFISICAL_CLIENT_SECRET', ''),
                },
              },
            });
          },
          inject: [ConfigService],
        },
        InfisicalService,
      ],
      exports: [InfisicalService],
    };
  }
}

The infisical.service.ts

import { Inject, Injectable, Logger, OnModuleInit } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { InfisicalClient } from '@infisical/sdk';
import { InfisicalModuleOptions } from './infisical-module-options.type';

@Injectable()
export class InfisicalService implements OnModuleInit {
  private logger = new Logger(InfisicalService.name);
  private fallbackToConfig = false;
  private secrets: Record<string, string | boolean | undefined> = {};
  private readonly initializationPromise: Promise<void>;
  private readonly PROCESS_ENVS: string[] = [
    'DATABASE_URL',
    'GOOGLE_APPLICATION_CREDENTIALS',
  ];

  constructor(
    private readonly config: ConfigService,
    private readonly client: InfisicalClient,
    @Inject('INFISICAL_OPTIONS') private readonly options: InfisicalModuleOptions,
  ) {
    this.initializationPromise = this.init();
  }

  async onModuleInit() {
    await this.initializationPromise;
  }

  private async init() {
    if (!this.config.get<string>('INFISICAL_SITE_URL')) {
      this.logger.log('Use config from ConfigService');
      this.fallbackToConfig = true;
      return;
    }
    try {
      const secrets = await this.client.listSecrets({
        environment: this.config.get<string>('INFISICAL_ENV', ''),
        projectId: this.config.get<string>('INFISICAL_PROJECT_ID', ''),
        path: this.options.path || '/', // path to infisical project's path
        includeImports: true,
      });
      secrets.forEach(secret => {
        this.secrets[secret.secretKey] = secret.secretValue;
        if (this.PROCESS_ENVS.includes(secret.secretKey)) {
          // ENVs where should load directly into process
          // like prisma's DATABASE_URL & google cloud credential
          process.env[secret.secretKey] = secret.secretValue;
        }
      });

      this.logger.log('Secrets loaded from Infisical');
    } catch (error) {
      this.logger.warn(
        'Failed to fetch secrets from Infisical, falling back to ConfigService',
      );
      this.fallbackToConfig = true;
    }
  }

  public get<T = string>(key: string): T {
    if (this.fallbackToConfig) {
      return this.config.get<T>(key) as T;
    }

    if (Object.keys(this.secrets).length > 0) {
      return this.secrets[key] as T;
    }
    const value = this.secrets[key];
    if (value === undefined) {
      return this.config.get<T>(key) as T;
    }
    return value as T;
  }
}

The infisical-module-options.type

export type InfisicalModuleOptions = {
  path?: string;
  fallbackFile?: string | string[];
};

Use it

Write env in your dotenv

INFISICAL_ENV=dev # the slot of environments
INFISICAL_PROJECT_ID=<your-infisical-project-id>
INFISICAL_SITE_URL=<your-infisical-site-url>
INFISICAL_CLIENT_ID=<your-infisical-client-id>
INFISICAL_CLIENT_SECRET=<your-infisical-client-secret>

INFISICAL_ENV
INFISICAL_PROJECT_ID
INFISICAL_CLIENT_ID and INFISICAL_CLIENT_SECRET

And import it into your app.module.ts

@Module({
  imports: [InfisicalModule.forRoot({path: '/'})]
})

Then, you can use it as ConfigService of nestjs

infisicalService.get<string>('YOUR_ENV_SETUP_IN_INFISICAL')

That is

Enhance your python code security using bandit

whchi — Thu, 29 Feb 2024 03:11:38 +0000

In the constantly evolving realm of technology, ensuring the security of your code is also an important part of software development.

Here, I am using Bandit, a tool designed to find common security issues in Python code, to improve my project's security.

Severity vs Confidence

In the context of Information Security, severity and confidence are two important metrics. Both of them are leveled into Low, Medium and High.

Severity, it measures the seriousness of the consequences that may arise if the security issue is exploited or left unaddressed.

Confidence, it reflects how well the information is validated, verified, or understood.

Call to action

The result of a Bandit scan is a detailed report that outlines potential security issues in the code. This report includes the severity and confidence of each issue, as well as the part of the code where the issue was detected. The report can be
output in several formats, including CSV, HTML, JSON, text, XML, and YAML. This allows developers to easily parse and analyze the results and take appropriate action to improve the security of their code.

The following are some simple judgment criteria after scan.

High Severity, High Confidence: Immediate action is typically taken due to a well-understood and verified security threat with potentially severe consequences.
High Severity, Low Confidence: Caution is exercised, and further investigation is needed to increase confidence in the assessment before taking decisive action.
Low Severity, High Confidence: Proactive measures may be taken even for low-severity issues if there is high confidence in the assessment.
Low Severity, Low Confidence: Ongoing monitoring and investigation are required to either confirm the low risk or gather additional information.

Setup

With pre-commit you can integrate bandit into your python project very easily

pyproject.toml: skip folders you don't want to be scanned

[tool.bandit]
exclude_dirs = [
    ".venv",
    ".git",
    "__pycache__",
]

2 .pre-commit-config.yml: add pre-commit hook

repos:
  - repo: https://github.com/PyCQA/bandit
    rev: 1.7.7
    hooks:
      - id: bandit
        args: ["-c", "pyproject.toml", "-r", "."]
        additional_dependencies: ["bandit[toml]"]

That is.

Speed up your pull request review with CodiumAI PR agent

whchi — Tue, 12 Dec 2023 16:03:33 +0000

Pull Request (PR) is an essential process in software development that ensures code quality and collaboration among developers. PRs are also a way to learn from others and get better at coding, a good PR process helps people work together better and keeps the code easy to use and understand.

Problems with PR

However, PRs can be a bottleneck in software development lifecycle. Developers often face challenges such as large codebases, time constraints, and the need for thorough code analysis.

Here are some common problems of PR:

Time Consumption(No time to review): Reviewers who are familiar with the codebase often do not have enough time to conduct a thorough review in a short period.
Context Switching: Reviewers may have to switch contexts frequently, especially if they are reviewing multiple PRs from different parts of the codebase.
Feedback Fatigue: Reviewers may experience fatigue, especially when dealing with a high volume of PRs, which can lead to less thorough reviews.
Technical Debt and Bugs: Identifying deeper issues like technical debt or potential bugs can be challenging, especially under time constraints.
Unclear Descriptions: If the PR descriptions are not clear or detailed enough, it becomes difficult for reviewers to understand the purpose and scope of the changes, leading to inefficient reviews.
Coding style: Inconsistencies in coding style and lack of adherence to best practices can lead to future maintenance issues.
Large PRs: ****Very large PRs can be overwhelming to review, often resulting in missed issues or superficial reviews.

There are several tools available to address these issues, such as linters, automated tests, and contribution guidelines. Additionally, training sessions can be beneficial if you are part of a well-resourced company (although they might not be as common in open-source projects)

How AI Can Help

In November 2022, OpenAI launched ChatGPT, showcasing how AI, particularly through Large Language Model (LLM) APIs, can empower individuals and enhance the PR review experience.

Subsequently, numerous AI tools based on Large Language Models (LLMs) were developed to address the aforementioned issues. These tools have been created by both large enterprises, such as GitHub, AWS, and Microsoft, and startups, including CodiumAI, BLACKBOX AI, and Tabnine.

In this section, I will provide an extensive comparison of some of the most notable tools designed to facilitate PR reviews. This comparison will focus specifically on two key examples:

From an enterprise perspective: GitHub Copilot
From a startup perspective: CodiumAI PR Agent

Comparison of CodiumAI PR agent and Github Copilot

I’m still in the waiting list of Github Copilot, so the Github part in this comparison table might change

	CodiumAI PR Agent	Github Copilot
pricing	free	paid
opensource	✅
PR review	`/review`	`copilot:summary`
PR description	`/describe`	`copilot:walkthrough`
code suggestion	`/improve`	✅
interestring tricks	`/ask "your-question"`	`copilot:poem`
support code hosting platforms	github, gitlab, bitbucket, AWS CodeCommit, azure devops, gerrit	github
Granularity	focus in PR	more general
website	https://github.com/Codium-ai/pr-agent	https://githubnext.com/projects/copilot-for-pull-requests

CodiumAI PR Agent also includes commands such as /update_changelog for writing release logs, /similiar_issue to find identical issues in large codebases, /add_docs for more detailed descriptions, and /generate_labels to enhance documentation clarity.

Real world example using CodiumAI PR Agent

Here's an example of package development, focusing only on the tools listed in the comparison table.

/review
/describe
/improve
/ask "what is the biggest changes? what is the latest commit doing?"

I've tested all the commands of CodiumAI PR Agent except for /similar_issue. That's a total of 7 features, which cost me $0.86 for using the OpenAI API, and it takes me only 5 minutes to review 17 files.

That’s a huge improvement.

Conclusion

AI is revolutionizing individual performance across various fields. I currently use tools like ChatGPT, Bard, and claude.ai for scripting codes, solving math problems, and creating tests. These tools often generate an initial proof of concept (POC) or even a minimum viable product (MVP) in under a minute, significantly boosting my overall productivity.

I'm also exploring the use of PR agents in my work. So far, I find them suitable for open-source projects, and as AI technology advances, I anticipate their integration into enterprise environments in the near future.

Mocking Async API Calls in FastAPI Tests

whchi — Sat, 02 Dec 2023 08:48:13 +0000

When engaging in software development, there is often a need to send SMS, emails, or make API calls to other systems. When testing such programs, it is common to mock these interactions to ensure that the testing process does not fail due to an unavailable network connection.

In FastAPI, there are two ways to define functions: using def and async def. According to the official documentation, testing these functions requires the use of TestClient for def and httpx.AsyncClient for async def.

This article provides a practical method for mocking the API caller within async def functions.

code

Here the API endpoint makes an API call to 3rd party

# example_router.py

import httpx

@router.post('/examples')
async def add_example(payload) -> Any:
    try:
        async with httpx.AsyncClient() as client:
            r = await client.post('https://3rd-party-api.example.com',
                                  json={**payload.dict()})
            r.raise_for_status()
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))
    return JsonResponse(content={})

We should mock the httpx.AsyncClient inside add_example.

First, make a fixture

# conftest.py

@pytest.fixture
async def async_client(app: FastAPI) -> AsyncClient:
    return AsyncClient(app=app, base_url='http://localhost:1234')

Then write the test with mock, the key points are:

Uses the with patch to mock the httpx.AsyncClient.
Mock your calling method, here is post

You cannot patch the whole httpx.AsyncClient using @patch decorator because it will mock all httpx.AsyncClient including the one declared in your test.

# test_example_router.py

from unittest.mock import AsyncMock, patch

from httpx import AsyncClient
import pytest

@pytest.mark.asyncio
async def test_contact_us(async_client: AsyncClient) -> None:
    with patch('httpx.AsyncClient') as mock_client:
        mock_post = AsyncMock()
        mock_client.return_value.__aenter__.return_value.post = mock_post

        response = await async_client.post(url='/api/examples',
                                           json={
                                               'desc': 'desc',
                                               'name': 'Brid Pett',
                                               'phone': '123456789',
                                               'email': 'test@cc.cc',
                                           })
        mock_post.assert_called_once()
        assert response.status_code == status_code

That's all.

Reading notes | Software Engineer-The Soft Part

whchi — Fri, 20 Oct 2023 02:46:37 +0000

This is a very pragmatic book, and it is also quite thin. The author is Addy Osmani, the person in charge of the Google Chrome project. The content is a compilation of his past experiences and soft skills guidance.

I will note down some parts of the book that I personally found impactful, there are 33 points.

notes

Mastery of technology means delivering high value per hour worked. This means you can identify which tasks bring value and help the team focus on those tasks. It also means knowing how to avoid work that does not add value. The best engineers can even guide the entire team to avoid work that does not add value.
Critical thinking is thinking purposefully to form your own conclusions. It allows you to focus more on root causes and avoid potential future problems.
Avoid falling into the trap of assuming correlation implies causation. Someone with critical thinking may question such assumptions and ask why we believe it to be true.
The long-term value of learning fundamentals is their transferability, while the short-term value is helping you make better decisions. Transferable skills have macro and micro aspects. Macroscopically, the basic principles of programming languages are mostly the same, and frequently applying the basic principles can generate tremendous value.
Microscopically, syntax may be familiar but the skills are not transferable. You should also not overfocus on fundamentals, the key is to learn for application.
Having a solid foundation of knowledge allows you to make better decisions and go further, such as considering frameworks or avoiding choosing the wrong technologies.
"You haven't mastered a tool until you understand when it
should not be used." - @kelseyhightower
Focus on the user/developer experience, don't make decisions based on specific solutions (e.g. just using Laravel because it's good).
Use boring (proven) technologies, then consciously choose the tools most suited to solving the problem. FOMO may not be an effective approach in tech - understanding how to use things is important, but the focus should still be on delivering value, unless those technologies can increase the value you produce.
The best time to learn new technologies is by using new projects - use the Feynman learning technique to accelerate learning.
To enable a project to evolve, become its caretaker rather than its owner - document it well so it can continue when you're gone.
The value of software lies in the accumulated knowledge of its producers, not the code itself - don't rewrite code for no reason, you're discarding collective knowledge.
YAGNI (You aren't gonna need it) means don't add features until needed, focus on what really delivers value. But maintain appropriate abstraction - avoid premature abstraction (AHA). Decoupling solutions for specific problems while trying to extract reusability is better.
The best modules are those with maximum benefit and minimum cost. Interfaces should be narrow, functionality deep.
A good mindset for legacy code is to not assume every line is relevant - understand it then remove unnecessary parts.
Defining "done" is important when tackling problems - avoids unnecessary future revisions. This could include meeting acceptance criteria, passing tests, documentation, etc.
Debug messages show the truth, don't keep making small changes trying to fix issues.
Design docs are part of software engineering, not an afterthought. Coordinate reviews of design docs, compare to original as it evolves to validate addressing issues.
Focus communication on kindness - may take more time but makes others more willing to communicate for effective outcomes. Avoid jargon, match vocabulary to audience.
Saying no is better than over promising.
A senior engineer should be skilled at designing software systems and human/team systems. You can lead a diverse engineering team, delegate tasks, guide them to focus on code quality, performance and simplicity. Provide feedback when needed, defend them when necessary. You should also be able to market yourself, your work, and problem-solving abilities for exposure in the organization. Overall, manage relationships well internally and with management.
Guide junior engineers to ask the right questions in a way that lets them know you're glad they asked.
Strongly defend your views but re-examine assumptions when new evidence arrives.
Teach the team to fish - coordinate internally and externally, aim to increase team output. More senior means focusing more on managing relationships with management and the team.
Team efficiency is efficiency - accept imposter syndrome, it can motivate you to learn.
Understand the business model, your code hugely impacts business logic. Insight into commercial software is key to influencing, as you're closer to the market so naturally do impactful things.
Start from the user experience and work backwards to required tech.
Mentoring is guiding - suggest ideas for mentees to try. If they can't solve a problem, show your approach and thinking for reference e.g. debugging steps rather than the answer directly.
Don't try to do too much at once, focused deep work time is very important.
Estimates can be updated anytime, software estimates are inherently complex until requirements are deeply understood, then they become more accurate. A better way is rough estimate, detailed estimate, staged rollout.
A leader confidently admitting not knowing something is powerful. This confidence reduces expectations for senior engineers to know everything. You absolutely don't need all the answers, but admitting you're human and committed to solving problems together is most important.
Tech debt repayment should be regular time allocated by tech leads for cleanup - prevention over cure.
Focus on the problem - issues in other projects may also be issues in yours.
"Surround yourself by excellence and work with people who are
the best as what they do" - Brian Staufenbiel

source

https://addyosmani.com/blog/software-engineering-soft-parts/

Localize your FastAPI validation message

whchi — Thu, 17 Aug 2023 06:14:04 +0000

When working on website frontend development, a significant amount of time is spent on handling form validation. If your service caters to a global audience (not only within your country), it's advisable to incorporate internationalization (i18n) error messages into it.

If the situation involves frontend-backend separation, managing the validation i18n message communication becomes a troublesome task.

The question arises: should the validation messages be managed on the backend or the frontend?

Generally, messages should be managed closer to the rendering (frontend mostly) interface for better organization. A common approach is to use error codes paired with corresponding messages.

However, there are numerous validation methods for forms, and if error codes were applied to forms, maintenance would become quite challenging. Therefore, it's best to centrally manage form-type validation by either the frontend or the backend.

FastAPI employs Pydantic to encapsulate most scenarios of form validation and provides corresponding error messages. This works well in a purely English environment, but if we require i18n, it's clear that this approach falls short.

This article will work through the whole concept of how to integrate i18n into FastAPI's request validation error messages with pseudo code.

middleware

Firstly we need to know what locale it is, we can archive this using middleware

class LocaleMiddleware:
   async def dispatch(req, call_next):
       req.locale = incoming_locale
       return await call_next(req)
app.add_middleware(LocaleMiddleware)

exception handler

Then we register our own exception handler to handle RequestValidationError

async def i18n_exception_handler(request, exc):
     msg = make_i18n_msg(exc, request.state.locale)
     return JSONResponse({"errors": exc}, status=422)

app.add_exception_handler(RequestValidationError, i18n_exception_handler)

translate

Finally, we make translate action, the exc of FastAPI is object of object, so we need to extract the message recursively

def make_i18n_msg(exc, locale):
   if isinstanceof(exc, wrapper):
     return make_i18n_msg(exc, locale)
   return Translator(locale).t('trans.file.key')

And we need a translation file looks like

// zh-TW.json
{
  "field required": "欄位必填",
  "extra fields not permitted": "不允許額外的欄位",
}
// ja-JP.json
{
  "field required": "フィールドは必須です",
  "extra fields not permitted": "余分なフィールドは許可されていません",
}

Then when you send with locale to your API pydantic validation, you will get something like

{
  "errors": [
    {
      "loc": [
        "body",
        "string"
      ],
      "msg": "フィールドは必須です",
      "type": "value_missing.field_required"
    },
   {
      "loc": [
        "body",
        "string"
      ],
      "msg": "余分なフィールドは許可されていません",
      "type": "value_error.extra_fields_are_not_permitted"
    }
}

That's the essence of it, and I've written a package for it.

fastapi-validation-i18n

You can accomplish this by adhering to the documentation, contributions are also encouraged and welcome.

Nginx setup for Next.js subdomain routing

whchi — Sat, 12 Aug 2023 07:01:13 +0000

Don't do it unless you can 100% control your nginx

In this article, I will guide you through the process of setting up Nginx(only) for subdomain routing to a Next.js router. To illustrate this, I'll use a page router as an example.

Here's a sample page router structure:

pages/
├──subdomain-1/
│           ├──index.tsx
│           └──blogs/
└──subdomain-2/
            ├──index.tsx
            └──orders/

The expect behavior is

subdomain-1.com -> /pages/subdomain-1/index.tsx
subdomain-1.com/blogs -> /pages/subdomain-1/blogs/index.tsx
subdomain-2.com/orders -> /pages/subdomain-2/orders/index.tsx

nginx.conf

The idea is simple, use server_name to setup 2 virtual hosts and proxy_pass proxy to the backend

http {
    upstream nextjs {
        http://localhost:3000;
    }
    server {
        listen 80;
        server_name subdomain-1.com;

        location / {
            # for i18n
            set $locale_prefix '/subdomain-1';
            set $rest $request_uri;
            if ($request_uri ~ ^/(en|ja|zh-TW)(.*)$) {
                set $locale_prefix '/$1/subdomain-1';
                set $rest $2;
            }
            proxy_pass http://nextjs$locale_prefix$rest;
            proxy_set_header Host $host;
            proxy_set_header X-Real-IP $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_set_header X-Forwarded-Proto $scheme;
        }
    }

    server {
        listen 80;
        server_name subdomain-2;
        location / {
          set $locale_prefix '/subdomain-2';
          set $rest $request_uri;
          if ($request_uri ~ ^/(en|ja|zh-TW)(.*)$) {
              set $locale_prefix '/$1/subdomain-2';
              set $rest $2;
          }
          proxy_pass http://nextjs$locale_prefix$rest;
          proxy_set_header Host $host;
          proxy_set_header X-Real-IP $remote_addr;
          proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
          proxy_set_header X-Forwarded-Proto $scheme;
        }
    }
}

next.js

you need to replace your paths in code, use router.push('/blogs') instead of router.push('/domain-2/blogs') and separate the local and production environment's behavior.

// @ts-nocheck
export const setPath = (path: string): string => {
  const PATH_PREFIXES = ['/subdomain-1', '/subdomain-2'];
  if (process.env.NODE_ENV !== 'development') {
    for (const prefix of PATH_PREFIXES) {
      if (path.startsWith(prefix)) {
        return path.slice(prefix.length);
      }
    }
  }
  return path;
};

router.push(setPath('/subdomain-1/blogs'));

pros and cons

pros
- microservices alignment
- deployment specific subdomain won't affect your application
- tls are managed by nginx, you can support different level of subdomain
- cleaner code structure, each folder points to a subdomain
cons
- development and deployment are more complex
- difficult to maintain, next.js folder will be constrained by nginx config
- initial setup is difficult, frontends need to know about nginx config

Typically, in Next.js, you should use middleware for subdomain routing.

Don't do it unless you can 100% control your nginx

Protect your FastAPI document with HTTP basic authN

whchi — Wed, 09 Aug 2023 02:32:03 +0000

When working in enterprise with frontends as a backend engineer, it common to deploy a staging(test) environment to expedite development.

One of the advantages of using FastAPI is it built-in API documentation. However, we don't want to expose it in the staging environment to the public, unless you can control the networking aspect.

In this article, I'll demonstrate how to secure it using HTTP basic authentication.

Code

disable doc url by env

if app_env == 'staging':
    app = FastAPI(docs_url=None, openapi_url=None, redoc_url=None)

create a middleware for http basic authN

import base64
import secrets

from starlette.middleware.base import BaseHTTPMiddleware, RequestResponseEndpoint
from starlette.requests import Request
from starlette.responses import Response


class ApidocBasicAuthMiddleware(BaseHTTPMiddleware):

    async def dispatch(  # type: ignore
            self, request: Request, call_next: RequestResponseEndpoint):
        if request.url.path in ['/docs', '/openapi.json', '/redoc']:
            auth_header = request.headers.get('Authorization')
            if auth_header:
                try:
                    scheme, credentials = auth_header.split()
                    if scheme.lower() == 'basic':
                        decoded = base64.b64decode(credentials).decode('ascii')
                        username, password = decoded.split(':')
                        correct_username = secrets.compare_digest(
                            username, 'YOUR_USERNAME')
                        correct_password = secrets.compare_digest(
                            password, 'YOUR_PASSWORD')
                        if correct_username and correct_password:
                            return await call_next(request)
                except Exception:
                    ...
            response = Response(content='Unauthorized', status_code=401)
            response.headers['WWW-Authenticate'] = 'Basic'
            return response
        return await call_next(request)

app.add_middleware(ApidocBasicAuthMiddleware)

replace YOUR_USERNAME and YOUR_PASSWORD

enable document paths and document html

from typing import Any, Dict

from fastapi.openapi.docs import get_redoc_html, get_swagger_ui_html
from fastapi.openapi.utils import get_openapi

@app.get(
    '/docs',
    tags=['documentation'],
    include_in_schema=False,
)
async def get_swagger_documentation() -> HTMLResponse:
    return get_swagger_ui_html(openapi_url='/openapi.json', title='docs')


@app.get(
    '/openapi.json',
    tags=['documentation'],
    include_in_schema=False,
)
async def openapi() -> Dict[str, Any]:
    return get_openapi(title='FastAPI', version='0.1.0', routes=app.routes)


@app.get(
    '/redoc',
    tags=['documentation'],
    include_in_schema=False,
)
async def get_redoc() -> HTMLResponse:
    return get_redoc_html(openapi_url='/openapi.json', title='docs')

That is, you will see the login modal when entering doc path

Put it all together

import base64
import secrets

from starlette.middleware.base import BaseHTTPMiddleware, RequestResponseEndpoint
from starlette.requests import Request
from starlette.responses import Response
from typing import Any, Dict
from fastapi import FastAPI
from fastapi.openapi.docs import get_redoc_html, get_swagger_ui_html
from fastapi.openapi.utils import get_openapi

app = FastAPI(docs_url=None, openapi_url=None, redoc_url=None)
app.add_middleware(ApidocBasicAuthMiddleware)

@app.get(
    '/docs',
    tags=['documentation'],
    include_in_schema=False,
)
async def get_swagger_documentation() -> HTMLResponse:
    return get_swagger_ui_html(openapi_url='/openapi.json', title='docs')


@app.get(
    '/openapi.json',
    tags=['documentation'],
    include_in_schema=False,
)
async def openapi() -> Dict[str, Any]:
    return get_openapi(title='FastAPI', version='0.1.0', routes=app.routes)


@app.get(
    '/redoc',
    tags=['documentation'],
    include_in_schema=False,
)
async def get_redoc() -> HTMLResponse:
    return get_redoc_html(openapi_url='/openapi.json', title='docs')

Forem: whchi

Managing Object Ownership in PostgreSQL Migrations

"Owner" in PostgreSQL

Why Ownership Matters in Migration

Solutions For Handling Ownership in Migration

1. use pg_dump & pg_restore correctly

2. Set Owner During Database Creation

3. Use a One-Time Ownership Migration Script

4. Use GRANT When Ownership Change Isn't Required

Final Advice

Introduction to access control model: ACL, RBAC, ABAC

ACL

RBAC

ABAC

Comparison Table

Setting up TLS connection for containerized PostgreSQL database

Understanding PostgreSQL Client Connection Types

Setting Up the Server(One-Way Verification)

1. Create Certificate and Key

2. Configure PostgreSQL

3. Update Access Rules

4. Set Up Docker Compose

Connecting to Your Database

Setting Up the Server(mTLS)

1. Create certifications for server and client

2. Configure PostgreSQL

3. Update Access Rules

4. Update docker-compose.yml

Connecting to Your Database

Setting up TLS connection for containerized PostgreSQL database

Why Do We Need Encryption?

Understanding PostgreSQL Client Connection Types

Setting Up the Server(One-Way Verification)

1. Create Certificate and Key

2. Configure PostgreSQL

3. Update Access Rules

4. Set Up Docker Compose

Connecting to Your Database

Setting Up the Server(mTLS)

1. Create certifications for server and client

2. Configure PostgreSQL

3. Update Access Rules

4. Update docker-compose.yml

Connecting to Your Database

Integrate Self-host Infisical into your NestJS project

Why centralized secret management is necessary

Flexibility in containerized deployment

Convenience in multi-developer scenarios

A little about Infisical

Advantages of Infisical

Paid features

Writing a NestJS Module to integrate Infisical

Use it

Enhance your python code security using bandit

Severity vs Confidence

Call to action

Setup

Speed up your pull request review with CodiumAI PR agent

Problems with PR

How AI Can Help

Comparison of CodiumAI PR agent and Github Copilot

Real world example using CodiumAI PR Agent

Conclusion

Mocking Async API Calls in FastAPI Tests

code

Reading notes | Software Engineer-The Soft Part

notes

source

Localize your FastAPI validation message

middleware

exception handler

translate

Nginx setup for Next.js subdomain routing

nginx.conf

next.js

pros and cons

Protect your FastAPI document with HTTP basic authN

Code

Put it all together

1. use `pg_dump` & `pg_restore` correctly

4. Use `GRANT` When Ownership Change Isn't Required