Postgresql - View Schema Privileges

postgresql - view schema privileges

in console util psql:

\dn+

will show you

     Name      |  Owner   |   Access privileges   |      Description   

PostgreSQL: Show all the privileges for a concrete user

table permissions:

select 
 * 
from information_schema.role_table_grants 
where grantee='YOUR_USER'
;

ownership:

select 
   * 
from pg_tables 
where tableowner = 'YOUR_USER'
;

schema permissions:

select  
  r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable
from pg_namespace
join lateral (
  SELECT
    *
  from
    aclexplode(nspacl) as x
) a on true
join pg_user e on a.grantee = e.usesysid
join pg_user r on a.grantor = r.usesysid 
 where e.usename = 'YOUR_USER'
;

PostgreSQL: How to view current grant usage on schema permissions?

The grants are stored in ACLs (=Access Control Lists) for each schema. You can view them by looking a pg_namespace

select nspname, nspacl 
from pg_namespace

The format of the acl is explained in the manual

Schema privileges vs Database privileges in PostgreSQL

What are the privileges granted by that command?

According to the privileges documentation, a GRANT ALL on the DATABASE level encompasses:

• CREATE: allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database.
• CONNECT: Allows the grantee to connect to the database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf).
• TEMPORARY: Allows temporary tables to be created while using the database.

By default, anyone (PUBLIC) has CONNECT and TEMPORARY privileges already, so GRANT ALL PRIVILEGES ON DATABASE db1 TO user1; will only affect the CREATE privilege. Judge yourself whether that's really needed.

Instead of creating the schema and granting all privileges on it to user1, you may want to grant the CREATE privilege on the database, and let the user1 create their schema themselves (so that they become its owner and will thereby get all the privileges on it). It will allow them to also create any other schema then.

postgresql - `Access priviledges` in `List of schemas`

This is well documented in the manual

Table 5.1 shows the one-letter abbreviations that are used for these privilege types in ACL (Access Control List) values. You will see these letters in the output of the psql commands listed below, or when looking at ACL columns of system catalogs.

+============+==============+================================================================================+
| Privilege  | Abbreviation | Applicable Object Types                                                        |
+============+==============+================================================================================+
| SELECT     | r (“read”)   | LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column           |
| INSERT     | a (“append”) | TABLE, table column                                                            |
| UPDATE     | w (“write”)  | LARGE OBJECT, SEQUENCE, TABLE, table column                                    |
| DELETE     | d            | TABLE                                                                          |
| TRUNCATE   | D            | TABLE                                                                          |
| REFERENCES | x            | TABLE, table column                                                            |
| TRIGGER    | t            | TABLE                                                                          |
| CREATE     | C            | DATABASE, SCHEMA, TABLESPACE                                                   |
| CONNECT    | c            | DATABASE                                                                       |
| TEMPORARY  | T            | DATABASE                                                                       |
| EXECUTE    | X            | FUNCTION, PROCEDURE                                                            |
| USAGE      | U            | DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE |
+============+==============+================================================================================+

And then further down:

[...] where each aclitem describes the permissions of one grantee that have been granted by a particular grantor

For example, calvin=r*w/hobbes specifies that the role calvin has the privilege SELECT (r) with grant option (*) as well as the non-grantable privilege UPDATE (w), both granted by the role hobbes

Postgresql 2 users with different permissions on a specific schema

Thank you @JGH, that was it. : the user2 had to give himself access to user1 to schema some_shema.
What i did :

psql
CREATE USER user2 WITH PASSWORD '***';
\c my_database
CREATE SCHEMA some_schema AUTHORIZATION user2;
\c my_database user2
create table some_schema.test(id int);
insert into some_schema.t(100);
GRANT USAGE ON SCHEMA some_schema TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA some_schema TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema GRANT SELECT ON TABLES TO user1;

then :

\c my_database user1
select * from some_schema.t;

=> shows result 100 :)

Display default access privileges for relations, sequences and functions in Postgres

Using a SQL query

SELECT 
  nspname,         -- schema name
  defaclobjtype,   -- object type
  defaclacl        -- default access privileges
FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;

Where the value of defaclobjtype is r = relation (table, view), S = sequence, f = function.

These access privileges are only for newly created objects within the schema namespace.

What does GRANT USAGE ON SCHEMA do exactly?

GRANTs on different objects are separate. GRANTing on a database doesn't GRANT rights to the schema within. Similiarly, GRANTing on a schema doesn't grant rights on the tables within.

If you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you can't access the table.

The rights tests are done in order:

Do you have `USAGE` on the schema? 
    No:  Reject access. 
    Yes: Do you also have the appropriate rights on the table? 
        No:  Reject access. 
        Yes: Check column privileges.

Your confusion may arise from the fact that the public schema has a default GRANT of all rights to the role public, which every user/group is a member of. So everyone already has usage on that schema.

The phrase:

(assuming that the objects' own privilege requirements are also met)

Is saying that you must have USAGE on a schema to use objects within it, but having USAGE on a schema is not by itself sufficient to use the objects within the schema, you must also have rights on the objects themselves.

It's like a directory tree. If you create a directory somedir with file somefile within it then set it so that only your own user can access the directory or the file (mode rwx------ on the dir, mode rw------- on the file) then nobody else can list the directory to see that the file exists.

If you were to grant world-read rights on the file (mode rw-r--r--) but not change the directory permissions it'd make no difference. Nobody could see the file in order to read it, because they don't have the rights to list the directory.

If you instead set rwx-r-xr-x on the directory, setting it so people can list and traverse the directory but not changing the file permissions, people could list the file but could not read it because they'd have no access to the file.

You need to set both permissions for people to actually be able to view the file.

Same thing in Pg. You need both schema USAGE rights and object rights to perform an action on an object, like SELECT from a table.

(The analogy falls down a bit in that PostgreSQL doesn't have row-level security yet, so the user can still "see" that the table exists in the schema by SELECTing from pg_class directly. They can't interact with it in any way, though, so it's just the "list" part that isn't quite the same.)

---

Related Topics