Troubleshooting¶
pg_vault_tables must be loaded via shared_preload_libraries¶
The extension is not enabled on this server.
Add it to postgresql.conf and restart — a reload will not do:
If you see this on a standby, that replica is missing the setting. Replication has been working fine regardless; only reading vault tables is affected. See Replication and Standbys.
unrecognized parameter "permissions"¶
Same cause, different symptom. You are running CREATE TABLE ... WITH (permissions = ...) on a server where the extension is not loaded, so PostgreSQL does not recognise the option.
openssl/ssl.h: No such file or directory when building¶
Install the development headers:
sudo apt-get install libssl-dev libkrb5-dev # Debian, Ubuntu
sudo dnf install openssl-devel krb5-devel # Rocky, RHEL, Fedora
The same applies to gssapi/gssapi.h. See Installation.
a vault table must specify "permissions"¶
USING vault was given without a permissions list. A vault table with no permissions would refuse everything forever and could never be altered, so it is treated as a mistake.
"permissions" must include "insert" or "insertonce"¶
The permissions list grants no way to put a row into the table. None of update, delete, truncate or drop adds data — they only govern rows that are already there — so the table could never hold anything, and a vault table cannot be altered afterwards to correct it.
Add insert (or insertonce, for a table written exactly once).
A common way to arrive here is reaching for delete when what was wanted is retention. Rows are removed after their retention period whether or not delete is granted, so permissions = 'insert' with retention = 90 is the usual declaration for "kept for 90 days, then purged".
changing ... is not permitted on vault table "x"¶
One of the definition-level permissions is missing. The message names what was attempted and the detail line lists what the table actually grants:
| Message | Permission needed |
|---|---|
changing trigger state ... |
trigger |
changing row level security ... |
rls |
changing policies ... |
policy |
changing logged or unlogged ... |
log |
changing tablespace ... |
tablespace |
adding a column ... |
addcolumn |
changing a column default ... |
coldefault |
These are fixed at creation like every other permission, so a table that did not grant one cannot be given it later. The remedy is a new table with the declaration you want.
Note that rls and policy are separate: being allowed to switch row level security on does not allow the policies to be rewritten, or the reverse.
a vault table may not declare a column named "_$purge_ts"¶
That name belongs to the extension. On a table with retention it is the deadline column, and the extension creates it for you; on a table without retention it would be a lookalike that governs nothing and can never be made real, because retention cannot be added to an existing vault table.
Set retention and the column appears on its own. If you want a date of your own on the table, give it any other name.
Ordinary tables are unaffected — this rule applies only to tables using the vault access method.
column "x" is not updatable on vault table "y"¶
The table's update grant names the columns it may change, and this is not one of them. The detail line lists the ones that are.
Working as intended, and there is no way to widen the list — it is fixed at creation like every other permission. Note that a column added after creation can never be in it, so ADD COLUMN on such a table produces a column that cannot afterwards be updated.
If the value being written is the same as the value already there, this does not fire: what is refused is the value moving, not the column being named in SET.
direct catalogue modification of a vault table is not permitted¶
A transaction would have left a vault table's definition altered — its declared options, its access method, its storage or its columns. The transaction is refused and nothing persists.
This is deliberate and there is no setting to relax it. A vault table's definition is fixed when it is created, and that is checked independently of whatever statement was run.
Catalogue rows belonging to other objects are not affected. Ordinary DBA work on anything that is not a vault table is untouched, which is why the check compares the vault tables across the transaction rather than blocking catalogue writes outright.
Inside an explicit transaction the refusal arrives at the next statement, or at COMMIT if there is no next statement.
If a vault table's catalogue row is genuinely corrupt and needs manual repair, the route is to remove the extension from shared_preload_libraries, restart, repair, and restart back — loud and visible, and see Uninstalling for what else that implies.
storage parameters cannot be changed on vault table "x"¶
ALTER TABLE ... SET (...) and RESET (...) are refused on a vault table, and no permission changes that. It is a limitation rather than a policy: PostgreSQL validates a table's whole storage-parameter list as a unit, and this extension keeps permissions and retention in that same list without registering them with core, so the statement is refused before the extension is consulted — even for a parameter that has nothing to do with it.
Set them when the table is created, where they work normally:
CREATE TABLE t (id int) USING vault
WITH (permissions = 'insert', fillfactor = 70,
autovacuum_vacuum_scale_factor = 0.01);
Everything at column level is unaffected — SET STORAGE, SET COMPRESSION, SET STATISTICS and SET (n_distinct) all work, as do clustering, replica identity and anything to do with indexes.
The same mechanism is why no ALTER TABLE can reach permissions or retention, which is worth considerably more than the convenience it costs.
ALTER TABLE is not permitted on vault table "x"¶
The form attempted is one no permission grants — ADD COLUMN, DROP COLUMN, SET ACCESS METHOD, storage parameters and so on. Only the five definition-level permissions unlock anything, and only the specific forms listed above.
A statement mixing a permitted form with a refused one fails whole, so ALTER TABLE t OWNER TO x, ADD COLUMN y int produces this message and applies neither part.
"truncate" cannot be granted on a table that sets "retention"¶
They cannot be combined. TRUNCATE empties a table in one operation without consulting retention, so the pair would declare a retention period that a single statement could ignore.
Grant delete instead if rows must be removable before their deadline.
row in vault table "x" is still within its retention period¶
Working as intended. The row has not reached its deadline.
To delete only eligible rows:
To see when a row becomes eligible:
vault table "x" already holds its one permitted insert¶
The table grants insertonce and has been used. Nothing can reset it.
Note that a rolled-back insert does not consume it. If you are seeing this on what you believe is an empty table, check whether an earlier transaction actually committed.
drop is not permitted on vault table "x"¶
The table does not grant drop, so it cannot be removed by anybody. See Uninstalling for what your options actually are, and Migrating away if what you need is the data rather than the table.
Deadlines changed after copying a table to a new one¶
The copy did not have pg_vault_tables.restore_mode set, so every row was given a fresh deadline measured from the copy rather than keeping its own. There is no way to recover the original values from the new table — they were never written. If the source table still exists, redo the copy following Copying a Retained Table.
Restored rows all expire in the future¶
restore_mode was not set during the restore, so every row was given a fresh retention period.
There is no way to recalculate the original deadlines after the fact — the information is not in the database. Restore again from the same dump, this time with the setting on. See Backups and Restores.
A logical replication subscription has stalled¶
Check the subscriber's log for a violation record. The usual cause is the subscriber's copy of the table granting less than the publisher sends — for instance insert where the stream contains updates.
Both sides need the same permissions unless you specifically intend otherwise.
PostgreSQL will not start after removing the extension files¶
shared_preload_libraries still names a library that is no longer on disk.
Edit postgresql.conf to remove it and start again — or put the file back. See Uninstalling for the correct order.
Something else¶
Violation records in the server log are the first place to look. They record what was attempted, why it was refused, and who by. See Monitoring Violations.