Horton

Thread

  1. Create trigger on after logon on schema

    aditya desai <admad123@gmail.com> — 2021-10-06T15:22:45Z

    Hi,
Is there any way to write below Oracle trigger in postgres?

CREATE TRIGGER T1 after logon on schema for each row
execute procedure p1;

Regards,
Aditya.

  2. Re: Create trigger on after logon on schema

    hubert depesz lubaczewski <depesz@depesz.com> — 2021-10-06T15:44:44Z

    On Wed, Oct 06, 2021 at 08:52:45PM +0530, aditya desai wrote:
> Hi,
> Is there any way to write below Oracle trigger in postgres?
> 
> CREATE TRIGGER T1 after logon on schema for each row
> execute procedure p1;

What did it do?

Please note that not everyone knowing Pg knows Oracle.

I can imagine that trigger after logon means that p1 procedure will be
called after user successfully logs in, but what does the "for each row"
part mean?

In PG, there are no login triggers. And, in any case, noone logs to
"schema".

Best regards,

depesz

  3. Re: Create trigger on after logon on schema

    aditya desai <admad123@gmail.com> — 2021-10-06T16:09:28Z

    Hi Depesz,
Sorry , I didn't mention it clearly. We are using these triggers for audit
purposes.

Triggers calls a procedure to insert username,machine.IPAddress into
LogonTime in an audit table.
We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP on
schema that will audit the user details in the audit table.

Any alternative solution in Postgres is welcome.

Thanks in advance.

Regards,
Aditya.



On Wed, Oct 6, 2021 at 9:14 PM hubert depesz lubaczewski <depesz@depesz.com>
wrote:

> On Wed, Oct 06, 2021 at 08:52:45PM +0530, aditya desai wrote:
> > Hi,
> > Is there any way to write below Oracle trigger in postgres?
> >
> > CREATE TRIGGER T1 after logon on schema for each row
> > execute procedure p1;
>
> What did it do?
>
> Please note that not everyone knowing Pg knows Oracle.
>
> I can imagine that trigger after logon means that p1 procedure will be
> called after user successfully logs in, but what does the "for each row"
> part mean?
>
> In PG, there are no login triggers. And, in any case, noone logs to
> "schema".
>
> Best regards,
>
> depesz
>
>

  4. Re: Create trigger on after logon on schema

    Steve Midgley <science@misuse.org> — 2021-10-06T18:16:04Z

    On Wed, Oct 6, 2021 at 9:10 AM aditya desai <admad123@gmail.com> wrote:

> Hi Depesz,
> Sorry , I didn't mention it clearly. We are using these triggers for audit
> purposes.
>
> Triggers calls a procedure to insert username,machine.IPAddress into
> LogonTime in an audit table.
> We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP on
> schema that will audit the user details in the audit table.
>
> Any alternative solution in Postgres is welcome.
>
>
>

In my experience, this is solved in a middle tier that brokers access to
Postgres. So the middle tier would handle login (even if brokering the
credentials back to Pg to achieve the login). And then the middle tier can
execute the logic to do login auditing along the lines you want.

That said, I could imagine logging the kind of information you describe not
on login, but on certain sql executions (DDL). I think you could set a
trigger to record IP and other system stats related to each of those
actions (this isn't possible to my knowledge for select statements). This
would be finer grained than logging login data, but would maybe accomplish
the same thing? Here's the kind of system info you can collect and log:
https://www.postgresql.org/docs/current/functions-info.html

I think it's probably more realistic to use the former method: log/audit
everything in a middle tier that brokers access to Postgres.

Steve

  5. Re: Create trigger on after logon on schema

    Erik Brandsberg <erik@heimdalldata.com> — 2021-10-06T18:26:58Z

    Following up to Steve Midgley's comment, Heimdall Data provides a proxy
that can provide this level of auditing, and even trigger insertion of read
queries into an audit table if you want.  Disclaimer, I'm the CTO of
Heimdall Data...

On Wed, Oct 6, 2021 at 12:09 PM aditya desai <admad123@gmail.com> wrote:

> Hi Depesz,
> Sorry , I didn't mention it clearly. We are using these triggers for audit
> purposes.
>
> Triggers calls a procedure to insert username,machine.IPAddress into
> LogonTime in an audit table.
> We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP on
> schema that will audit the user details in the audit table.
>
> Any alternative solution in Postgres is welcome.
>
> Thanks in advance.
>
> Regards,
> Aditya.
>
>
>
> On Wed, Oct 6, 2021 at 9:14 PM hubert depesz lubaczewski <
> depesz@depesz.com> wrote:
>
>> On Wed, Oct 06, 2021 at 08:52:45PM +0530, aditya desai wrote:
>> > Hi,
>> > Is there any way to write below Oracle trigger in postgres?
>> >
>> > CREATE TRIGGER T1 after logon on schema for each row
>> > execute procedure p1;
>>
>> What did it do?
>>
>> Please note that not everyone knowing Pg knows Oracle.
>>
>> I can imagine that trigger after logon means that p1 procedure will be
>> called after user successfully logs in, but what does the "for each row"
>> part mean?
>>
>> In PG, there are no login triggers. And, in any case, noone logs to
>> "schema".
>>
>> Best regards,
>>
>> depesz
>>
>>

-- 
*Erik Brandsberg*
erik@heimdalldata.com

www.heimdalldata.com
+1 (866) 433-2824 x 700
[image: AWS Competency Program]
<https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA>

  6. Re: Create trigger on after logon on schema

    aditya desai <admad123@gmail.com> — 2021-10-07T05:19:30Z

    Thanks Erirk and Stever! I will dig more into it. Trying to use EVENT
TRIGGERS for DDL operations. Couldn't find great solution for LOGON yet.
Will try and check the middle tier path.

Regards,
Aditya.

On Wed, Oct 6, 2021 at 11:57 PM Erik Brandsberg <erik@heimdalldata.com>
wrote:

> Following up to Steve Midgley's comment, Heimdall Data provides a proxy
> that can provide this level of auditing, and even trigger insertion of read
> queries into an audit table if you want.  Disclaimer, I'm the CTO of
> Heimdall Data...
>
> On Wed, Oct 6, 2021 at 12:09 PM aditya desai <admad123@gmail.com> wrote:
>
>> Hi Depesz,
>> Sorry , I didn't mention it clearly. We are using these triggers for
>> audit purposes.
>>
>> Triggers calls a procedure to insert username,machine.IPAddress into
>> LogonTime in an audit table.
>> We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP on
>> schema that will audit the user details in the audit table.
>>
>> Any alternative solution in Postgres is welcome.
>>
>> Thanks in advance.
>>
>> Regards,
>> Aditya.
>>
>>
>>
>> On Wed, Oct 6, 2021 at 9:14 PM hubert depesz lubaczewski <
>> depesz@depesz.com> wrote:
>>
>>> On Wed, Oct 06, 2021 at 08:52:45PM +0530, aditya desai wrote:
>>> > Hi,
>>> > Is there any way to write below Oracle trigger in postgres?
>>> >
>>> > CREATE TRIGGER T1 after logon on schema for each row
>>> > execute procedure p1;
>>>
>>> What did it do?
>>>
>>> Please note that not everyone knowing Pg knows Oracle.
>>>
>>> I can imagine that trigger after logon means that p1 procedure will be
>>> called after user successfully logs in, but what does the "for each row"
>>> part mean?
>>>
>>> In PG, there are no login triggers. And, in any case, noone logs to
>>> "schema".
>>>
>>> Best regards,
>>>
>>> depesz
>>>
>>>
>
> --
> *Erik Brandsberg*
> erik@heimdalldata.com
>
> www.heimdalldata.com
> +1 (866) 433-2824 x 700
> [image: AWS Competency Program]
> <https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA>
>

  7. Re: Create trigger on after logon on schema

    aditya desai <admad123@gmail.com> — 2021-10-07T08:48:32Z

    To cut a long story short. We need Virtual Private Database kinda of setup
in Postgres.

Regards,
Aditya.

On Thu, Oct 7, 2021 at 10:49 AM aditya desai <admad123@gmail.com> wrote:

> Thanks Erirk and Stever! I will dig more into it. Trying to use EVENT
> TRIGGERS for DDL operations. Couldn't find great solution for LOGON yet.
> Will try and check the middle tier path.
>
> Regards,
> Aditya.
>
> On Wed, Oct 6, 2021 at 11:57 PM Erik Brandsberg <erik@heimdalldata.com>
> wrote:
>
>> Following up to Steve Midgley's comment, Heimdall Data provides a proxy
>> that can provide this level of auditing, and even trigger insertion of read
>> queries into an audit table if you want.  Disclaimer, I'm the CTO of
>> Heimdall Data...
>>
>> On Wed, Oct 6, 2021 at 12:09 PM aditya desai <admad123@gmail.com> wrote:
>>
>>> Hi Depesz,
>>> Sorry , I didn't mention it clearly. We are using these triggers for
>>> audit purposes.
>>>
>>> Triggers calls a procedure to insert username,machine.IPAddress into
>>> LogonTime in an audit table.
>>> We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP on
>>> schema that will audit the user details in the audit table.
>>>
>>> Any alternative solution in Postgres is welcome.
>>>
>>> Thanks in advance.
>>>
>>> Regards,
>>> Aditya.
>>>
>>>
>>>
>>> On Wed, Oct 6, 2021 at 9:14 PM hubert depesz lubaczewski <
>>> depesz@depesz.com> wrote:
>>>
>>>> On Wed, Oct 06, 2021 at 08:52:45PM +0530, aditya desai wrote:
>>>> > Hi,
>>>> > Is there any way to write below Oracle trigger in postgres?
>>>> >
>>>> > CREATE TRIGGER T1 after logon on schema for each row
>>>> > execute procedure p1;
>>>>
>>>> What did it do?
>>>>
>>>> Please note that not everyone knowing Pg knows Oracle.
>>>>
>>>> I can imagine that trigger after logon means that p1 procedure will be
>>>> called after user successfully logs in, but what does the "for each row"
>>>> part mean?
>>>>
>>>> In PG, there are no login triggers. And, in any case, noone logs to
>>>> "schema".
>>>>
>>>> Best regards,
>>>>
>>>> depesz
>>>>
>>>>
>>
>> --
>> *Erik Brandsberg*
>> erik@heimdalldata.com
>>
>> www.heimdalldata.com
>> +1 (866) 433-2824 x 700
>> [image: AWS Competency Program]
>> <https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA>
>>
>

  8. Re: Create trigger on after logon on schema

    Metin Ulusinan <metin.ulusinan@ssicilian.net> — 2021-10-07T20:53:39Z

    Hi,
I found an extension for postgresql for login hook. Please try this and
share us your experience. It's seems answer your needs.


https://github.com/splendiddata/login_hook



On Thu, Oct 7, 2021 at 8:19 AM aditya desai <admad123@gmail.com> wrote:

> Thanks Erirk and Stever! I will dig more into it. Trying to use EVENT
> TRIGGERS for DDL operations. Couldn't find great solution for LOGON yet.
> Will try and check the middle tier path.
>
> Regards,
> Aditya.
>
> On Wed, Oct 6, 2021 at 11:57 PM Erik Brandsberg <erik@heimdalldata.com>
> wrote:
>
>> Following up to Steve Midgley's comment, Heimdall Data provides a proxy
>> that can provide this level of auditing, and even trigger insertion of read
>> queries into an audit table if you want.  Disclaimer, I'm the CTO of
>> Heimdall Data...
>>
>> On Wed, Oct 6, 2021 at 12:09 PM aditya desai <admad123@gmail.com> wrote:
>>
>>> Hi Depesz,
>>> Sorry , I didn't mention it clearly. We are using these triggers for
>>> audit purposes.
>>>
>>> Triggers calls a procedure to insert username,machine.IPAddress into
>>> LogonTime in an audit table.
>>> We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP on
>>> schema that will audit the user details in the audit table.
>>>
>>> Any alternative solution in Postgres is welcome.
>>>
>>> Thanks in advance.
>>>
>>> Regards,
>>> Aditya.
>>>
>>>
>>>
>>> On Wed, Oct 6, 2021 at 9:14 PM hubert depesz lubaczewski <
>>> depesz@depesz.com> wrote:
>>>
>>>> On Wed, Oct 06, 2021 at 08:52:45PM +0530, aditya desai wrote:
>>>> > Hi,
>>>> > Is there any way to write below Oracle trigger in postgres?
>>>> >
>>>> > CREATE TRIGGER T1 after logon on schema for each row
>>>> > execute procedure p1;
>>>>
>>>> What did it do?
>>>>
>>>> Please note that not everyone knowing Pg knows Oracle.
>>>>
>>>> I can imagine that trigger after logon means that p1 procedure will be
>>>> called after user successfully logs in, but what does the "for each row"
>>>> part mean?
>>>>
>>>> In PG, there are no login triggers. And, in any case, noone logs to
>>>> "schema".
>>>>
>>>> Best regards,
>>>>
>>>> depesz
>>>>
>>>>
>>
>> --
>> *Erik Brandsberg*
>> erik@heimdalldata.com
>>
>> www.heimdalldata.com
>> +1 (866) 433-2824 x 700
>> [image: AWS Competency Program]
>> <https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA>
>>
>

  9. Re: Create trigger on after logon on schema

    aditya desai <admad123@gmail.com> — 2021-10-11T07:48:44Z

    Thanks Metin.

On Fri, Oct 8, 2021 at 2:27 AM Metin Ulusinan <metin.ulusinan@ssicilian.net>
wrote:

> Hi,
> I found an extension for postgresql for login hook. Please try this and
> share us your experience. It's seems answer your needs.
>
>
> https://github.com/splendiddata/login_hook
>
>
>
> On Thu, Oct 7, 2021 at 8:19 AM aditya desai <admad123@gmail.com> wrote:
>
>> Thanks Erirk and Stever! I will dig more into it. Trying to use EVENT
>> TRIGGERS for DDL operations. Couldn't find great solution for LOGON yet.
>> Will try and check the middle tier path.
>>
>> Regards,
>> Aditya.
>>
>> On Wed, Oct 6, 2021 at 11:57 PM Erik Brandsberg <erik@heimdalldata.com>
>> wrote:
>>
>>> Following up to Steve Midgley's comment, Heimdall Data provides a proxy
>>> that can provide this level of auditing, and even trigger insertion of read
>>> queries into an audit table if you want.  Disclaimer, I'm the CTO of
>>> Heimdall Data...
>>>
>>> On Wed, Oct 6, 2021 at 12:09 PM aditya desai <admad123@gmail.com> wrote:
>>>
>>>> Hi Depesz,
>>>> Sorry , I didn't mention it clearly. We are using these triggers for
>>>> audit purposes.
>>>>
>>>> Triggers calls a procedure to insert username,machine.IPAddress into
>>>> LogonTime in an audit table.
>>>> We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP
>>>> on schema that will audit the user details in the audit table.
>>>>
>>>> Any alternative solution in Postgres is welcome.
>>>>
>>>> Thanks in advance.
>>>>
>>>> Regards,
>>>> Aditya.
>>>>
>>>>
>>>>
>>>> On Wed, Oct 6, 2021 at 9:14 PM hubert depesz lubaczewski <
>>>> depesz@depesz.com> wrote:
>>>>
>>>>> On Wed, Oct 06, 2021 at 08:52:45PM +0530, aditya desai wrote:
>>>>> > Hi,
>>>>> > Is there any way to write below Oracle trigger in postgres?
>>>>> >
>>>>> > CREATE TRIGGER T1 after logon on schema for each row
>>>>> > execute procedure p1;
>>>>>
>>>>> What did it do?
>>>>>
>>>>> Please note that not everyone knowing Pg knows Oracle.
>>>>>
>>>>> I can imagine that trigger after logon means that p1 procedure will be
>>>>> called after user successfully logs in, but what does the "for each
>>>>> row"
>>>>> part mean?
>>>>>
>>>>> In PG, there are no login triggers. And, in any case, noone logs to
>>>>> "schema".
>>>>>
>>>>> Best regards,
>>>>>
>>>>> depesz
>>>>>
>>>>>
>>>
>>> --
>>> *Erik Brandsberg*
>>> erik@heimdalldata.com
>>>
>>> www.heimdalldata.com
>>> +1 (866) 433-2824 x 700
>>> [image: AWS Competency Program]
>>> <https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA>
>>>
>>