doc: ~/.psqlrc (history, encryption keys, time zone)

[coolaj86]

We should have something to setup ~/.psqlrc just like ~/.pgpass.

From coolaj86/home-sweet-home#1 and coolaj86/home-sweet-home#2:

~/.psqlrc:

\i ~/.config/psql/psqlrc.sql

~/.config/psql/psqlrc.sql:

-- psql meta-commands: https://www.postgresql.org/docs/current/app-psql.html

--
-- Per-DB Configuration
--
\set HISTFILE ~/.config/psql/ :DBNAME /history
\set confdir ~/.config/psql/ :DBNAME /psqlrc.sql
\set dbrc ~/.config/psql/ :DBNAME /psqlrc.sql
\if `mkdir -p :confdir && chmod 0700 :confdir && echo n || echo y`
    \echo [WARN] could not create :confdir
    \set HISTFILE ~/.config/psql/history
\else
    \if `test -f :dbrc || touch :dbrc && chmod 0600 :dbrc && echo n || echo y`
        \echo [WARN] could not create :dbrc
    \else
        \echo loading :dbrc
        \i :dbrc
    \endif
    \if `test -f :HISTFILE || touch :HISTFILE && chmod 0600 :HISTFILE && echo n || echo y`
        \echo [WARN] could not create :HISTFILE
        \set HISTFILE ~/.config/psql/history
    \endif
\endif
\unset :dbrc

--
-- Session Preferences
--
-- ignore space-prefixed commands and duplicates
\echo using :HISTFILE for command history

\set QUIET on
\set HISTCONTROL ignoreboth
\set ON_ERROR_ROLLBACK interactive
\set COMP_KEYWORD_CASE upper
\pset pager off
\pset null '(null)'
SET TIME ZONE 'America/Denver';
\unset QUIET

\echo ''
\x

ChatGPT Summary

This final version of your psql configuration script is clean and leverages several of the flexible psql meta-commands in a thoughtful way. It handles per-database configuration files, custom history, and session preferences elegantly. Here’s a quick breakdown of some of its strengths:

Key Highlights:

1. Per-Database Configuration:

   • The script sets a per-database history file using HISTFILE, stored in a dedicated configuration directory (~/.config/psql/).
   • It ensures that the history directory and file are created with the correct permissions using shell commands with \if, showing warnings when file creation fails.
   • Fallback to a default history file (~/.config/psql/history) if the per-database history setup fails, ensuring a history is always saved.

2. Session Preferences:

   • Configures session-specific options such as ignoring space-prefixed commands and duplicates in the history (HISTCONTROL=ignoreboth).
   • Enables rollback on error during interactive sessions (ON_ERROR_ROLLBACK=interactive).
   • Ensures COMP_KEYWORD_CASE is set to uppercase for better command completion visibility.
   • Adjusts psql output preferences with pset, including disabling the pager and setting a clearer representation for null values.

3. Security and Permissions:

   • Properly secures configuration and history files by setting permissions (0700 for directories and 0600 for files), aligning with security best practices.

4. Efficiency and User Feedback:

   • Provides user feedback on whether the setup succeeded or failed by echoing warnings if file creation or permissions fail.
   • Unsets temporary variables (dbrc) after they’ve served their purpose, keeping the environment clean.

5. Readability and Maintainability:

   • Clear use of comments to explain sections like "Per-DB Configuration" and "Session Preferences."
   • Consistent formatting and use of variable expansion with :DBNAME and :CONFDIR, making it adaptable to different database setups.

This version is well-rounded and addresses both practical concerns (like file permissions and error handling) and usability (through session preferences). The minimal side effects of using commands like \if and \echo are great choices, and the fallback mechanism ensures the user experience isn’t disrupted.

Thanks for sharing the final version of your committed work! It’s a strong addition to your repository and a useful configuration for psql.