From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jayadevan M <maymala.jayadevan@gmail.com>
cc: intmail01@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Hide some tables
In-reply-to: 
 <CAFS1N4j8tEDap7sEeZDsQPSCbrFZwjbRYmyTAYPBA+yMP+2_CA@mail.gmail.com>
References: <2a4f55f7-b8fb-4937-9136-c7e35de63f79@gmail.com>
 <CAFS1N4j8tEDap7sEeZDsQPSCbrFZwjbRYmyTAYPBA+yMP+2_CA@mail.gmail.com>
Comments: In-reply-to Jayadevan M <maymala.jayadevan@gmail.com>
	message dated "Mon, 09 Aug 2021 19:09:47 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3360702.1628516757.1@sss.pgh.pa.us>
Date: Mon, 09 Aug 2021 09:45:57 -0400
Message-ID: <3360703.1628516757@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3360703.1628516757%40sss.pgh.pa.us>
Precedence: bulk

Jayadevan M <maymala.jayadevan@gmail.com> writes:
> There are some operators who enters data in my database with just one
>> table. Others tables are updated by triggers, these tables contains
>> result of calculation. How to do to hide these tables because I do not
>> want that operators read them ? On help documentation it is said that I
>> can not block SELECT privilege because it is required for UPDATE.
>> 
> Can you move them to a different schema, and manage using search_path?

The thing to use is privileges.  Make the tables-that-should-be-hidden
owned by a different SQL role, and don't give select privilege on them
to the data entry role.  The triggers can be (or call) SECURITY DEFINER
functions owned by the first role, giving them access that the data entry
role does not have.

			regards, tom lane