Thread: strange permission error
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?
Mr Dash Four <mr.dash.four@googlemail.com> writes:
> 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 ;
> What is causing the above permission error? I have deliberately
> restricted user access to pg_catalog
You caused it yourself, then. Don't do that. (Or if you must,
it's your own responsibility to fix things when they break. But
preventing read access to pg_catalog seems pretty crippling.)
FWIW, it's probably the "'user: ' || u_name" expressions that result
in this specific failure.
regards, tom lane
> You caused it yourself, then. Don't do that. (Or if you must, > it's your own responsibility to fix things when they break. But > preventing read access to pg_catalog seems pretty crippling.) > I don't want arbitrary program to have access to the system catalogue and read willy-nilly, thanks. > FWIW, it's probably the "'user: ' || u_name" expressions that result > in this specific failure. > I found what is the cause of this - I had to add an explicit cast on all text expressions, like "user: "::text as well as u_name::text. That way the problem goes away, so it should.
On 10/01/12 1:20 AM, Mr Dash Four wrote: >> FWIW, it's probably the "'user: ' || u_name" expressions that result >> in this specific failure. > I found what is the cause of this - I had to add an explicit cast on > all text expressions, like "user: "::text as well as u_name::text. > That way the problem goes away, so it should. that doesn't make any sense at all. 'user: ' *is* text by default. I didn't notice you displaying your table definitions, but assuming u_name is TExT or VARCHAR(...) it should have worked without any explicit casts if you broke the permissions on the pg_catalog so badly that the SQL planner can't look up the data types of the fields of your own tables, well, thats just wrong. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
> 'user: ' *is* text by default. I didn't notice you displaying > your table definitions, but assuming u_name is TExT or VARCHAR(...) > it should have worked without any explicit casts u_name is a custom-defined type, consisting of user name (text/varchar), a number (longint), host name (text/varchar) and the IP address used by that user to log in, so yes, once I added explicit cast everything is back to normal and functions properly. > if you broke the permissions on the pg_catalog so badly that the SQL > planner can't look up the data types of the fields of your own tables, > well, thats just wrong. What's the alternative? I am not willing to let an arbitrary program using connection credentials, which have the ability to read my entire system catalogue. What happens if that connection is hijacked by an attacker? If they have access to that catalogue they would be able to take a snapshot of my entire database structure and exploit it - I can't allow that to happen and is the main reason I restricted access.
Mr Dash Four <mr.dash.four@googlemail.com> writes:
>> if you broke the permissions on the pg_catalog so badly that the SQL
>> planner can't look up the data types of the fields of your own tables,
>> well, thats just wrong.
> What's the alternative?
Perhaps more careful thought about your threat model?
> I am not willing to let an arbitrary program
> using connection credentials, which have the ability to read my entire
> system catalogue. What happens if that connection is hijacked by an
> attacker?
1. Use SSL connections, with appropriate certificate verification at
both ends.
2. If somebody manages to hijack your connection, you have much worse
problems than whether they can read your system catalogs. They can at
least copy, and probably modify, your user data. The catalogs are
unlikely to contain anything that's very interesting to an attacker
who knows enough about your operations to hijack a connection in the
first place.
regards, tom lane
> 2. If somebody manages to hijack your connection, you have much worse > problems than whether they can read your system catalogs. They can at > least copy, and probably modify, your user data. If I have restricted those permissions (i.e. access to specific schemas only, allowing specific operations - like INSERT only on just the tables needed for that particular db user) how would a user, who hijacked the connection, be able to "at least copy, and probably modify user data" then? > The catalogs are > unlikely to contain anything that's very interesting to an attacker > who knows enough about your operations to hijack a connection in the > first place. > They give a comprehensive information about the entire structure of the database - that, at least to me, is good-enough reason to restrict such an access.