pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

audit table with permissions
2+ messages / 2 participants
[nested] [flat]

* audit table with permissions
@ 2018-02-28 21:12  chris <chrisk@pgsqlrocket.com>
  0 siblings, 1 reply; 2+ messages in thread

From: chris @ 2018-02-28 21:12 UTC (permalink / raw)
  To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>

I created functions and a trigger to audit tables on my database but I 
have a problem with permissions on the functions and function schema I 
created. I can grant permissions to the current user of the original 
table being audited but I need a way to automate grants and revokes made 
to the original table so that any time those change in the future I 
don't have to worry about updating permissions or leaving a hole in 
security by granting all users full permissions to the functions and 
function schema.

 From my understanding event triggers can't be set the grant/revoke only 
create, alter or drop.

Any input would really help me, I'm very fresh to writing functions and 
triggers.

Thanks,

Chris





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: audit table with permissions
@ 2018-02-28 22:42  Bear Giles <bgiles@coyotesong.com>
  parent: chris <chrisk@pgsqlrocket.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Bear Giles @ 2018-02-28 22:42 UTC (permalink / raw)
  To: chris <chrisk@pgsqlrocket.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>

On Wed, Feb 28, 2018 at 2:12 PM, chris <chrisk@pgsqlrocket.com> wrote:

> I created functions and a trigger to audit tables on my database but I
> have a problem with permissions on the functions and function schema I
> created. I can grant permissions to the current user of the original table
> being audited but I need a way to automate grants and revokes made to the
> original table so that any time those change in the future I don't have to
> worry about updating permissions or leaving a hole in security by granting
> all users full permissions to the functions and function schema.
>
> From my understanding event triggers can't be set the grant/revoke only
> create, alter or drop.
>
> Any input would really help me, I'm very fresh to writing functions and
> triggers.
>
> Thanks,
>
> Chris
>

​What about 'create function ... security definer'? That will run the
function with the permissions of the person who defined the function, not
the person who executes the function ('security invoker'). You still have
to worry about changing permissions on the table but only have to worry
about how they impact the user that created the function.​

Bear

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2018-02-28 22:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-28 21:12 audit table with permissions chris <chrisk@pgsqlrocket.com>
2018-02-28 22:42 ` Bear Giles <bgiles@coyotesong.com>

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