Re: Help me understanding the schema - Mailing list pgsql-admin

On Mon, Nov 30, 2009 at 5:19 PM, Kiswono Prayogo <kiswono@gmail.com> wrote:
> 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
Yes, yes, yes.

> 4. when the schema not defined on queries, it's always search from public
> schema
No, but close.  It uses the schemas as listed, in order, in
search_path, which defaults to $user, public.

> 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?
yes, yes.

> 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?
No, perms on schemas control schema actions like create.  perms on
tables control user access.

> 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..
yes.

> 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..
Yes, but the perms are set per table, not schema.  Use roles, so that
you grant access to finance tables for update to the finance role,
then grant that role to individual users as needed.  Revoke that role
from users to revoke access.

>  - 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?
Yes and no. This doesn't make things any safer really.  However, using
functions and triggers can allow you to encapsulate business logic in
such a way as to make life easier for the user as well as you.

>  - 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.

This is only reliable if you have identd and are running everything on
one server.