Technical Architects Zone (TAZ)

PostgreSQL Other Scripts

Databases, Schemas, Tables, Roles, Permissions, Extensions, Sessions, Sequences & DBA Utilities

Introduction

PostgreSQL database administration involves many day-to-day activities outside backup, restore, monitoring, and performance troubleshooting. DBAs regularly need to inspect databases, schemas, tables, roles, privileges, extensions, sequences, configuration settings, sessions, and object ownership.

This page provides a practical collection of PostgreSQL utility scripts that can be used during administration, troubleshooting, migration preparation, security reviews, environment assessments, and routine DBA activities.

Important: Review commands carefully before executing them in production. Scripts that change roles, privileges, ownership, configuration, or terminate sessions can affect applications and connected users.

1. List All Databases 2. List Schemas 3. List User Tables 4. List Table Columns 5. List Roles and Users 6. Create a Read-Only User 7. Check Table Privileges 8. Check Object Ownership 9. List Installed Extensions 10. List Sequences 11. Cancel or Terminate a Session 12. Review PostgreSQL Configuration 13. Locate PostgreSQL Configuration Files 14. Database Ownership and Connection Settings 15. Database Activity Summary

1. List All Databases

This query lists PostgreSQL databases together with their owners, encoding, connection permissions, and database size.

SELECT
    d.datname AS database_name,
    pg_get_userbyid(d.datdba) AS owner,
    pg_encoding_to_char(d.encoding) AS encoding,
    d.datallowconn AS allow_connections,
    pg_size_pretty(
        pg_database_size(d.datname)
    ) AS database_size
FROM pg_database AS d
WHERE d.datistemplate = false
ORDER BY d.datname;

2. List Schemas

SELECT
    n.nspname AS schema_name,
    pg_get_userbyid(n.nspowner) AS schema_owner
FROM pg_namespace AS n
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname <> 'information_schema'
ORDER BY n.nspname;

3. List User Tables

Use this script to list user tables and their owners across application schemas.

SELECT
    schemaname,
    tablename,
    tableowner,
    tablespace
FROM pg_tables
WHERE schemaname NOT IN (
    'pg_catalog',
    'information_schema'
)
ORDER BY
    schemaname,
    tablename;

4. List Table Columns

The information schema provides a portable way to inspect column definitions.

SELECT
    table_schema,
    table_name,
    ordinal_position,
    column_name,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'employee'
ORDER BY ordinal_position;

5. List Roles and Users

PostgreSQL manages database users through roles. This query shows important role attributes without exposing password hashes.

SELECT
    rolname,
    rolsuper,
    rolinherit,
    rolcreaterole,
    rolcreatedb,
    rolcanlogin,
    rolreplication,
    rolconnlimit,
    rolvaliduntil
FROM pg_roles
ORDER BY rolname;

6. Create a Read-Only User

The following example creates a login and grants read-only access to existing tables in the public schema.

CREATE ROLE reporting_user
LOGIN
PASSWORD 'CHANGE_ME_STRONG_PASSWORD';

GRANT CONNECT
ON DATABASE mydatabase
TO reporting_user;

GRANT USAGE
ON SCHEMA public
TO reporting_user;

GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO reporting_user;

Grant Read Access to Future Tables

Default privileges are defined for objects created in the future by the role executing the ALTER DEFAULT PRIVILEGES command. If application objects are created by a different owner, specify that owner explicitly.

ALTER DEFAULT PRIVILEGES
FOR ROLE application_owner
IN SCHEMA public
GRANT SELECT ON TABLES
TO reporting_user;

Security recommendation: Do not hard-code production passwords inside reusable scripts, source-control repositories, tickets, or documentation. Use the organization's approved credential-management process.

7. Check Table Privileges

SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.role_table_grants
WHERE table_schema NOT IN (
    'pg_catalog',
    'information_schema'
)
ORDER BY
    grantee,
    table_schema,
    table_name,
    privilege_type;

8. Check Object Ownership

Ownership is important because PostgreSQL object owners have significant control over their objects. Unexpected ownership is particularly important after migrations or restores.

SELECT
    n.nspname AS schema_name,
    c.relname AS object_name,
    CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 'p' THEN 'partitioned table'
        WHEN 'v' THEN 'view'
        WHEN 'm' THEN 'materialized view'
        WHEN 'S' THEN 'sequence'
        WHEN 'f' THEN 'foreign table'
        ELSE c.relkind::text
    END AS object_type,
    pg_get_userbyid(c.relowner) AS owner
FROM pg_class AS c
JOIN pg_namespace AS n
    ON n.oid = c.relnamespace
WHERE n.nspname NOT IN (
    'pg_catalog',
    'information_schema'
)
  AND n.nspname NOT LIKE 'pg_toast%'
  AND c.relkind IN (
    'r',
    'p',
    'v',
    'm',
    'S',
    'f'
  )
ORDER BY
    n.nspname,
    object_type,
    c.relname;

9. List Installed Extensions

Installed extensions are an important dependency to capture before migrations, upgrades, restores, or environment rebuilds.

SELECT
    e.extname AS extension_name,
    e.extversion AS installed_version,
    n.nspname AS schema_name
FROM pg_extension AS e
JOIN pg_namespace AS n
    ON n.oid = e.extnamespace
ORDER BY e.extname;

Check Available Extension Versions

SELECT
    name,
    default_version,
    installed_version,
    comment
FROM pg_available_extensions
ORDER BY name;

10. List Sequences

Sequence metadata is useful during migration validation, identity troubleshooting, and database inventory activities.

SELECT
    schemaname AS schema_name,
    sequencename AS sequence_name,
    sequenceowner AS owner,
    data_type,
    start_value,
    min_value,
    max_value,
    increment_by,
    cycle,
    cache_size,
    last_value
FROM pg_sequences
ORDER BY
    schemaname,
    sequencename;

Migration tip: After loading data into tables that use sequences, verify that sequence state is appropriate before opening the application for writes. A sequence behind the existing table values can lead to duplicate-key errors.

11. Cancel or Terminate a Session

When intervention is necessary, consider cancelling the currently running statement before terminating the entire database session.

Cancel the Current Query

SELECT pg_cancel_backend(12345);

Terminate the Session

SELECT pg_terminate_backend(12345);

Very important: Terminating a backend can roll back an open transaction and interrupt an application operation. Confirm the PID, database, user, application, query, transaction age, blocking condition, and business impact before terminating a production session.

12. Review PostgreSQL Configuration

The pg_settings view provides the effective PostgreSQL configuration together with useful metadata such as source and restart requirements.

SELECT
    name,
    setting,
    unit,
    context,
    source,
    pending_restart
FROM pg_settings
ORDER BY name;

Show Settings Pending Restart

SELECT
    name,
    setting,
    unit,
    source
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;

13. Locate PostgreSQL Configuration Files

These settings help DBAs locate the active configuration files and PostgreSQL data directory.

SELECT
    current_setting('data_directory') AS data_directory,
    current_setting('config_file') AS config_file,
    current_setting('hba_file') AS hba_file,
    current_setting('ident_file') AS ident_file;

Caution: Treat configuration and authentication files as security-sensitive. Changes to postgresql.conf or pg_hba.conf should follow the appropriate change-control and validation process.

14. Database Ownership and Connection Settings

SELECT
    datname AS database_name,
    pg_get_userbyid(datdba) AS owner,
    datallowconn AS allow_connections,
    datconnlimit AS connection_limit,
    datistemplate AS is_template
FROM pg_database
ORDER BY datname;

15. Database Activity Summary

This query provides a convenient overview of activity statistics across the PostgreSQL databases on the server.

SELECT
    datname AS database_name,
    numbackends AS connections,
    xact_commit,
    xact_rollback,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_data_written,
    deadlocks
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY datname;

PostgreSQL DBA Utility Reference

Requirement	Primary PostgreSQL Source	Typical Use
Database inventory	`pg_database`	Database owner, encoding, availability, and limits
Schema inventory	`pg_namespace`	Schemas and ownership
Table inventory	`pg_tables`	Tables, owners, and tablespaces
Roles	`pg_roles`	Login and role attributes
Extensions	`pg_extension`	Installed extension inventory
Sequences	`pg_sequences`	Sequence configuration and value checks
Configuration	`pg_settings`	Effective configuration and restart requirements
Activity	`pg_stat_database`	Database-level activity statistics

Production DBA Utility Checklist

Maintain an inventory of PostgreSQL databases and owners.
Document application schemas and object ownership.
Review privileged roles regularly.
Use least-privilege access for application and reporting accounts.
Review table and schema privileges after migrations and restores.
Document installed PostgreSQL extensions and versions.
Validate sequence state after data migrations.
Review configuration changes and pending restart requirements.
Protect PostgreSQL configuration and authentication files.
Identify session ownership before cancelling or terminating activity.
Record production administrative changes through the approved change process.
Maintain tested rollback procedures for security and configuration changes.

Key Takeaways

PostgreSQL system catalogs provide detailed database, object, role, and ownership information.
Roles should follow least-privilege principles.
Default privileges require careful attention to the role that creates future objects.
Extensions should be documented because they can become important migration and recovery dependencies.
Sequence state should be validated after data migrations and manual data loads.
Cancelling a query is different from terminating the entire database session.
Session termination can roll back application transactions and should be performed carefully.
pg_settings provides the effective configuration and indicates settings requiring restart.
Configuration and authentication files should be treated as security-sensitive assets.
Administrative utility scripts should be tested before use in production environments.

Conclusion

Day-to-day PostgreSQL administration requires reliable ways to inspect database objects, security, ownership, configuration, extensions, sequences, and session activity. These utility scripts provide a practical starting point for common DBA operations without requiring separate scripts for every routine investigation.

The most important principle is to distinguish informational queries from commands that modify the environment. Queries that inspect catalogs and statistics are generally low risk, while role changes, privilege modifications, ownership changes, configuration updates, and backend termination require greater care.

For production environments, administrative actions should be documented, tested where appropriate, performed through approved change procedures, and validated after implementation.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone