v8.3.4 metadata changes while users active

On 04/04/2012 09:26 PM, Gauthier, Dave wrote:

v8.3.4 on linux

Here's the problem...

I need toboot them off and prevent them from getting back in so that I
can make the changes, then re-enable them. There are 2 users, lets call
them "selectuser" and "moduser" who have "select" and
"select,insert,update,delete" respectively and a dozenor so tables, plus
many stored procedures and functions. So I'm not sure revoke/grant is
such a great idea because I think I'd have to grant the privs back to
all those elements.

Thanks fora ny ideas?

Well something along this can work

try this as root

iptables -I INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j DROP
boot ogff your users
make changes
iptables -D INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j DROP

regards,

Gabriel

Hmmmm... I don't have root access :-(

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gabriel Ramirez
Sent: Wednesday, April 04, 2012 11:00 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] v8.3.4 metadata changes while users active

On 04/04/2012 09:26 PM, Gauthier, Dave wrote:

v8.3.4 on linux

Here's the problem...

I need toboot them off and prevent them from getting back in so that I
can make the changes, then re-enable them. There are 2 users, lets call
them "selectuser" and "moduser" who have "select" and
"select,insert,update,delete" respectively and a dozenor so tables, plus
many stored procedures and functions. So I'm not sure revoke/grant is
such a great idea because I think I'd have to grant the privs back to
all those elements.

Thanks fora ny ideas?

Well something along this can work

try this as root

iptables -I INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j DROP
boot ogff your users
make changes
iptables -D INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j DROP

regards,

Gabriel

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

On 04/04/12 7:26 PM, Gauthier, Dave wrote:

v8.3.4 on linux

8.3 is currently at 8.3.18, there's been a LOT of fixes.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Try to reject the connections by using pg_hba.conf, only accepting
connections of localhost or your IP.
El 04/04/2012 22:18, "Gauthier, Dave" <dave.gauthier@intel.com> escribió:

On Thu, 2012-04-05 at 03:17 +0000, Gauthier, Dave wrote:

Hmmmm... I don't have root access :-(

In that case, ask your sysadmin to grant you sudo access to iptables or,
if he thinks that is excessive, to write a wrapper script that
enables/disables just that port and give you sudo access to that script.

Martin

Hey,

Gauthier, Dave wrote:

Hmmmm... I don't have root access :-(

Do you have the ability to alter the users/roles? If so, you
could set their connection limit to 0 and then kick them off.
Do your work and then set their connection limit back to the
value it was before. The default is -1 (unlimited).

Here's how to set the connection limit:
alter role moduser connection limit 0;

This is only really viable if the set of users is small. You
mentioned only 2 before. I'm not sure if it was exactly 2 users
or 2 "types" of users.

Just a thought.

Bosco.

Hmmm.... This sounds like it might work.
There are, in fact, only 2 users (roles). Lets call them "selectuser" and "moduser"
So, as the DBA, I just...

alter role selectuser connection limit 0;
alter role moduser connection limit 0;

Then kick everyone off (I usually use "pg_ctl kill TERM <procpid>" to do this)

Make changes as the DBA
Then...

alter role selectuser connection limit -1;
alter role moduser connection limit -1;

Done !

Correct?

-----Original Message-----
From: Bosco Rama [mailto:postgres@boscorama.com]
Sent: Thursday, April 05, 2012 11:27 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] v8.3.4 metadata changes while users active

Hey,

Gauthier, Dave wrote:

Hmmmm... I don't have root access :-(

Do you have the ability to alter the users/roles? If so, you
could set their connection limit to 0 and then kick them off.
Do your work and then set their connection limit back to the
value it was before. The default is -1 (unlimited).

Here's how to set the connection limit:
alter role moduser connection limit 0;

This is only really viable if the set of users is small. You
mentioned only 2 before. I'm not sure if it was exactly 2 users
or 2 "types" of users.

Just a thought.

Bosco.

Gauthier, Dave wrote:

Hmmm.... This sounds like it might work.
There are, in fact, only 2 users (roles). Lets call them "selectuser" and "moduser"
So, as the DBA, I just...

alter role selectuser connection limit 0;
alter role moduser connection limit 0;

Then kick everyone off (I usually use "pg_ctl kill TERM <procpid>" to do this)

Make changes as the DBA
Then...

alter role selectuser connection limit -1;
alter role moduser connection limit -1;

Done !

Correct?

Yep. That should work. The only reservation I'd bring up here is that you
should be doing this as a user other than one of those two users. Otherwise
you may lock yourself out of the DB. I assume you'll be doing this as either
a PG superuser (e.g. postgres) or a user distinct from the two above.

HTH

Bosco.

I'll be running this as "postgres"
Thanks for the help.
Hope others may find this useful.

-----Original Message-----
From: Bosco Rama [mailto:postgres@boscorama.com]
Sent: Thursday, April 05, 2012 12:04 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] v8.3.4 metadata changes while users active

Gauthier, Dave wrote:

Hmmm.... This sounds like it might work.
There are, in fact, only 2 users (roles). Lets call them "selectuser" and "moduser"
So, as the DBA, I just...

alter role selectuser connection limit 0;
alter role moduser connection limit 0;

Then kick everyone off (I usually use "pg_ctl kill TERM <procpid>" to do this)

Make changes as the DBA
Then...

alter role selectuser connection limit -1;
alter role moduser connection limit -1;

Done !

Correct?

Yep. That should work. The only reservation I'd bring up here is that you
should be doing this as a user other than one of those two users. Otherwise
you may lock yourself out of the DB. I assume you'll be doing this as either
a PG superuser (e.g. postgres) or a user distinct from the two above.

HTH

Bosco.