Clawdie AI
00e25fa8f9
- Add permissions.sql with GRANT + ALTER DEFAULT PRIVILEGES - Add fix-permissions.sh for quick repair on existing databases - Add POSTGRES-PERMISSIONS.md documentation - Update setup-db-jail.sh to run permissions.sql after migrations - Update skills-memory.ts to apply permissions after artifact import Fixes: permission denied for table memories
96 lines
3.2 KiB
SQL
96 lines
3.2 KiB
SQL
-- PostgreSQL permissions setup
|
|
-- Date: 06.apr.2026
|
|
--
|
|
-- This file sets up permissions for the Clawdie application database user.
|
|
-- Must be run AFTER all schema migrations.
|
|
--
|
|
-- Usage:
|
|
-- psql -v db_user=<app_user> -d <database> -f permissions.sql
|
|
--
|
|
-- What it does:
|
|
-- 1. GRANT ALL on ALL existing tables and sequences
|
|
-- 2. ALTER DEFAULT PRIVILEGES for future tables/sequences
|
|
--
|
|
-- Why DEFAULT PRIVILEGES:
|
|
-- - Future tables created by postgres superuser automatically get permissions
|
|
-- - No need to remember GRANT when adding new tables
|
|
-- - Idempotent and safe to re-run
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- GRANT ON EXISTING TABLES AND SEQUENCES
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- Grant permissions on all existing tables
|
|
DO $$
|
|
DECLARE
|
|
tbl record;
|
|
app_user text := current_setting('db_user', true);
|
|
BEGIN
|
|
IF app_user IS NULL OR app_user = '' THEN
|
|
RAISE EXCEPTION 'db_user variable not set. Use: psql -v db_user=<username>';
|
|
END IF;
|
|
|
|
FOR tbl IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
|
|
LOOP
|
|
EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %I TO %I', tbl.tablename, app_user);
|
|
RAISE NOTICE 'Granted ALL on table % to %', tbl.tablename, app_user;
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- Grant permissions on all existing sequences
|
|
DO $$
|
|
DECLARE
|
|
seq record;
|
|
app_user text := current_setting('db_user', true);
|
|
BEGIN
|
|
FOR seq IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public'
|
|
LOOP
|
|
EXECUTE format('GRANT ALL PRIVILEGES ON SEQUENCE %I TO %I', seq.sequencename, app_user);
|
|
RAISE NOTICE 'Granted ALL on sequence % to %', seq.sequencename, app_user;
|
|
END LOOP;
|
|
END $$;
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- ALTER DEFAULT PRIVILEGES FOR FUTURE TABLES
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- Auto-grant permissions on future tables created by postgres superuser
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
|
|
IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON TABLES TO CURRENT_SETTING('db_user');
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
|
|
IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON SEQUENCES TO CURRENT_SETTING('db_user');
|
|
|
|
-- Also set default privileges for the current role (in case migrations run as app user)
|
|
ALTER DEFAULT PRIVILEGES
|
|
IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON TABLES TO CURRENT_SETTING('db_user');
|
|
|
|
ALTER DEFAULT PRIVILEGES
|
|
IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON SEQUENCES TO CURRENT_SETTING('db_user');
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- VERIFICATION
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- Log what was granted
|
|
DO $$
|
|
DECLARE
|
|
app_user text := current_setting('db_user', true);
|
|
table_count int;
|
|
seq_count int;
|
|
BEGIN
|
|
SELECT count(*) INTO table_count
|
|
FROM pg_tables WHERE schemaname = 'public';
|
|
|
|
SELECT count(*) INTO seq_count
|
|
FROM pg_sequences WHERE schemaname = 'public';
|
|
|
|
RAISE NOTICE 'Permissions setup complete for user: %', app_user;
|
|
RAISE NOTICE ' Tables in public schema: %', table_count;
|
|
RAISE NOTICE ' Sequences in public schema: %', seq_count;
|
|
RAISE NOTICE ' Default privileges set for future objects';
|
|
END $$;
|