Help me understanding the schema - Mailing list pgsql-admin

From Kiswono Prayogo
Subject Help me understanding the schema
Date
Msg-id 99024f5f0911301619j33cad0f0i44ec88899c4cdf81@mail.gmail.com
Whole thread Raw
Responses Re: Help me understanding the schema  (Scott Marlowe <scott.marlowe@gmail.com>)
List 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

pgsql-admin by date:

Previous
From: "Marty Dinniss"
Date:
Subject: Windows 7
Next
From: Scott Marlowe
Date:
Subject: Re: Replication solution