Can you make a simple view non-updatable?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Can you make a simple view non-updatable?

Ryan Murphy
Hello.

I enjoy using VIEWs.  Often my views are updatable, either automatically (due to being a simple 1-table view, or due to a TRIGGER).  Sometimes they are meant to be just read-only.

Is there any way to set a VIEW to be read-only -- specifically, can I do this for a view that is automatically updatable due to being simple?

The reason I want this:  It will help me encode into my schema the distinction between views that are supposed to behave like full-fledged "subtypes" of a larger relation and need to be updatable, vs those that are merely a report / literally just a "view".

Thanks!
Ryan
Reply | Threaded
Open this post in threaded view
|

Re: Can you make a simple view non-updatable?

Thiemo Kellner, NHC Barhufpflege
Zitat von Ryan Murphy <[hidden email]>:

> Is there any way to set a VIEW to be read-only -- specifically, can I do
> this for a view that is automatically updatable due to being simple?

Without saying anything about if this is directly possible, using  
different users with appropriate grants Comes to my mind, i. e. maybe  
it is time to overhaul the security concept.


Cheer, Thiemo

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.


Reply | Threaded
Open this post in threaded view
|

Re: Can you make a simple view non-updatable?

Ryan Murphy

maybe it is time to overhaul the security concept.

I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views.  However, if possible it would be nice to get an error message about the VIEW not being updatable, rather than a user access error, which could be misleading.

When I try to insert into a non-updatable VIEWs, I get this message:

ERROR:  cannot insert into view "test_view"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.

It would be great to see something like this when trying to insert into a simple VIEW that I had made non-updatable:

ERROR:  cannot insert into view "test_view2"
DETAIL:  This view has manually been made non-updatable.

Reply | Threaded
Open this post in threaded view
|

Re: Can you make a simple view non-updatable?

Ron-2


On 06/08/2018 04:17 AM, Ryan Murphy wrote:

maybe it is time to overhaul the security concept.

I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views.  However, if possible it would be nice to get an error message about the VIEW not being updatable, rather than a user access error, which could be misleading.

When I try to insert into a non-updatable VIEWs, I get this message:

ERROR:  cannot insert into view "test_view"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.

It would be great to see something like this when trying to insert into a simple VIEW that I had made non-updatable:

ERROR:  cannot insert into view "test_view2"
DETAIL:  This view has manually been made non-updatable.

Something like CREATE READ ONLY VIEW test_view2 AS SELECT ....

--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: Can you make a simple view non-updatable?

Thiemo Kellner, NHC Barhufpflege-2
In reply to this post by Ryan Murphy
Zitat von Ryan Murphy <[hidden email]>:

> I could see how I could revoke permissions from, say, all users that aren't
> superusers to INSERT or UPDATE certain views.  However, if possible it
> would be nice to get an error message about the VIEW not being updatable,
> rather than a user access error, which could be misleading.

I feel it would be the other way round. If you deny something that is  
technically possible, I would be puzzled to get an error about a  
technical impossibility. If you want to stop people updating a view,  
in my opion, it is very well to tell them they are not allowed to and  
not that it isn't technically possible. So there can start a  
discussion whether they are rightfully denied to update data therein,  
instead of first discussing why it is not technically possible to  
update to find out it IS technically possible but it was actually  
denied.

> When I try to insert into a non-updatable VIEWs, I get this message:
>
> ERROR:  cannot insert into view "test_view"
> DETAIL:  Views containing GROUP BY are not automatically updatable.
> HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT
> trigger or an unconditional ON INSERT DO INSTEAD rule.
>
> It would be great to see something like this when trying to insert into a
> simple VIEW that I had made non-updatable:
>
> ERROR:  cannot insert into view "test_view2"
> DETAIL:  This view has manually been made non-updatable.

You still could put a trigger on the views throwing according  
exceptions for specific users. I think I partly used that already but  
can't find the code at the moment.



--
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
Öffentlicher PGP-Schlüssel:  
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Reply | Threaded
Open this post in threaded view
|

Re: Can you make a simple view non-updatable?

Adrian Klaver-4
In reply to this post by Ryan Murphy
On 06/08/2018 01:38 AM, Ryan Murphy wrote:
> Hello.
>
> I enjoy using VIEWs.  Often my views are updatable, either automatically
> (due to being a simple 1-table view, or due to a TRIGGER).  Sometimes
> they are meant to be just read-only.
>
> Is there any way to set a VIEW to be read-only -- specifically, can I do
> this for a view that is automatically updatable due to being simple?

Using INSTEAD OF trigger?:

create view ct_vw as select * from container;

insert into ct_vw (c_id, cdesc, cell_per, c_size, c_units) values
('test', 'test container', 1, 2, 4);
INSERT 1836533 1


CREATE OR REPLACE FUNCTION public.vw_ro()
  RETURNS trigger
  LANGUAGE plpgsql
AS $function$
BEGIN
     RAISE NOTICE 'Read only view';
     RETURN NULL;
END;
$function$

CREATE TRIGGER ro_trg INSTEAD OF INSERT or UPDATE or DELETE ON ct_vw FOR
EACH ROW EXECUTE  procedure vw_ro();

insert into ct_vw (c_id, cdesc, cell_per, c_size, c_units) values
('test', 'test container', 1, 2, 4);
NOTICE:  Read only view
INSERT 0 0

update ct_vw set cell_per = 100 where c_id = '200PT';
NOTICE:  Read only view
UPDATE 0

delete from ct_vw where c_id = '200PT';
NOTICE:  Read only view
DELETE 0


>
> The reason I want this:  It will help me encode into my schema the
> distinction between views that are supposed to behave like full-fledged
> "subtypes" of a larger relation and need to be updatable, vs those that
> are merely a report / literally just a "view".
>
> Thanks!
> Ryan


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Can you make a simple view non-updatable?

Ryan Murphy
On Fri, Jun 8, 2018 at 8:27 AM, Adrian Klaver <[hidden email]> wrote:

Using INSTEAD OF trigger?:

Yep, that's one way.
 

Previous Thread Next Thread