Iam postgres
Jump to navigation
Jump to search
Two options
I have a postgresql application that uses postgrest for http api and keycloak to issue JWTs for access on the http api.
Please create an easy to manage generic IAM system for the Postgresql database that can easily add new schemas.tables access to JWT issued users where the users have CRUD access to the objects they create. If possible without getting too complex add a group system where other JWTs users could easily have CRUD access to resource by adding them to a group. I'd like this to be as simple as possible and performant so it works with large tables and can easily add new tables. Use example tables of todos and chats to test this out. I'm sure you have existing methods that people have done this before so I'm trusting you to come up with the best solution.
Here are related roles that postgrest uses
-- Owner for schemas/objects (no login)
create role api_owner noinherit;
-- Runtime roles (what requests run as)
create role web_anon nologin;
create role web_user nologin;
-- PostgREST connection user
create role authenticator login noinherit password 'mypass';
-- Allow PostgREST to SET ROLE to runtime roles
grant web_anon to authenticator;
grant web_user to authenticator;
Which response is more helpful?
Your choice will help Gemini give you more helpful responses. Loading the response may take a moment.
Choice A
Of course. Here is a comprehensive, simple, and performant IAM system for PostgreSQL that integrates seamlessly with PostgREST and Keycloak JWTs.
This solution is built on standard PostgreSQL features like Row-Level Security (RLS) and is designed to be generic. You can easily apply this pattern to new schemas and tables.
The Core Concept
User Identity: We'll use the JWT sub (subject) claim, a unique UUID from Keycloak, to identify users.
Auto-Provisioning: A helper function will automatically create a user record in our database on their first API call, making user management zero-touch.
Ownership: Every protected resource (like a todo or a chat message) will have an owner_id column.
Group Sharing: Resources can be shared with groups via a simple _shares table for each resource type. This allows multiple groups to access a single resource with fine-grained permissions (read, write, delete).
Enforcement: PostgreSQL's Row-Level Security (RLS) policies enforce all access rules directly in the database, ensuring they are secure, performant, and cannot be bypassed.
1. IAM Schema and Helper Functions
First, we create a dedicated schema iam to hold our core tables and functions. This keeps the security logic separate from your application data.
The most important piece is the iam.current_user_id() function, which securely reads the user's ID from the JWT and creates a local user record if one doesn't exist.
SQL
-- Create a schema to hold our IAM logic
CREATE SCHEMA iam;
ALTER SCHEMA iam OWNER TO api_owner;
--------------------------------------------------------------------------------
-- TABLE: iam.users
-- Stores a local reference to our application users based on their JWT sub.
--------------------------------------------------------------------------------
CREATE TABLE iam.users (
id UUID PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE iam.users OWNER TO api_owner;
COMMENT ON TABLE iam.users IS 'Stores application users, keyed by their Keycloak JWT sub.';
--------------------------------------------------------------------------------
-- HELPER FUNCTION: iam.current_user_sub()
-- Extracts the 'sub' claim from the current JWT.
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION iam.current_user_sub()
RETURNS UUID AS $$
SELECT nullif(current_setting('request.jwt.claims', true)::jsonb ->> 'sub', '')::uuid;
$$ LANGUAGE sql STABLE;
ALTER FUNCTION iam.current_user_sub() OWNER TO api_owner;
COMMENT ON FUNCTION iam.current_user_sub() IS 'Get the UUID of the currently authenticated user from the JWT sub claim.';
--------------------------------------------------------------------------------
-- HELPER FUNCTION: iam.current_user_id()
-- Gets the current user''s UUID and auto-provisions a local record if needed.
-- This is the main function you will use in policies and default values.
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION iam.current_user_id()
RETURNS UUID AS $$
DECLARE
user_id UUID := iam.current_user_sub();
BEGIN
IF user_id IS NOT NULL THEN
-- On first-time access, create a local user record automatically.
INSERT INTO iam.users (id) VALUES (user_id) ON CONFLICT (id) DO NOTHING;
END IF;
RETURN user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION iam.current_user_id() OWNER TO api_owner;
COMMENT ON FUNCTION iam.current_user_id() IS 'Get current user ID, creating a local record on first access.';
--------------------------------------------------------------------------------
-- TABLES: iam.groups & iam.group_members
-- For managing group-based permissions.
--------------------------------------------------------------------------------
CREATE TABLE iam.groups (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
owner_id UUID NOT NULL REFERENCES iam.users(id) DEFAULT iam.current_user_id(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE iam.groups OWNER TO api_owner;
CREATE INDEX ON iam.groups(owner_id);
CREATE TABLE iam.group_members (
group_id BIGINT NOT NULL REFERENCES iam.groups(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
PRIMARY KEY (group_id, user_id)
);
ALTER TABLE iam.group_members OWNER TO api_owner;
-- Allow regular users to manage their own groups and memberships
GRANT SELECT, INSERT, UPDATE, DELETE ON iam.groups TO web_user;
GRANT SELECT, INSERT, DELETE ON iam.group_members TO web_user;
-- RLS for groups: Users can only see/manage groups they own or are members of.
ALTER TABLE iam.groups ENABLE ROW LEVEL SECURITY;
CREATE POLICY groups_rls_policy ON iam.groups FOR ALL TO web_user
USING (
owner_id = iam.current_user_id() OR
EXISTS (
SELECT 1 FROM iam.group_members gm WHERE gm.group_id = id AND gm.user_id = iam.current_user_id()
)
);
-- RLS for group members: Users can only see/manage memberships for groups they own.
ALTER TABLE iam.group_members ENABLE ROW LEVEL SECURITY;
CREATE POLICY group_members_rls_policy ON iam.group_members FOR ALL TO web_user
USING (
EXISTS (
SELECT 1 FROM iam.groups g WHERE g.id = group_id AND g.owner_id = iam.current_user_id()
)
);
2. Example Application Schema and Tables
Now, let's create a data schema with our todos and chats tables. Notice how they follow a simple pattern:
An owner_id column that defaults to the current user.
A corresponding _shares table for group permissions.
SQL
-- A schema for our application data
CREATE SCHEMA data;
ALTER SCHEMA data OWNER TO api_owner;
--------------------------------------------------------------------------------
-- EXAMPLE 1: TODOS
--------------------------------------------------------------------------------
CREATE TABLE data.todos (
id BIGSERIAL PRIMARY KEY,
owner_id UUID NOT NULL REFERENCES iam.users(id) DEFAULT iam.current_user_id(),
title TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE data.todos OWNER TO api_owner;
CREATE TABLE data.todo_shares (
todo_id BIGINT NOT NULL REFERENCES data.todos(id) ON DELETE CASCADE,
group_id BIGINT NOT NULL REFERENCES iam.groups(id) ON DELETE CASCADE,
can_read BOOLEAN NOT NULL DEFAULT true,
can_write BOOLEAN NOT NULL DEFAULT false,
can_delete BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (todo_id, group_id)
);
ALTER TABLE data.todo_shares OWNER TO api_owner;
CREATE INDEX ON data.todo_shares(group_id);
--------------------------------------------------------------------------------
-- EXAMPLE 2: CHATS
--------------------------------------------------------------------------------
CREATE TABLE data.chats (
id BIGSERIAL PRIMARY KEY,
owner_id UUID NOT NULL REFERENCES iam.users(id) DEFAULT iam.current_user_id(),
message TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE data.chats OWNER TO api_owner;
CREATE TABLE data.chat_shares (
chat_id BIGINT NOT NULL REFERENCES data.chats(id) ON DELETE CASCADE,
group_id BIGINT NOT NULL REFERENCES iam.groups(id) ON DELETE CASCADE,
can_read BOOLEAN NOT NULL DEFAULT true,
can_write BOOLEAN NOT NULL DEFAULT false,
can_delete BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (chat_id, group_id)
);
ALTER TABLE data.chat_shares OWNER TO api_owner;
CREATE INDEX ON data.chat_shares(group_id);
3. Permissions and RLS Policies
This is where we enforce the rules. We grant basic CRUD permissions to the web_user role and then apply RLS policies to filter which rows they can actually affect.
The policy logic is the same for every table: A user can access a row if they are the owner, OR if the row has been shared with a group they are a member of.
SQL
-- Grant basic permissions to the web_user role
GRANT USAGE ON SCHEMA iam, data TO web_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA data TO web_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA data TO web_user;
--------------------------------------------------------------------------------
-- RLS POLICY: TODOS
--------------------------------------------------------------------------------
ALTER TABLE data.todos ENABLE ROW LEVEL SECURITY;
CREATE POLICY todos_rls_policy ON data.todos FOR ALL TO web_user
USING (
-- The user is the owner
owner_id = iam.current_user_id() OR
-- The todo is shared with a group the user is a member of
EXISTS (
SELECT 1
FROM data.todo_shares s
JOIN iam.group_members gm ON s.group_id = gm.group_id
WHERE s.todo_id = id
AND gm.user_id = iam.current_user_id()
AND (
-- Logic for different commands
CASE
WHEN (current_setting('request.method', true) = 'DELETE') THEN s.can_delete
WHEN (current_setting('request.method', true) IN ('POST', 'PATCH')) THEN s.can_write
ELSE s.can_read -- Default to read for GET, HEAD, etc.
END
)
)
)
WITH CHECK (
-- When inserting or updating, check for ownership or write access
owner_id = iam.current_user_id() OR
EXISTS (
SELECT 1
FROM data.todo_shares s
JOIN iam.group_members gm ON s.group_id = gm.group_id
WHERE s.todo_id = id AND gm.user_id = iam.current_user_id() AND s.can_write
)
);
-- RLS for shares: only the owner of the parent resource can manage its shares
ALTER TABLE data.todo_shares ENABLE ROW LEVEL SECURITY;
CREATE POLICY todo_shares_rls_policy ON data.todo_shares FOR ALL TO web_user
USING (
EXISTS (
SELECT 1 FROM data.todos t WHERE t.id = todo_id AND t.owner_id = iam.current_user_id()
)
);
--------------------------------------------------------------------------------
-- RLS POLICY: CHATS (Identical logic, just different table names)
--------------------------------------------------------------------------------
ALTER TABLE data.chats ENABLE ROW LEVEL SECURITY;
CREATE POLICY chats_rls_policy ON data.chats FOR ALL TO web_user
USING (
owner_id = iam.current_user_id() OR
EXISTS (
SELECT 1
FROM data.chat_shares s
JOIN iam.group_members gm ON s.group_id = gm.group_id
WHERE s.chat_id = id
AND gm.user_id = iam.current_user_id()
AND (
CASE
WHEN (current_setting('request.method', true) = 'DELETE') THEN s.can_delete
WHEN (current_setting('request.method', true) IN ('POST', 'PATCH')) THEN s.can_write
ELSE s.can_read
END
)
)
)
WITH CHECK (
owner_id = iam.current_user_id() OR
EXISTS (
SELECT 1
FROM data.chat_shares s
JOIN iam.group_members gm ON s.group_id = gm.group_id
WHERE s.chat_id = id AND gm.user_id = iam.current_user_id() AND s.can_write
)
);
ALTER TABLE data.chat_shares ENABLE ROW LEVEL SECURITY;
CREATE POLICY chat_shares_rls_policy ON data.chat_shares FOR ALL TO web_user
USING (
EXISTS (
SELECT 1 FROM data.chats c WHERE c.id = chat_id AND c.owner_id = iam.current_user_id()
)
);
How to Use and Extend It
Workflow Example
User A (Alice) calls POST /todos with {"title": "My first todo"}.
The DEFAULT on owner_id sets her as the owner.
The RLS WITH CHECK clause passes because she is the owner.
Alice calls POST /iam.groups with {"name": "Project Team"}. She becomes the owner of this group.
Alice adds User B (Bob) to the group by calling POST /iam.group_members with {"group_id": 1, "user_id": "bob-s-uuid"}.
Alice shares her todo with the group by calling POST /data.todo_shares with {"todo_id": 1, "group_id": 1, "can_write": true}.
Bob can now call GET /todos?id=eq.1 and see Alice's todo. He can also PATCH it because can_write is true. He cannot delete it.
Adding a New Table (data.notes)
It's a simple, repeatable 3-step process:
Create the Tables: Create data.notes (with owner_id) and data.note_shares by copying the todos example.
Grant Permissions: GRANT ALL ON data.notes, data.note_shares TO web_user;
Apply RLS Policies: Copy the RLS policies from data.todos and data.todo_shares, then find-and-replace the table and column names.
This design is highly performant because the iam.current_user_id() is evaluated once per transaction, and the RLS subqueries are simple EXISTS checks that benefit greatly from standard PostgreSQL indexes.
This response is more helpful
Choice B
Of course. Here is a generic, performant, and easy-to-manage IAM system for PostgreSQL and PostgREST that uses JWTs for authentication.
This solution is centered around a dedicated iam schema and a reusable SQL function that enforces Row-Level Security (RLS). This approach ensures that access control is handled directly and securely within the database.
Core Concepts
Ownership: Every protected resource (a row in a table) has an owner_id. This is automatically set to the user's ID (sub claim from the JWT) when the resource is created.
Group Sharing: Resources can optionally be assigned a group_id. If a group_id is set, any user who is a member of that group gains full CRUD access to that specific resource.
Centralized Logic: A single, generic SQL function, iam.check_resource_access, contains all the logic to determine if a user can access a resource. This function checks for both direct ownership and group membership.
Row-Level Security (RLS): PostgreSQL's RLS feature is used to apply this access logic to every query, ensuring that users can only see or modify data they are authorized to access. This is enforced for the web_user role that PostgREST uses for authenticated requests.
SQL Implementation
Here is the complete SQL script to set up the IAM system.
Part 1: Initial Role Setup
These are the standard PostgREST roles you provided.
SQL
CREATE ROLE api_owner NOINHERIT;
CREATE ROLE web_anon NOLOGIN;
CREATE ROLE web_user NOLOGIN;
CREATE ROLE authenticator LOGIN NOINHERIT PASSWORD 'mypass';
GRANT web_anon TO authenticator;
GRANT web_user TO authenticator;
Part 2: Create the IAM Schema and Helper Function
This schema will hold our group tables and the core access logic. The helper function provides an easy way to get the current authenticated user's ID from the JWT.
SQL
CREATE SCHEMA iam;
CREATE FUNCTION iam.get_current_user_id()
RETURNS uuid AS $$
BEGIN
RETURN (current_setting('request.jwt.claims', true)::jsonb ->> 'sub')::uuid;
EXCEPTION
WHEN OTHERS THEN
RETURN '00000000-0000-0000-0000-000000000000'::uuid;
END;
$$ LANGUAGE plpgsql STABLE;
CREATE TABLE iam.groups (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL DEFAULT iam.get_current_user_id(),
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON iam.groups (owner_id);
CREATE TABLE iam.group_members (
group_id uuid NOT NULL REFERENCES iam.groups(id) ON DELETE CASCADE,
user_id uuid NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (group_id, user_id)
);
CREATE INDEX ON iam.group_members (user_id);
Part 3: Create Example Application Tables
Here we set up an app schema with todos and chats tables. Note the addition of the owner_id and group_id columns, which are key to the IAM system.
SQL
CREATE SCHEMA app;
CREATE TABLE app.todos (
id serial PRIMARY KEY,
task text NOT NULL,
done boolean NOT NULL DEFAULT false,
owner_id uuid NOT NULL DEFAULT iam.get_current_user_id(),
group_id uuid REFERENCES iam.groups(id) ON DELETE SET NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON app.todos (owner_id);
CREATE INDEX ON app.todos (group_id);
CREATE TABLE app.chats (
id serial PRIMARY KEY,
message text NOT NULL,
owner_id uuid NOT NULL DEFAULT iam.get_current_user_id(),
group_id uuid REFERENCES iam.groups(id) ON DELETE SET NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON app.chats (owner_id);
CREATE INDEX ON app.chats (group_id);
Part 4: Create the Generic RLS Function
This is the core of the IAM system. It's a single function that checks if the current user is either the direct owner or a member of the group the resource is assigned to.
SQL
CREATE FUNCTION iam.check_resource_access(_owner_id uuid, _group_id uuid)
RETURNS boolean AS $$
DECLARE
current_user_id uuid := iam.get_current_user_id();
BEGIN
IF current_user_id = _owner_id THEN
RETURN true;
END IF;
IF _group_id IS NOT NULL THEN
RETURN EXISTS (
SELECT 1
FROM iam.group_members gm
WHERE gm.group_id = _group_id AND gm.user_id = current_user_id
);
END IF;
RETURN false;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
Part 5: Apply Security Policies and Grant Permissions
Now we enable RLS and apply policies that use our generic function. We also apply policies to the iam tables themselves so users can manage their own groups. Finally, we grant the necessary permissions to the web_user role.
SQL
ALTER SCHEMA iam OWNER TO api_owner;
ALTER TABLE iam.groups OWNER TO api_owner;
ALTER TABLE iam.group_members OWNER TO api_owner;
ALTER FUNCTION iam.get_current_user_id() OWNER TO api_owner;
ALTER FUNCTION iam.check_resource_access(uuid, uuid) OWNER TO api_owner;
ALTER SCHEMA app OWNER TO api_owner;
ALTER TABLE app.todos OWNER TO api_owner;
ALTER TABLE app.chats OWNER TO api_owner;
ALTER TABLE iam.groups ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow owners to manage their groups" ON iam.groups
FOR ALL TO web_user
USING (owner_id = iam.get_current_user_id())
WITH CHECK (owner_id = iam.get_current_user_id());
ALTER TABLE iam.group_members ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow group owners to manage members" ON iam.group_members
FOR ALL TO web_user
USING (EXISTS (SELECT 1 FROM iam.groups WHERE id = group_id AND owner_id = iam.get_current_user_id()))
WITH CHECK (EXISTS (SELECT 1 FROM iam.groups WHERE id = group_id AND owner_id = iam.get_current_user_id()));
CREATE POLICY "Allow members to view other members" ON iam.group_members
FOR SELECT TO web_user
USING (EXISTS (SELECT 1 FROM iam.group_members WHERE group_id = iam.group_members.group_id AND user_id = iam.get_current_user_id()));
ALTER TABLE app.todos ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow CRUD for owners and group members" ON app.todos
FOR ALL TO web_user
USING (iam.check_resource_access(owner_id, group_id))
WITH CHECK (iam.check_resource_access(owner_id, group_id));
ALTER TABLE app.chats ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow CRUD for owners and group members" ON app.chats
FOR ALL TO web_user
USING (iam.check_resource_access(owner_id, group_id))
WITH CHECK (iam.check_resource_access(owner_id, group_id));
GRANT USAGE ON SCHEMA app, iam TO web_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app, iam TO web_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO web_user;
How to Use the System
Let's assume you have two users with the following JWT sub IDs:
User A: 11111111-1111-1111-1111-111111111111
User B: 22222222-2222-2222-2222-222222222222
Example Workflow
User A creates a private todo.
POST /todos with body {"task": "My private task"}
The database automatically sets owner_id to User A's ID. Only User A can read or modify this todo.
User A creates a group.
POST /groups with body {"name": "Project Team"}
This creates a group owned by User A. Let's say its new id is 99999999-9999-9999-9999-999999999999.
User A adds User B to the group.
POST /group_members with body {"group_id": "999...", "user_id": "222..."}
Only User A can do this because they own the group.
User A creates a shared chat message.
POST /chats with body {"message": "Team, let's sync up", "group_id": "999..."}
Now, both User A (as the owner) and User B (as a group member) can read, update, or delete this specific chat message.
How to Add New Protected Tables
Adding a new table to this IAM system is simple and follows a consistent pattern.
Add Columns: Add owner_id and group_id columns to your new table, using the same definitions as the todos and chats examples.
SQL
CREATE TABLE app.new_table (
id serial PRIMARY KEY,
-- your other columns
owner_id uuid NOT NULL DEFAULT iam.get_current_user_id(),
group_id uuid REFERENCES iam.groups(id) ON DELETE SET NULL
);
Add Indexes: Create indexes on the new owner_id and group_id columns for performance.
SQL
CREATE INDEX ON app.new_table (owner_id);
CREATE INDEX ON app.new_table (group_id);
Apply Policy: Enable RLS and apply the generic policy.
SQL
ALTER TABLE app.new_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow CRUD for owners and group members" ON app.new_table
FOR ALL TO web_user
USING (iam.check_resource_access(owner_id, group_id))
WITH CHECK (iam.check_resource_access(owner_id, group_id));
Grant Permissions: Grant the web_user role permissions on the new table.
SQL
GRANT SELECT, INSERT, UPDATE, DELETE ON app.new_table TO web_user;
GRANT USAGE, SELECT ON SEQUENCE app.new_table_id_seq TO web_user;