I agree with the sentiment that IAM in PostgreSQL is complex.
What makes it complex is that there are 3 layers of objects (Database, Schema, Tables) and also implicit grants given to DB object owners
To be able to select from a table you 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 we need to understand acl entries of this format
`grantee=privilege-abbreviation[]/grantor:`
* 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](https://www.postgresql.org/docs/current/ddl-priv.html)
e.g. in the following 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)
`=r/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.
What makes it complex is that there are 3 layers of objects (Database, Schema, Tables) and also implicit grants given to DB object owners
To be able to select from a table you 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 we need to understand acl entries of this format
`grantee=privilege-abbreviation[]/grantor:`
* 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](https://www.postgresql.org/docs/current/ddl-priv.html)
e.g. in the following 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)
`=r/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.