Обсуждение: 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.

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.