strange permission error

From: Mr Dash Four <mr(dot)dash(dot)four(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: strange permission error
Date: 2012-09-30 12:53:57
Message-ID: 506840E5.2090002@googlemail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message bryanck 2012-09-30 13:28:23 Re: Postgresql 9.2 OOM
Previous Message Ondrej Ivanič 2012-09-30 11:24:07 Re: transitive pruning optimization on the right side of a join for partition tables