pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate actions (with user name) inside PostgreSQL DB - any version on postgreSQL
17+ messages / 8 participants
[nested] [flat]
* Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 10:44 Khangelani Gama <kgama@argility.com>
0 siblings, 1 reply; 17+ messages in thread
From: Khangelani Gama @ 2012-03-14 10:44 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 10:54 Robin Iddon <robin@edesix.com>
parent: Khangelani Gama <kgama@argility.com>
0 siblings, 2 replies; 17+ messages in thread
From: Robin Iddon @ 2012-03-14 10:54 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 11:24 Khangelani Gama <kgama@argility.com>
parent: Robin Iddon <robin@edesix.com>
1 sibling, 3 replies; 17+ messages in thread
From: Khangelani Gama @ 2012-03-14 11:24 UTC (permalink / raw)
To: Robin Iddon <robin@edesix.com>; pgsql-admin
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 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 12:39 Khangelani Gama <kgama@argility.com>
parent: Robin Iddon <robin@edesix.com>
1 sibling, 2 replies; 17+ messages in thread
From: Khangelani Gama @ 2012-03-14 12:39 UTC (permalink / raw)
To: pgsql-admin
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 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 13:03 Robin Iddon <robin@edesix.com>
parent: Khangelani Gama <kgama@argility.com>
1 sibling, 1 reply; 17+ messages in thread
From: Robin Iddon @ 2012-03-14 13:03 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; +Cc: pgsql-admin
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 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.
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 13:39 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Khangelani Gama <kgama@argility.com>
1 sibling, 0 replies; 17+ messages in thread
From: Scott Ribe @ 2012-03-14 13:39 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; +Cc: pgsql-admin
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 not going to fix that with some magic setting somewhere.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 14:02 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Khangelani Gama <kgama@argility.com>
2 siblings, 0 replies; 17+ messages in thread
From: Kevin Grittner @ 2012-03-14 14:02 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 15:01 David Ondrejik <david.ondrejik@noaa.gov>
parent: Robin Iddon <robin@edesix.com>
0 siblings, 1 reply; 17+ messages in thread
From: David Ondrejik @ 2012-03-14 15:01 UTC (permalink / raw)
To: Robin Iddon <robin@edesix.com>; Khangelani Gama <kgama@argility.com>; pgsql-admin
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...."). The user will then be prompted for THEIR password. If
there userid is in the sudouser file, the command will run with root
privileges. It also logs their actions in a log file (usually in /var/log -
but that is configurable) which has only read permissions for root.
Once the first sudo command is run, the sudousers can continue to run
commands with root privileges for 10 minutes in that shell (time is
adjustable). We use it at my office and it has been effective.
You can then scour the log file to see what activities have been carried
out by your sudousers...a possible work around to this problem.
Dave
On Wed, Mar 14, 2012 at 9:03 AM, Robin Iddon <robin@edesix.com> wrote:
> 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 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
>
--
Dave Ondrejik - Senior Hydrologist
National Weather Service
Middle Atlantic River Forecast Center
328 Innovation Blvd, Suite #330
State College, PA 16870
(814) 231-2403
david.ondrejik@noaa.gov
See us on the web at:
http://www.weather.gov/marfc
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 15:14 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David Ondrejik <david.ondrejik@noaa.gov>
0 siblings, 2 replies; 17+ messages in thread
From: Scott Ribe @ 2012-03-14 15:14 UTC (permalink / raw)
To: David Ondrejik <david.ondrejik@noaa.gov>; +Cc: Robin Iddon <robin@edesix.com>; Khangelani Gama <kgama@argility.com>; pgsql-admin
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
???
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 15:28 David Ondrejik <david.ondrejik@noaa.gov>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 17+ messages in thread
From: David Ondrejik @ 2012-03-14 15:28 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Robin Iddon <robin@edesix.com>; Khangelani Gama <kgama@argility.com>; pgsql-admin
*Sure, you mean like this command:
sudo su root
???*
Luckily...that option will fail using sudo...and it will be logged that it
was attempted. I see where you are going with this Scott and there is an
option that will work to give you a root shell, but I don't want to
advertise that. That unadvertised command...if issued...would also
be logged and lists the userid of the person who used the command. So there
is some trail to track back to the original user.
Dave
On Wed, Mar 14, 2012 at 11:14 AM, Scott Ribe <scott_ribe@elevated-dev.com>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
>
> ???
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>
--
Dave Ondrejik - Senior Hydrologist
National Weather Service
Middle Atlantic River Forecast Center
328 Innovation Blvd, Suite #330
State College, PA 16870
(814) 231-2403
david.ondrejik@noaa.gov
See us on the web at:
http://www.weather.gov/marfc
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 15:46 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David Ondrejik <david.ondrejik@noaa.gov>
0 siblings, 2 replies; 17+ messages in thread
From: Scott Ribe @ 2012-03-14 15:46 UTC (permalink / raw)
To: David Ondrejik <david.ondrejik@noaa.gov>; +Cc: Khangelani Gama <kgama@argility.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 16:14 David Ondrejik <david.ondrejik@noaa.gov>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 17+ messages in thread
From: David Ondrejik @ 2012-03-14 16:14 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Khangelani Gama <kgama@argility.com>; pgsql-admin
Scott,
You are correct and I agree with you. The sudo option would work best with
individuals who do not have much Linux SA experience to know or figure
out these options.
I agree with the original premises posted by several that limiting the
number of people with root access is the best option. But was trying to
provide another option. I am not sure why so many people need root access
simply to adjust a table. If trusted, they should be able to do that using
their own account. I guess you could scour the history files of everyone
(short time step) and archive that data if needed to provide an activity
trail???
BUT...Robin was completely correct when stating:
"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."
Scott - I think the above is also the point you are also trying to
make...and again I agree.
Short of removing everyone with root access and developing software that
forces the user to input username and password to change table
entries...there may not be much that can be done. If you don't trust people
with root password, don't give it to them.
Dave
On Wed, Mar 14, 2012 at 11:46 AM, Scott Ribe <scott_ribe@elevated-dev.com>wrote:
> 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
>
>
>
>
>
--
Dave Ondrejik - Senior Hydrologist
National Weather Service
Middle Atlantic River Forecast Center
328 Innovation Blvd, Suite #330
State College, PA 16870
(814) 231-2403
david.ondrejik@noaa.gov
See us on the web at:
http://www.weather.gov/marfc
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 16:24 Kris Deugau <kdeugau@vianet.ca>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 17+ messages in thread
From: Kris Deugau @ 2012-03-14 16:24 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 16:44 Robin Iddon <robin@edesix.com>
parent: Kris Deugau <kdeugau@vianet.ca>
0 siblings, 0 replies; 17+ messages in thread
From: Robin Iddon @ 2012-03-14 16:44 UTC (permalink / raw)
To: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 17:10 Scott Marlowe <scott.marlowe@gmail.com>
parent: Khangelani Gama <kgama@argility.com>
2 siblings, 0 replies; 17+ messages in thread
From: Scott Marlowe @ 2012-03-14 17:10 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; +Cc: Robin Iddon <robin@edesix.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-14 21:15 Walter Hurry <walterhurry@lavabit.com>
parent: Khangelani Gama <kgama@argility.com>
2 siblings, 0 replies; 17+ messages in thread
From: Walter Hurry @ 2012-03-14 21:15 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
@ 2012-03-15 04:33 Khangelani Gama <kgama@argility.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 17+ messages in thread
From: Khangelani Gama @ 2012-03-15 04:33 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; David Ondrejik <david.ondrejik@noaa.gov>; +Cc: pgsql-admin
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 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2012-03-15 04:33 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-14 10:44 Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL Khangelani Gama <kgama@argility.com>
2012-03-14 10:54 ` Robin Iddon <robin@edesix.com>
2012-03-14 11:24 ` Khangelani Gama <kgama@argility.com>
2012-03-14 14:02 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-03-14 17:10 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-03-14 21:15 ` Walter Hurry <walterhurry@lavabit.com>
2012-03-14 12:39 ` Khangelani Gama <kgama@argility.com>
2012-03-14 13:03 ` Robin Iddon <robin@edesix.com>
2012-03-14 15:01 ` David Ondrejik <david.ondrejik@noaa.gov>
2012-03-14 15:14 ` Scott Ribe <scott_ribe@elevated-dev.com>
2012-03-14 15:28 ` David Ondrejik <david.ondrejik@noaa.gov>
2012-03-14 15:46 ` Scott Ribe <scott_ribe@elevated-dev.com>
2012-03-14 16:14 ` David Ondrejik <david.ondrejik@noaa.gov>
2012-03-15 04:33 ` Khangelani Gama <kgama@argility.com>
2012-03-14 16:24 ` Kris Deugau <kdeugau@vianet.ca>
2012-03-14 16:44 ` Robin Iddon <robin@edesix.com>
2012-03-14 13:39 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox