On Tuesday 14 December 2010 5:58:16 pm Paul McGarry wrote: > On Wed, Dec 15, 2010 at 12:39 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: > > You did not say what version you are using. In 9.0 you have this option: > > http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges > >.html > > Our servers are a mix of 8.3 and 8.4. > > > I generally do as superuser; > > > > SET ROLE user; > > CREATE TABLE table_name; > > > > It requires you knowing the user_name/role_name for each db, though it > > would be possible to create a script that automates that by doing a > > lookup. > > It's a pity something nasty like: > SET ROLE (select pg_get_userbyid(relowner) from pg_class where > relname='existingtable'); > doesn't work. > > Using SET ROLE like that is certainly helpful though, I have about ~30 > to update so anything that makes it easier is a help. > > Paul Well you could wrap it in a function and add the function to your dbs. For new databases add it to template1 and it will automatically be added, assuming you are not overriding the default template on CREATE DATABASE. See here for system functions that might help: http://www.postgresql.org/docs/9.0/interactive/functions-info.html Also you can get the db owner for the db by querying pg_database and pg_user where datdba in pg_database maps to usesysid in pg_user. -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
