PostgreSQL Database Roles

Roles are used to manage database access permissions. Roles can own database objects and can assign privileges on those objects to other roles to control who has access to which objects. Database roles are global across a database cluster installation(not per individual database).

Create PostgreSQL Database Roles:

Syntax:

CREATE ROLE name [ [ WITH ] option [ … ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ’password’
| VALID UNTIL ’timestamp’
| IN ROLE role_name [, …]
| IN GROUP role_name [, …]
| ROLE role_name [, …]
| ADMIN role_name [, …]
| USER role_name [, …]
| SYSID uid

Example:

CREATE ROLE k2admin;

k2info=# CREATE ROLE k2admin;
CREATE ROLE

Create Role with password.

CREATE ROLE k2admin1 with password ‘MyPassword123’;

Create a role with password valid until specific period.

CREATE ROLE k2admin1 with password ‘MyPassword123′ valid until ’01-01-2018’;

To get the list roles available in PostgreSQL server.

k2info=# select rolname from pg_roles;
rolname
————–
enterprisedb
k2admin
(2 rows)

select rolname from pg_roles;

\du

PostgreSQL Database Roles

Powered by k2schools