Обсуждение: changing the permission of _lots_ of tables
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 --
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
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
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