All-Inclusive Guide: How to Check and Manage Postgres User Privileges

PostgreSQL offers granular control of user privileges at multiple levels – from entire servers and databases down to tables and columns. As per reports, over 50% of PostgreSQL deployments have improper privilege configurations resulting in serious performance and security issues. Hence a strong understanding of managing privileges is crucial for any Postgres administrator.

Granular Privilege System

Postgres offers a robust privilege system to control user access to databases, schemas, tables, columns and various other objects.

Some key privileges are:

• CONNECT – Allows connecting to Postgres server and a specific database
• CREATE – Allows creating new databases, schemas, tables etc.
• SELECT – Reading data from tables/views
• INSERT – To insert new rows in tables
• UPDATE – For updating existing rows
• DELETE – Deleting rows from tables
• TRUNCATE – Deleting all table rows
• REFERENCES – To create foreign keys linking tables
• TRIGGER – Creating/deleting triggers on tables

These can be granted on different database objects:

Object	Applicable Privileges
Server	CONNECT
Database	CONNECT, CREATE, TEMPORARY
Schemas/Tables	CREATE, SELECT, INSERT, UPDATE, DELETE,TRUNCATE, REFERENCES, TRIGGER
Columns	SELECT, INSERT, UPDATE, REFERENCES
Functions	EXECUTE

For example, the SELECT privilege can be granted on specific tables/columns allowing the user to only view particular data.

Such fine-grained access control enables administrators to enforce the principle of least privilege – i.e provide users only the minimum permissions needed for their workflow. This enhances security and performance.

Checking Privileges

As an administrator, you need visibility into what privileges are granted to each user and role across different databases, schemas, tables etc. Postgres offers various system catalogs and commands for this.

Note: All commands below should be run by an admin user like postgres

Check All Privileges for a User

To check all privileges granted to a user ‘john‘ across the PostgreSQL instance:

SELECT * FROM information_schema.role_table_grants  
WHERE grantee=‘john‘;

This covers privileges on various database objects like tables, sequences, functions.

Partial sample output:

grantor	grantee	table_catalog	table_schema	table_name	privilege_type
postgres	john	postgres	public	products	INSERT

Check Privileges on Specific Table

To see what permissions a user has on a particular table like ‘products‘:

SELECT table_schema, table_name, privilege_type  
FROM information_schema.role_table_grants
WHERE table_name=‘products‘ AND grantee=‘john‘; 

Output:

table_schema	table_name	privilege_type
public	products	INSERT
public	products	SELECT

This details exact privileges granted on that table.

Similarly, you can check column level privileges too.

Check Privileges on a Database

To see what all privileges a user has on a specific database:

SELECT * FROM information_schema.schema_privileges  
WHERE grantee=‘john‘ AND table_schema=‘products_db‘;

Output:

grantor	grantee	schema_name	privilege_type
postgres	john	products_db	USAGE

This covers privileges like CONNECT, CREATE, TEMPORARY on the database.

Using PgAdmin

You can also leverage PgAdmin‘s graphical interface:

1. Login to PgAdmin
2. Navigate to the database/schema/table
3. Right click and select Properties
4. Go to the Privileges tab

This shows all users and their granted/revoked privileges on that object.

PgAdmin privileges tab (Image credits: EnterpriseDB)

The \z Meta-command

The \z meta-command in psql also displays privileges:

\z mydatabase

Partial sample output:

Name	Privileges
john	CREATE
mary	CONNECT, TEMPORARY

This covers all privileges granted on that database.

Thus with the above commands, you can easily audit and view user privileges at multiple levels in PostgreSQL. Lets look at troubleshooting next.

Troubleshooting Privilege Issues

While managing user privileges, you may encounter scenarios like:

1. User complains of permission errors while accessing specific data
2. A user is able to access/modify data that they should not have access
3. Admin queries suddenly start failing with permission errors after a change

Here is an overview of potential causes and solutions for such privilege issues:

Issue	Potential Causes	Solutions
Permission denied errors	Privileges not granted properly	Use GRANT command to assign required permissions
	Privileges have been revoked	Check if a REVOKE command removed access
Accessing unauthorized data	Overly permissive privilege grants	Revoke unnecessary privileges using REVOKE command
	Privilege escalation attacks	Review activity logs, rotate passwords
Admin queries failing	Privileges revoked/reduced for admin	Check security logs
	Object owner changed	Reassign ownership or reset permissions

Let‘s analyze some real-world examples of such errors.

1. Permission denied to access products table

User John has SELECT privilege on table products. But executing a query gives a permission error:

SELECT * FROM products;

ERROR: permission denied for relation products

Checking privileges shows that John has SELECT granted.

Investigating further reveals that recently privileges were revoked from public schema which products table is part of. Hence John lost access too.

Solution: Running GRANT SELECT ON public.products fixes it.

2. Unauthorized data access

User Mary has only SELECT permission on customers table. But she is able to INSERT new rows illegally.

Checks show Mary has no INSERT privilege on customers table but has INSERT on orders table in the same schema. Because the ownership and permissions of orders table are incorrectly configured, Mary is able to exploit this to also insert into customers illegally.

Solution: Revoked INSERT on orders table for Mary fixes issue. Ownership/privileges were incorrectly set – they need review.

Thus a deep investigation covering multiple tables, schemas is sometimes needed to troubleshoot permission errors in PostgreSQL.

Granting and Revoking Privileges

Once privileges have been audited/checked for users, DDL commands like GRANT and REVOKE can be used for modifying permissions.

Granting Privileges

To grant SELECT and INSERT privileges to user John on products table:

GRANT SELECT, INSERT ON public.products TO john;

Additional options like WITH GRANT OPTION allows the grantee to grant this privilege to others.

To grant all privileges on the products database to admin:

GRANT ALL PRIVILEGES ON DATABASE products TO admin; 

Various other permutations like granting privileges on schemas, functions etc. is possible.

Note: By default, only the object owner or superusers can grant privileges to other roles.

Revoking Privileges

If user privileges need to be downgraded or revoked, the REVOKE command is used:

Revoke all privileges of user Mary on products database:

REVOKE ALL PRIVILEGES ON DATABASE products FROM mary; 

This revokes all permissions like connect, create etc. on that database from Mary.

REVOKE can similarly be used on specific tables, schemas etc.

Key Takeaways

• GRANT and REVOKE enables controlling user privileges dynamically
• Grant only minimum required permissions
• Periodically review privileges to minimize attack surface

Now that you have a solid understanding of managing Postgres privileges, let‘s shift focus on database roles.

Postgres User Roles

Instead of granting privileges user-by-user, roles provide a more effective mechanism.

A role groups many privileges. It can be assigned to users thereby inheriting all privileges defined for that role.

For example an ‘analyst’ role can have SELECT permissions on various tables related to analytics. By adding users like John, Mary to this role, they automatically get SELECT access to required tables.

Check Roles for a User

We can query pg_roles view to list roles that a user is part of:

SELECT * FROM pg_roles WHERE rolname=‘john‘;

This covers if john is a:

• Normal role
• Superuser
• Replication role
• Inherited role

Information like encrypted password, if rolreplication is set and other flags are visible.

Check Privileges per Role

To check what privileges are available to a specific role like analyst_role:

SELECT * FROM information_schema.role_table_grants   
WHERE grantee=‘analyst_role‘;

This displays all table, schema, database privileges inherited by users attached to that role.

User Role Membership

To check which all users are part of a specific role, say analyst_role:

SELECT * FROM pg_roles WHERE rolname IN  
   (SELECT member FROM pg_auth_members WHERE roleid IN 
        (SELECT oid FROM pg_roles WHERE rolname=‘analyst_role‘)); 

This lists all direct and indirect members belonging to that role.

Managing Roles

Roles can be created, modified and deleted as well.

For example:

CREATE ROLE analyst;

GRANT SELECT ON public.clients TO analyst;

GRANT analyst TO john, mary, joe;

ALTER ROLE analyst_role RENAME TO analyst;

This leverages roles for easier privilege management in PostgreSQL.

Security Best Practices

Managing privileges properly is crucial for security. Here are key best practices:

1. Least privilege – Grant only minimum absolutely needed permissions
2. Do not use PUBLIC role – Avoid granting permissions to special PUBLIC role
3. Revoke unnecessary permissions using REVOKE
4. Review privileges periodically e.g. every month
5. Analyze logs, system catalogs to detect privilege issues
6. Use roles to group privileges instead of user-by-user grants

Adopting these can minimize attack surface by hardening the database privileges architecture.

Performance Implications

Excessive user privileges can severely impact PostgreSQL performance too due to:

1. Shared buffers – More reads/writes causing contention
2. WAL generation – Increased data changes resulting in heavier transaction logs
3. Locking – Unrequired locking of rows/tables by extraneous queries slowing down other users/applications.
4. Vacuuming – Frequent updates/deletes triggering more auto-vacuum activity

Metrics like database IO utilization, lock percentages and transaction log volume should be monitored to catch any deviation from baseline specifically after user permission changes.

Auditing Tools

Dedicated tools like OmniPITR help auditing and reporting on Postgres user privileges through features like:

• Graphical visualization of all privilege grants schema- and system-wide
  -Notifications on privilege changes or new role creation
• Comparison reports to detect privilege inconsistencies
• Periodic privilege assessment through automated audits
• Monitoring usage of superuser or other sensitive roles

Such tools can provide deeper visibility beyond native SQL commands over managing privileges securely.

Privilege Management: PostgreSQL vs Other Databases

MySQL/MariaDB: Supports similar privilege system with grants on databases, tables etc. More limited than Postgres especially around row-level security and column privileges. Privilege auditing is more complex and manual.

Microsoft SQL Server: Relatively granular privileges supported especially through securables. However, no column-level grants possible. Hierarchical inheritance of permissions causes issues.

Oracle: Also offers fine-grained access control. However grants/procedures are more complex and less intuitive. No consistent security concepts across different Oracle database technologies.

Thus PostgreSQL offers one of the most advanced and secure privilege management constructs that administrators can fully utilize.

Conclusion

PostgreSQL provides a robust privilege system catering from instance-level all through columns and functions. System catalogs, useful SQL commands and PgAdmin facilitate inspecting granted privileges at varying granularities. By leveraging these for continuous auditing along with features like roles, production databases can be secured while unlocking performance gains too.