139 lines
4.4 KiB
SQL
139 lines
4.4 KiB
SQL
-- Database Access Setup for Friend
|
|
-- Run this script to grant database access to a friend
|
|
--
|
|
-- Usage:
|
|
-- For Supabase: Run in Supabase SQL Editor
|
|
-- For Local PostgreSQL: sudo -u postgres psql institutional_trader < setup_friend_access.sql
|
|
--
|
|
-- IMPORTANT: Replace 'friend_user' and 'secure_password_here' with actual values!
|
|
|
|
-- ============================================
|
|
-- OPTION 1: Full Access (Read/Write)
|
|
-- ============================================
|
|
|
|
-- Create user (if doesn't exist)
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_user WHERE usename = 'friend_user') THEN
|
|
CREATE USER friend_user WITH PASSWORD 'secure_password_here';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- Grant database connection
|
|
GRANT CONNECT ON DATABASE postgres TO friend_user;
|
|
|
|
-- Grant schema usage
|
|
GRANT USAGE ON SCHEMA public TO friend_user;
|
|
|
|
-- Grant permissions on all existing tables
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO friend_user;
|
|
|
|
-- Grant permissions on all sequences (for auto-increment columns)
|
|
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO friend_user;
|
|
|
|
-- Grant permissions on future tables (default privileges)
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO friend_user;
|
|
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT SELECT, USAGE ON SEQUENCES TO friend_user;
|
|
|
|
-- Grant execute on functions (if you have custom functions)
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO friend_user;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT EXECUTE ON FUNCTIONS TO friend_user;
|
|
|
|
-- ============================================
|
|
-- OPTION 2: Read-Only Access (Uncomment to use instead)
|
|
-- ============================================
|
|
|
|
/*
|
|
-- Create read-only user
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_user WHERE usename = 'friend_readonly') THEN
|
|
CREATE USER friend_readonly WITH PASSWORD 'secure_password_here';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- Grant database connection
|
|
GRANT CONNECT ON DATABASE postgres TO friend_readonly;
|
|
|
|
-- Grant schema usage
|
|
GRANT USAGE ON SCHEMA public TO friend_readonly;
|
|
|
|
-- Grant SELECT only on all existing tables
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA public TO friend_readonly;
|
|
|
|
-- Grant SELECT on future tables
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT SELECT ON TABLES TO friend_readonly;
|
|
|
|
-- Grant execute on functions (read-only functions only)
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO friend_readonly;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT EXECUTE ON FUNCTIONS TO friend_readonly;
|
|
*/
|
|
|
|
-- ============================================
|
|
-- OPTION 3: Limited Access to Specific Tables
|
|
-- ============================================
|
|
|
|
/*
|
|
-- Create user with limited access
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_user WHERE usename = 'friend_limited') THEN
|
|
CREATE USER friend_limited WITH PASSWORD 'secure_password_here';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- Grant database connection
|
|
GRANT CONNECT ON DATABASE postgres TO friend_limited;
|
|
GRANT USAGE ON SCHEMA public TO friend_limited;
|
|
|
|
-- Grant access only to specific tables (example: options_flow and daily_analysis)
|
|
GRANT SELECT, INSERT, UPDATE ON TABLE public.options_flow TO friend_limited;
|
|
GRANT SELECT ON TABLE public.daily_analysis TO friend_limited;
|
|
|
|
-- Grant access to sequences for those tables
|
|
GRANT SELECT, USAGE ON SEQUENCE public.options_flow_id_seq TO friend_limited;
|
|
*/
|
|
|
|
-- ============================================
|
|
-- Verify Access
|
|
-- ============================================
|
|
|
|
-- Check user exists
|
|
SELECT usename, usecreatedb, usesuper
|
|
FROM pg_user
|
|
WHERE usename IN ('friend_user', 'friend_readonly', 'friend_limited');
|
|
|
|
-- Check table permissions
|
|
SELECT
|
|
grantee,
|
|
table_schema,
|
|
table_name,
|
|
privilege_type
|
|
FROM information_schema.table_privileges
|
|
WHERE grantee IN ('friend_user', 'friend_readonly', 'friend_limited')
|
|
ORDER BY grantee, table_name;
|
|
|
|
-- ============================================
|
|
-- Connection Strings for Friend
|
|
-- ============================================
|
|
|
|
-- For Supabase:
|
|
-- postgresql://friend_user:secure_password_here@db.[project-ref].supabase.co:5432/postgres
|
|
--
|
|
-- For Local PostgreSQL:
|
|
-- postgresql://friend_user:secure_password_here@your-server-ip:5432/institutional_trader
|
|
--
|
|
-- For SSH Tunnel (most secure):
|
|
-- 1. Friend runs: ssh -L 5432:localhost:5432 user@your-server-ip
|
|
-- 2. Friend connects to: postgresql://friend_user:password@localhost:5432/institutional_trader
|
|
|