1

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.

0
1

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.

1
  • Thanks to this query, I was finally able to identify a background script which was setting the relowner and relacl of the view to a role with permissions only within the schema staging_fiat. Thus, it's working now. Thank you! – Chickenmarkus Jan 15 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.