Thread: Database Ownership

Is there any reason to have databases -not- owned by the users: Postgres or
root?

Thanks!

--
Jillian

Jillian,

> Is there any reason to have databases -not- owned by the users: Postgres or
> root?

Yes.  If you have a multi-user database, you will want to restrict what the
users can change.  In that case, the user has to be different from the
database owner, as the DB owner can change permissions.

Also, if your database is going to back up a web site, it's a good idea for
the web server to be a user with restricted permissions in case the site gets
hacked.

-Josh Berkus

Jillian,

> How can I change ownership of a db to another user once it's been created?

The best way, really, is to pg_dump the database, drop it, and re-create it
under the new user.

You can change the owner by hacking the pg_database and pg_class tables.
However, this is fraught with potential fatal errors, so I'm not going to
attempt to describe it.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology     josh@agliodbs.com
    and data management solutions     (415) 565-7293
   for law firms, small businesses      fax 621-2533
    and non-profit organizations.     San Francisco

Jillian,

> Can you describe how to properly use pg_dump please?

Sorry, Jillian, you're going to have to read the documentation or buy a
book.  The Wrox Press book covers pg_dump in some depth.
http://techdocs.postgresql.org/techdocs/bookreviews.php

-Josh