If you’ve ever managed a PostgreSQL database with more than one user, you’ve probably run into the question of who should be allowed to do what. Maybe you have an analyst who only needs to read data, or an application role that needs to insert and update rows but should never be allowed to drop a table. This is exactly the problem the GRANT command solves. In this guide, I’ll walk you through everything you need to know about GRANT in PostgreSQL, from the basic syntax to real-world examples, common mistakes, and best practices I’ve picked up over years of working with production databases.
What Is the GRANT Command?
GRANT is a PostgreSQL command used to give specific privileges to a role (which could be a user or a group) on a database object. Database objects include tables, views, sequences, functions, schemas, and even entire databases. Without GRANT, a newly created role in PostgreSQL has almost no privileges beyond what’s granted by default to the PUBLIC pseudo-role, so this command is essential for setting up any kind of meaningful access control.
Think of GRANT as the mechanism that turns PostgreSQL’s role system from an abstract idea into something functional. You can create as many roles as you like, but until you grant them privileges, they can’t actually interact with your data in useful ways.
Basic Syntax of GRANT
The general syntax for granting privileges on a table looks like this:
GRANT privilege_type [, ...]
ON object_type object_name
TO role_name [, ...]
[WITH GRANT OPTION];
Let’s break this down piece by piece:
- privilege_type: This is the specific action you’re allowing, such as
SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES, orTRIGGERfor tables. Different object types support different privilege types. - object_type object_name: This specifies what you’re granting access to, like
TABLE employees,SCHEMA public, orDATABASE mydb. - role_name: The role (user or group) receiving the privilege.
- WITH GRANT OPTION: An optional clause that allows the receiving role to grant the same privilege to other roles.
Here’s a simple example:
GRANT SELECT ON employees TO analyst_role;
This gives the analyst_role permission to read data from the employees table, nothing more.
Types of Privileges You Can Grant
PostgreSQL supports a fairly rich set of privileges depending on the object type. Here’s a rundown of the most commonly used ones:
Table-Level Privileges
SELECT— read rows from a tableINSERT— add new rowsUPDATE— modify existing rowsDELETE— remove rowsTRUNCATE— quickly remove all rowsREFERENCES— create foreign key constraints that reference this tableTRIGGER— create triggers on the table
Schema-Level Privileges
CREATE— create new objects within the schemaUSAGE— access objects within the schema (this one trips up a lot of beginners, more on that below)
Database-Level Privileges
CONNECT— connect to the databaseCREATE— create new schemas within the databaseTEMP— create temporary tables
Function Privileges
EXECUTE— run the function
Sequence Privileges
USAGE— usenextval()andcurrval()SELECT— read the current valueUPDATE— change the sequence value
Granting Privileges on Different Object Types
Granting on Tables
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
This is probably the most common use case you’ll see day-to-day. It allows app_user to read, add, and modify rows in the orders table, but not delete them.
If you want to grant all standard privileges at once, you can use the ALL PRIVILEGES shortcut:
GRANT ALL PRIVILEGES ON orders TO admin_user;
Granting on Multiple Tables
Rather than running the same GRANT statement over and over for every table, you can grant on all tables in a schema:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;
This is a huge time-saver when you have dozens or hundreds of tables and want to give a role blanket read access.
Granting on Schemas
A very common beginner mistake is granting table privileges without also granting USAGE on the containing schema. If a role doesn’t have USAGE on a schema, it can’t even see the objects inside it, no matter what table-level privileges it has.
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;
Granting on Databases
GRANT CONNECT ON DATABASE mydb TO app_user;
This allows a role to connect to a specific database. Without this, even if a role has passwords and other privileges configured, it simply won’t be able to open a connection to that database.
Granting on Sequences
Sequences are often overlooked, but if your table has a SERIAL or IDENTITY column, the underlying sequence needs its own grant if you want the role to insert rows successfully:
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;
Granting on Functions
GRANT EXECUTE ON FUNCTION calculate_total(integer) TO app_user;
Granting to Multiple Roles at Once
GRANT SELECT ON orders TO analyst_role, reporting_role, audit_role;
Using Default Privileges
One of the more advanced but genuinely useful features related to GRANT is ALTER DEFAULT PRIVILEGES. Normally, GRANT only applies to objects that already exist. If you create a new table tomorrow, your previous grants won’t automatically apply to it. That’s where default privileges come in:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO reporting_role;
With this in place, any new table created in the public schema will automatically grant SELECT to reporting_role the moment it’s created. This is incredibly useful in environments where tables are created programmatically or as part of migrations, and you don’t want to remember to run GRANT every single time.
The WITH GRANT OPTION Clause
Sometimes you want a role to not just have a privilege, but to also be able to pass that privilege on to others. That’s what WITH GRANT OPTION is for:
GRANT SELECT ON employees TO team_lead WITH GRANT OPTION;
Now team_lead can run their own GRANT statements to give SELECT on employees to other roles. Use this carefully, because it decentralizes control over your permission model, and it can become hard to track who granted what to whom.
Checking Existing Privileges
Before and after running GRANT statements, it’s smart to verify what privileges actually exist. You can query the information_schema for this:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'employees';
Or use the \dp meta-command in psql:
\dp employees
This shows you the access privileges for the table in a compact format, listing which roles have which privileges.
Practical Real-World Example
Let’s say you’re setting up a typical three-tier access model for a company database: an admin role, an application role, and a read-only reporting role.
-- Create the roles first
CREATE ROLE db_admin;
CREATE ROLE app_role LOGIN PASSWORD 'secure_password';
CREATE ROLE reporting_role LOGIN PASSWORD 'another_password';
-- Give admin full control
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;
-- App role gets read/write but not destructive privileges
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_role;
-- Reporting role only reads
GRANT USAGE ON SCHEMA public TO reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;
-- Make sure future tables also follow this pattern
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting_role;
This pattern scales well and is a good starting template for most small to medium applications.
Common Use Cases for GRANT
- Application accounts: giving a backend service the minimum privileges it needs (usually SELECT, INSERT, UPDATE, and occasionally DELETE).
- Read-only analytics users: connecting BI tools like Metabase, Tableau, or Looker with a role that can only run SELECT queries.
- Third-party integrations: exposing a limited slice of your data to external contractors without giving them full database access.
- Team-based access: separating developers, DBAs, and support staff into different roles with different privilege sets.
- Auditing roles: creating a role that can read system catalogs and logs without touching application data.
Troubleshooting Common GRANT Issues
“Permission Denied for Table” Even After Granting
This is almost always a schema USAGE issue. Double-check that the role has USAGE on the schema containing the table:
GRANT USAGE ON SCHEMA public TO your_role;
Grants Not Applying to New Tables
Remember, GRANT is not retroactive and it’s not automatically forward-looking either. If you want new tables to inherit privileges, you need ALTER DEFAULT PRIVILEGES, as shown earlier.
Role Can Connect But Can’t See Any Tables
Check that CONNECT was granted on the database, and USAGE on the schema. It’s easy to grant table-level privileges and forget these two prerequisites.
Sequence Errors on Insert
If a role can insert into a table but gets an error related to sequences, it usually means the sequence backing the SERIAL column wasn’t granted:
GRANT USAGE, SELECT ON SEQUENCE table_name_id_seq TO your_role;
Privileges Granted to the Wrong Role Name
PostgreSQL role names are case-sensitive when quoted and case-insensitive when unquoted (they get folded to lowercase). If you created a role with mixed case using quotes, like "AppUser", you must always refer to it with the exact same quoting in your GRANT statements.
Best Practices for Using GRANT
- Follow the principle of least privilege: only grant what a role truly needs. It’s much easier to add a privilege later than to figure out what to revoke from an overly permissive role.
- Use roles as groups: instead of granting privileges directly to individual login roles, create group roles (like
readonlyorapp_writer) and grant privileges to those, then add individual users as members. This makes management far simpler at scale. - Document your grants: keep a script or migration file that tracks every GRANT statement you run, so you have a single source of truth for your permission model.
- Use default privileges for consistency: especially in schemas where tables are created frequently,
ALTER DEFAULT PRIVILEGESsaves you from privilege drift. - Audit regularly: periodically run queries against
information_schema.role_table_grantsto review who has access to what, and revoke anything that’s no longer needed. - Avoid overusing WITH GRANT OPTION: it’s convenient but can quickly make your access control model harder to reason about.
- Pair GRANT with REVOKE: whenever you change a role’s responsibilities, remember that GRANT only adds privileges. If a role no longer needs a privilege, you must explicitly REVOKE it.
GRANT vs. Role Membership
It’s worth clarifying a point of confusion for people newer to PostgreSQL’s permission system: GRANT is used both for privileges on objects (like SELECT on a table) and for role membership (like adding a user to a group role). The syntax looks similar but does different things:
-- Granting an object privilege
GRANT SELECT ON employees TO analyst_role;
-- Granting role membership
GRANT analyst_role TO jane_doe;
In the second example, jane_doe becomes a member of analyst_role, inheriting whatever privileges that role has (assuming the role was created with INHERIT, which is the default). This dual use of GRANT is powerful once you understand it: you can build a hierarchy of group roles with specific privilege sets, then simply add and remove individual users from those groups as their responsibilities change, rather than managing privileges per-user.
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
CREATE ROLE jane_doe LOGIN PASSWORD 'x';
GRANT readonly TO jane_doe;
Now jane_doe automatically has read access to everything readonly can read, and if you need to revoke her access later, you just remove her from the role rather than hunting down every individual grant.
Granting Column-Level Privileges
GRANT doesn’t have to apply to an entire table. You can restrict privileges to specific columns, which is useful when a role needs to update most of a table but shouldn’t touch certain sensitive fields:
GRANT SELECT (id, first_name, last_name), UPDATE (first_name, last_name)
ON employees
TO hr_assistant_role;
With this grant, hr_assistant_role can read and update names, but has no access at all to other columns like salary or social security number, even though it has some privileges on the table as a whole.
Granting on Specific Rows with Row-Level Security
GRANT itself operates at the object and column level, but PostgreSQL also supports row-level security (RLS) policies for finer-grained control over which rows a role can see or modify. This is a separate feature from GRANT, but the two work together: a role first needs table-level privileges via GRANT, and then RLS policies further restrict which specific rows those privileges apply to.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY region_policy ON orders
FOR SELECT
USING (region = current_setting('app.current_region'));
GRANT SELECT ON orders TO regional_sales_role;
Without both the GRANT and the enabled policy, access won’t work as expected, so it’s worth remembering these are complementary layers rather than alternatives to each other.
Frequently Asked Questions
Does GRANT apply retroactively to objects that already exist plus future ones?
No. A standard GRANT statement only applies to the objects that exist at the time you run it. For future objects, you need ALTER DEFAULT PRIVILEGES, which was covered earlier in this guide.
What happens if I grant the same privilege twice?
Nothing bad. PostgreSQL doesn’t create duplicate entries or throw an error; the privilege is simply already there, so running the same GRANT statement again is harmless.
How do I remove a privilege I granted by mistake?
Use REVOKE, which is the direct counterpart to GRANT:
REVOKE SELECT ON employees FROM analyst_role;
Can I see the exact SQL to grant privileges matching an existing role?
Yes, tools like pg_dump --schema-only will include GRANT statements for existing objects, which is a handy way to audit or replicate a permission setup between environments.
Does granting a privilege on a schema automatically grant it on the tables inside?
No. Schema-level privileges like USAGE and CREATE are separate from table-level privileges like SELECT and INSERT. You need both: USAGE on the schema to see what’s inside, and explicit grants on the individual tables (or ALL TABLES IN SCHEMA) for actual data access.
Can I grant privileges to PUBLIC instead of a specific role?
Yes, GRANT SELECT ON employees TO PUBLIC; grants the privilege to every role in the database, present and future. This is convenient for genuinely public reference data, but should be used sparingly, since it’s easy to forget that PUBLIC grants exist and accidentally expose more than intended. It’s generally safer to be explicit about which roles get which privileges rather than relying on PUBLIC as a catch-all.
Wrapping Up
The GRANT command is one of the foundational tools for managing access control in PostgreSQL, and understanding it well will save you a lot of headaches as your database and team grow. Start with the principle of least privilege, use group roles to keep things organized, and don’t forget the often-missed pieces like schema USAGE and sequence privileges. Once you get comfortable with these patterns, setting up secure, well-organized access control in PostgreSQL becomes second nature.