In my database I have restricted access to a particular user
(non-superuser), which is used when a cron job passes a series of sql
script files for execution via psql. During one such statement (below) I
get the following set of error:
ERROR: permission denied for schema pg_catalog
CONTEXT: SQL function "textanycat" during inlining
STATEMENT: COPY ( SELECT v FROM dandy.audit_v ) TO STDOUT ;
This is produced as a result of the following command in my sql script:
\copy (SELECT v FROM dandy.audit_v ) TO '/opt/pg/audits/violations.txt';
The audit_v itself contains a long series of statements like this one:
CREATE OR REPLACE VIEW dandy.audit_v AS
SELECT q.v FROM (
SELECT 1 AS c, 'Security violations (local):' AS v
UNION SELECT 2, 'user: ' || u_name FROM dandy.audit_dump where uid in
(SELECT uid FROM dandy.a_users WHERE cg='local' AND NOT permanent)
--[...]
UNION SELECT 15 AS c, 'Security violations (other):' AS v
UNION SELECT 16, 'user: ' || u_name FROM dandy.audit_dump where uid
NOTNULL AND uid not in (SELECT uid FROM dandy.a_users WHERE NOT permanent)
UNION SELECT 17 AS c, 'Security violations (unknown):' AS v
UNION SELECT 18, 'user: ' || u_name FROM dandy.audit_dump where uid ISNULL
) q
ORDER BY q.c ASC;
What is causing the above permission error? I have deliberately
restricted user access to pg_catalog and have a very similar \copy
statement prior to the above which executes without any permission
errors, which suggests that something in the view above isn't quite
right. Any ideas?