### Install pglinter on Debian/Ubuntu

Source: https://pglinter.readthedocs.io/en/latest/install

Download and install the DEB package using wget and dpkg.

```bash
export PG_MAJOR_VERSION=18
export PGLINTER_VERSION=1.0.1
export ARCH=amd64
wget https://github.com/pmpetit/pglinter/releases/download/${PGLINTER_VERSION}/postgresql_pglinter_${PG_MAJOR_VERSION}-${PGLINTER_VERSION}_${ARCH}.deb

sudo dpkg -i postgresql_pglinter_${PG_MAJOR_VERSION}_${PGLINTER_VERSION}_${ARCH}.deb
```

--------------------------------

### Verify pglinter installation

Source: https://pglinter.readthedocs.io/en/latest/install

Run the hello_pglinter function to confirm the extension is working.

```sql
postgres=# select hello_pglinter();
 hello_pglinter
-----------------
 Hello, pglinter
(1 row)
postgres=#
```

--------------------------------

### Install pglinter on RedHat/Rocky/Alma Linux

Source: https://pglinter.readthedocs.io/en/latest/install

Download and install the RPM package using wget and rpm or yum.

```bash
export PG_MAJOR_VERSION=18
export PGLINTER_VERSION=1.0.1
export ARCH=x86_64
wget https://github.com/pmpetit/pglinter/releases/download/${PGLINTER_VERSION}/postgresql_pglinter_${PG_MAJOR_VERSION}-${PGLINTER_VERSION}-1.aarch64.rpm

sudo rpm -i postgresql_pglinter_${PG_MAJOR_VERSION}_${PGLINTER_VERSION-1}_${ARCH}.rpm
# or
sudo yum localinstall postgresql_pglinter_${PG_MAJOR_VERSION}-${PGLINTER_VERSION}-1.${ARCH}.rpm
```

--------------------------------

### Verify pglinter installation

Source: https://pglinter.readthedocs.io/en/latest/install

SQL commands to verify the installation of the pglinter extension in the database.

```sql
postgres=# \dx
                          List of installed extensions
  Name   | Version | Default version |   Schema   |         Description
---------+---------+-----------------+------------+------------------------------
 plpgsql | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
 app       | app      | UTF8     | libc            | C       | C     |        |           |
 postgres  | postgres | UTF8     | libc            | C       | C     |        |           |
 template0 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
(4 rows)

postgres=# \c app
You are now connected to database "app" as user "postgres".
app=# \dx
                                           List of installed extensions
   Name   | Version | Default version |   Schema   |                         Description
----------+---------+-----------------+------------+--------------------------------------------------------------
 pglinter | 1.0.1   | 1.0.1           | public     | pglinter: PostgreSQL Database Linting and Analysis Extension
 plpgsql  | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
(2 rows)

app=#
```

--------------------------------

### Install the pglinter extension

Source: https://pglinter.readthedocs.io/en/latest

Execute this command within your PostgreSQL database to enable the extension functionality.

```sql
CREATE EXTENSION pglinter;
```

--------------------------------

### YAML Configuration Structure

Source: https://pglinter.readthedocs.io/en/latest/configure

Example of the expected YAML format for rule definitions.

```yaml
metadata:
  export_timestamp: "2024-01-01T12:00:00Z"
  total_rules: 25
  format_version: "1.0"
rules:
  - id: 1
    name: "Tables Without Primary Key"
    code: "B001"
    enable: true
    scope: "BASE"
    message: "table without primary key"
    fixes:
      - "Add primary key constraints to tables"
      - "Consider surrogate keys for tables without natural keys"
  - id: 2
    name: "Redundant Indexes"
    code: "B002"
    enable: false
    # ... more rule properties
```

--------------------------------

### Enable T010 Rule

Source: https://pglinter.readthedocs.io/en/latest/rules

Install the required anon extension and enable the T010 rule.

```sql
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
```

```sql
SELECT pglinter.enable_rule('T010');
```

--------------------------------

### Implement Custom Rules

Source: https://pglinter.readthedocs.io/en/latest/configure

Example structure for future custom rule implementations in Rust.

```rust
// Custom rule example (future feature)
pub struct CustomRule {
    threshold: i64,
    enabled: bool,
}

impl DatabaseRule for CustomRule {
    fn execute(&self) -> Result<Option<RuleResult>, String> {
        // Custom rule logic
    }
}
```

--------------------------------

### Environment-Named Schema Convention

Source: https://pglinter.readthedocs.io/en/latest/rules

Example of preferred naming conventions to avoid environment-specific schema prefixes.

```sql
-- Instead of: myapp_prod.users, myapp_stg.users
-- Use: myapp.users in databases: myapp_prod, myapp_stg
```

--------------------------------

### Install CloudNativePG operator

Source: https://pglinter.readthedocs.io/en/latest/install

Apply the CloudNativePG operator manifest to the Kubernetes cluster.

```bash
kubectl apply --server-side -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/main/releases/cnpg-1.28.0-rc1.yam
```

--------------------------------

### Modify Rules Configuration

Source: https://pglinter.readthedocs.io/en/latest/configure

Examples of YAML modifications for disabling rules, updating messages, or changing fix suggestions.

```yaml
# Example modifications:

# 1. Disable a rule
- id: 2
  code: "B002"
  enable: false  # Disable redundant index checking

# 2. Modify rule message
- id: 1
  code: "B001"
  message: "Custom message: Found {0} tables needing primary keys"

# 3. Update fix suggestions
- id: 3
  code: "B003"
  fixes:
    - "Add indexes to foreign key columns"
    - "Consider composite indexes for multi-column FKs"
    - "Custom fix suggestion for your environment"
```

--------------------------------

### Create kind cluster

Source: https://pglinter.readthedocs.io/en/latest/install

Command to initialize the kind cluster using the provided configuration file.

```bash
kind create cluster --config kind-with-imagevolume.yaml
```

--------------------------------

### Connect to pglinter Docker container

Source: https://pglinter.readthedocs.io/en/latest/install

Use psql to connect to the running container instance.

```bash
export PGPASSWORD=x
psql --host=localhost --port=6543 --user=postgres
```

--------------------------------

### Run pglinter Docker container

Source: https://pglinter.readthedocs.io/en/latest/install

Launch a PostgreSQL container with the pglinter extension pre-installed.

```bash
docker run -d -e POSTGRES_PASSWORD=x -p 6543:5432 ghcr.io/pmpetit/postgresql_pglinter:latest
```

--------------------------------

### Manage Configuration Database

Source: https://pglinter.readthedocs.io/en/latest/configure

Commands to inspect, backup, and restore the pglinter configuration tables.

```sql
-- View rule configuration table
\d pglinter.rules

-- Backup configuration
pg_dump -t pglinter.rules mydb > pglinter_config_backup.sql

-- Restore configuration
psql -d mydb -f pglinter_config_backup.sql
```

--------------------------------

### Configure Production Environment

Source: https://pglinter.readthedocs.io/en/latest/configure

Enable all security and performance-related rules for production deployments.

```sql
-- Ensure all critical rules are enabled
SELECT pglinter.enable_rule('B001'); -- Primary keys
SELECT pglinter.enable_rule('B002'); -- Redundant indexes
SELECT pglinter.enable_rule('B003'); -- FK indexing
SELECT pglinter.enable_rule('B004'); -- Unused indexes
SELECT pglinter.enable_rule('B005'); -- Schema security
SELECT pglinter.enable_rule('C001'); -- Memory configuration
SELECT pglinter.enable_rule('C002'); -- pg_hba security
SELECT pglinter.enable_rule('C003'); -- MD5 password encryption
```

--------------------------------

### Version Control and Deployment

Source: https://pglinter.readthedocs.io/en/latest/configure

Commands for tracking configurations in git and deploying them to databases.

```bash
# Initialize rule configuration in git
git add rules_production.yaml rules_staging.yaml rules_dev.yaml
git commit -m "Add pglinter rule configurations"

# Deploy environment-specific rules
psql -d production_db -c "SELECT pglinter.import_rules_from_file('/deploy/rules_production.yaml');"
psql -d staging_db -c "SELECT pglinter.import_rules_from_file('/deploy/rules_staging.yaml');"
```

--------------------------------

### pglinter.show_rules()

Source: https://pglinter.readthedocs.io/en/latest

Displays the current status and configuration of all available linting rules.

```APIDOC
## SELECT pglinter.show_rules()

### Description
Returns a list of all rules available in the extension, including their current enabled/disabled status.
```

--------------------------------

### Backup and Restore Workflow

Source: https://pglinter.readthedocs.io/en/latest/configure

Standard procedure for backing up, testing, and applying rule changes.

```bash
# 1. Backup current configuration
psql -d mydb -t -c "SELECT pglinter.export_rules_to_yaml();" > backup_$(date +%Y%m%d).yaml

# 2. Modify rules as needed
# Edit the YAML file with your preferred editor

# 3. Test import in development first
psql -d dev_db -c "SELECT pglinter.import_rules_from_file('modified_rules.yaml');"

# 4. Validate configuration works
psql -d dev_db -c "SELECT * FROM pglinter.get_violations();"

# 5. Apply to production
psql -d prod_db -c "SELECT pglinter.import_rules_from_file('modified_rules.yaml');"
```

--------------------------------

### Run Integration Tests

Source: https://pglinter.readthedocs.io/en/latest/examples

Create test scenarios with known violations and retrieve them using get_violations.

```sql
-- Create diverse test scenario
CREATE TABLE users_no_pk (
    id INT,  -- B001: No primary key
    username TEXT,
    email TEXT
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    category TEXT,
    price NUMERIC
);

-- Create redundant indexes (B002)
CREATE INDEX idx_name_1 ON products (name);
CREATE INDEX idx_name_2 ON products (name); -- redundant

-- Create foreign key without index (B003)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,  -- Foreign key without index
    product_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users_no_pk(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Run comprehensive check
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  FROM pglinter.get_violations();
```

--------------------------------

### pglinter.show_rules()

Source: https://pglinter.readthedocs.io/en/latest/rules

Retrieves a list of all available linting rules.

```APIDOC
## SELECT pglinter.show_rules()

### Description
Returns a table containing all available rules in the system.

### Method
SQL SELECT

### Parameters
None
```

--------------------------------

### Run PGLinter Checks

Source: https://pglinter.readthedocs.io/en/latest/examples

Retrieve all identified violations from the database using the default configuration.

```sql
SELECT * FROM pglinter.get_violations();
```

--------------------------------

### Build custom pglinter Docker image

Source: https://pglinter.readthedocs.io/en/latest/install

Build a custom image for a specific PostgreSQL version using make.

```bash
DOCKER_PG_MAJOR_VERSION=16 make docker_image
```

--------------------------------

### Integrate PGLinter into CI/CD Pipeline

Source: https://pglinter.readthedocs.io/en/latest/examples

Uses psql to query violation counts and exit with a failure status if issues are detected.

```bash
# In your CI/CD pipeline
VIOLATIONS=$(psql -d your_database -t -c "SELECT count(*) FROM pglinter.get_violations();")

# Check if any violations were found
if [ "$VIOLATIONS" -gt 0 ]; then
    echo "Database violations found:"
    psql -d your_database -c "SELECT * FROM pglinter.get_violations();"
    exit 1
fi
```

--------------------------------

### Create database resource

Source: https://pglinter.readthedocs.io/en/latest/install

Kubernetes manifest to create a database within the PostgreSQL cluster.

```yaml
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: cluster-pglinter-app
spec:
  name: app
  owner: app
  cluster:
    name: cluster-pglinter
  extensions:
  - name: pglinter
```

--------------------------------

### View and Explain Rules

Source: https://pglinter.readthedocs.io/en/latest/rules

Retrieve lists of rules or specific rule details using SQL queries.

```sql
-- Show all available rules
SELECT * FROM pglinter.show_rules();

-- Show only enabled rules
SELECT * FROM pglinter.show_rules() WHERE enable = true;

-- Show rules by category
SELECT * FROM pglinter.show_rules() WHERE code LIKE 'B%';

-- Get rule details
SELECT pglinter.explain_rule('B001');
```

--------------------------------

### Configure kind cluster for ImageVolume

Source: https://pglinter.readthedocs.io/en/latest/install

YAML configuration to enable the ImageVolume feature gate in a kind cluster.

```yaml
kind: Cluster
apiVersion: kind.x-k8s.io/v1alpha4
name: imagevolume-cluster
# -----------------------------------------------
# 1. Enable the Feature Gate cluster-wide
# -----------------------------------------------
featureGates:
  ImageVolume: true
# -----------------------------------------------
```

--------------------------------

### Integrate Pglinter into CI/CD Pipelines

Source: https://pglinter.readthedocs.io/en/latest/rules

Use these commands to fetch violations during deployment or filter for specific critical rule codes.

```sql
# Get all violations as part of deployment pipeline
psql -c "SELECT * FROM pglinter.get_violations()"

# Check for violations from specific critical rules
psql -c "SELECT * FROM pglinter.get_violations() WHERE rule_code IN ('B001', 'C002')"
```

--------------------------------

### Import and Export Rule Configuration

Source: https://pglinter.readthedocs.io/en/latest/examples

Export current rule settings to YAML or import them from a file or string.

```sql
-- Export current rule configuration to YAML
SELECT pglinter.export_rules_to_yaml();

-- Export to file
SELECT pglinter.export_rules_to_file('/tmp/pglinter_config.yaml');

-- Import from YAML string
SELECT pglinter.import_rules_from_yaml('
metadata:
  export_timestamp: "2024-01-01T00:00:00Z"
  total_rules: 2
  format_version: "1.0"
rules:
  - code: "B001"
    enabled: true
');

-- Import from file
SELECT pglinter.import_rules_from_file('/tmp/pglinter_config.yaml');
```

--------------------------------

### Validate Imported Configurations

Source: https://pglinter.readthedocs.io/en/latest/configure

SQL queries to verify rule states and test execution after import.

```sql
-- Verify rules were imported correctly
SELECT code, enable, scope
FROM pglinter.rules
WHERE code IN ('B001', 'B002', 'B003')
ORDER BY code;

-- Test rule execution
SELECT * FROM pglinter.get_violations();

-- Check for any import errors in PostgreSQL logs
```

--------------------------------

### Explain Rule Details

Source: https://pglinter.readthedocs.io/en/latest/rules

Retrieve detailed information about a specific rule.

```sql
pglinter.explain_rule('B001')
```

--------------------------------

### Inspect and test pglinter rules

Source: https://pglinter.readthedocs.io/en/latest/configure

Use these queries to verify enabled rules and check for specific violations in the database.

```sql
-- Verify which rules are enabled
SELECT rule_code, enabled
FROM pglinter.show_rules()
ORDER BY rule_code;

-- Test a specific rule
SELECT * FROM pglinter.get_violations() WHERE rule_code = 'B001';
```

--------------------------------

### Retrieve and filter pglinter violations

Source: https://pglinter.readthedocs.io/en/latest/how-to

Demonstrates how to enable the extension and query violations, including filtering by specific rule codes.

```sql
-- Connect to your database
\c your_database

-- Create the extension
CREATE EXTENSION pglinter;

-- Get all violations
SELECT * FROM pglinter.get_violations();

-- Filter violations for a specific rule
SELECT * FROM pglinter.get_violations() WHERE rule_code = 'B001';  -- Tables without primary keys
SELECT * FROM pglinter.get_violations() WHERE rule_code = 'B002';  -- Redundant indexes

-- To get object name
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  from pglinter.get_violations()
```

--------------------------------

### Debug PGLinter Rules

Source: https://pglinter.readthedocs.io/en/latest/examples

Provides SQL commands to explain, check status, or list available linting rules.

```sql
-- Enable verbose logging for specific rule
SELECT pglinter.explain_rule('B001');

-- Check rule status
SELECT pglinter.is_rule_enabled('B001');

-- View all available rules
SELECT pglinter.list_rules();
```

--------------------------------

### Export Rules to YAML

Source: https://pglinter.readthedocs.io/en/latest/configure

Generate a YAML representation of current rule configurations using SQL.

```sql
-- Export all current rules to YAML format
SELECT pglinter.export_rules_to_yaml();
```

--------------------------------

### pglinter.explain_rule(rule_code)

Source: https://pglinter.readthedocs.io/en/latest

Provides a detailed explanation for a specific linting rule.

```APIDOC
## SELECT pglinter.explain_rule(rule_code)

### Description
Returns a detailed description and context for a specific rule, helping users understand why a violation was flagged.

### Parameters
- **rule_code** (text) - Required - The unique identifier for the rule (e.g., 'B002').
```

--------------------------------

### Format Console Output

Source: https://pglinter.readthedocs.io/en/latest/configure

Use psql extended display mode to improve readability of violation reports.

```sql
-- Output violations
SELECT * FROM pglinter.get_violations();

-- Format output for better readability
\x on
SELECT * FROM pglinter.get_violations();
\x off
```

--------------------------------

### Configure and Debug Rules

Source: https://pglinter.readthedocs.io/en/latest/rules

Query rule settings and view underlying violation-location queries.

```sql
-- Check current rule settings
SELECT code, enable, scope
FROM pglinter.rules
WHERE code = 'B001';

-- Update rule thresholds

-- Get rule threshold levels

-- Show q4 violation-location query used by a rule (for debugging)
SELECT pglinter.show_rule_queries('B001');
```

--------------------------------

### Configure Development Environment

Source: https://pglinter.readthedocs.io/en/latest/configure

Disable specific rules that may be too restrictive during the development phase.

```sql
-- Disable strict rules that might not apply during development
SELECT pglinter.disable_rule('B005'); -- Public schema security
SELECT pglinter.disable_rule('T009'); -- Role grants
SELECT pglinter.disable_rule('T010'); -- Reserved keywords
```

--------------------------------

### Pull pglinter Docker image

Source: https://pglinter.readthedocs.io/en/latest/install

Download the latest pglinter Docker image from the GitHub Container Registry.

```bash
docker pull ghcr.io/pmpetit/postgresql_pglinter:latest
```

--------------------------------

### Reset pglinter to default settings

Source: https://pglinter.readthedocs.io/en/latest/configure

Execute this command to re-enable all rules to their default state.

```sql
-- Re-enable all rules (default state)
SELECT pglinter.enable_rule(rule_code)
FROM pglinter.show_rules();
```

--------------------------------

### Monitor Execution Time

Source: https://pglinter.readthedocs.io/en/latest/rules

Enable timing in psql to monitor the execution time of rules.

```sql
\timing
```

--------------------------------

### Add Primary Keys to Tables

Source: https://pglinter.readthedocs.io/en/latest/rules

Use these SQL commands to define surrogate or composite primary keys to ensure data integrity.

```sql
-- Add a surrogate primary key
ALTER TABLE users ADD COLUMN id SERIAL PRIMARY KEY;

-- Add a composite primary key
ALTER TABLE user_roles ADD PRIMARY KEY (user_id, role_id);
```

--------------------------------

### View Available Rules

Source: https://pglinter.readthedocs.io/en/latest/examples

Retrieve information about available rules and their current status.

```sql
-- List all available rules with their status
SELECT pglinter.list_rules();

-- Show detailed rule status
SELECT pglinter.show_rules();

-- Get explanation for a specific rule
SELECT pglinter.explain_rule('B001');
```

--------------------------------

### Import Modified Rules

Source: https://pglinter.readthedocs.io/en/latest/configure

Import rule configurations from inline YAML strings or external files.

```sql
-- Import rules from YAML content (inline)
SELECT pglinter.import_rules_from_yaml('
metadata:
  export_timestamp: "2024-01-01T12:00:00Z"
  total_rules: 1
  format_version: "1.0"
rules:
  - id: 1
    name: "Tables Without Primary Key"
    code: "B001"
    enable: true
    scope: "BASE"
    message: "table without primary key"
    fixes:
      - "Add primary key constraints"
');

-- Import rules from file
SELECT pglinter.import_rules_from_file('/path/to/modified_rules.yaml');
```

--------------------------------

### Detect Schema Names with Environment Prefixes/Suffixes (S002)

Source: https://pglinter.readthedocs.io/en/latest/examples

Flags schema names containing environment indicators like dev, test, or prod.

```sql
-- Problematic schema names
CREATE SCHEMA dev_application;
CREATE SCHEMA test_schema;
CREATE SCHEMA prod_data;

-- Check schema naming conventions
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  FROM pglinter.get_violations() WHERE rule_code = 'S002';
```

--------------------------------

### View and Inspect Rules

Source: https://pglinter.readthedocs.io/en/latest/configure

Use these functions to list available rules, check their current status, or retrieve detailed explanations for specific rules.

```sql
-- Show all available rules with their status
SELECT pglinter.show_rules();

-- Check if a specific rule is enabled
SELECT pglinter.is_rule_enabled('B001');

-- Get detailed information about a rule
SELECT pglinter.explain_rule('B002');
```

--------------------------------

### Configuration Import/Export

Source: https://pglinter.readthedocs.io/en/latest/examples

Functions to export current rule configurations to YAML or import them from YAML strings or files.

```APIDOC
## SELECT pglinter.export_rules_to_yaml()

### Description
Returns the current rule configuration as a YAML string.

## SELECT pglinter.export_rules_to_file(path TEXT)

### Description
Exports the current rule configuration to a file at the specified path.

## SELECT pglinter.import_rules_from_yaml(yaml_content TEXT)

### Description
Imports rule configurations from a provided YAML string.

## SELECT pglinter.import_rules_from_file(path TEXT)

### Description
Imports rule configurations from a file at the specified path.
```

--------------------------------

### Define PostgreSQL cluster with pglinter

Source: https://pglinter.readthedocs.io/en/latest/install

Kubernetes manifest to deploy a PostgreSQL cluster with the pglinter extension enabled.

```yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-pglinter
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:18-minimal-trixie
  instances: 1

  storage:
    size: 1Gi

  postgresql:
    extensions:
    - name: pglinter
      image:
        reference: ghcr.io/pmpetit/pglinter:1.0.1-18-trixie
```

--------------------------------

### Rule Configuration Functions

Source: https://pglinter.readthedocs.io/en/latest/examples

Functions to enable, disable, or bulk-configure linting rules.

```APIDOC
## SELECT pglinter.is_rule_enabled(rule_code TEXT)

### Description
Checks if a specific rule is currently enabled.

## SELECT pglinter.disable_rule(rule_code TEXT)

### Description
Disables a specific rule.

## SELECT pglinter.enable_rule(rule_code TEXT)

### Description
Enables a specific rule.

## SELECT pglinter.disable_all_rules()

### Description
Disables all linting rules.

## SELECT pglinter.enable_all_rules()

### Description
Enables all linting rules.
```

--------------------------------

### Configure Default Privileges for Schemas

Source: https://pglinter.readthedocs.io/en/latest/rules

Use these statements to ensure future tables inherit appropriate permissions for specific roles.

```sql
-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myschema_rw;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
GRANT SELECT ON TABLES TO myschema_ro;
```

--------------------------------

### Identify and Rename Objects with Reserved Keywords

Source: https://pglinter.readthedocs.io/en/latest/rules

Detects database objects using reserved SQL keywords and provides syntax for renaming them to avoid syntax errors.

```sql
-- Check for objects using reserved keywords (example with 'user' table)
SELECT tablename
FROM pg_tables
WHERE tablename IN ('user', 'table', 'select', 'from', 'where', 'order', 'group');

-- Rename objects that use reserved keywords
ALTER TABLE "user" RENAME TO app_user;
ALTER TABLE "order" RENAME TO customer_order;
```

--------------------------------

### Fixing Unbounded Cursor Triggers in PostgreSQL

Source: https://pglinter.readthedocs.io/en/latest/rules

Demonstrates the difference between an inefficient trigger function performing a full table scan and an optimized version using a filtered cursor.

```sql
-- Bad: trigger function with unbounded cursor
CREATE OR REPLACE FUNCTION audit_all_rows()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM orders LOOP  -- no WHERE clause: full table scan on every trigger fire
        -- process rec
    END LOOP;
    RETURN NEW;
END;
$$;

-- Good: cursor limited to the affected row only
CREATE OR REPLACE FUNCTION audit_related_rows()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM orders WHERE customer_id = NEW.customer_id LOOP
        -- process only relevant rows
    END LOOP;
    RETURN NEW;
END;
$$;
```

--------------------------------

### Identify and Fix Tables Without Primary Keys (B001)

Source: https://pglinter.readthedocs.io/en/latest/examples

Use these queries to detect tables lacking primary keys and apply the fix by adding a primary key constraint.

```sql
-- Create a problematic table
CREATE TABLE my_table_without_pk (
    id INT,
    name TEXT,
    code TEXT,
    enable BOOL DEFAULT TRUE
);

-- Check for tables without primary keys
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  from pglinter.get_violations() WHERE rule_code = 'B001';

-- Get detailed explanation
SELECT pglinter.explain_rule('B001');
```

```sql
ALTER TABLE my_table_without_pk ADD PRIMARY KEY (id);
```

--------------------------------

### Disable Rules in Bulk

Source: https://pglinter.readthedocs.io/en/latest/configure

Demonstrates how to disable a subset of rules using a query filter.

```sql
-- Disable all base rules
SELECT pglinter.disable_rule(rule_code)
FROM pglinter.show_rules()
WHERE rule_code LIKE 'B%';
```

--------------------------------

### Add Custom Fix Suggestions

Source: https://pglinter.readthedocs.io/en/latest/configure

YAML snippet for adding custom fix instructions to a rule.

```yaml
- code: "B001"
  fixes:
    - "Run migration script: add_missing_primary_keys.sql"
    - "Contact DBA team for legacy table primary keys"
    - "See company wiki: Primary Key Standards"
```

--------------------------------

### Manage Rule Status

Source: https://pglinter.readthedocs.io/en/latest/examples

Enable or disable specific rules or toggle all rules at once.

```sql
-- Check if a rule is enabled
SELECT pglinter.is_rule_enabled('B001');

-- Disable a specific rule
SELECT pglinter.disable_rule('B001');

-- Enable a specific rule
SELECT pglinter.enable_rule('B001');

-- Disable all rules (useful for testing)
SELECT pglinter.disable_all_rules();

-- Enable all rules
SELECT pglinter.enable_all_rules();
```

--------------------------------

### Detect Unsecured Public Schema (S003)

Source: https://pglinter.readthedocs.io/en/latest/examples

Checks for security risks associated with default permissions on the public schema.

```sql
-- Check public schema security
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  FROM pglinter.get_violations() WHERE rule_code = 'S003';

-- View current public schema permissions
SELECT * FROM information_schema.usage_privileges WHERE object_name = 'public';
```

--------------------------------

### Save Export to File

Source: https://pglinter.readthedocs.io/en/latest/configure

Redirect the exported YAML output to a file using command-line tools.

```bash
# Export rules and save to file
psql -d mydb -t -c "SELECT pglinter.export_rules_to_yaml();" > rules_config.yaml

# Or using the built-in file export function (if available)
# SELECT pglinter.export_rules_to_file('/tmp/rules_config.yaml');
```

--------------------------------

### Define Environment-Specific Rule Sets

Source: https://pglinter.readthedocs.io/en/latest/configure

Conceptual structure for managing different rule sets across environments.

```yaml
# Development environment - permissive
rules_dev.yaml:
- Some rules disabled
- Disable strict naming conventions
- Focus on structural issues

# Staging environment - moderate settings
rules_staging.yaml:
- All rules enabled
- Validate before production

# Production environment - strict settings
rules_production.yaml:
- All critical rules enabled
- Focus on security and performance
```

--------------------------------

### Rule Import/Export Functions

Source: https://pglinter.readthedocs.io/en/latest/rules

Functions to manage rule configurations via YAML format.

```APIDOC
## Rule Import/Export

### Methods
- `pglinter.export_rules_to_yaml()`: Exports current rule configurations to a YAML string.
- `pglinter.import_rules_from_yaml(yaml_content)`: Imports rules from a provided YAML string.
- `pglinter.import_rules_from_file(file_path)`: Imports rules from a YAML file at the specified path.

### Parameters
- **yaml_content** (text) - Required - The YAML formatted string containing rule definitions.
- **file_path** (text) - Required - The absolute path to the YAML file.
```

--------------------------------

### Manage analysis rules

Source: https://pglinter.readthedocs.io/en/latest

Functions for listing available rules, toggling their status, and retrieving detailed explanations for specific rules.

```sql
-- Show all rules
   SELECT pglinter.show_rules();

   -- Disable a specific rule
   SELECT pglinter.disable_rule('B001');

   -- Get rule explanation
   SELECT pglinter.explain_rule('B002');
```

--------------------------------

### Identify and Fix Schema and Table Owner Mismatches

Source: https://pglinter.readthedocs.io/en/latest/rules

Use these SQL queries to detect tables with mismatched owners and to update their ownership to match the schema owner.

```sql
-- Find schemas where table owners don't match schema owner
SELECT
    n.nspname AS schema_name,
    r_schema.rolname AS schema_owner,
    c.relname AS table_name,
    r_table.rolname AS table_owner
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_roles r_schema ON n.nspowner = r_schema.oid
JOIN pg_roles r_table ON c.relowner = r_table.oid
WHERE
    c.relkind = 'r'  -- regular tables only
    AND n.nspowner <> c.relowner  -- owners are different
    AND n.nspname NOT IN ('information_schema', 'pg_catalog');

-- Align table ownership with schema ownership
ALTER TABLE myschema.table1 OWNER TO schema_owner_role;
ALTER TABLE myschema.table2 OWNER TO schema_owner_role;

-- Or change all tables in a schema at once
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'myschema'
    LOOP
        EXECUTE 'ALTER TABLE myschema.' || rec.tablename || ' OWNER TO schema_owner_role';
    END LOOP;
END $$;
```

--------------------------------

### Detect Schema Without Default Role Grants (S001)

Source: https://pglinter.readthedocs.io/en/latest/examples

Identifies schemas lacking proper role grants. Requires cleanup of the created schema and role after testing.

```sql
-- Create problematic schema setup
CREATE ROLE s001_owner LOGIN;
CREATE SCHEMA s001_schema AUTHORIZATION s001_owner;
-- No default privileges granted

-- Check schema security
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  FROM pglinter.get_violations() WHERE rule_code = 'S001';

-- Cleanup
DROP SCHEMA s001_schema CASCADE;
DROP ROLE s001_owner;
```

--------------------------------

### Index Foreign Key Columns

Source: https://pglinter.readthedocs.io/en/latest/rules

Create indexes on foreign key columns to optimize JOIN operations and referential integrity checks.

```sql
-- Add index to foreign key column
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite index for multi-column foreign keys
CREATE INDEX idx_order_items_order_product ON order_items (order_id, product_id);
```

--------------------------------

### Monitor Pglinter Violations

Source: https://pglinter.readthedocs.io/en/latest/rules

Execute this query to retrieve a list of all current violations for enabled rules.

```sql
-- Get current violations for all enabled rules
SELECT * FROM pglinter.get_violations();
```

--------------------------------

### Secure Public Schema Access

Source: https://pglinter.readthedocs.io/en/latest/rules

Commands to identify and restrict schemas that allow public object creation.

```sql
-- Check which schemas allow public creation
SELECT nspname
FROM pg_namespace
WHERE HAS_SCHEMA_PRIVILEGE('public', nspname, 'CREATE')
AND nspname NOT IN ('information_schema', 'pg_catalog');

-- Secure a schema by revoking public creation privileges
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON SCHEMA myschema FROM PUBLIC;

-- Grant creation privileges to specific roles only
GRANT CREATE ON SCHEMA myschema TO app_role;
```

--------------------------------

### Detect and Standardize Mixed Table Ownership

Source: https://pglinter.readthedocs.io/en/latest/rules

Identifies schemas containing tables owned by multiple users and provides commands to standardize ownership to a single role.

```sql
-- Find schemas with mixed table ownership
SELECT
    schemaname,
    array_agg(DISTINCT tableowner) AS owners,
    count(DISTINCT tableowner) AS owner_count
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
GROUP BY schemaname
HAVING count(DISTINCT tableowner) > 1;

-- Standardize table ownership within a schema
ALTER TABLE schema_name.table1 OWNER TO schema_owner_role;
ALTER TABLE schema_name.table2 OWNER TO schema_owner_role;
```

--------------------------------

### Rename Objects to Lowercase

Source: https://pglinter.readthedocs.io/en/latest/rules

Standardize database object names to lowercase to avoid quoting requirements and improve portability.

```sql
-- Rename table to lowercase
ALTER TABLE "UserProfiles" RENAME TO user_profiles;

-- Rename column to lowercase
ALTER TABLE users RENAME COLUMN "FirstName" TO first_name;
```

--------------------------------

### Verify Rule Status

Source: https://pglinter.readthedocs.io/en/latest/rules

Check if a specific rule is enabled in the pglinter rules table.

```sql
SELECT enable FROM pglinter.rules WHERE code = 'B001';
```

--------------------------------

### Identify Unused Tables via SQL

Source: https://pglinter.readthedocs.io/en/latest/rules

Use this query to find tables in PostgreSQL that have zero sequential or index scans, indicating they may be unused.

```sql
-- Check table usage statistics
SELECT schemaname, tablename, seq_scan, idx_scan, n_tup_ins, n_tup_upd
FROM pg_stat_user_tables
WHERE seq_scan = 0 AND idx_scan = 0;
```

--------------------------------

### Identify and Fix Uppercase Table/Column Names (B005)

Source: https://pglinter.readthedocs.io/en/latest/examples

Detect uppercase identifiers that require quoting and migrate them to lowercase for better portability.

```sql
-- Create tables with uppercase names (problematic)
CREATE TABLE "UPPERCASE_TABLE" (
    "ID" INT PRIMARY KEY,
    "NAME" TEXT
);

-- Check for uppercase identifiers
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  FROM pglinter.get_violations() WHERE rule_code = 'B005';
```

```sql
CREATE TABLE lowercase_table (
    id INT PRIMARY KEY,
    name TEXT
);
```

--------------------------------

### Configure Custom Rule Sets

Source: https://pglinter.readthedocs.io/en/latest/rules

Use these functions to disable specific rules, such as noisy rules in a development environment.

```sql
-- Create environment-specific rule sets
-- Disable noisy rules in development
SELECT pglinter.disable_rule('B004');
SELECT pglinter.disable_rule('B005');
```

--------------------------------

### Rule Management Functions

Source: https://pglinter.readthedocs.io/en/latest/examples

Functions for listing, explaining, and managing the status of linting rules.

```APIDOC
## SELECT pglinter.list_rules()

### Description
Lists all available linting rules and their current enabled/disabled status.

## SELECT pglinter.show_rules()

### Description
Displays detailed status information for all rules.

## SELECT pglinter.explain_rule(rule_code TEXT)

### Description
Retrieves a detailed explanation for a specific rule identified by its code (e.g., 'B001').

### Parameters
- **rule_code** (TEXT) - Required - The unique identifier for the rule.
```

--------------------------------

### Query Violation Reports

Source: https://pglinter.readthedocs.io/en/latest/examples

Retrieve and aggregate violation data from the pglinter engine.

```sql
-- Get all violations
SELECT * FROM pglinter.get_violations();

-- Count violations per rule
SELECT rule_code, count(*) AS violation_count
FROM pglinter.get_violations()
GROUP BY rule_code
ORDER BY rule_code;

-- Check for violations with explanation
SELECT v.rule_code, v.message
FROM pglinter.get_violations() v;
```

--------------------------------

### Execute Rule Violations

Source: https://pglinter.readthedocs.io/en/latest/rules

Retrieve and aggregate violation data for enabled rules.

```sql
-- Get all violations for enabled rules
SELECT * FROM pglinter.get_violations();

-- Filter violations for a specific rule
SELECT * FROM pglinter.get_violations() WHERE rule_code = 'B001';
SELECT * FROM pglinter.get_violations() WHERE rule_code = 'C002';

-- Count violations per rule
SELECT rule_code, count(*) AS violation_count
FROM pglinter.get_violations()
GROUP BY rule_code
ORDER BY rule_code;
```

--------------------------------

### Inspect Rule Queries

Source: https://pglinter.readthedocs.io/en/latest/rules

Retrieve the violation-location query associated with a specific rule.

```sql
SELECT pglinter.show_rule_queries('B001');
```

--------------------------------

### Debug Rule Queries

Source: https://pglinter.readthedocs.io/en/latest/examples

Inspect the underlying queries used by a specific rule for debugging purposes.

```sql
-- Show q4 violation-location query used by a rule (for debugging)
SELECT pglinter.show_rule_queries('B001');
```

--------------------------------

### Run database analysis

Source: https://pglinter.readthedocs.io/en/latest

Use these functions to retrieve a list of detected violations, optionally filtered by specific rule codes.

```sql
-- Get all violations for enabled rules
   SELECT * FROM pglinter.get_violations();

   -- Filter violations for a specific rule
   SELECT * FROM pglinter.get_violations() WHERE rule_code = 'B001';
```

--------------------------------

### pglinter.get_violations()

Source: https://pglinter.readthedocs.io/en/latest

Retrieves a list of all detected database violations based on currently enabled rules.

```APIDOC
## SELECT pglinter.get_violations()

### Description
Returns a set of all violations detected by the enabled pglinter rules. This function can be filtered by specific rule codes.

### Usage
```sql
-- Get all violations
SELECT * FROM pglinter.get_violations();

-- Filter by specific rule
SELECT * FROM pglinter.get_violations() WHERE rule_code = 'B001';
```
```

--------------------------------

### Identify and Remove Unused Indexes

Source: https://pglinter.readthedocs.io/en/latest/rules

Locate indexes that are not being utilized by the query planner and remove them to reduce maintenance overhead.

```sql
-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Drop unused index
DROP INDEX idx_unused_column;
```

--------------------------------

### Audit and Reassign Schema Ownership

Source: https://pglinter.readthedocs.io/en/latest/rules

Query to identify schemas owned by system roles and commands to reassign ownership to functional roles.

```sql
-- Find schemas owned by system roles or superusers
SELECT
    n.nspname AS schema_name,
    r.rolname AS owner_name,
    r.rolsuper AS is_superuser
FROM pg_namespace n
JOIN pg_roles r ON n.nspowner = r.oid
WHERE (
    r.rolsuper IS TRUE
    OR r.rolname LIKE 'pg_%'
    OR r.rolname = 'postgres'
)
AND n.nspname NOT IN ('information_schema', 'pg_catalog');

-- Change schema ownership to a functional role
ALTER SCHEMA myschema OWNER TO app_owner_role;
```

--------------------------------

### pglinter.get_violations()

Source: https://pglinter.readthedocs.io/en/latest/how-to

Retrieves all detected violations for enabled rules in the PostgreSQL database.

```APIDOC
## SELECT pglinter.get_violations()

### Description
Retrieves a set of violations for all enabled rules in the database. Each record includes the rule code and identifiers for the affected database object.

### Method
SQL SELECT

### Endpoint
pglinter.get_violations()

### Response
- **rule_code** (text) - The code of the violated rule.
- **classid** (oid) - The OID of the system catalog containing the object.
- **objid** (oid) - The OID of the object.
- **objsubid** (int) - The sub-identifier of the object.

### Usage Example
```sql
-- Get all violations
SELECT * FROM pglinter.get_violations();

-- Filter by specific rule
SELECT * FROM pglinter.get_violations() WHERE rule_code = 'B001';

-- Get readable object details
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
FROM pglinter.get_violations();
```
```

--------------------------------

### Enable and Disable Rules

Source: https://pglinter.readthedocs.io/en/latest/rules

Modify the active status of rules or check their current state.

```sql
-- Disable a specific rule
SELECT pglinter.disable_rule('B001');

-- Enable a specific rule
SELECT pglinter.enable_rule('B001');

-- Disable all rules
SELECT pglinter.disable_all_rules();

-- Enable all rules
SELECT pglinter.enable_all_rules();

-- Check if rule is enabled
SELECT pglinter.is_rule_enabled('B001');
```

--------------------------------

### Find Foreign Key Type Mismatches via SQL

Source: https://pglinter.readthedocs.io/en/latest/rules

Use this query to detect instances where a foreign key column's data type does not match the referenced column's data type.

```sql
-- Find foreign key type mismatches
SELECT
    tc.table_schema,
    tc.table_name,
    tc.constraint_name,
    kcu.column_name,
    col1.data_type AS fk_column_type,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column,
    col2.data_type AS referenced_column_type
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
JOIN information_schema.columns col1 ON kcu.table_name = col1.table_name AND kcu.column_name = col1.column_name
JOIN information_schema.columns col2 ON ccu.table_name = col2.table_name AND ccu.column_name = col2.column_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND col1.data_type != col2.data_type;
```

--------------------------------

### Identify and Remove Redundant Indexes

Source: https://pglinter.readthedocs.io/en/latest/rules

Query pg_stat_user_indexes to find indexes with zero scans, then drop them to reclaim storage and improve write performance.

```sql
-- Find potentially redundant indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Drop redundant index
DROP INDEX idx_users_email_duplicate;
```

--------------------------------

### Rule Management Functions

Source: https://pglinter.readthedocs.io/en/latest/configure

Functions for querying rule status and modifying rule configurations.

```APIDOC
## SQL Functions

### pglinter.show_rules()
Returns a list of all available rules and their current status.

### pglinter.is_rule_enabled(rule_code)
Checks if a specific rule is currently enabled.
- **rule_code** (text) - Required - The identifier of the rule to check.

### pglinter.explain_rule(rule_code)
Returns detailed information about a specific rule.
- **rule_code** (text) - Required - The identifier of the rule to explain.

### pglinter.disable_rule(rule_code)
Disables a specific rule.
- **rule_code** (text) - Required - The identifier of the rule to disable.

### pglinter.enable_rule(rule_code)
Enables a specific rule.
- **rule_code** (text) - Required - The identifier of the rule to enable.
```

--------------------------------

### Rule State Management Functions

Source: https://pglinter.readthedocs.io/en/latest/rules

Functions to enable or disable specific rules or all rules at once.

```APIDOC
## Rule State Management

### Methods
- `pglinter.enable_rule(code)`: Enables a specific rule.
- `pglinter.disable_rule(code)`: Disables a specific rule.
- `pglinter.enable_all_rules()`: Enables all rules.
- `pglinter.disable_all_rules()`: Disables all rules.
- `pglinter.is_rule_enabled(code)`: Returns a boolean indicating if the rule is enabled.

### Parameters
- **code** (text) - Required (for specific rule functions) - The unique code identifier for the rule.
```

--------------------------------

### Toggle Rules

Source: https://pglinter.readthedocs.io/en/latest/configure

Functions to enable or disable individual rules by their code identifier.

```sql
-- Disable a rule you don't want to check
SELECT pglinter.disable_rule('B001');

-- Re-enable a rule
SELECT pglinter.enable_rule('B001');

-- Disable multiple rules
SELECT pglinter.disable_rule('B004');
```

--------------------------------

### Identify and Fix Redundant Indexes (B002)

Source: https://pglinter.readthedocs.io/en/latest/examples

Detect duplicate indexes or indexes redundant with unique constraints and remove them to optimize storage and write performance.

```sql
-- Create table with redundant indexes
CREATE TABLE test_table_with_redundant_indexes (
    id INT PRIMARY KEY,
    name TEXT,
    email VARCHAR(255),
    status VARCHAR(50)
);

-- Create redundant indexes
CREATE INDEX idx_name_1 ON test_table_with_redundant_indexes (name);
CREATE INDEX idx_name_2 ON test_table_with_redundant_indexes (name); -- redundant!

-- Create table with unique constraint and redundant index
CREATE TABLE orders_table_with_constraint (
    order_id SERIAL PRIMARY KEY,
    customer_id INT UNIQUE,
    product_name VARCHAR(255)
);

-- This index is redundant with the unique constraint above
CREATE INDEX my_idx_customer ON orders_table_with_constraint (customer_id);

-- Check for redundant indexes
SELECT
  rule_code,
  (pg_identify_object(classid, objid, objsubid)).type,
  (pg_identify_object(classid, objid, objsubid)).schema,
  (pg_identify_object(classid, objid, objsubid)).name,
  (pg_identify_object(classid, objid, objsubid)).identity
  FROM pglinter.get_violations() WHERE rule_code = 'B002';
```

```sql
DROP INDEX idx_name_2;
DROP INDEX my_idx_customer; -- The unique constraint already provides an index
```