Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create trigger on after logon on schema

Create trigger on after logon on schema

From
aditya desai
Date:
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

From
hubert depesz lubaczewski
Date:
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

From
aditya desai
Date:
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

From
Steve Midgley
Date:


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

From
Erik Brandsberg
Date:
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
AWS Competency Program

Re: Create trigger on after logon on schema

From
aditya desai
Date:
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
AWS Competency Program

Re: Create trigger on after logon on schema

From
aditya desai
Date:
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
AWS Competency Program

Re: Create trigger on after logon on schema

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





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
AWS Competency Program

Re: Create trigger on after logon on schema

From
aditya desai
Date:
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.





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
AWS Competency Program