PostgreSQL – Permission Denied for View Beyond Its Own Schema

permissionspostgresqlschemaview

There are two schemes rohdaten_fiat and staging_fiat. I have created a view in the latter one based on the first one.

CREATE VIEW staging_fiat.udz_odx_dtcs AS
SELECT * FROM rohdaten_fiat.udz_odx_relational_dtcs;

While keeping the database connection alive I can access the view, e. g.

SELECT * FROM staging_fiat.udz_odx_dtcs;

It runs under the following parameters:

SELECT current_user, current_setting('search_path'::text) AS search_path
     , relowner::regrole, relnamespace::regnamespace, relname, relacl
     , pg_get_viewdef(c.oid) AS view_definition
FROM   pg_class c
WHERE  relname = 'udz_odx_dtcs' OR relname = 'udz_odx_relational_dtcs';

See: https://pastebin.com/Hn7jAUat

However, after closing the connection and reconnect to the database, I can not:

SELECT * FROM staging_fiat.udz_odx_dtcs;

ERROR:  permission denied for relation view

The environmental parameters are the same (see https://pastebin.com/Hn7jAUat). Curiously, I can execute the query of the view manually:

SELECT * FROM rohdaten_fiat.udz_odx_relational_dtcs;

Only the view does not work. Where get the permissions lost? And why?

It's PostgreSQL 10.12. The connection including the search_path is absolutely identical.

Best Answer

To diagnose, inspect the output of this query before and after reconnecting in your question:

SELECT current_user, current_setting('search_path'::text) AS search_path
     , relowner::regrole, relnamespace::regnamespace, relname, relacl
     , pg_get_viewdef(c.oid) AS view_definition
FROM   pg_class c
WHERE  relname = 'view'  -- actual view name
OR    (relname = 'table' AND relnamespace = 'schema1');   -- actual table & schema name

You should get two or more rows each time. I omitted the schema of the view on purpose to see whether there might be others with the same name.

Oh, and make sure, your transaction is committed. Read the manual here.

This is not a direct answer, but it should enable you to get your answer.

Related Question