Skip to content

Instantly share code, notes, and snippets.

@yyyyaaa

yyyyaaa/grant-admin.sql

Last active September 26, 2025 02:16
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save yyyyaaa/c6530527b472f6f73d63c7ea71038e89 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save yyyyaaa/c6530527b472f6f73d63c7ea71038e89 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
grant-admin.sql
/*
================================================================================
LAUNCHQL ADMIN OWNERSHIP TRANSFER SCRIPT
================================================================================
PURPOSE:
Promotes an existing user to admin status and transfers comprehensive ownership
of LaunchQL infrastructure resources to them. Includes transaction safety,
rollback mechanisms, and detailed reporting.
USAGE:
1. Edit the configuration parameters below (lines ~18-27)
2. Run the script: psql -f grant-admin-ownership.sql
REQUIRED PARAMETERS TO CONFIGURE:
- target_user_id: UUID of the user to promote (get from lql_public.users)
- admin_username: New username for the admin user
- admin_display_name: Display name for the admin user
- excluded_databases: Databases to exclude from ownership transfer
- admin_patterns: Comma-separated list of usernames considered admin
EXAMPLE CONFIGURATION:
\set target_user_id '12345678-1234-1234-1234-123456789abc'
\set admin_username 'mycompany_admin'
\set admin_display_name 'MyCompany Administrator'
\set excluded_databases ('lql','system')
\set admin_patterns 'admin,superadmin,administrator,root'
WHAT THIS SCRIPT DOES:
✅ Creates backup table for rollback safety
✅ Starts transaction for automatic rollback on errors
✅ Updates user credentials to admin status
✅ Transfers ownership of ALL LaunchQL resources:
- Databases (except excluded ones)
- User data: addresses, emails, phone numbers, crypto addresses
- Authentication: auth accounts, connected accounts
- Secrets: simple secrets, encrypted secrets
- Infrastructure: sites, domains, APIs (via database ownership)
- App data: levels, permissions, memberships
✅ Provides detailed transfer counts and reporting
✅ Commits transaction only if everything succeeds
✅ Shows comprehensive ownership summary
SAFETY FEATURES:
- Transaction rollback on any error
- Backup table (ownership_backup) for manual recovery
- System database exclusion (prevents breaking core LaunchQL)
- Detailed logging of all operations
- User existence check before proceeding
ROLLBACK INSTRUCTIONS (if needed):
If you need to manually rollback changes, the ownership_backup table
contains the original ownership state. You can restore it with:
SELECT * FROM ownership_backup; -- View original state
-- Then manually update tables using the backup data
PREREQUISITES:
- User must already exist in lql_public.users
- Must have admin privileges to modify ownership
- All referenced schemas/tables must exist
ESTIMATED RUNTIME:
Typically completes in under 30 seconds for most deployments.
================================================================================
*/
-- Grant Admin Rights to Specific User
-- Configurable script for making a user an admin with ownership of infrastructure
-- CONFIGURATION PARAMETERS (modify these as needed)
-- User ID to promote to admin (can be obtained from lql_public.users)
\set target_user_id '7586472e-fcbb-47f6-f6f7-41b552c515f6'
-- Admin credentials
\set admin_username 'superadmin'
\set admin_display_name 'LaunchQL Super Administrator'
-- System databases to exclude from ownership transfer
\set excluded_databases ('lql')
-- Admin detection patterns (comma-separated usernames to consider as admin)
\set admin_patterns 'admin,superadmin,administrator'
\echo 'Setting up admin user...'
\echo 'Target User ID: ' :'target_user_id'
\echo 'Admin Username: ' :'admin_username'
-- Create backup table for rollback (automatically dropped at end of session)
CREATE TEMP TABLE ownership_backup AS
SELECT 'database' as resource_type, name::text as resource_name, owner_id as original_owner_id
FROM collections_public.database
WHERE name != 'lql'
UNION ALL
SELECT 'user_secrets', name::text, owner_id
FROM lql_simple_secrets.user_secrets
UNION ALL
SELECT 'addresses', (street_address || ' ' || city)::text, owner_id
FROM lql_public.addresses
UNION ALL
SELECT 'emails', email::text, owner_id
FROM lql_public.emails
UNION ALL
SELECT 'phone_numbers', phone_number::text, owner_id
FROM lql_public.phone_numbers
UNION ALL
SELECT 'crypto_addresses', address::text, owner_id
FROM lql_public.crypto_addresses
UNION ALL
SELECT 'connected_accounts', username::text, owner_id
FROM lql_public.connected_accounts
UNION ALL
SELECT 'auth_accounts', email::text, owner_id
FROM lql_roles_public.auth_accounts
UNION ALL
SELECT 'user_encrypted_secrets', name::text, owner_id
FROM lql_encrypted.user_encrypted_secrets
UNION ALL
SELECT 'app_levels', name::text, owner_id
FROM lql_status_public.app_levels;
\echo 'Created backup of current ownership state for rollback safety'
BEGIN; -- Start transaction for rollback safety
DO $$
DECLARE
target_user_id uuid := :'target_user_id'::uuid;
admin_username text := :'admin_username';
admin_display_name text := :'admin_display_name';
user_exists boolean := false;
transferred_count integer := 0;
BEGIN
-- Check if the user exists
SELECT EXISTS(SELECT 1 FROM lql_public.users WHERE id = target_user_id) INTO user_exists;
IF user_exists THEN
-- Update the user to be admin
UPDATE lql_public.users
SET
username = admin_username,
display_name = admin_display_name
WHERE id = target_user_id;
RAISE NOTICE 'Updated user % to admin: % (%)', target_user_id, admin_username, admin_display_name;
-- Grant ownership of all databases (except system database 'lql')
UPDATE collections_public.database
SET owner_id = target_user_id
WHERE name != 'lql';
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % databases to admin user', transferred_count;
-- Grant ownership of user secrets
UPDATE lql_simple_secrets.user_secrets
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % user secrets to admin user', transferred_count;
-- Grant ownership of user addresses
UPDATE lql_public.addresses
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % addresses to admin user', transferred_count;
-- Grant ownership of user emails
UPDATE lql_public.emails
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % emails to admin user', transferred_count;
-- Grant ownership of user phone numbers
UPDATE lql_public.phone_numbers
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % phone numbers to admin user', transferred_count;
-- Grant ownership of user crypto addresses
UPDATE lql_public.crypto_addresses
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % crypto addresses to admin user', transferred_count;
-- Grant ownership of connected accounts
UPDATE lql_public.connected_accounts
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % connected accounts to admin user', transferred_count;
-- Grant ownership of auth accounts
UPDATE lql_roles_public.auth_accounts
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % auth accounts to admin user', transferred_count;
-- Grant ownership of encrypted secrets
UPDATE lql_encrypted.user_encrypted_secrets
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % encrypted secrets to admin user', transferred_count;
-- Grant ownership of app levels
UPDATE lql_status_public.app_levels
SET owner_id = target_user_id;
GET DIAGNOSTICS transferred_count = ROW_COUNT;
RAISE NOTICE 'Transferred % app levels to admin user', transferred_count;
RAISE NOTICE 'SUCCESS: User % is now a super admin with comprehensive ownership!', target_user_id;
ELSE
RAISE WARNING 'ERROR: User % does not exist! Rolling back transaction...', target_user_id;
ROLLBACK;
RETURN;
END IF;
END $$;
-- Commit the transaction if everything succeeded
COMMIT;
\echo ''
\echo '=== ADMIN USERS SUMMARY ==='
SELECT
id as user_id,
username,
display_name,
type,
CASE
WHEN username = ANY(string_to_array(:'admin_patterns', ',')) THEN '✅ ADMIN'
ELSE '👤 Regular'
END as role_status
FROM lql_public.users
ORDER BY
CASE WHEN username = ANY(string_to_array(:'admin_patterns', ',')) THEN 0 ELSE 1 END,
username;
\echo ''
\echo '=== DATABASE OWNERSHIP ==='
SELECT
d.name as database_name,
d.owner_id,
u.username as owner_username,
u.display_name as owner_display_name
FROM collections_public.database d
LEFT JOIN lql_public.users u ON d.owner_id = u.id
ORDER BY d.name;
\echo ''
\echo '=== COMPREHENSIVE OWNERSHIP REPORT ==='
\echo 'Resources owned by user ID: ' :'target_user_id'
SELECT
'Databases' as resource_type,
COUNT(*)::text as count
FROM collections_public.database d
WHERE d.owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Sites (via databases)' as resource_type,
COUNT(*)::text as count
FROM meta_public.sites s
JOIN collections_public.database d ON s.database_id = d.id
WHERE d.owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Domains (via databases)' as resource_type,
COUNT(*)::text as count
FROM meta_public.domains dom
JOIN collections_public.database d ON dom.database_id = d.id
WHERE d.owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'APIs (via databases)' as resource_type,
COUNT(*)::text as count
FROM meta_public.apis a
JOIN collections_public.database d ON a.database_id = d.id
WHERE d.owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'User Secrets' as resource_type,
COUNT(*)::text as count
FROM lql_simple_secrets.user_secrets
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Addresses' as resource_type,
COUNT(*)::text as count
FROM lql_public.addresses
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Emails' as resource_type,
COUNT(*)::text as count
FROM lql_public.emails
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Phone Numbers' as resource_type,
COUNT(*)::text as count
FROM lql_public.phone_numbers
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Crypto Addresses' as resource_type,
COUNT(*)::text as count
FROM lql_public.crypto_addresses
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Connected Accounts' as resource_type,
COUNT(*)::text as count
FROM lql_public.connected_accounts
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Auth Accounts' as resource_type,
COUNT(*)::text as count
FROM lql_roles_public.auth_accounts
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'Encrypted Secrets' as resource_type,
COUNT(*)::text as count
FROM lql_encrypted.user_encrypted_secrets
WHERE owner_id = :'target_user_id'::uuid
UNION ALL
SELECT
'App Levels' as resource_type,
COUNT(*)::text as count
FROM lql_status_public.app_levels
WHERE owner_id = :'target_user_id'::uuid
ORDER BY resource_type;
\echo ''
\echo '✅ Admin setup complete!'
\echo ''
\echo 'CONFIGURATION USED:'
\echo 'Target User ID: ' :'target_user_id'
\echo 'Admin Username: ' :'admin_username'
\echo 'Display Name: ' :'admin_display_name'
\echo 'Excluded Databases: ' :'excluded_databases'
\echo 'Admin Patterns: ' :'admin_patterns'
\echo ''
\echo 'OWNERSHIP TRANSFERRED:'
\echo '✅ All databases (except system database "lql")'
\echo '✅ All sites, domains, and APIs (via database ownership)'
\echo '✅ All user resources: addresses, emails, phone numbers'
\echo '✅ All authentication accounts and connected accounts'
\echo '✅ All secrets (simple and encrypted)'
\echo '✅ All crypto addresses and app levels'
\echo ''
\echo 'SAFETY FEATURES:'
\echo '✅ Transaction rollback on errors'
\echo '✅ Backup table created for manual rollback'
\echo '✅ Excluded system database from transfer'
\echo '✅ Detailed transfer count reporting'
\echo ''
\echo 'The user is now a comprehensive admin with ownership across the entire LaunchQL infrastructure!'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment