FrançaisGet started

Users and roles

CREATE ROLE and CREATE USER, login roles and group roles, the LOGIN, SUPERUSER and CREATEDB attributes, membership and inheritance, the owner of an object and the principle of least privilege.

  • 25 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • Role: what is it?

    Show the answer

    An entity of the database server that holds rights and may own objects; it stands for a person, a program or a group.

    PostgreSQL has a single notion: CREATE USER and CREATE GROUP are only spellings of CREATE ROLE.

  • With these statements, and knowing that the lecture group has the SELECT privilege on client, what can alice do?

    ```sql
    CREATE ROLE lecture;
    CREATE ROLE compta;
    CREATE USER alice;
    GRANT lecture TO compta;
    GRANT compta TO alice;
    `

    • Read the client table, through compta then lecture
    • Nothing, since a group cannot be a member of another group
    • Nothing, since alice was not granted SELECT herself
    • Read and change client, since compta is an accounting group
    Show the answer

    Read the client table, through compta then lecture

    Membership is passed on: alice is a member of compta, compta of lecture, and each member inherits the group's rights. Nothing here grants writing.

  • In PostgreSQL, CREATE USER is the same as CREATE ROLE with the … attribute.

    Show the answer

    In PostgreSQL, CREATE USER is the same as CREATE ROLE with the LOGIN attribute.

    A role created by plain CREATE ROLE has NOLOGIN: it cannot connect, which suits a group.

  • User (CREATE USER): what is it?

    Show the answer

    A role that is allowed to open a connection to the database.

    CREATE USER alice is the same as CREATE ROLE alice LOGIN: only the LOGIN attribute tells the two spellings apart.

  • A flaw in the website let a visitor drop tables. The site connects to the database as a superuser. Which fix limits the damage in future?

    • A longer password, changed every month, for the superuser
    • Make the site the owner of every table and of the whole schema
    • A REVOKE on every table, addressed to the server superuser
    • A role dedicated to the site, limited to SELECT, INSERT, UPDATE and DELETE
    Show the answer

    A role dedicated to the site, limited to SELECT, INSERT, UPDATE and DELETE

    The flaw remains possible, but the role can no longer drop tables. A password does not change what the role can do, an owner can drop its table, and no REVOKE takes anything from a superuser.

Study this deck