How to Use the pglinter Extension in PostgreSQL 18

pglinter is a PostgreSQL extension designed to analyze database structure and configuration using predefined rules. Instead of inspecting schemas manually, pglinter automatically detects common design, performance, and security issues such as:

• Tables without primary keys
• Redundant or unused indexes
• Foreign key problems
• Insecure schemas
• Weak authentication settings
• Naming inconsistencies

It acts like a static analyzer for PostgreSQL, helping DBAs and developers enforce best practices and improve database quality.

Installation on PostgreSQL 18

Because pglinter is distributed as a compiled package, installation happens at two levels:

1. Linux level (installing binaries)
2. PostgreSQL level (registering the extension)

1. Set environment variables

export PG_MAJOR_VERSION=18
export PGLINTER_VERSION=<your_version>
export ARCH=amd64

These variables ensure you download the package built specifically for PostgreSQL 18 and your CPU architecture.

2. Download the package

wget https://github.com/pmpetit/pglinter/releases/download/${PGLINTER_VERSION}/postgresql_pglinter_${PG_MAJOR_VERSION}_${PGLINTER_VERSION}_${ARCH}.deb

At this stage, the file is only downloaded. PostgreSQL cannot use it yet.

3. Install the package

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

This step places:

• pglinter.so into PostgreSQL’s library directory
• SQL and control files into the extension directory

Without this step, CREATE EXTENSION will fail.

4. Connect as postgres and enable the extension

sudo su postgres
psql -p 5433

Inside psql:

CREATE EXTENSION pglinter;

Verify:

\dx+ pglinter

You should see functions, schemas, tables like this

      Objects in extension "pglinter"
                     Object description                     
------------------------------------------------------------
 function hello_pglinter()
 function pglinter.check_rule(text,text)
 function pglinter."check"(text)
 function pglinter.disable_all_rules()
 function pglinter.disable_rule(text)
 function pglinter.enable_all_rules()
 function pglinter.enable_rule(text)
 function pglinter.explain_rule(text)
 function pglinter.export_rules_to_file(text)
 function pglinter.export_rules_to_yaml()
 function pglinter.get_rule_levels(text)
 function pglinter.import_rules_from_file(text)
 function pglinter.import_rules_from_yaml(text)
 function pglinter.is_rule_enabled(text)
 function pglinter.list_rules()
 function pglinter.show_rule_queries(text)
 function pglinter.show_rules()
 function pglinter.update_rule_levels(text,integer,integer)
 schema pglinter
 sequence pglinter.rules_id_seq
 table pglinter.rules
 type pglinter.rules
 type pglinter.rules[]
(23 rows)

Verifying Installation

A quick sanity check:

SELECT * FROM hello_pglinter();

Output:

Hello, pglinter

This confirms the extension is loaded correctly.

Exploring Available Rules

To list all rules:

SELECT * FROM pglinter.list_rules();

This returns rule codes such as:

• B001 – Tables without primary keys
• B002 – Redundant indexes
• B004 – Unused indexes
• C001–C003 – Security checks
• S001–S005 – Schema ownership and access rules

                                list_rules                                 
---------------------------------------------------------------------------
 ?? Available Rules:                                                      +
 ============================================================             +
 ? [B001] DISABLED - HowManyTableWithoutPrimaryKey                       +
 ? [B002] DISABLED - HowManyRedudantIndex                                +
 ? [B003] DISABLED - HowManyTableWithoutIndexOnFk                        +
 ? [B004] ENABLED - HowManyUnusedIndex                                   +
 ? [B005] DISABLED - HowManyObjectsWithUppercase                         +
 ? [B006] DISABLED - HowManyTablesNeverSelected                          +
 ? [B007] DISABLED - HowManyTablesWithFkOutsideSchema                    +
 ? [B008] DISABLED - HowManyTablesWithFkMismatch                         +
 ? [B009] DISABLED - HowManyTablesWithSameTrigger                        +
 ? [B010] DISABLED - HowManyTablesWithReservedKeywords                   +
 ? [B011] DISABLED - SeveralTableOwnerInSchema                           +
 ? [B012] DISABLED - CompositePrimaryKeyTooManyColumns                   +
 ? [C001] DISABLED - PgHbaEntriesWithMethodTrustShouldNotExists          +
 ? [C002] DISABLED - PgHbaEntriesWithMethodTrustOrPasswordShouldNotExists+
 ? [C003] DISABLED - PasswordEncryptionIsMd5                             +
 ? [S001] DISABLED - SchemaWithDefaultRoleNotGranted                     +
 ? [S002] DISABLED - SchemaPrefixedOrSuffixedWithEnvt                    +
 ? [S003] DISABLED - UnsecuredPublicSchema                               +
 ? [S004] DISABLED - OwnerSchemaIsInternalRole                           +
 ? [S005] DISABLED - SchemaOwnerDoNotMatchTableOwner                     +
 ============================================================
(1 row)

Each rule belongs to a category:

• B - Base design
• C - Configuration/security
• S - Schema governance

To display rule status in a formatted view:

SELECT * FROM pglinter.show_rules();

This shows which rules are enabled or disabled.

Enabling and Disabling Rules

Enable all rules

SELECT * FROM pglinter.enable_all_rules();

Disable all rules

SELECT * FROM pglinter.disable_all_rules();

Enable a specific rule

Example: enable unused index detection (B004):

SELECT * FROM pglinter.enable_rule('B004');

Confirm:

SELECT pglinter.is_rule_enabled('B004');

Understanding a Rule in Detail

To understand what a rule does:

SELECT pglinter.explain_rule('B004');

This provides:

• Rule purpose
• Description
• Warning/error thresholds
• Remediation guidance

For B004, the rule compares total manual indexes against unused indexes using pg_stat_user_indexes, ignoring primary and unique constraint indexes.

Viewing Internal Queries Used by a Rule

You can inspect the actual SQL behind any rule:

SELECT pglinter.explain_rule('B004');

This reveals the exact queries used to:

• Count total indexes
• Count unused indexes

This transparency makes pglinter especially valuable for learning and auditing.

Exporting and Importing Rule Configuration

Export rules to YAML (inline)

SELECT pglinter.export_rules_to_yaml();

Export rules to a file

SELECT pglinter.export_rules_to_file('/tmp/pglinter_rules.yaml');

This is useful for:

• Version control
• Sharing configurations across environments
• CI pipelines

Adjusting Warning and Error Thresholds

Each rule has two levels:

• warning_level
• error_level

Check current levels:

SELECT * FROM pglinter.get_rule_levels('B004');

Update them:

SELECT pglinter.update_rule_levels('B004', 1, 10);

Now the rule raises warnings at 1% and errors at 10%.

With pglinter, you can:

• Detect unused indexes before they waste memory
• Enforce primary keys across all tables

• Prevent insecure public schemas
• Catch foreign key mismatches
• Maintain consistent ownership

pglinter turns PostgreSQL into a self-auditing system.

Instead of relying on manual reviews, it continuously evaluates your database using formalized rules. With features like rule inspection, YAML export, and adjustable thresholds, it fits naturally into modern DevOps and database optimization workflows.

If you are building performance-focused or domain-specific PostgreSQL systems, pglinter becomes a powerful foundation for enforcing quality at the database layer.