Securing your database requires more than just creating accounts; you must define exactly what each user can see and modify. In PostgreSQL, access control is managed by granting privileges to roles (which encompass both individual users and groups).
This guide explores practical methods to assign specific table rights using the SQL GRANT command.
Table of Contents
Method 1: Granting Standard Table-Level Privileges
The most direct way to manage permissions is through table-level grants. This method allows you to specify exactly which SQL operations a user is permitted to perform on a whole table.
Core Privileges Include:
SELECT: Allows reading data and usingCOPY TO.INSERT: Allows adding new rows to the table.UPDATE: Permits modifying existing data.DELETE: Grants the right to remove rows.TRUNCATE: Allows emptying the entire table instantly.
Basic Syntax: GRANT SELECT, INSERT ON table_name TO username;
Executing this command ensures the user can query and add data but cannot delete it. If you need to grant every possible permission at once, you can use the ALL keyword (e.g., GRANT ALL ON orders TO dev_lead;). For a comprehensive breakdown of these options, refer to the PostgreSQL GRANT Statement: A Complete Guide.
Method 2: Assigning Granular Column-Level Permissions
Sometimes, granting access to an entire table is too risky. If a table contains sensitive fields like salaries or personal IDs, you can grant permissions on a per-column basis.
Syntax for Column-Level Grants: GRANT SELECT (id, product_name), UPDATE (price) ON products TO sales_staff;
In this example, the sales_staff can view product names and update prices but remain blocked from seeing other sensitive columns in the same table. This level of precision is a cornerstone of modern data security. To verify which columns your users can currently access, see our guide on How to view access privileges in PostgreSQL.
Method 3: Managing Rights Through Role Inheritance
Instead of manually assigning permissions to every individual user, you can grant privileges to a “group role” and let users inherit those rights.
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
Step-by-Step Group Setup:
- Create a group role (usually with
NOLOGIN):CREATE ROLE analytics_grp;. - Grant table rights to the group:
GRANT SELECT ON sales_data TO analytics_grp;. - Add users to the group:
GRANT analytics_grp TO alice;.
By default, users like “alice” will automatically inherit all permissions granted to analytics_grp. This simplifies administration as you only need to update the group’s permissions once for it to reflect across all members. To understand the difference between active login roles and inherited group rights, explore Understanding PostgreSQL Access Control: Login Roles vs. Connect Privileges.
The Prerequisite: Schema “USAGE” Rights
A common pitfall is granting table privileges but forgetting schema-level access. In PostgreSQL, a user cannot “look up” or access a table unless they have the USAGE privilege on the schema that contains it.
Required Command: GRANT USAGE ON SCHEMA public TO username;
Without this, the user will receive a “permission denied” error even if they technically have SELECT rights on the table itself. If your users are still struggling to find their tables after you’ve granted access, you may need to check What is search_path in PostgreSQL and How to Configure it.
Summary of Key Table Privileges
| Privilege | ACL Flag | Purpose |
|---|---|---|
SELECT | r | Read data from tables or sequences. |
INSERT | a | Append new data to a table. |
UPDATE | w | Modify existing data within a table. |
DELETE | d | Remove specific rows from a table. |
TRUNCATE | D | Instantly delete all data in a table. |
REFERENCES | x | Create foreign keys referencing the table. |
FAQs
Who can grant privileges on a table? Ordinarily, only the owner of the table or a superuser can grant or revoke privileges.
What does the PUBLIC role do? Granting a privilege to PUBLIC gives that right to every current and future role in the database system.
How can a user grant their own permissions to others? You must include the WITH GRANT OPTION clause (e.g., GRANT SELECT ON mytable TO joe WITH GRANT OPTION;).
How do I remove a privilege? Use the REVOKE command (e.g., REVOKE UPDATE ON products FROM username;).
Why can’t my user see the table after I granted access? Ensure they have the CONNECT privilege on the database and the USAGE privilege on the schema. Also, check if their search_path includes the schema where the table resides.
