Privileges_1.htm
Security options: role , user,

Super-Users:  is an account created during a new installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using pgAdminIII, or by issuing an SQL query such as:

ALTER USER postgres WITH PASSWORD 'VeryVerySecret';

Introduction to Privileges

privilege is a right to execute a particular type of SQL statement or to access another user's object. Some examples of privileges include the right to:

  • Connect to the database (create a session)
  • Create a table
  • Select rows from another user's table
  • Execute another user's stored procedure

You grant privileges to users so these users can accomplish tasks required for their jobs. You should grant a privilege only to a user who requires that privilege to accomplish the necessary work. Excessive granting of unnecessary privileges can compromise security. A user can receive a privilege in two different ways:

  • You can grant privileges to users explicitly. For example, you can explicitly grant to user SCOTT the privilege to insert records into the employees table.
  • You can also grant privileges to a role (a named group of privileges), and then grant the role to one or more users. For example, you can grant the privileges to select, insert, update, and delete records from the employees table to the role named clerk, which in turn you can grant to users scott and brian.

Because roles allow for easier and better management of privileges, you should normally grant privileges to roles and not to specific users.

 
Ref : http://www.postgresql.org/docs/9.2/static/user-manag.html

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

The concept of roles subsumes the concepts of "users" and "groups". In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.

 
http://www.postgresql.org/docs/9.2/static/ddl-priv.html

 Super-Users:  is an account created during a new installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using pgAdminIII, or by issuing an SQL query such as:

ALTER USER postgres WITH PASSWORD 'new password1234';


Chapter 5.6. Privileges

When an object is created, it is assigned an owner. The owner is normally the role that executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object. To allow other roles to use it, privileges must be granted.

There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE. The privileges applicable to a particular object vary depending on the object's type (table, function, etc). For complete information on the different types of privileges supported by PostgreSQL, refer to the GRANT reference page. The following sections and chapters will also show you how those privileges are used.

The right to modify or destroy an object is always the privilege of the owner only.

An object can be assigned to a new owner with an ALTER command of the appropriate kind for the object, e.g. ALTER TABLE. Superusers can always do this; ordinary roles can only do it if they are both the current owner of the object (or a member of the owning role) and a member of the new owning role.

To assign privileges, the GRANT command is used. For example, if joe is an existing user, and accounts is an existing table, the privilege to update the table can be granted with:

GRANT UPDATE ON accounts TO joe;

Writing ALL in place of a specific privilege grants all privileges that are relevant for the object type.

The special "user" name PUBLIC can be used to grant a privilege to every user on the system. Also, "group" roles can be set up to help manage privileges when there are many users of a database — for details see Chapter 20.

To revoke a privilege, use the fittingly named REVOKE command:

REVOKE ALL ON accounts FROM PUBLIC;

The special privileges of the object owner (i.e., the right to do DROP, GRANT, REVOKE, etc.) are always implicit in being the owner, and cannot be granted or revoked. But the object owner can choose to revoke his own ordinary privileges, for example to make a table read-only for himself as well as others.

Ordinarily, only the object's owner (or a superuser) can grant or revoke privileges on an object. However, it is possible to grant a privilege "with grant option", which gives the recipient the right to grant it in turn to others. If the grant option is subsequently revoked then all who received the privilege from that recipient (directly or through a chain of grants) will lose the privilege. For details see the GRANT and REVOKE reference pages.