On Tuesday 14 December 2010 5:02:56 pm Paul McGarry wrote: > I have a number of DBs that are all the same structure but are owned > by different users and I want to add a table to each of those DBs. > > At the moment I have to either connect to each DB as it's user (which > means digging up it's password) or do it as the DB super user and run > an ALTER table ... OWNER specifying the specific user for each of the > DBs. > > My task would be easier if I could do something like: > > ===== > > CREATE TABLE test > > ( > test_id integer NOT NULL, > test_string text > }; > > ALTER TABLE test OWNER DBOWNER; > > ====== > > Where DBOWNER magically resolved to the owner of the DB, so I wouldn't > have to futz with different users, I could just use the same SQL > script on every DB without caring about the specific users. > > Anyone have any ideas on how something like this could be done? > > Paul 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 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. -- 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
