# Roles and GRANT

> Grant object privileges and demonstrate row-level security with an ordinary database role.

---

LLMS index: [llms.txt](/en/llms.txt)

---

PostgreSQL has a single concept — the *role* — for both users and groups. A role can log in (with `LOGIN`), be granted into other roles (for group-like behavior), own objects, and have privileges granted to it. `GRANT` adds privileges; `REVOKE` removes them. Every database object has an owner with full rights; everyone else needs explicit grants. The principle is *least privilege*: give each role only what it needs.

## What you'll learn {#learn}

- Creating roles with and without login
- Granting and revoking privileges on schemas, tables, and columns
- The `USAGE` privilege on schemas (often forgotten)
- `DEFAULT PRIVILEGES` for objects created in the future
- Row-level security (RLS) — a brief introduction

## Example {#example}

```sql {num="55.1" caption="Roles and GRANT"}
-- Inspect the current session
SELECT current_user, session_user;

-- Create ordinary roles (requires CREATEROLE or superuser privileges)
-- CREATE ROLE app_read NOLOGIN;                                 -- a "group" role
-- CREATE ROLE alice LOGIN PASSWORD 'change-me' IN ROLE app_read;
-- CREATE ROLE app_write NOLOGIN;

-- Grant access to a schema and read on tables
-- GRANT USAGE  ON SCHEMA public TO app_read;
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

-- Future tables in this schema also get SELECT for app_read
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public
--   GRANT SELECT ON TABLES TO app_read;

-- Write-level
-- GRANT app_read TO app_write;                          -- inheritance: app_write also reads
-- GRANT INSERT, UPDATE, DELETE ON fruits TO app_write;

-- Revoke
-- REVOKE INSERT ON fruits FROM app_write;

-- Inspect your privileges on a table
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'fruits' AND grantee = current_user;

-- Row-level security: per-row visibility decided by a policy
-- (demo on a separate table so we don't change `fruits` permissions)
DROP TABLE IF EXISTS tenant_docs;
CREATE TABLE tenant_docs (
  id       integer PRIMARY KEY,
  tenant   text NOT NULL,
  content  text NOT NULL
);
INSERT INTO tenant_docs VALUES (1, 'acme', 'hello'), (2, 'globex', 'world');

ALTER TABLE tenant_docs ENABLE ROW LEVEL SECURITY;
-- Apply the policy to the table owner as well (superusers/BYPASSRLS still bypass it).
ALTER TABLE tenant_docs FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tenant_docs
  USING (tenant = current_setting('app.tenant', true));

-- Set the tenant for the session and try a select
SET app.tenant TO 'acme';
SELECT * FROM tenant_docs;        -- only sees the 'acme' row

SET app.tenant TO 'globex';
SELECT * FROM tenant_docs;        -- only sees the 'globex' row

-- Cleanup
DROP POLICY IF EXISTS tenant_isolation ON tenant_docs;
DROP TABLE IF EXISTS tenant_docs;
```

Table privileges include `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`, and, in PostgreSQL 17, `MAINTAIN`. On a schema, the most important are `USAGE` (lets a role refer to objects inside) and `CREATE` (lets the role create new objects). On a database, `CONNECT`, `CREATE`, and `TEMPORARY` matter.

Privileges on tables are *not* inherited from schema-level grants. To use a table in schema `public`, a role needs `USAGE` on `public` *and* a privilege (e.g. `SELECT`) on the table. Missing the schema-level `USAGE` produces the common "permission denied for schema public" error.

`DEFAULT PRIVILEGES` solves the "new tables get no grants" problem: any object created later by a role inherits the configured grants automatically. They apply to objects created by the configured creating role, not automatically to objects created by every role. Configure each migration/owner role that creates tables.

Row-level security (RLS) is a more advanced feature: you `ENABLE ROW LEVEL SECURITY` on a table and write `POLICY`s that filter rows by predicate. With RLS, a query like `SELECT * FROM tenant_docs` returns only the rows visible to the current role/setting when the role is subject to the policy. A freely changeable `app.tenant` setting is only a demonstration: a real application must prevent untrusted clients from choosing another tenant.

## Run the example {#run}

To run, use a non-superuser role without `BYPASSRLS` that can create tables in your scratch database. `FORCE ROW LEVEL SECURITY` makes this table owner obey the policy. Superusers still see both tenants. The commented role-management commands need appropriate role-administration privileges.

```text
$ psql -f source/roles-and-grant.sql postgres
 current_user | session_user
--------------+--------------
 your_user    | your_user
(1 row)
 ...
```

## Common pitfalls {#pitfalls}

- Forgetting `GRANT USAGE ON SCHEMA public`. A role can have `SELECT` on every table and still fail to query because it lacks schema-level usage.
- Granting privileges on existing tables without configuring `DEFAULT PRIVILEGES` — every new table the app creates is unreachable by the read-only role.
- Mixing `SECURITY DEFINER` functions with a permissive `search_path` opens a privilege-escalation door. Always set `search_path` on such functions.
- Storing passwords in `CREATE ROLE ... PASSWORD 'plain'` is acceptable for personal databases; for production use SCRAM authentication and never hardcode passwords in SQL scripts.

> [!TIP] Tip
> A common pattern is three roles: `app_owner` (owns objects, used for migrations), `app_read` (read-only role for analytics and replicas), `app_write` (read+write role for the application). Grant `app_owner` ownership; grant rights to the others.

> [!NOTE] Try it
> With superuser access, create a role and grant it `SELECT` only on `fruits`. Reconnect as that role (`psql -U newrole`) and confirm reads work but writes do not. Then experiment with RLS: enable on a table, add a policy, and watch how `SELECT` results change with `SET`.

## Downloads {#downloads}

SQL file: [roles-and-grant.sql](/examples/source/roles-and-grant.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)