How to Grant Privileges in PostgreSQL

Abdul Wahab Junaid

3 years ago

When I first started managing PostgreSQL databases professionally, I made the classic beginner mistake: I just gave every application user SUPERUSER because it made errors go away. It works, right up until it doesn’t — until someone accidentally drops a table they shouldn’t have had access to in the first place. Learning to grant privileges properly, scoped tightly to what each role actually needs, is one of those skills that pays for itself the first time something almost goes wrong and doesn’t, because the access simply wasn’t there to misuse.

What Is GRANT in PostgreSQL?

GRANT is the SQL command used to give roles (PostgreSQL’s unified concept for both users and groups) specific privileges on database objects — tables, sequences, views, functions, schemas, databases, and more. It’s the mechanism behind PostgreSQL’s entire access control model.

If you also need to remove privileges, I’ve got a companion article, “How to Revoke Privileges in PostgreSQL,” that covers the other half of this picture.

Basic Syntax

GRANT { privilege [, ...] | ALL [PRIVILEGES] }
    ON [object_type] object_name [, ...]
    TO role_name [, ...]
    [WITH GRANT OPTION];

privilege — one or more specific privileges like SELECT, INSERT, UPDATE, DELETE.
ALL [PRIVILEGES] — grants every applicable privilege for that object type.
object_type object_name — the target: a table, schema, database, sequence, or function.
role_name — who receives the privilege. PUBLIC grants to every current and future role.
WITH GRANT OPTION — allows the recipient to further grant this privilege to others.

Table Privileges

The most common privileges you’ll grant on tables are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.

GRANT SELECT, INSERT, UPDATE ON employees TO app_user;

For a read-only reporting role:

GRANT SELECT ON employees TO reporting_role;

For column-level granularity (useful when a role should see most of a table but not, say, a salary column):

GRANT SELECT (id, name, department) ON employees TO limited_view_role;

To grant everything at once (use sparingly):

GRANT ALL PRIVILEGES ON employees TO admin_role;

Granting on Multiple Tables at Once

GRANT SELECT ON employees, departments, salaries TO reporting_role;

Or, to cover every table currently in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;

Keep in mind: this only applies to tables that exist right now. Tables created later won’t automatically inherit this grant unless you also set up default privileges (see below).

Sequence Privileges

If a role needs to insert into a table with a SERIAL or IDENTITY column, it typically also needs privileges on the underlying sequence:

GRANT USAGE, SELECT ON SEQUENCE employees_id_seq TO app_user;

Or for all sequences in a schema:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

Schema Privileges

Schema privileges are often overlooked, but without USAGE on a schema, a role can’t even see the objects inside it, regardless of what table-level grants exist.

GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

USAGE allows referencing objects in the schema; CREATE allows creating new objects within it. Grant CREATE sparingly — it’s more powerful than people often realize.

Database Privileges

GRANT CONNECT ON DATABASE production TO app_user;
GRANT TEMPORARY ON DATABASE production TO app_user;

CONNECT allows the role to connect to the database at all. TEMPORARY (or TEMP) allows creating temporary tables.

Function and Procedure Privileges

GRANT EXECUTE ON FUNCTION calculate_bonus(integer) TO hr_role;

For all functions in a schema:

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO hr_role;

Role Membership (Grouping Roles)

GRANT isn’t just for object privileges — it’s also how you assign role membership, which is PostgreSQL’s way of implementing groups:

CREATE ROLE readonly_group NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

CREATE ROLE analyst LOGIN PASSWORD 'secure_password';
GRANT readonly_group TO analyst;

Now analyst inherits every privilege readonly_group has, and if you need to grant a new privilege to every analyst-type role in the future, you only need to grant it once to readonly_group.

Granting Default Privileges for Future Objects

This is the piece that trips up most people who are new to PostgreSQL privilege management. Grants on ALL TABLES IN SCHEMA only cover tables that already exist. For tables created in the future, you need:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO reporting_role;

Note that default privileges are tied to the role that creates the objects. If your application creates tables as app_owner, you need to set the default privilege as that role:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT SELECT ON TABLES TO reporting_role;

WITH GRANT OPTION

GRANT SELECT ON employees TO team_lead WITH GRANT OPTION;

This allows team_lead to grant SELECT on employees to other roles. Use this carefully — it decentralizes control over who has access, which can make auditing harder down the line.

Practical Example: Setting Up a Typical Application Role

Here’s a pattern I use often when provisioning a new application database role:

-- Create the role
CREATE ROLE app_service LOGIN PASSWORD 'strong_password' NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- Allow it to connect
GRANT CONNECT ON DATABASE production TO app_service;

-- Allow it to use the schema
GRANT USAGE ON SCHEMA public TO app_service;

-- Grant CRUD on all current tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_service;

-- Grant sequence usage for auto-increment columns
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;

-- Ensure future tables/sequences get the same treatment
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_service;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_service;

This gives the application exactly what it needs to function without handing over ownership or administrative rights.

Checking Current Privileges

\dp employees      -- table privileges
\ddp                -- default privileges

Or via information_schema:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'employees';

Common Use Cases

Provisioning new application service accounts with exactly the access they need.
Setting up read-only roles for reporting, dashboards, or BI tools.
Creating tiered access — junior analysts get SELECT, senior analysts get SELECT and INSERT, admins get everything.
Delegating limited administrative capability to team leads via WITH GRANT OPTION without making them full superusers.
Granting column-level access to protect sensitive fields like salaries or personal data while still allowing broader table access.

Troubleshooting Common Issues

“permission denied for table” even after granting SELECT** — check whether USAGE on the containing schema was also granted; it’s a separate, required privilege.

Grants not applying to new tables — you granted privileges on ALL TABLES IN SCHEMA but didn’t also set up ALTER DEFAULT PRIVILEGES, so newly created tables aren’t covered.

Role has a grant but still can’t see it in \dp — the role might be receiving access indirectly through group membership rather than a direct grant. Check \du to see role memberships.

Application still failing after granting everything expected — check whether it’s connecting as a different role than you think (common with connection poolers), or whether search_path is pointed at a different schema than the one you granted privileges on.

Best Practices

Follow least privilege: grant the minimum set of privileges a role actually needs, and expand only when there’s a concrete reason.
Use role membership (groups) instead of granting the same set of privileges to many individual roles directly — it’s much easier to maintain.
Always pair GRANT ... ON ALL TABLES IN SCHEMA with ALTER DEFAULT PRIVILEGES so future objects aren’t accidentally left unprotected or inaccessible.
Avoid WITH GRANT OPTION unless you have a specific delegation need — it spreads out control in ways that are harder to audit later.
Reserve ALL PRIVILEGES and superuser-equivalent access for genuine administrative roles, not application service accounts.
Periodically review grants against actual usage patterns — privileges tend to accumulate over time and rarely get cleaned up without a deliberate audit.

Wrapping Up

GRANT is the backbone of PostgreSQL’s security model, and getting comfortable with it — table, schema, sequence, function, and default privileges — makes the difference between a database that’s secure by design and one that’s held together by everyone just happening to be careful. Start narrow, grant what’s needed, and expand deliberately rather than defaulting to broad access because it’s the path of least resistance in the moment.