Postgres Pro
Downloads
Home   >   mailing lists

Thread: changing the permission of _lots_ of tables

changing the permission of _lots_ of tables

From
"Roderick A. Anderson"
Date:
Back in September Bobby Gontarski asked about copy db1 to db2.

Jim Nasby came back with the suggestion of using db1 as the template for
creating db2.

Works great ... up to a point.  I now need to change the users and
owners associated with all the tables without changing their permissions.

Is there a way or hack to do mass changes like these.


After creating the database db2, adding db2dba and db2user, and
connecting to db2 I have:

    table_A ... table_I owned by db1dba and need to change those so the
owner is db2dba.

    db1user has SELECT, INSERT, UPDATE on table_A, table_C and table_I
but only SELECT on table_B, table_D ... table_H and I need to change
those to db2user.

As development continues; next month; db1 may have 2 new tables.

Any ideas or suggestions of ways to scriptify/automagically accomplish this?


TIA,
Rod
--

Re: changing the permission of _lots_ of tables

From
Richard Huxton
Date:
Roderick A. Anderson wrote:
> Works great ... up to a point.  I now need to change the users and
> owners associated with all the tables without changing their permissions.
>
> Is there a way or hack to do mass changes like these.

Write a small plpgsql function to take a pattern and set permissions
based on that. A quick bit of googling should find some examples for you.

--
   Richard Huxton
   Archonet Ltd

Re: changing the permission of _lots_ of tables

From
"A. Kretschmer"
Date:
am  Wed, dem 13.12.2006, um  7:55:48 +0000 mailte Richard Huxton folgendes:
> Roderick A. Anderson wrote:
> >Works great ... up to a point.  I now need to change the users and
> >owners associated with all the tables without changing their permissions.
> >
> >Is there a way or hack to do mass changes like these.
>
> Write a small plpgsql function to take a pattern and set permissions
> based on that. A quick bit of googling should find some examples for you.

Perhaps this one:
http://people.planetpostgresql.org/greg/index.php?/archives/38-Scripting-with-psql.html


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: changing the permission of _lots_ of tables

From
"Roderick A. Anderson"
Date:
Andreas, Richard,

Thanks.  The link below is like of what I was looking for if there
wasn't an already written script.  been through all three of my Pg books
but didn't find any mention of this type of scripting.


Rod
--
A. Kretschmer wrote:
> am  Wed, dem 13.12.2006, um  7:55:48 +0000 mailte Richard Huxton folgendes:
>> Roderick A. Anderson wrote:
>>> Works great ... up to a point.  I now need to change the users and
>>> owners associated with all the tables without changing their permissions.
>>>
>>> Is there a way or hack to do mass changes like these.
>> Write a small plpgsql function to take a pattern and set permissions
>> based on that. A quick bit of googling should find some examples for you.
>
> Perhaps this one:
> http://people.planetpostgresql.org/greg/index.php?/archives/38-Scripting-with-psql.html
>
>
> Regards, Andreas