Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create Tables As Specific Role

Create Tables As Specific Role

From
Carlos Mennens
Date:
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'?

Re: Create Tables As Specific Role

From
Guillaume Lelarge
Date:
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