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.

• Sahap Asci: this answer may help you if you use psql.
• user236778: That was useful for knowing previleges on table by a user . But how to know whether a user have permission to create a sequence, views and function ? One obvious way is to try creating them and check . But I don't have write permission to do that .

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

• user1822: "* is not attached to the user*" - hmm, as you grant the create privilege on a schema to a user (or role), I would say it is (somehow) "tied" to a user (the grantee).
• Laurenz Albe: @a_horse_with_no_name I was thinking literally: the privileges are ACL settings on objects, not properties of the user. I'll try to pick a better way to say it.
• user236778: Yes , you guessed it correctly, I am a oracle user but now learning postgres as well . We are migrating the data from oracle to postgres . So faced some issue with grants after migrating oracle data to postgres . So, do we have any query or psql command for knowing that whether a user has rights to create a sequence or not ? Or in postgres there is no such grants for sequence exist .
• Laurenz Albe: That would be \dn+ in psql. Then you see which of the schemas the user has the CREATE permission on.