How to GRANT Permissions in PostgreSQL

Galaxy Glossary

How do I grant permissions in Amazon Redshift or PostgreSQL?

GRANT assigns table, schema, and database privileges to users or roles in PostgreSQL and Amazon Redshift.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does the GRANT command do?

GRANT adds one or more privileges—such as SELECT, INSERT, or USAGE—to a user, group, or role on a chosen database object. In Redshift, GRANT works the same way as PostgreSQL and supports tables, schemas, sequences, and databases.

Which privileges can I grant?

Common privileges are SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRUNCATE (table), USAGE (schema), and ALL PRIVILEGES.Choose the minimal set needed for the task to follow the principle of least privilege.

How do I grant SELECT on a single table?

Run GRANT SELECT ON TABLE Orders TO analytics_role; to let analytics_role read order history while blocking writes.

How do I grant privileges on multiple tables?

Specify a comma-separated list: GRANT SELECT, UPDATE ON Customers, Orders TO app_user; This minimizes repeated statements and keeps permissions consistent.

Can I grant permissions on future tables?

Use ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_role; New tables inherit the privilege automatically.

How do I allow schema access?

Grant USAGE on the schema first: GRANT USAGE ON SCHEMA public TO analytics_role; Without USAGE the role cannot access any objects inside the schema even when it has table-level privileges.

How do I delegate grant ability?

Add WITH GRANT OPTION to let the grantee pass privileges onward: GRANT SELECT ON TABLE Products TO lead_dev WITH GRANT OPTION;

Best practices for GRANT

Create roles for logical access patterns (e.g., reporting_role) and grant privileges to roles, not individual users.Revoke default PUBLIC privileges to tighten security.

.

Why How to GRANT Permissions in PostgreSQL is important

How to GRANT Permissions in PostgreSQL Example Usage


-- Give the reporting dashboard read-only access
GRANT USAGE ON SCHEMA public TO reporting_role;
GRANT SELECT ON Customers, Orders, Products, OrderItems TO reporting_role;

-- Allow the ETL app to modify inventory data
GRANT SELECT, UPDATE ON Products TO etl_app;

-- Ensure every new table is queryable by analytics_role
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_role;

How to GRANT Permissions in PostgreSQL Syntax


GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRUNCATE }
       [, ...] | ALL [ PRIVILEGES ] }
    ON { TABLE | SEQUENCE | SCHEMA | DATABASE } object_name [, ...]
    TO { user_name | GROUP group_name | ROLE role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

-- Example variations
GRANT USAGE ON SCHEMA schema_name TO role_name;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name
  GRANT SELECT ON TABLES TO role_name;

Common Mistakes

Granting privileges directly to individual users. Fix: create roles (e.g., analytics_role) and grant to roles, then add users to roles for easier management.
Forgetting to grant USAGE on the schema. Even with table privileges, users cannot access objects without schema USAGE. Always GRANT USAGE ON SCHEMA first.

Frequently Asked Questions (FAQs)

Is WITH GRANT OPTION risky?

Yes. The grantee can pass privileges to others, making auditing harder. Use it sparingly and monitor role memberships.

How do I remove a privilege?

Use REVOKE, e.g., REVOKE SELECT ON TABLE Orders FROM analytics_role; Revoked rights take effect immediately.

Does GRANT affect existing sessions?

Privileges apply instantly; users may need to reconnect for cache updates, but no server restart is required.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie