agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jayadevan M <maymala.jayadevan@gmail.com>
Cc: intmail01@gmail.com
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Hide some tables
Date: Mon, 09 Aug 2021 09:45:57 -0400
Message-ID: <3360703.1628516757@sss.pgh.pa.us> (raw)
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>

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

view thread (4+ messages)  latest in thread

Message-ID: <3360703.1628516757@sss.pgh.pa.us>
Permalink:  ../3360703.1628516757@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3360703.1628516757@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, maymala.jayadevan@gmail.com, intmail01@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Hide some tables
  In-Reply-To: <3360703.1628516757@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox