1

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.

2
  • this answer may help you if you use psql.
    – Sahap Asci
    Jul 12, 2020 at 13:24
  • 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 .
    – user236778
    Jul 13, 2020 at 5:51

1 Answer 1

2

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
4
  • "* 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).
    – user1822
    Jul 13, 2020 at 7:00
  • @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. Jul 13, 2020 at 7:12
  • 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 .
    – user236778
    Jul 13, 2020 at 12:12
  • 1
    That would be \dn+ in psql. Then you see which of the schemas the user has the CREATE permission on. Jul 13, 2020 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.