London Fruit And Herb Tea, Canada

Starting with the use of schemas, users and permissions with Postgres. ScreenCast

Hi all, in recent days we have been developing postgres and play with us to create the basis for the system, due to the complexity represented using multiple tables and the administration of them decided to organize them into patterns, dividing the logical basis using common tables found in a scheme that is visible to all here present a small introduction to the schemes.

To create a schema, just use the command: CREATE SCHEMA

esquema1;

schemes are used in separate databases for giving us a logical choice to take one time running a real system and a test within the same base but separated by a diagram, we also have two different schemas same table names that represent us without fail.

If we create a table in our new scheme we will:

CREATE TABLE esquema1.tabla1 (id_tabla1 integer, name text);

Now to create postgres users will be used: CREATE USER daniel

;

Thus we already have a schema and a user within our database, with which we can practice the use of privileges.
You can assign users to various types of authorization for different parts of the database. For example:

* The authorization of reading.

* push authorization.

* The authorization to upgrade.

* The permit deletion.

Each of these types of authorization is called privilege. You can give each user all these types of privileges, none of them or a combination thereof on specific parts of the database, such as a relationship table view or a schema. The standard includes the privileges SQL select, insert, update and delete.

The SQL data language includes commands to grant and remove privileges. The instruction is used to grant authorizations. The basic form of this command is:

privileges grant-list-on-behalf of relationship or view-to-list or role-

the list of privileges allows the granting of various privileges with a single command, the following statement grants the user daniel grant authorization on the relationship table1 select

esquema1.tabla1 to grant select on daniel

authorization may be granted update on all attributes of the relationship or only on some. If authorization is included in an update education grant, the list of attributes on which the authorization is given in brackets can update just after the update keyword, if you omit the attribute list, the update privilege is granted on all the attributes of the relationship.

The following statement grants the user daniel grant authorization attribute update on the amount of the loan relationship:

grant update (name) on esquema1.tabla1 to daniel

privilege insert can also specify a list of attributes, any inclusion in the list must specify only those attributes and the system assigns the rest of the defaults (if any for them) or set to zero.

public user name refers to all current and future users of the system. Therefore, the privileges granted to public implicitly granted to all current and future users.
now to remove authorization revoke statement is used. grant is very similar to the syntax is:

revoke privileges-list-on-behalf of the relationship and name of the view-from-user list or role-

therefore to remove the previous privileges would be as follows:

esquema1.tabla1 to revoke select on daniel

revoke update (name) on esquema1.tabla1 to daniel

far this introductory article, there is much to even try, I hope to these items on the users, schemas and privileges.

Greetings!

Sources:

Database Fundamentals. fifth edition Silberschatz, Korth, Sudarshan

Official List of postgres

http://www.postgresql.org/