PostgreSQL Permissions: Reading dp Output Correctly

This guide explains how to read the output of \dp (and \z) in the PostgreSQL psql command-line tool, so you can accurately interpret access privileges for tables, views, and sequences. You’ll learn the exact format of the ACL (access control list) column, what each symbol and role name means, and how to translate the output into a clear picture of who can do what.

What \dp Shows

When you run \dp or \z in psql (they are synonyms), PostgreSQL lists all tables, views, and sequences in the current database, along with their owners and access privileges. The output has four columns: Schema, Name, Type, and Access privileges. The first three are straightforward—they identify the object. The fourth column, Access privileges, is where most of the confusion lies. If that column is empty, it means the object has no explicit privileges set, which implies that only the owner (and superusers) can access it, subject to the default privileges.

Anatomy of an ACL Entry

Each entry in the Access privileges column is a comma-separated list of ACL (access control list) items. Each item follows this pattern:

role=privileges/grantor

For example:

alice=arwdDxt/alice, bob=r/alice

This means:

• alice has privileges arwdDxt and the grantor is alice (she granted them to herself, or they were set by default).
• bob has privilege r (SELECT), and the grantor is alice (Alice granted it to Bob).

If the role name is empty (e.g., =r/postgres), it refers to the pseudo-role PUBLIC, meaning the privilege is granted to all roles.

Privilege Codes and What They Mean

The letters in the privilege list are abbreviations. Here is the complete mapping for tables, views, and sequences:

• r – SELECT (read)
• w – UPDATE (write)
• a – INSERT (append)
• d – DELETE
• D – TRUNCATE
• x – REFERENCES (ability to create foreign keys)
• t – TRIGGER
• X – EXECUTE (for functions/procedures, but not shown in \dp for tables)
• U – USAGE (for schemas, sequences, etc.)
• C – CREATE (for schemas, databases)
• c – CONNECT (for databases)
• T – TEMPORARY (for databases)
• s – SELECT (for sequences, same as r but displayed as s in some contexts? Actually for sequences, r is SELECT and U is USAGE)

For sequences specifically, the relevant privileges are r (SELECT), w (UPDATE), and U (USAGE). The s letter is not used; it appears in some documentation but is not part of the current privilege set.

Reading the Owner and Grantor

The owner of an object is shown in the Owner column (in older versions) or can be inferred from the ACL. The owner always has all privileges implicitly, even if the ACL is empty. The grantor is the role that issued the GRANT command. This is important because if you revoke a privilege from a grantor, it cascades to all grantees who received the privilege from that grantor.

Common Misconceptions

One common mistake is thinking that an empty ACL means “no one can access it.” Actually, it means “no explicit privileges have been granted,” so only the owner (and superusers) can access it. Another misconception is that the PUBLIC entry (e.g., =r/postgres) means everyone has that privilege. That is true, but it can be revoked with REVOKE ... FROM PUBLIC.

Another pitfall: the * character in the privilege list (e.g., alice=arwdDxt*) indicates that the privilege was granted with the GRANT OPTION, meaning the grantee can grant it to others. This is easy to miss because it appears right after the privilege letters, before the slash.

How to Get More Detail

If you need to see the exact SQL statements that produced the current privileges, use \dp+ to include a description column, or query the system catalogs directly:

SELECT relname, relacl FROM pg_class WHERE relname = 'mytable';

This returns the ACL as an array of aclitem objects, which you can expand with aclexplode() to see each grant separately:

SELECT grantee, privilege_type FROM aclexplode((SELECT relacl FROM pg_class WHERE relname = 'mytable'));

This gives a clear, row-per-privilege view, which is often easier to read than the condensed \dp output.

Frequently Asked Questions

Why does my \dp output show no privileges for a table I created?

When you create a table, PostgreSQL does not automatically create an ACL entry. The table is owned by you, and you have all privileges by virtue of ownership. The ACL column stays empty until you run a GRANT or REVOKE command. So an empty ACL is normal for newly created objects.

What does =r/postgres mean?

The role name before the equals sign is empty, which means the privilege applies to PUBLIC—all roles. So =r/postgres means that PUBLIC has SELECT privilege, and the grantor was the role postgres.

How can I see the privileges for a specific role only?

You can filter the output using grep, or query the system catalogs with aclexplode() and a WHERE clause. For example:

SELECT grantee, privilege_type FROM aclexplode((SELECT relacl FROM pg_class WHERE relname = 'mytable')) WHERE grantee = 'bob';

Does the owner always have all privileges even if the ACL says otherwise?

Yes. The owner has all privileges implicitly, regardless of the ACL. The ACL only shows explicit grants and revocations. You cannot revoke privileges from the owner; you can only change ownership with ALTER TABLE ... OWNER TO.

What is the difference between \dp and \z?

There is no difference. \z is an alias for \dp in psql. Both list tables, views, and sequences with their access privileges.

Why do I see arwdDxt for my table but not for a view?

Views only support SELECT, INSERT, UPDATE, DELETE, and TRUNCATE (in some cases). They do not have TRIGGER or REFERENCES privileges, so the ACL for a view typically shows fewer letters, such as arwd.

Related

• Cut iPhone Battery & Data Use for Apple Sports Live Activities
• Best One Piece Devil Fruits Ranked: Top Picks
• Why Faculty Diversity Lags Behind Students