Transactions and security

A database is shared: many people and programs read and change it at once, and not all of them should see everything. Transactions keep concurrent changes correct; roles and privileges decide who may do what.

Transactions

A transaction groups statements so they succeed or fail together: BEGIN, the statements, then COMMIT to keep them or ROLLBACK to undo them. Moving a reserve from one claim to another is two updates that must happen together or not at all. The guarantees are called ACID:

• Atomicity: all of the transaction happens, or none of it.
• Consistency: every constraint holds when it commits.
• Isolation: concurrent transactions do not see each other's unfinished work.
• Durability: once committed, a change survives a crash.

PostgreSQL implements isolation with MVCC (multi-version concurrency control): an update writes a new version of the row, so readers keep seeing the old one until the writer commits, and readers never block writers. The default isolation level, read committed, lets each statement see what was committed before it began; repeatable read and serializable give the whole transaction one consistent view.

Roles and privileges

A role is a user or a group. Privileges on objects are granted to roles: SELECT, INSERT, UPDATE, DELETE on tables (or on single columns), USAGE on schemas, EXECUTE on functions. A role can be a member of another and inherit its privileges, so rights are granted to group roles, such as reporting, and people join the group.

Row-level security adds a filter per role to a table: once enabled, a role sees only the rows a policy allows, and nothing if no policy applies. The table's owner and superusers bypass it unless the table is set to force ROW LEVEL SECURITY.

Backups

SELECT rolname, rolsuper, rolcanlogin
FROM pg_roles
WHERE rolname NOT LIKE 'pg\_%'
ORDER BY rolname;