Обсуждение: Create trigger on after logon on schema

Поиск

Список

Период

Сортировка

Create trigger on after logon on schema

От

aditya desai

Дата:

06 октября 2021 г., 18:22:45

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.

Re: Create trigger on after logon on schema

От

hubert depesz lubaczewski

Дата:

06 октября 2021 г., 18:44:44

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

Re: Create trigger on after logon on schema

От

aditya desai

Дата:

06 октября 2021 г., 19:09:28

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

Re: Create trigger on after logon on schema

От

Steve Midgley

Дата:

06 октября 2021 г., 21:16:04

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

Re: Create trigger on after logon on schema

От

Erik Brandsberg

Дата:

06 октября 2021 г., 21:26:58

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

Re: Create trigger on after logon on schema

От

aditya desai

Дата:

07 октября 2021 г., 08:19:30

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

Re: Create trigger on after logon on schema

От

aditya desai

Дата:

07 октября 2021 г., 11:48:32

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

Re: Create trigger on after logon on schema

От

Metin Ulusinan

Дата:

07 октября 2021 г., 23:53:39

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

Re: Create trigger on after logon on schema

От

aditya desai

Дата:

11 октября 2021 г., 10:48:44

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Create trigger on after logon on schema