Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create objects(table etc) to be stored in specific schema

Create objects(table etc) to be stored in specific schema

From
Sam Turnbull
Date:
Hi Gurus,

I am still picking up skill on postgres and have received a specific requirement.

Create database : DB
Create user: USERA
Schema : SCHEMAC
User USERA should have all the access to create objects/execute in its own schema.

1. I have created database DB with owner as DB(created one user as DB).
2. created user USERA using $createuser command, with role as USERA, no create database/super users  etc are not given
3. Created Schema as - command -"Create schema SCHEMAC authorization USERA"

But objects are getting created in public,
I understand by default its the feature and is looking for assistance in how can I redirect to SCHEMAC.

Any other better way of doing is also welcome.

Thanks
Naresh


Re: Create objects(table etc) to be stored in specific schema

From
Payal Singh
Date:
you can either alter the default search path - http://www.postgresql.org/docs/9.3/static/runtime-config-client.html

or you can schema qualify your table/objects each time. For instance SCHEMAC.OBJECT

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253

On Thu, Oct 15, 2015 at 8:10 AM, Sam Turnbull <samturnbull73@gmail.com> wrote:
Hi Gurus,

I am still picking up skill on postgres and have received a specific requirement.

Create database : DB
Create user: USERA
Schema : SCHEMAC
User USERA should have all the access to create objects/execute in its own schema.

1. I have created database DB with owner as DB(created one user as DB).
2. created user USERA using $createuser command, with role as USERA, no create database/super users  etc are not given
3. Created Schema as - command -"Create schema SCHEMAC authorization USERA"

But objects are getting created in public,
I understand by default its the feature and is looking for assistance in how can I redirect to SCHEMAC.

Any other better way of doing is also welcome.

Thanks
Naresh