111 commands · 7 cheat sheets · SQL
Transactions and Security master quick reference
Browse 111 commands from 7 focused cheat sheets on page 1 of 1. Each example opens its matching detailed section.
Transactions and Security · 17 commands
PostgreSQL Advisory Locks Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Lock for the transaction | SELECT pg_advisory_xact_lock(7001, 42); | View examples |
| Try a transaction lock | SELECT pg_try_advisory_xact_lock(7001, 42); | View examples |
| Take a shared lock | SELECT pg_advisory_xact_lock_shared(7001, 42); | View examples |
| Try a shared lock | SELECT pg_try_advisory_xact_lock_shared(7001, 42); | View examples |
| Lock for the session | SELECT pg_advisory_lock(8001, 17); | View examples |
| Try a session lock | SELECT pg_try_advisory_lock(8001, 17); | View examples |
| Unlock one session level | SELECT pg_advisory_unlock(8001, 17); | View examples |
| Unlock all session locks | SELECT pg_advisory_unlock_all(); | View examples |
| Bound lock waits | SET LOCAL lock_timeout = '2s'; | View examples |
| Order multiple keys | SELECT DISTINCT resource_id
FROM app.requested_resources
WHERE batch_id = 88
ORDER BY resource_id; | View examples |
| Limit before locking | SELECT pg_advisory_xact_lock(q.id)
FROM (
SELECT id
FROM app.jobs
ORDER BY id
LIMIT 10
) AS q; | View examples |
| List advisory locks | SELECT pid, mode, granted, classid, objid, objsubid
FROM pg_locks
WHERE locktype = 'advisory'; | View examples |
| Find advisory waiters | SELECT pid, mode, waitstart
FROM pg_locks
WHERE locktype = 'advisory' AND NOT granted
ORDER BY waitstart; | View examples |
| Find blocking processes | SELECT pid, pg_blocking_pids(pid) AS blockers
FROM pg_locks
WHERE locktype = 'advisory' AND NOT granted; | View examples |
| Use one 64-bit key | SELECT pg_try_advisory_xact_lock(9223372036854770000::bigint); | View examples |
| Namespace with two keys | SELECT pg_try_advisory_xact_lock(7001, 42); | View examples |
| Detect recovery | SELECT pg_is_in_recovery(); | View examples |
Transactions and Security · 17 commands
PostgreSQL Deadlocks, Timeouts, and Cancellation Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Lock rows in key order | SELECT id
FROM app.accounts
WHERE id = ANY (ARRAY[41,72])
ORDER BY id FOR
UPDATE; | View examples |
| Lock a table explicitly | LOCK TABLE app.ledger IN SHARE ROW EXCLUSIVE MODE; | View examples |
| Inspect deadlock delay | SHOW deadlock_timeout; | View examples |
| Set transaction-local lock timeout | SET LOCAL lock_timeout = '2s'; | View examples |
| Disable the lock timeout | SET LOCAL lock_timeout = '0'; | View examples |
| Set a statement timeout | SET LOCAL statement_timeout = '30s'; | View examples |
| Set a role default | ALTER ROLE report_user SET statement_timeout = '30s'; | View examples |
| Inspect the timeout | SHOW statement_timeout; | View examples |
| Bound idle transactions | SET idle_in_transaction_session_timeout = '60s'; | View examples |
| Bound a transaction | SET transaction_timeout = '5min'; | View examples |
| Find blocking PIDs | SELECT pg_blocking_pids(12345); | View examples |
| List lock waiters | SELECT pid, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'; | View examples |
| Inspect held locks | SELECT * FROM pg_locks WHERE pid = 12345; | View examples |
| Cancel a backend query | SELECT pg_cancel_backend(12345); | View examples |
| Terminate a backend | SELECT pg_terminate_backend(12345, 5000); | View examples |
| Inspect lock-wait logging | SHOW log_lock_waits; | View examples |
| Inspect active transaction age | SELECT pid, xact_start, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start; | View examples |
Transactions and Security · 16 commands
PostgreSQL Extensions and Extension Security Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| List available extensions | SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name; | View examples |
| Inspect installable versions | SELECT version,
installed,
superuser,
trusted,
relocatable
FROM pg_available_extension_versions
WHERE name = 'pgcrypto'; | View examples |
| Create a controlled schema | CREATE SCHEMA extensions AUTHORIZATION extension_owner; | View examples |
| Protect the target schema | REVOKE CREATE ON SCHEMA extensions FROM PUBLIC; | View examples |
| Install a pinned version | CREATE EXTENSION pgcrypto
WITH SCHEMA extensions VERSION '1.3'; | View examples |
| Install only when absent | CREATE EXTENSION IF NOT EXISTS pgcrypto
WITH SCHEMA extensions VERSION '1.3'; | View examples |
| Inspect installed extensions | SELECT extname,
extversion,
extnamespace::regnamespace,
extrelocatable
FROM pg_extension
ORDER BY extname; | View examples |
| Inspect update paths | SELECT source, target, path
FROM pg_extension_update_paths('pgcrypto')
ORDER BY source, target; | View examples |
| Update to a reviewed version | ALTER EXTENSION pgcrypto UPDATE TO '1.3'; | View examples |
| Move a relocatable extension | ALTER EXTENSION pgcrypto SET SCHEMA extensions_v2; | View examples |
| List extension members | SELECT pg_describe_object(d.classid,d.objid,d.objsubid)
FROM pg_depend d
WHERE d.refobjid = (
SELECT oid
FROM pg_extension
WHERE extname='pgcrypto'
); | View examples |
| Attach an existing object | ALTER EXTENSION app_tools ADD FUNCTION app_tools.normalize_email(text); | View examples |
| Drop without cascading | DROP EXTENSION pgcrypto RESTRICT; | View examples |
| Preserve configuration data | SELECT pg_catalog.pg_extension_config_dump('app_tools.settings', 'WHERE NOT built_in'); | View examples |
| Secure an update script path | SELECT pg_catalog.set_config('search_path', 'pg_catalog, pg_temp', true); | View examples |
| Pin a function search path | ALTER FUNCTION extensions.secure_length(text)
SET search_path = pg_catalog, pg_temp; | View examples |
Transactions and Security · 18 commands
PostgreSQL pgcrypto and Data Encryption Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Install pgcrypto | CREATE EXTENSION IF NOT EXISTS pgcrypto
WITH SCHEMA extensions; | View examples |
| Check its version | SELECT extversion
FROM pg_extension
WHERE extname = 'pgcrypto'; | View examples |
| Check FIPS mode | SELECT extensions.fips_mode(); | View examples |
| Hash text with SHA-256 | SELECT encode(extensions.digest('payload', 'sha256'), 'hex'); | View examples |
| Hash binary data | SELECT extensions.digest(decode('00ff', 'hex'), 'sha512'); | View examples |
| Create an HMAC | SELECT extensions.hmac('payload', 'secret-key', 'sha256'); | View examples |
| Encode an HMAC | SELECT encode(extensions.hmac('payload', 'secret-key', 'sha256'), 'base64'); | View examples |
| Generate a bcrypt hash | SELECT extensions.crypt('password', extensions.gen_salt('bf', 12)); | View examples |
| Verify a password | SELECT extensions.crypt('candidate', password_hash) = password_hash
FROM app.users
WHERE id = 42; | View examples |
| Generate SHA-512 crypt | SELECT extensions.crypt('password', extensions.gen_salt('sha512crypt', 100000)); | View examples |
| Encrypt text symmetrically | SELECT extensions.pgp_sym_encrypt('secret', 'passphrase', 'cipher-algo=aes256'); | View examples |
| Decrypt symmetric text | SELECT extensions.pgp_sym_decrypt(ciphertext, 'passphrase')
FROM app.secrets
WHERE secret_id = 42; | View examples |
| Encrypt binary data | SELECT extensions.pgp_sym_encrypt_bytea(decode('00ff', 'hex'), 'passphrase'); | View examples |
| Encrypt with a public key | SELECT extensions.pgp_pub_encrypt('secret', extensions.dearmor(public_key))
FROM app.keyring
WHERE key_id = 3; | View examples |
| Decrypt with a private key | SELECT extensions.pgp_pub_decrypt(ciphertext, extensions.dearmor(private_key), 'key-passphrase'); | View examples |
| Generate random bytes | SELECT extensions.gen_random_bytes(32); | View examples |
| Generate a random UUID | SELECT pg_catalog.gen_random_uuid(); | View examples |
| Check builtin crypto policy | SHOW pgcrypto.builtin_crypto_enabled; | View examples |
Transactions and Security · 14 commands
PostgreSQL Row-Level Security Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Enable row security | ALTER TABLE app.invoice ENABLE ROW LEVEL SECURITY; | View examples |
| Apply RLS to the owner | ALTER TABLE app.invoice FORCE ROW LEVEL SECURITY; | View examples |
| Grant baseline privileges | GRANT
SELECT, INSERT,
UPDATE, DELETE
ON app.invoice TO app_runtime; | View examples |
| Filter visible rows | CREATE POLICY invoice_select
ON app.invoice FOR
SELECT TO app_runtime
USING (tenant_id = app.current_tenant_id()); | View examples |
| Validate inserted rows | CREATE POLICY invoice_insert
ON app.invoice FOR INSERT TO app_runtime
WITH CHECK (tenant_id = app.current_tenant_id()); | View examples |
| Constrain updates | CREATE POLICY inv_update
ON app.invoice FOR
UPDATE TO app_runtime
USING (tenant_id = app.current_tenant_id())
WITH CHECK (tenant_id = app.current_tenant_id()); | View examples |
| Add an allow path | CREATE POLICY support_read
ON app.invoice AS PERMISSIVE FOR
SELECT TO support_agent
USING (assigned_to = current_user); | View examples |
| Add a mandatory condition | CREATE POLICY no_legal_hold_delete
ON app.invoice AS RESTRICTIVE FOR DELETE TO app_runtime
USING (NOT legal_hold); | View examples |
| Remove RLS bypass | ALTER ROLE app_runtime NOBYPASSRLS; | View examples |
| Error instead of filtering | SET LOCAL row_security = off; | View examples |
| Scope tenant context | SET LOCAL app.tenant_id = '42'; | View examples |
| Read optional context | SELECT current_setting('app.tenant_id', true); | View examples |
| Inspect effective definitions | SELECT *
FROM pg_policies
WHERE schemaname = 'app' AND tablename = 'invoice'; | View examples |
| Check RLS for this caller | SELECT row_security_active('app.invoice'::regclass); | View examples |
Transactions and Security · 12 commands
PostgreSQL Schemas, Roles, and Privileges Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a schema | CREATE SCHEMA app AUTHORIZATION app_owner; | View examples |
| Set role search path | ALTER ROLE app_runtime
SET search_path = app, pg_catalog; | View examples |
| Create a login role | CREATE ROLE app_runtime LOGIN PASSWORD NULL; | View examples |
| Create a group role | CREATE ROLE app_readonly NOLOGIN; | View examples |
| Grant role membership | GRANT app_readonly TO analyst_user; | View examples |
| Allow schema lookup | GRANT USAGE ON SCHEMA app TO app_readonly; | View examples |
| Grant existing table reads | GRANT
SELECT
ON ALL TABLES IN SCHEMA app TO app_readonly; | View examples |
| Grant sequence use | GRANT USAGE,
SELECT
ON ALL SEQUENCES IN SCHEMA app TO app_runtime; | View examples |
| Revoke public schema creation | REVOKE CREATE ON SCHEMA public FROM PUBLIC; | View examples |
| Grant future table reads | ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT
SELECT
ON TABLES TO app_readonly; | View examples |
| Test a privilege | SELECT has_table_privilege('app_runtime', 'app.orders', 'SELECT'); | View examples |
| Transfer ownership | ALTER TABLE app.orders OWNER TO app_owner; | View examples |
Transactions and Security · 17 commands
PostgreSQL Transactions and Locking Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Start a transaction | BEGIN; | View examples |
| Commit changes | COMMIT; | View examples |
| Discard changes | ROLLBACK; | View examples |
| Create a savepoint | SAVEPOINT before_optional_step; | View examples |
| Roll back partway | ROLLBACK TO SAVEPOINT before_optional_step; | View examples |
| Release a savepoint | RELEASE SAVEPOINT before_optional_step; | View examples |
| Use read committed | BEGIN ISOLATION LEVEL READ COMMITTED; | View examples |
| Use repeatable read | BEGIN ISOLATION LEVEL REPEATABLE READ; | View examples |
| Use serializable | BEGIN ISOLATION LEVEL SERIALIZABLE; | View examples |
| Declare read-only work | BEGIN READ ONLY; | View examples |
| Limit statement duration locally | SET LOCAL statement_timeout = '5s'; | View examples |
| Lock selected rows | SELECT * FROM accounts WHERE id = 42 FOR UPDATE; | View examples |
| Use a weaker update lock | SELECT * FROM accounts WHERE id = 42 FOR NO KEY UPDATE; | View examples |
| Fail instead of waiting | SELECT * FROM jobs WHERE id = 7 FOR UPDATE NOWAIT; | View examples |
| Skip locked work | SELECT *
FROM jobs
WHERE status = 'ready'
ORDER BY id FOR
UPDATE SKIP LOCKED
LIMIT 1; | View examples |
| Lock a table explicitly | LOCK TABLE reports IN SHARE MODE; | View examples |
| Lock in stable order | SELECT *
FROM accounts
WHERE id IN (1, 2)
ORDER BY id FOR
UPDATE; | View examples |