Обсуждение: Changing table owner to db owner.
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
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@gmail.com
On Wed, Dec 15, 2010 at 12:39 PM, Adrian Klaver <adrian.klaver@gmail.com> 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
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@gmail.com> 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@gmail.com
Try this: 1. Open psql console as user postgres. 2. Execute SELECT ('\\c ' || datname || '\nALTER TABLE the_table_name OWNER TO ' || rolname || ';\n') from pg_database join pg_authid on datdba = pg_authid.oid; 3. Check results. 4. Select results with mouse. 5. Paste it to postgres console (Middle Button on Linux :)) Kind regards, Radosław Smogura http://www.softperience.eu Paul McGarry <paul@paulmcgarry.com> Wednesday 15 December 2010 02:02:56 > 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
Try this: 1. Open psql console as user postgres. 2. Execute SELECT ('\\c ' || datname || '\nALTER TABLE the_table_name OWNER TO ' || rolname || ';\n') from pg_database join pg_authid on datdba = pg_authid.oid; 3. Check results. 4. Select results with mouse. 5. Paste it to postgres console (Middle Button on Linux :)) Kind regards, Radosław Smogura http://www.softperience.eu Paul McGarry <paul@paulmcgarry.com> Wednesday 15 December 2010 02:02:56 > 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