Managing database security requires knowing exactly what your users can access. Checking permissions in PostgreSQL helps you monitor data safety and identify roles with excessive privileges.
This guide explains four practical methods for verification. Whether you need to audit global role attributes or specific table-level access, PostgreSQL provides built-in meta-commands and functions that reveal accurate security information without requiring external software.
You will use terminal commands and SQL queries that work across all PostgreSQL environments. Each method serves specific needs, from quick global checks to detailed object analysis. Master these approaches to control your database security effectively.
Table of Contents
Method 1: Using the \du Command
The \du command reveals global role attributes. It calculates the high-level capabilities your users possess across the entire database cluster rather than just a single database.
Run this command within the psql terminal: \du username
The output displays essential attributes such as Superuser status, Create role, Create DB, and Replication. Using this ensures you understand who can bypass all permission checks or manage other roles. This is the quickest way to see an aggregate view of a user’s administrative power.
Method 2: Applying the \dp Command
The \dp command (or \z) lists specific object privileges. It provides quick access information for tables, views, and sequences by displaying an Access Control List (ACL).
Use this syntax for checking table permissions: \dp table_name
The output displays an “Access privileges” column showing exactly who has rights like SELECT (r), INSERT (a), or UPDATE (w). For a deeper dive into decoding these specific strings, see our guide on Reading PostgreSQL Permissions Correctly: A Guide to \dp Output. This approach reveals the explicit grants made to specific roles.
Method 3: Employing Inquiry Functions
PostgreSQL inquiry functions deliver programmatic confirmation of rights. They show whether a user has a specific privilege like CONNECT or USAGE for a particular object.
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
Run a SELECT statement to view precise boolean results: SELECT has_table_privilege('username', 'table_name', 'select');
This returns t (true) or f (false). This method works ideally for scripts or applications that need to verify rights before execution. You can find more examples of this in our post on How to check if a user can access or modify a table in PostgreSQL.
Method 4: Querying the Information Schema
The information_schema tables locate permissions matching standard SQL criteria. You can search by privilege type across your entire schema to find every table a user can interact with.
To locate all tables a user can read: SELECT table_name FROM information_schema.table_privileges WHERE grantee = 'username' AND privilege_type = 'SELECT';
This proves valuable when hunting for forgotten permissions that might pose a security risk. If you discover a user has too much access, you should consult the PostgreSQL GRANT Statement: A Complete Guide to learn how to revoke those rights.
Understanding Unified Roles vs. Privileges
PostgreSQL stores data rights in a unified system where “users” and “groups” are both simply Roles.
- Apparent Power: The
\ducommand shows role-level attributes, like being a superuser who bypasses all checks. - Actual Rights: The
\dpcommand reveals the specific interaction rights with objects. - Inheritance: Roles can inherit privileges from other roles they are members of.
This difference matters for security management, especially when navigating the Understanding PostgreSQL Access Control: Login Roles vs. Connect Privileges architecture.
Step-by-Step Process
- Launch your terminal and connect to your database using
psql -U username. - Navigate to the correct database context, as roles are global but object privileges are database-specific.
- Execute \du to check global user status and administrative attributes.
- Review the ACL using
\dpfor specific tables to see the grantee list. - Use Inquiry Functions like
has_database_privilegewhen you need a definitive yes/no answer for connection rights.
Practical Tips
- Combine \dn+ and \dp: Use
\dn+to see if a user is blocked at the schema level despite having table rights. - Check “PUBLIC”: Always check rights given to the
PUBLICrole, as these apply to every user in the system. - Case Sensitivity: Role and table names are case-sensitive if they were created with double quotes.
- Search Path: For safety, use What is search_path in PostgreSQL and How to Configure it to verify which schema objects are actually visible to your user.
Summary Table
| Task | Recommended Command |
|---|---|
| Check global roles | \du [username] |
| Check table rights | \dp [table_name] |
| List DB connection rights | \l |
| Boolean table check | SELECT has_table_privilege(...) |
| Search all grants | Query information_schema.table_privileges |
FAQs
What’s the quickest way to check user permissions in Postgres? Use \du for global role attributes and \dp table_name for object-specific rights. These commands display formatted tables with human-readable attributes.
Why do \du and \dp show different information? The \du command shows role-level attributes (like administrative status), while \dp shows object-level grants (like who can specifically read a certain table).
How do I check if a user can connect to a database? Use the command \l to see connection privileges or run SELECT has_database_privilege('user', 'db', 'CONNECT');.
Can I check permissions for a group? Yes. Since groups are just roles, you can use \du group_name or the pg_has_role() function to see membership and inherited rights.
What does the ‘r’ mean in the \dp output? The letter ‘r’ stands for the SELECT (read) privilege. Other common letters include ‘a’ (INSERT/append), ‘w’ (UPDATE/write), and ‘d’ (DELETE).
