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

sql postgresql privileges
Clint Pachl picture Clint Pachl · Jan 28, 2013 · Viewed 22.1k times · Source

After altering the default privileges on a Postgres database object, how can you view them?

For instance, if you grant all privileges to role_name for all tables created in the schema schema_name:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO role_name;

Answer

Zyphrax picture Zyphrax · Feb 13, 2015

Using the psql(1) interactive terminal

There is another way, at least in recent Postgres versions.
Use the \ddp command

               Default access privileges
     Owner      | Schema |   Type   | Access privileges 
----------------+--------+----------+-------------------
 role_x         |        | function | =X/role_x
 role_x         |        | sequence | 
 role_x         |        | table    | 
 role_x         |        | type     | =U/role_x

Read more about it under the Notes section here:
http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html

Related questions

Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More
Restrict user privileges to one particular database only

PostgreSQL Version 9.1, i am logging into database with default user: "postgres" and my database contains default role "public" list of database i have, 1.database1 2.database2 3.database3 now, i need to create a user "newuser" which will have only privilege to "…

Read More
Grant privileges for a particular database in PostgreSQL

I'm moving from MySQL to PostgreSQL and have hit a wall with user privileges. I am used to assigning a user all privileges to all tables of a database with the following command: # MySQL grant all privileges on mydatabase.* to …

Read More