Обсуждение: v8.3.4 metadata changes while users active
v8.3.4 on linux
Here's the problem...
I have to make some metadata changes, add columns to a table, add constraints, drop a view, recreate it. I used to be able to do this by booting users off, and then quickly make the changes before they get back in. They now have software that seems to connect immediately upon detecting loss of connection, so there's no more time wiondow for this old method to work. The idea of doing this in a transaction doesn't seem to work. It just sits there, the users stay connected.
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?
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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.