Help me understanding the schema

From: Kiswono Prayogo <kiswono(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Help me understanding the schema
Date: 2009-12-01 00:19:40
Message-ID: 99024f5f0911301619j33cad0f0i44ec88899c4cdf81@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

hi, i've used postgresql for few months now, but i still don't understand
about the schema/database part and it's security consideration..
was there any better documentation (with pictures ^^) than this:
http://www.postgresql.org/docs/current/static/ddl-schemas.html

so, here's my current statement, if someone kind enough to correct me,
please tell me if its right or wrong:
1. one database may contain many schema
2. one schema may contain many objects (tables, functions, etc)
3. multiple user can connect to single database
4. when the schema not defined on queries, it's always search from public
schema
5. tables created on schema x, can be joined using query on other schema
6. schema are related to database not the user, so, if we create a schema,
it shows on others user as well that having access to that database?
7. we can set the privilege of user x for schema y
ie. database aaa contains schema a1, a2 and a3. user xx can query from
schema a1 only, user yy can query from schema a2 only?
8. tables on one schema not related to other schema? it means when i create
table on schema x, it won't show on schema y..
9. so the best practice of database security is:
- create multiple schema, only relate schema to required user, eg. finance
schema can only be accessed by finance people, humanresource schema can only
be accessed by humanresource people..
- do not allow queries/manipulation from users, always use views for
queries/stored-procedure for manipulation/trigger for data integrity or
protection and set the privilege to specific user?
- on web application, set the running user (of the CGI process) to sameuser
login type, so we don't need to store any password on file.

--
Regards,
Kiswono P
GB

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2009-12-01 01:06:10 Re: setting up a client only service
Previous Message Tena Sakai 2009-11-30 21:44:21 setting up a client only service