Show all privileges given to a schema or user in postgres

Question

I want to know what are the privileges a given schema or user has in Postgres. Like whether it can create a table, view, function, sequences or not.

I found one query but it showed privileges on table only. Please suggest any appropriate query.

Answer 1

In PostgreSQL, the right to create tables, views, functions, operators, data types and the like is not properties of the user (or “role” in PostgreSQL).

You manage this with privileges on schemas: if there is a schema where the user has the CREATE privilege, the user can create any object he or she wishes in that schema. If you want to keep a PostgreSQL user from creating objects, you don't give them CREATE on any schema. Since the public schema by default gives CREATE to everybody, you'd have to

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

which is a good idea anyway for security reasons.

There are only few privileges that are tied to the user, because they pertain to global objects or no objects at all:

• CREATEDB: the right to run CREATE DATABASE
• CREATEROLES: the right to run CREATE ROLE
• REPLICATION: the right to establish a replication connection; that is for example needed for pg_basebackup