Thread: Changing table owner to db owner.

Changing table owner to db owner.

From

Paul McGarry

Date:

15 December 2010, 00:05:19

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

Re: Changing table owner to db owner.

From

Adrian Klaver

Date:

15 December 2010, 00:39:11

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

Re: Changing table owner to db owner.

From

Paul McGarry

Date:

15 December 2010, 00:58:23

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

Re: Changing table owner to db owner.

From

Adrian Klaver

Date:

15 December 2010, 01:18:56

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

Re: Changing table owner to db owner.

From

Radosław Smogura

Date:

15 December 2010, 20:20:02

 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

Re: Changing table owner to db owner.

From

Radosław Smogura

Date:

15 December 2010, 23:09:44

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