Thread: Inserts restricted to a trigger
Is there are way to restrict direct access to a table for inserts but allow a trigger on another table to perform an insert for that user?
I'm trying to implement an audit table without allowing user tampering with the audit information.
Thanks in advance,
Miles Elam
On 6/17/19 4:54 PM, Miles Elam wrote: > Is there are way to restrict direct access to a table for inserts but > allow a trigger on another table to perform an insert for that user? > > I'm trying to implement an audit table without allowing user tampering > with the audit information. Would the below not work?: CREATE the table as superuser or other privileged user Have trigger function run as above user(use SECURITY DEFINER) > > > Thanks in advance, > > Miles Elam -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver wrote: > On 6/17/19 4:54 PM, Miles Elam wrote: > > Is there are way to restrict direct access to a table for inserts but > > allow a trigger on another table to perform an insert for that user? > > > > I'm trying to implement an audit table without allowing user tampering > > with the audit information. > > Would the below not work?: > CREATE the table as superuser or other privileged user > Have trigger function run as above user(use SECURITY DEFINER) and make sure not to give any other users insert/update/delete permissions on the audit table. > > Thanks in advance, > > > > Miles Elam > > -- > Adrian Klaver > adrian.klaver@aklaver.com
That seems straightforward. Unfortunately I also want to know the user/role that performed the operation. If I use SECURITY DEFINER, I get the superuser account back from CURRENT_USER, not the actual user.
Sorry, should have included that in the original email. How do I restrict access while still retaining info about the current user/role?
On Mon, Jun 17, 2019 at 5:47 PM <raf@raf.org> wrote:
Adrian Klaver wrote:
> On 6/17/19 4:54 PM, Miles Elam wrote:
> > Is there are way to restrict direct access to a table for inserts but
> > allow a trigger on another table to perform an insert for that user?
> >
> > I'm trying to implement an audit table without allowing user tampering
> > with the audit information.
>
> Would the below not work?:
> CREATE the table as superuser or other privileged user
> Have trigger function run as above user(use SECURITY DEFINER)
and make sure not to give any other users insert/update/delete
permissions on the audit table.
> > Thanks in advance,
> >
> > Miles Elam
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
Have you tried session_user?
security definer language sql as $$
select current_user::text, session_user::text;
$$;
Then log in as different user and:
=> select (xx()).*;
cur | sess
----------+-------
postgres | write
cur | sess
----------+-------
postgres | write
On Tue, Jun 18, 2019 at 6:30 PM Miles Elam <miles.elam@productops.com> wrote:
That seems straightforward. Unfortunately I also want to know the user/role that performed the operation. If I use SECURITY DEFINER, I get the superuser account back from CURRENT_USER, not the actual user.Sorry, should have included that in the original email. How do I restrict access while still retaining info about the current user/role?On Mon, Jun 17, 2019 at 5:47 PM <raf@raf.org> wrote:Adrian Klaver wrote:
> On 6/17/19 4:54 PM, Miles Elam wrote:
> > Is there are way to restrict direct access to a table for inserts but
> > allow a trigger on another table to perform an insert for that user?
> >
> > I'm trying to implement an audit table without allowing user tampering
> > with the audit information.
>
> Would the below not work?:
> CREATE the table as superuser or other privileged user
> Have trigger function run as above user(use SECURITY DEFINER)
and make sure not to give any other users insert/update/delete
permissions on the audit table.
> > Thanks in advance,
> >
> > Miles Elam
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
Thanks for the suggestion. Unfortunately we only have a single login role (it's a web app) and then we SET ROLE according to the contents of a JSON Web Token. So we end up with SESSION_USER as the logged in user and the active role as CURRENT_USER.
It may be that we're just stuck with a gap and need to just try and keep track of our mutation points, such as limit what is accessible through REST or GraphQL, and there is no way to fundamentally lock this down in Postgres. I was checking the mailing list to see if I'd missed anything.
On Tue, Jun 18, 2019 at 9:47 AM Torsten Förtsch <tfoertsch123@gmail.com> wrote:
Have you tried session_user?create function xx() returns table (cur text, sess text)security definer language sql as $$select current_user::text, session_user::text;$$;Then log in as different user and:=> select (xx()).*;
cur | sess
----------+-------
postgres | writeOn Tue, Jun 18, 2019 at 6:30 PM Miles Elam <miles.elam@productops.com> wrote:That seems straightforward. Unfortunately I also want to know the user/role that performed the operation. If I use SECURITY DEFINER, I get the superuser account back from CURRENT_USER, not the actual user.Sorry, should have included that in the original email. How do I restrict access while still retaining info about the current user/role?On Mon, Jun 17, 2019 at 5:47 PM <raf@raf.org> wrote:Adrian Klaver wrote:
> On 6/17/19 4:54 PM, Miles Elam wrote:
> > Is there are way to restrict direct access to a table for inserts but
> > allow a trigger on another table to perform an insert for that user?
> >
> > I'm trying to implement an audit table without allowing user tampering
> > with the audit information.
>
> Would the below not work?:
> CREATE the table as superuser or other privileged user
> Have trigger function run as above user(use SECURITY DEFINER)
and make sure not to give any other users insert/update/delete
permissions on the audit table.
> > Thanks in advance,
> >
> > Miles Elam
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
On 6/18/19 10:14 AM, Miles Elam wrote: > Thanks for the suggestion. Unfortunately we only have a single login > role (it's a web app) and then we SET ROLE according to the contents of > a JSON Web Token. So we end up with SESSION_USER as the logged in user > and the active role as CURRENT_USER. Have not tried it but nested function?: 1) Outer function runs as normal user and grabs the CURRENT_USER. This is passed into 2) 2) Audit function that runs with SECURITY DEFINER. Other option is to record the CURRENT_USER in the table the trigger is on and just pass that to the audit function. > > It may be that we're just stuck with a gap and need to just try and keep > track of our mutation points, such as limit what is accessible through > REST or GraphQL, and there is no way to fundamentally lock this down in > Postgres. I was checking the mailing list to see if I'd missed anything. > > > On Tue, Jun 18, 2019 at 9:47 AM Torsten Förtsch <tfoertsch123@gmail.com > <mailto:tfoertsch123@gmail.com>> wrote: > > Have you tried session_user? > > create function xx() returns table (cur text, sess text) > security definer language sql as $$ > select current_user::text, session_user::text; > $$; > > Then log in as different user and: > > => select (xx()).*; > cur | sess > ----------+------- > postgres | write > > > On Tue, Jun 18, 2019 at 6:30 PM Miles Elam > <miles.elam@productops.com <mailto:miles.elam@productops.com>> wrote: > > That seems straightforward. Unfortunately I also want to know > the user/role that performed the operation. If I use SECURITY > DEFINER, I get the superuser account back from CURRENT_USER, not > the actual user. > > Sorry, should have included that in the original email. How do I > restrict access while still retaining info about the current > user/role? > > > On Mon, Jun 17, 2019 at 5:47 PM <raf@raf.org > <mailto:raf@raf.org>> wrote: > > Adrian Klaver wrote: > > > On 6/17/19 4:54 PM, Miles Elam wrote: > > > Is there are way to restrict direct access to a table > for inserts but > > > allow a trigger on another table to perform an insert > for that user? > > > > > > I'm trying to implement an audit table without allowing > user tampering > > > with the audit information. > > > > Would the below not work?: > > CREATE the table as superuser or other privileged user > > Have trigger function run as above user(use SECURITY DEFINER) > > and make sure not to give any other users insert/update/delete > permissions on the audit table. > > > > Thanks in advance, > > > > > > Miles Elam > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, thanks for responding.
How would I restrict access to the SECURITY DEFINER function? If it can be called by the trigger, it can be called by the user as well I would think. Same issue as access to the table itself only now with a superuser intermediary, right?
On Tue, Jun 18, 2019 at 6:20 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/18/19 10:14 AM, Miles Elam wrote:
> Thanks for the suggestion. Unfortunately we only have a single login
> role (it's a web app) and then we SET ROLE according to the contents of
> a JSON Web Token. So we end up with SESSION_USER as the logged in user
> and the active role as CURRENT_USER.
Have not tried it but nested function?:
1) Outer function runs as normal user and grabs the CURRENT_USER. This
is passed into 2)
2) Audit function that runs with SECURITY DEFINER.
Other option is to record the CURRENT_USER in the table the trigger is
on and just pass that to the audit function.
>
> It may be that we're just stuck with a gap and need to just try and keep
> track of our mutation points, such as limit what is accessible through
> REST or GraphQL, and there is no way to fundamentally lock this down in
> Postgres. I was checking the mailing list to see if I'd missed anything.
>
>
> On Tue, Jun 18, 2019 at 9:47 AM Torsten Förtsch <tfoertsch123@gmail.com
> <mailto:tfoertsch123@gmail.com>> wrote:
>
> Have you tried session_user?
>
> create function xx() returns table (cur text, sess text)
> security definer language sql as $$
> select current_user::text, session_user::text;
> $$;
>
> Then log in as different user and:
>
> => select (xx()).*;
> cur | sess
> ----------+-------
> postgres | write
>
>
> On Tue, Jun 18, 2019 at 6:30 PM Miles Elam
> <miles.elam@productops.com <mailto:miles.elam@productops.com>> wrote:
>
> That seems straightforward. Unfortunately I also want to know
> the user/role that performed the operation. If I use SECURITY
> DEFINER, I get the superuser account back from CURRENT_USER, not
> the actual user.
>
> Sorry, should have included that in the original email. How do I
> restrict access while still retaining info about the current
> user/role?
>
>
> On Mon, Jun 17, 2019 at 5:47 PM <raf@raf.org
> <mailto:raf@raf.org>> wrote:
>
> Adrian Klaver wrote:
>
> > On 6/17/19 4:54 PM, Miles Elam wrote:
> > > Is there are way to restrict direct access to a table
> for inserts but
> > > allow a trigger on another table to perform an insert
> for that user?
> > >
> > > I'm trying to implement an audit table without allowing
> user tampering
> > > with the audit information.
> >
> > Would the below not work?:
> > CREATE the table as superuser or other privileged user
> > Have trigger function run as above user(use SECURITY DEFINER)
>
> and make sure not to give any other users insert/update/delete
> permissions on the audit table.
>
> > > Thanks in advance,
> > >
> > > Miles Elam
> >
> > --
> > Adrian Klaver
> > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 6/19/19 3:07 PM, Miles Elam wrote: > Hi Adrian, thanks for responding. > > How would I restrict access to the SECURITY DEFINER function? If it can > be called by the trigger, it can be called by the user as well I would > think. Same issue as access to the table itself only now with a > superuser intermediary, right? We may need to back this up: 1) What is the audit table recording? 2) How much access do your users have to the database itself, outside the Web app? 3) What access do users have in the Web app? > > > On Tue, Jun 18, 2019 at 6:20 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 6/18/19 10:14 AM, Miles Elam wrote: > > Thanks for the suggestion. Unfortunately we only have a single login > > role (it's a web app) and then we SET ROLE according to the > contents of > > a JSON Web Token. So we end up with SESSION_USER as the logged in > user > > and the active role as CURRENT_USER. > > Have not tried it but nested function?: > > 1) Outer function runs as normal user and grabs the CURRENT_USER. This > is passed into 2) > > 2) Audit function that runs with SECURITY DEFINER. > > Other option is to record the CURRENT_USER in the table the trigger is > on and just pass that to the audit function. > > > > > It may be that we're just stuck with a gap and need to just try > and keep > > track of our mutation points, such as limit what is accessible > through > > REST or GraphQL, and there is no way to fundamentally lock this > down in > > Postgres. I was checking the mailing list to see if I'd missed > anything. > > > > > > On Tue, Jun 18, 2019 at 9:47 AM Torsten Förtsch > <tfoertsch123@gmail.com <mailto:tfoertsch123@gmail.com> > > <mailto:tfoertsch123@gmail.com <mailto:tfoertsch123@gmail.com>>> > wrote: > > > > Have you tried session_user? > > > > create function xx() returns table (cur text, sess text) > > security definer language sql as $$ > > select current_user::text, session_user::text; > > $$; > > > > Then log in as different user and: > > > > => select (xx()).*; > > cur | sess > > ----------+------- > > postgres | write > > > > > > On Tue, Jun 18, 2019 at 6:30 PM Miles Elam > > <miles.elam@productops.com <mailto:miles.elam@productops.com> > <mailto:miles.elam@productops.com > <mailto:miles.elam@productops.com>>> wrote: > > > > That seems straightforward. Unfortunately I also want to know > > the user/role that performed the operation. If I use SECURITY > > DEFINER, I get the superuser account back from > CURRENT_USER, not > > the actual user. > > > > Sorry, should have included that in the original email. > How do I > > restrict access while still retaining info about the current > > user/role? > > > > > > On Mon, Jun 17, 2019 at 5:47 PM <raf@raf.org > <mailto:raf@raf.org> > > <mailto:raf@raf.org <mailto:raf@raf.org>>> wrote: > > > > Adrian Klaver wrote: > > > > > On 6/17/19 4:54 PM, Miles Elam wrote: > > > > Is there are way to restrict direct access to a > table > > for inserts but > > > > allow a trigger on another table to perform an > insert > > for that user? > > > > > > > > I'm trying to implement an audit table without > allowing > > user tampering > > > > with the audit information. > > > > > > Would the below not work?: > > > CREATE the table as superuser or other privileged user > > > Have trigger function run as above user(use > SECURITY DEFINER) > > > > and make sure not to give any other users > insert/update/delete > > permissions on the audit table. > > > > > > Thanks in advance, > > > > > > > > Miles Elam > > > > > > -- > > > Adrian Klaver > > > adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
On 6/19/19 3:07 PM, Miles Elam wrote: > Hi Adrian, thanks for responding. > > How would I restrict access to the SECURITY DEFINER function? If it can > be called by the trigger, it can be called by the user as well I would > think. Same issue as access to the table itself only now with a > superuser intermediary, right? > Should have also mentioned, if you are not adverse to a third party solution there is PGAudit: https://www.pgaudit.org/ -- Adrian Klaver adrian.klaver@aklaver.com
Thanks for the reply, Adrian.
2. Only thorough a web UI via an API service.
3. Should be limited to web app, but the data scientists may need direct access in the near future.
PGAudit does not cover our use case. We are making a temporal table system since PostgreSQL does not support one natively. For example: "What would this query have returned yesterday at 4:27pm PT?" Access is as expected for inserts but updates and deletes are logged to history tables. We cannot use 3rd party extensions because we are on AWS managed databases. We are following the model detailed here (https://wiki.postgresql.org/wiki/SQL2011Temporal) with some modifications.
Given the model listed in the link, it's not clear how we can prevent user tampering with history inserts. (History updates and deletes are already REVOKE restricted.) Since we are going through an API server via REST and/or GraphQL, the possibility is very unlikely, but we would prefer a defense in depth approach in case an oversight somehow allowed arbitrary query access to the database with the web user. For the most part, we're fairly well locked down, but I just can't quite see how to restrict aforementioned query access from inserting to the history in an ad-hoc manner rather than the trigger-based predetermined insert pattern.
On Thu, Jun 20, 2019 at 8:01 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/19/19 3:07 PM, Miles Elam wrote:
> Hi Adrian, thanks for responding.
>
> How would I restrict access to the SECURITY DEFINER function? If it can
> be called by the trigger, it can be called by the user as well I would
> think. Same issue as access to the table itself only now with a
> superuser intermediary, right?
>
Should have also mentioned, if you are not adverse to a third party
solution there is PGAudit:
https://www.pgaudit.org/
--
Adrian Klaver
adrian.klaver@aklaver.com
On 6/20/19 3:30 PM, Miles Elam wrote: > Thanks for the reply, Adrian. > > 1. The audit tables (plural) are recording the historical data for a > table, ie., updates and deletes. All the same data as found in a given > table along with the role that performed the operation, the transaction > id, and the time range where this data was in active use. > > 2. Only thorough a web UI via an API service. > > 3. Should be limited to web app, but the data scientists may need direct > access in the near future. > > PGAudit does not cover our use case. We are making a temporal table > system since PostgreSQL does not support one natively. For example: > "What would this query have returned yesterday at 4:27pm PT?" Access is > as expected for inserts but updates and deletes are logged to history > tables. We cannot use 3rd party extensions because we are on AWS managed > databases. We are following the model detailed here > (https://wiki.postgresql.org/wiki/SQL2011Temporal) with some modifications. > > Given the model listed in the link, it's not clear how we can prevent > user tampering with history inserts. (History updates and deletes are > already REVOKE restricted.) Since we are going through an API server via > REST and/or GraphQL, the possibility is very unlikely, but we would > prefer a defense in depth approach in case an oversight somehow allowed > arbitrary query access to the database with the web user. For the most > part, we're fairly well locked down, but I just can't quite see how to > restrict aforementioned query access from inserting to the history in an > ad-hoc manner rather than the trigger-based predetermined insert pattern. Some draft ideas: 1) A session table that among other things records the Web Token/user combination. Then in the trigger(s) that INSERT into the history table check that the user has a valid current token. 2) In the inner function use PG_CONTEXT: https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-CALL-STACK to determine whether the inner SECURITY DEFINER function is being called directly or through the outer trigger function. > > > > On Thu, Jun 20, 2019 at 8:01 AM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 6/19/19 3:07 PM, Miles Elam wrote: > > Hi Adrian, thanks for responding. > > > > How would I restrict access to the SECURITY DEFINER function? If > it can > > be called by the trigger, it can be called by the user as well I > would > > think. Same issue as access to the table itself only now with a > > superuser intermediary, right? > > > > Should have also mentioned, if you are not adverse to a third party > solution there is PGAudit: > > https://www.pgaudit.org/ > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com