Database roles and access control

Database Design

Lis Sulmont

Curriculum Manager

Granting and revoking access to a view

GRANT privilege(s) or REVOKE privilege(s)

ON object

TO role or FROM role

  • Privileges: SELECT, INSERT, UPDATE, DELETE, etc.
  • Objects: table, view, schema, etc.
  • Roles: a database user or a group of database users
GRANT UPDATE ON ratings TO PUBLIC; 
REVOKE INSERT ON films FROM db_user;
Database Design

Database roles

  • Manage database access permissions
  • A database role is an entity that contains information that:
    • Define the role's privileges
      • Can you login?
      • Can you create databases?
      • Can you write to tables?
    • Interact with the client authentication system
      • Password
  • Roles can be assigned to one or more users
  • Roles are global across a database cluster installation
Database Design

Create a role

  • Empty role
CREATE ROLE data_analyst;
  • Roles with some attributes set
CREATE ROLE intern WITH PASSWORD 'PasswordForIntern' VALID UNTIL '2020-01-01';

CREATE ROLE admin CREATEDB;

ALTER ROLE admin CREATEROLE;
1 http://bit.ly/postgresql_attributes
Database Design

GRANT and REVOKE privileges from roles

GRANT UPDATE ON ratings TO data_analyst;

REVOKE UPDATE ON ratings FROM data_analyst;

The available privileges in PostgreSQL are:

  • SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE
1 http://bit.ly/postgresql_privileges
Database Design

Users and groups (are both roles)

  • A role is an entity that can function as a user and/or a group
    • User roles
    • Group roles

Roles graphic where an outer circle is a group role and three inner circles within the outer circle are user roles

Database Design

Users and groups (are both roles)

Group role

CREATE ROLE data_analyst;

User role

CREATE ROLE intern WITH PASSWORD 'PasswordForIntern' VALID UNTIL '2020-01-01';
Database Design

Users and groups (are both roles)

Group role

CREATE ROLE data_analyst;

User role

CREATE ROLE alex WITH PASSWORD 'PasswordForIntern' VALID UNTIL '2020-01-01';

GRANT data_analyst TO alex;

REVOKE data_analyst FROM alex;
Database Design

Common PostgreSQL roles

Role Allowed access
pg_read_all_settings Read all configuration variables, even those normally visible only to superusers.
pg_read_all_stats Read all pg_stat_* views and use various statistics related extensions, even those normally visible only to superusers.
pg_signal_backend Send signals to other backends (eg: cancel query, terminate).
More... More...
1 http://bit.ly/default_roles_postgresql
Database Design

Benefits and pitfalls of roles

Benefits

  • Roles live on after users are deleted
  • Roles can be created before user accounts
  • Save DBAs time

Pitfalls

  • Sometimes a role gives a specific user too much access
    • You need to pay attention
Database Design

Let's practice!

Database Design

Preparing Video For Download...