Privileges_1.htm
PostgreSQL  manages users privileges thorough roles, and this is managed by a privilege system, where users are granted different privileges for the tables, schemas, functions or other database objects.
An object, since it's creation belongs to an owner, this owner will grant other roles to use it.

There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

Security options: role , user,

To create new users, you must either be a superuser or have the createrole or createuser privilege.

 

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';
 
Intro Privileges:

A privilege is a right to execute a particular type of SQL statement or to access another user's object. An user is granted a role which is decided by a super user like "postgres".

  • CREATE ROLE Doe WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN;
  • CREATE ROLE Scott WITH SUPERUSER;

Version 9.0 added a capability to GRANT or REVOKE privileges on all objects of a certain
kind in a specific schema:
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO Mary;
You still need to grant the privileges on the schema itself in a separate grant statement.

The user by default "porstgres" is a superuser.

The user created, "manas237",was  other roles as shown below.

However, the user "manas237' was not a superuser.

Dependencies :

Superuser, be default govern tablespaces.
In Unix  OS:
  • manas16@hvost:~$ createuser manas1234
  • Shall the new role be a superuser? (y/n) n
  • Shall the new role be allowed to create databases? (y/n) y
  • Shall the new role be allowed to create more new roles? (y/n) n
  • manas16@hvost:~$ createuser tim
    Shall the new role be a superuser? (y/n) y