Thread: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL

CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.
If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

What do you want to have happen to the timestamp/user?

You can obviously do:

update test set t4 = 9, user = <someuser>, timestamp = <sometimestamp>
where t1 = 001;

I'm assuming you're trying to store the user and timestamp somewhere
else, though?

Robin

On Wed, 2012-03-14 at 12:44 +0200, Khangelani Gama wrote:
>
>
> Hi
>
>
>
>
>
> Is it possible to have an update query that will specify actions,
> timestamp, user who’s making the update inside the database. Can this
> be done without any script but just in the transaction block
>
>
>
> Example:
>
>
>
> dbtest=# UPDATE table test set t4 = 9 where t1 = 001 then specify
> user_name,  timestamp() ;
>
>
>
>
>
>
>
>
>
> Thanks in advance
>
>
>
>
>
>
>
>
>
>
>
>
>
> C O N F I D E N T I A L I T Y   N O T I C E
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of
> Argility (Proprietary) Limited and/or its subsidiaries. Any review,
> use or dissemination thereof by anyone other than the intended
> addressee is prohibited. If you are not the intended addressee please
> notify the writer immediately and destroy the e-mail. Argility
> (Proprietary) Limited and its subsidiaries distance themselves from
> and accept no liability for unauthorised use of their e-mail
> facilities or e-mails sent other than strictly for business purposes.
>
>
>
>
>
>
>
> CONFIDENTIALITY NOTICE
> The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyoneother than the intended addressee is prohibited. 
> If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility
(Proprietary)Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of
theire-mail facilities or e-mails sent other than strictly for business purposes. 

thanks, the issue we have is that we have many Linux users having root
access into the system. So they're able to access the DB by just going in as
"su - superusername". If this user is able to make any updates inside the
database it might create problems. Auditors wants PostgreSQL to tell who
updated what inside the database besides client users that access the
database from outside using some applications. So this common
"superusername" doesn't tell the actual person who got into the system and
went onto make updates inside the database because they first logged as
their linux users before as going in as postgres user called
"superusername".






-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Robin Iddon
Sent: Wednesday, March 14, 2012 12:54 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Update actions (with user name) inside PostgreSQL DB -
any version on postgreSQL

What do you want to have happen to the timestamp/user?

You can obviously do:

update test set t4 = 9, user = <someuser>, timestamp = <sometimestamp> where
t1 = 001;

I'm assuming you're trying to store the user and timestamp somewhere else,
though?

Robin

On Wed, 2012-03-14 at 12:44 +0200, Khangelani Gama wrote:
>
>
> Hi
>
>
>
>
>
> Is it possible to have an update query that will specify actions,
> timestamp, user who’s making the update inside the database. Can this
> be done without any script but just in the transaction block
>
>
>
> Example:
>
>
>
> dbtest=# UPDATE table test set t4 = 9 where t1 = 001 then specify
> user_name,  timestamp() ;
>
>
>
>
>
>
>
>
>
> Thanks in advance
>
>
>
>
>
>
>
>
>
>
>
>
>
> C O N F I D E N T I A L I T Y   N O T I C E
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of
> Argility (Proprietary) Limited and/or its subsidiaries. Any review,
> use or dissemination thereof by anyone other than the intended
> addressee is prohibited. If you are not the intended addressee please
> notify the writer immediately and destroy the e-mail. Argility
> (Proprietary) Limited and its subsidiaries distance themselves from
> and accept no liability for unauthorised use of their e-mail
> facilities or e-mails sent other than strictly for business purposes.
>
>
>
>
>
>
>
> CONFIDENTIALITY NOTICE
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of Argility
> (Proprietary) Limited and/or its subsidiaries. Any review, use or
> dissemination thereof by anyone other than the intended addressee is
> prohibited.
> If you are not the intended addressee please notify the writer immediately
> and destroy the e-mail. Argility (Proprietary) Limited and its
> subsidiaries distance themselves from and accept no liability for
> unauthorised use of their e-mail facilities or e-mails sent other than
> strictly for business purposes.



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin




CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyoneother than the intended addressee is prohibited. 
If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility
(Proprietary)Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of
theire-mail facilities or e-mails sent other than strictly for business purposes. 

Hi, anyone with an idea based on my latest comments below? Thanks





-----Original Message-----
From: Khangelani Gama [mailto:kgama@argility.com]
Sent: Wednesday, March 14, 2012 1:25 PM
To: 'Robin Iddon'; 'pgsql-admin@postgresql.org'
Subject: RE: [ADMIN] Update actions (with user name) inside PostgreSQL DB -
any version on postgreSQL

thanks, the issue we have is that we have many Linux users having root
access into the system. So they're able to access the DB by just going in as
"su - superusername". If this user is able to make any updates inside the
database it might create problems. Auditors wants PostgreSQL to tell who
updated what inside the database besides client users that access the
database from outside using some applications. So this common
"superusername" doesn't tell the actual person who got into the system and
went onto make updates inside the database because they first logged as
their linux users before as going in as postgres user called
"superusername".






-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Robin Iddon
Sent: Wednesday, March 14, 2012 12:54 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Update actions (with user name) inside PostgreSQL DB -
any version on postgreSQL

What do you want to have happen to the timestamp/user?

You can obviously do:

update test set t4 = 9, user = <someuser>, timestamp = <sometimestamp> where
t1 = 001;

I'm assuming you're trying to store the user and timestamp somewhere else,
though?

Robin

On Wed, 2012-03-14 at 12:44 +0200, Khangelani Gama wrote:
>
>
> Hi
>
>
>
>
>
> Is it possible to have an update query that will specify actions,
> timestamp, user who’s making the update inside the database. Can this
> be done without any script but just in the transaction block
>
>
>
> Example:
>
>
>
> dbtest=# UPDATE table test set t4 = 9 where t1 = 001 then specify
> user_name,  timestamp() ;
>
>
>
>
>
>
>
>
>
> Thanks in advance
>
>
>
>
>
>
>
>
>
>
>
>
>
> C O N F I D E N T I A L I T Y   N O T I C E
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of
> Argility (Proprietary) Limited and/or its subsidiaries. Any review,
> use or dissemination thereof by anyone other than the intended
> addressee is prohibited. If you are not the intended addressee please
> notify the writer immediately and destroy the e-mail. Argility
> (Proprietary) Limited and its subsidiaries distance themselves from
> and accept no liability for unauthorised use of their e-mail
> facilities or e-mails sent other than strictly for business purposes.
>
>
>
>
>
>
>
> CONFIDENTIALITY NOTICE
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of Argility
> (Proprietary) Limited and/or its subsidiaries. Any review, use or
> dissemination thereof by anyone other than the intended addressee is
> prohibited.
> If you are not the intended addressee please notify the writer immediately
> and destroy the e-mail. Argility (Proprietary) Limited and its
> subsidiaries distance themselves from and accept no liability for
> unauthorised use of their e-mail facilities or e-mails sent other than
> strictly for business purposes.



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin




CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyoneother than the intended addressee is prohibited. 
If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility
(Proprietary)Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of
theire-mail facilities or e-mails sent other than strictly for business purposes. 

You lose if you have provided people you do not trust with root access
they can be whoever they want to be and there is nothing you can do
about it.  In my belief there is absolutely no way of making the system
safe with untrusted root users.  Don't waste your time trying.

Any attempt to create an audit trail by adding in username capture in
the software is doomed to being circumvented by anybody with root access
who doesn't want to be traced.

For example:

robin$ su - root
root# su - kgama
kgama$ su - root ... do something bad.

Now it looks like you did it, even though it was me.

Robin

On Wed, 2012-03-14 at 14:39 +0200, Khangelani Gama wrote:
> Hi, anyone with an idea based on my latest comments below? Thanks
>
>
>
>
>
> -----Original Message-----
> From: Khangelani Gama [mailto:kgama@argility.com]
> Sent: Wednesday, March 14, 2012 1:25 PM
> To: 'Robin Iddon'; 'pgsql-admin@postgresql.org'
> Subject: RE: [ADMIN] Update actions (with user name) inside PostgreSQL DB -
> any version on postgreSQL
>
> thanks, the issue we have is that we have many Linux users having root
> access into the system. So they're able to access the DB by just going in as
> "su - superusername". If this user is able to make any updates inside the
> database it might create problems. Auditors wants PostgreSQL to tell who
> updated what inside the database besides client users that access the
> database from outside using some applications. So this common
> "superusername" doesn't tell the actual person who got into the system and
> went onto make updates inside the database because they first logged as
> their linux users before as going in as postgres user called
> "superusername".
>
>
>
>
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Robin Iddon
> Sent: Wednesday, March 14, 2012 12:54 PM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Update actions (with user name) inside PostgreSQL DB -
> any version on postgreSQL
>
> What do you want to have happen to the timestamp/user?
>
> You can obviously do:
>
> update test set t4 = 9, user = <someuser>, timestamp = <sometimestamp> where
> t1 = 001;
>
> I'm assuming you're trying to store the user and timestamp somewhere else,
> though?
>
> Robin
>
> On Wed, 2012-03-14 at 12:44 +0200, Khangelani Gama wrote:
> >
> >
> > Hi
> >
> >
> >
> >
> >
> > Is it possible to have an update query that will specify actions,
> > timestamp, user who’s making the update inside the database. Can this
> > be done without any script but just in the transaction block
> >
> >
> >
> > Example:
> >
> >
> >
> > dbtest=# UPDATE table test set t4 = 9 where t1 = 001 then specify
> > user_name,  timestamp() ;
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Thanks in advance
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > C O N F I D E N T I A L I T Y   N O T I C E
> > The contents of and attachments to this e-mail are intended for the
> > addressee only, and may contain the confidential information of
> > Argility (Proprietary) Limited and/or its subsidiaries. Any review,
> > use or dissemination thereof by anyone other than the intended
> > addressee is prohibited. If you are not the intended addressee please
> > notify the writer immediately and destroy the e-mail. Argility
> > (Proprietary) Limited and its subsidiaries distance themselves from
> > and accept no liability for unauthorised use of their e-mail
> > facilities or e-mails sent other than strictly for business purposes.
> >
> >
> >
> >
> >
> >
> >
> > CONFIDENTIALITY NOTICE
> > The contents of and attachments to this e-mail are intended for the
> > addressee only, and may contain the confidential information of Argility
> > (Proprietary) Limited and/or its subsidiaries. Any review, use or
> > dissemination thereof by anyone other than the intended addressee is
> > prohibited.
> > If you are not the intended addressee please notify the writer immediately
> > and destroy the e-mail. Argility (Proprietary) Limited and its
> > subsidiaries distance themselves from and accept no liability for
> > unauthorised use of their e-mail facilities or e-mails sent other than
> > strictly for business purposes.
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
>
> CONFIDENTIALITY NOTICE
> The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyoneother than the intended addressee is prohibited. 
> If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility
(Proprietary)Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of
theire-mail facilities or e-mails sent other than strictly for business purposes. 
>
>

On Mar 14, 2012, at 6:39 AM, Khangelani Gama wrote:

> ...the issue we have is that we have many Linux users having root
> access into the system... Auditors wants PostgreSQL to tell who
> updated what inside the database...

Well, this is an obvious contradiction. You have an inherently non-auditable setup that needs to be audited, and you're
notgoing to fix that with some magic setting somewhere. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Khangelani Gama <kgama@argility.com> wrote:

> the issue we have is that we have many Linux users having root
> access into the system.

Which gives them rights to impersonate any other user on the system
and to erase any audit trail written on that system.

> Auditors wants PostgreSQL to tell who updated what inside the
> database

You might be able to create something which looks plausible without
solving the first problem, but it wouldn't be at all trustworthy.
Consider limiting access to root on your database servers and, in
general, pay attention to the concept of "separation of duties"[1].

-Kevin

[1] http://en.wikipedia.org/wiki/Separation_of_duties

--

On Mar 14, 2012, at 9:01 AM, David Ondrejik wrote:

> In Linux you can setup and use the "sudo" option. For those whom you don't wish to have root access, simply make them
sudousers,then change the root password. This will force those users to simply type "sudo" (w/o quotes) at the
beginningof each command they want to run (i.e. sudo psql db_name "insert into...."). 

Sure, you mean like this command:

sudo su root

???

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Sure, you mean like this command:

sudo su root

???

--

On Mar 14, 2012, at 9:28 AM, David Ondrejik wrote:

> So there is some trail to track back to the original user.

Yes, but once he has root shell, the trail ends there, and impersonation of anyone is once again trivial. Also, sudo su
root,does work on some unices, and the option you don't want to advertise is, in my opinion, trivially obvious--but
I'llrespect the idea and not advertise it. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

--

Scott Ribe wrote:
> On Mar 14, 2012, at 9:01 AM, David Ondrejik wrote:
>
>> In Linux you can setup and use the "sudo" option. For those whom you don't wish to have root access, simply make
themsudousers, then change the root password. This will force those users to simply type "sudo" (w/o quotes) at the
beginningof each command they want to run (i.e. sudo psql db_name "insert into...."). 
>
> Sure, you mean like this command:
>
> sudo su root

If properly (mis)configured.

On the other hand, you can provide very limited root access on a
command-by-command and user-by-user basis with more complex sudo
configurations, and while the first request will ask for a password,
further requests within the configured authorization timeout will still
be logged even if the user isn't asked for their password.

-kgd

Constrained sudo is no substitute for proper security.  If I was in charge of a database where personal details or credit card or financial information could be compromised I would not rely on constrained sudo.

The reason is that no matter how smart you think you are, some smarty pants always finds a way to abuse the root privileges they have been granted, possibly by exploiting a design flaw in the program they've been allowed to run as root.

For example, I am pretty sure that psql can be used to write files with arbitrary content (use your imagination with \copy ... or \echo ...).  I am pretty sure that as root you could overwrite /usr/bin/psql with another file of the same name that actually execs /bin/bash if invoked with a suitable command line option, but otherwise behaves just like /usr/bin/psql ...

If you don't believe me try this:

sudo psql <whatever ...>
dbname=>\pset tuples_only
dbname=>\o |/bin/bash
dbname=>select 'id';
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)

There are many more sophisticated examples.  The only solution is not to grant sudo to anyone you wouldn't grant root to.

Cheers,
Robin

On Wed, 2012-03-14 at 12:24 -0400, Kris Deugau wrote:

Scott Ribe wrote:
> On Mar 14, 2012, at 9:01 AM, David Ondrejik wrote:
>
>> In Linux you can setup and use the "sudo" option. For those whom you don't wish to have root access, simply make them sudousers, then change the root password. This will force those users to simply type "sudo" (w/o quotes) at the beginning of each command they want to run (i.e. sudo psql db_name "insert into....").
>
> Sure, you mean like this command:
>
> sudo su root

If properly (mis)configured.

On the other hand, you can provide very limited root access on a 
command-by-command and user-by-user basis with more complex sudo 
configurations, and while the first request will ask for a password, 
further requests within the configured authorization timeout will still 
be logged even if the user isn't asked for their password.

-kgd

On Wed, Mar 14, 2012 at 5:24 AM, Khangelani Gama <kgama@argility.com> wrote:
> thanks, the issue we have is that we have many Linux users having root
> access into the system.

That's the problem right there.  Setup a separate db server that only
your DBA can log into the shell on.  Then make it so that you have
proper access controls to the db via pg_hba.conf.

On Wed, 14 Mar 2012 13:24:36 +0200, Khangelani Gama wrote:

> thanks, the issue we have is that we have many Linux users having root
> access into the system. So they're able to access the DB by just going
> in as "su - superusername". If this user is able to make any updates
> inside the database it might create problems. Auditors wants PostgreSQL
> to tell who updated what inside the database besides client users that
> access the database from outside using some applications. So this common
> "superusername" doesn't tell the actual person who got into the system
> and went onto make updates inside the database because they first logged
> as their linux users before as going in as postgres user called
> "superusername".

http://dl.dropbox.com/u/6106778/message.jpg

Thanks to everyone for the ideas, the information you provided me with
was much needed.  it's much appreciated.




C O N F I D E N T I A L I T Y   N O T I C E
The contents of and attachments to this e-mail are intended for the
addressee only, and may contain the confidential information of Argility
(Proprietary) Limited and/or its subsidiaries. Any review, use or
dissemination thereof by anyone other than the intended addressee is
prohibited. If you are not the intended addressee please notify the writer
immediately and destroy the e-mail. Argility (Proprietary) Limited and its
subsidiaries distance themselves from and accept no liability for
unauthorised use of their e-mail facilities or e-mails sent other than
strictly for business purposes.

-----Original Message-----
From: Scott Ribe [mailto:scott_ribe@elevated-dev.com]
Sent: Wednesday, March 14, 2012 5:47 PM
To: David Ondrejik
Cc: Khangelani Gama; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Update actions (with user name) inside PostgreSQL DB
- any version on postgreSQL

On Mar 14, 2012, at 9:28 AM, David Ondrejik wrote:

> So there is some trail to track back to the original user.

Yes, but once he has root shell, the trail ends there, and impersonation
of anyone is once again trivial. Also, sudo su root, does work on some
unices, and the option you don't want to advertise is, in my opinion,
trivially obvious--but I'll respect the idea and not advertise it.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice




CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyoneother than the intended addressee is prohibited. 
If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility
(Proprietary)Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of
theire-mail facilities or e-mails sent other than strictly for business purposes.