Re: Create Tables As Specific Role

On Wed, 2011-11-09 at 12:20 -0500, Carlos Mennens wrote:
> I'm installing a calendar application called MRBS. The installation
> instructions require I create a role and database specifically for
> this web application. I'm currenlt logged in as my user account
> 'carlos' which is a superuser.
>
> postgres=# SELECT current_user;
> current_user
> --------------
> carlos
> (1 row)
>
> I've already created the role 'mrbs' for which will own the database
> and all it's tables:
>
> Role name | Attributes | Member of
> -----------+------------------------------------------------+-----------
> carlos | Superuser, Create role, Create DB, Replication | {}
> mrbs | | {}
> postgres | Superuser, Create role, Create DB, Replication | {}
>
> Now I'm creating the database & I've set the owner to the 'mrbs' role:
>
> Name | Owner | Encoding | Collate | Ctype | Access
> privileges
> -----------+----------+----------+-------------+-------------+-----------------------
> calendar | mrbs | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>
> Now I need to have PostgreSQL run a file in my /tmp directory which
> will create the tables. The instructions from the MRBS documentation
> say:
>
> "Create the MRBS tables using the supplied tables.*.sql file:
>
> [PostgreSQL] $ psql -a -f tables.pg.sql mrbs"
>
> If I do the suggested above, my user 'carlos' will own all the tables
> in the database 'calendar' which is owned my 'mrbs'. How can I execute
> the command above but force PostgreSQL to create the files as a
> different user and not 'carlos'?
>

Use the -U command line switch:

psql -a -U mrbs -f tables.pg.sql mrbs

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com