agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHide some tables
4+ messages / 3 participants
[nested] [flat]
* Hide some tables
@ 2021-08-09 12:57 intmail01@gmail.com <intmail01@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: intmail01@gmail.com @ 2021-08-09 12:57 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hi,
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.
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hide some tables
@ 2021-08-09 13:39 Jayadevan M <maymala.jayadevan@gmail.com>
parent: intmail01@gmail.com <intmail01@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jayadevan M @ 2021-08-09 13:39 UTC (permalink / raw)
To: intmail01@gmail.com; +Cc: pgsql-sql@lists.postgresql.org
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?
Regards,
Jayadevan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hide some tables
@ 2021-08-09 13:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jayadevan M <maymala.jayadevan@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-08-09 13:45 UTC (permalink / raw)
To: Jayadevan M <maymala.jayadevan@gmail.com>; +Cc: intmail01@gmail.com; pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hide some tables
@ 2021-08-09 17:48 intmail01@gmail.com <intmail01@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: intmail01@gmail.com @ 2021-08-09 17:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Jayadevan M <maymala.jayadevan@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
Le 09/08/2021 à 13:45, Tom Lane a écrit :
> 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
It works. Thank you
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-08-09 17:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-09 12:57 Hide some tables intmail01@gmail.com <intmail01@gmail.com>
2021-08-09 13:39 ` Jayadevan M <maymala.jayadevan@gmail.com>
2021-08-09 13:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-09 17:48 ` intmail01@gmail.com <intmail01@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox