Question 383981 in PostgreSQL Question posted in
The official documentation can be found here.

How do PostgreSQL permissions work? Permissions needed to select from table

In order to SELECT from table I need:

  • CONNECT on the Database
  • USAGE on the Schema (Given implicitly to schema owner)
  • SELECT on the Table (Given implicitly to table owner)

Is this correct?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    PostgreSQL has 3 layers of objects (Database, Schema, objects e.g. Tables) and also implicit grants given to DB object owners

    That means we need

    • CONNECT on the Database
    • USAGE on the Schema (Given implicitly to schema owner)
    • SELECT on the Table (Given implicitly to table owner)

    To see these privileges:

    • Use l+ to see privileges of Database
    • Use dn+ to see privileges of Schemas
    • Use dp+ to see privileges of Tables

    Privileges are seen here

    enter image description here

    We also need to understand acl entries of the following format:

    grantee=privilege-abbreviation[*]/grantor

    e.g. in the following dp+ acl example user has been given all permissions by postgres role

    user=arwdDxt/postgres

    If the “grantee” column is empty for a given object, it means the object has default owner privileges (all privileges) or it can mean privileges to PUBLIC role (every role that exists)

    =UC/postgres

    Also it's confusing when Public schema is used. You have CREATE permission on schema so when the tables are created with the same user you select data with and you have owner permissions out of the box.


  2. 0

    That’s correct and complete; the only exception is that there is no USAGE privilege on databases, so you don’t need that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search