Database Roles
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 functions) 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.
To create a role use the CREATE ROLE SQL command:
CREATE ROLE name;
To remove an existing role, use the analogous DROP ROLE command:
DROP ROLE name;
For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:
Createuser name;
Dropuser name;
To determine the set of existing roles, examine the pg_roles system catalog, for example
SELECT rolname FROM pg_roles;
Role Attributes
Database role can have a number of attributes that define its privileges and interact with the client authentication system.
| Attributes | Description |
| SUPERUSER | NOSUPERUSER | Determines if the role is a superuser. You must yourself be a superuser to create a new superuser. NOSUPERUSER is the default. |
| CREATEDB | NOCREATEDB | Determines if the role is allowed to create databases.NOCREATEDB is the default. |
| CREATEROLE | NOCREATEROLE | Determines if the role is allowed to create and manage other roles. NOCREATEROLE is the default. |
| INHERIT | NOINHERIT | Determines whether a role inherits the privileges of roles it is a member of. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. INHERIT is the default. |
| LOGIN | NOLOGIN | Determines whether a role is allowed to log in. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges (groups). NOLOGIN is the default. |
| CONNECTION LIMIT connlimit | If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit. |
| PASSWORD ‘password’ | Sets the role’s password. If you do not plan to use password authentication you can omit this option. If no password is specified, the password will be set to null and password authentication will always fail for that user. A null password can optionally be written explicitly as PASSWORD NULL. |
| ENCRYPTED | UNENCRYPTED | Controls whether the password is stored encrypted in the system catalogs. The default behavior is determined by the configuration parameter password_encryption (currently set to md5, for SHA-256 encryption, change this setting to password). If the presented password string is already in encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of |
| VALID UNTIL ‘timestamp’ | Sets a date and time after which the role’s password is no longer valid. If omitted the password will be valid for all time. |
| RESOURCE QUEUE queue_name | Assigns the role to the named resource queue for workload management. Any statement that role issues is then subject to the resource queue’s limits. Note that the RESOURCE QUEUE attribute is not inherited; it must be set on each user-level (LOGIN) role. |
| DENY {deny_interval | deny_point} | Restricts access during an interval, specified by day or day and time |
Role Membership
Revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.
To set up a group role, first create the role:
CREATE ROLE name;
Typically a role being used as a group would not have the LOGIN attribute, though you can set it if you wish.
Once the group role exists, you can add and remove members using the GRANT and REVOKE commands:
GRANT group_role TO role1, … ;
REVOKE group_role FROM role1, … ;
Following are the commands to check the role details.
\du
pg_roles
Parameter file
Parameters to be used in the PostgreSQL are described in the postgresql.conf file in the database cluster. The rows which start with ‘#’ are treated as comment. After executing initdb command, the changed parameters are investigated.
Managing Object Privileges
When an object (table, view, sequence, database, function, language, schema, or tablespace) 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. Greenplum Database supports the following privileges for each object type:
| Object Type | Privileges |
| Tables, Views, Sequences |
SELECT INSERT UPDATE DELETE RULE ALL |
| External Tables |
SELECT RULE ALL |
| Databases |
CONNECT CREATE TEMPORARY | TEMP ALL |
| Functions | EXECUTE |
| Procedural Languages | USAGE |
|
Schemas
|
CREATE USAGE ALL |
|
Custom Protocol
|
SELECT INSERT UPDATE DELETE RULE ALL |
Privileges must be granted for each object individually. For example, granting ALL on a database does not grant full access to the objects within that database. It only grants all of the database-level privileges (CONNECT, CREATE, TEMPORARY) to the database itself.