Adminer Plugin to manage users in Postgres database

postgres-user-management.php

<?php

class PostgresUserManagementPlugin extends Adminer\Plugin
{
    private string $schema = 'public';
    private string $message = '';
    private bool $isError = false;

    /* ---------------------------------------------------------------
     * HEAD — inject styles only (no output buffering tricks).
     * ------------------------------------------------------------- */
    function head()
    {
        if (!isset($_GET['user_mgmt'])) return;

        echo '<style>
            .um-container { margin-top: 10px; }
            .um-container table.layout th { text-align: left; padding-right: 20px; }
            .um-container form { display: flex; flex-direction: column; gap: 4px; }
            .um-container form > div { display: flex; flex-direction: column; }
            .um-container input[type="text"], .um-container input[type="password"], .um-container select { width: 300px; height: 28px }
            .um-container select[multiple=""] { height: 180px }
            
            .um-tabs { display: flex; gap: 0; margin-bottom: 20px; border-bottom: 2px solid #e0e0e0; }
            .um-tab { padding: 12px 24px; cursor: pointer; background: #f5f5f5; border: none; font-size: 14px; font-weight: 500; color: #666; transition: all 0.2s; border-radius: 4px 4px 0 0; }
            .um-tab:hover { background: #e8e8e8; color: #333; }
            .um-tab.active { background: white; color: #2196F3; border-bottom: 3px solid #2196F3; margin-bottom: -2px; }
            .um-tab-content { display: none; }
            .um-tab-content.active { display: block; }
            
            .um-users-table { width: 100%; border-collapse: collapse; background: white; box-shadow: 0 1px 3px rgba(0,0,0,0.1); border-radius: 8px; overflow: hidden; }
            .um-users-table thead { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; }
            .um-users-table th { padding: 16px; text-align: left; font-weight: 600; font-size: 13px; text-transform: uppercase; letter-spacing: 0.5px; }
            .um-users-table td { padding: 14px 16px; border-bottom: 1px solid #f0f0f0; font-size: 14px; }
            .um-users-table tbody tr { transition: background 0.2s; }
            .um-users-table tbody tr:hover { background: #f8f9ff; }
            .um-users-table tbody tr:last-child td { border-bottom: none; }
            
            .um-badge { display: inline-block; padding: 4px 12px; border-radius: 12px; font-size: 12px; font-weight: 600; }
            .um-badge.superuser { background: #ff5252; color: white; }
            .um-badge.createdb { background: #4caf50; color: white; }
            .um-badge.createrole { background: #ff9800; color: white; }
            .um-badge.inherit { background: #2196f3; color: white; }
            .um-badge.login { background: #9c27b0; color: white; }
            .um-badge.replication { background: #00bcd4; color: white; }
            
            .um-no-users { text-align: center; padding: 40px; color: #999; font-size: 16px; }
            .um-no-users svg { width: 64px; height: 64px; margin-bottom: 16px; opacity: 0.3; }
        </style>';
    }

    /**
     * NAVIGATION — sidebar link.
     */
    function navigation($missing)
    {
        $params = $_GET;
        $params['user_mgmt'] = '1';
        // Clear other page-specific params to ensure we go to our custom page
        unset($params['table'], $params['select'], $params['edit'], $params['sql'], $params['import'], $params['dump']);
        $url = '?' . http_build_query($params);

        echo '<p><a href="' . \Adminer\h($url) . '"' . (\Adminer\bold(isset($_GET['user_mgmt']))) . '>👤 User Management</a></p>';
    }

    /**
     * Override homepage to render custom page when user_mgmt is set
     */
    function homepage()
    {
	// \Adminer\page_header('User Management', "", array(), \Adminer\h("A"));
        // Handle Form Submission
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $action = $_POST['um_action'] ?? '';
            if ($action === 'create_user') {
                $this->handle();
            } elseif ($action === 'delete_user') {
                $this->deleteUser();
            }
        }
        if ($this->message) {
            echo "<p class='message'>" . $this->message . "</p>";
        }

        if (isset($_GET['user_mgmt'])) {
            // Render the tabbed interface
            $this->renderInterface();

            return false;
        }
    }

    private function renderInterface()
    {
        $activeTab = $_GET['um_tab'] ?? 'create';
        
        echo '<div class="um-container">';
        
        // Tabs
        echo '<div class="um-tabs">';
        echo '<button class="um-tab ' . ($activeTab === 'create' ? 'active' : '') . '" data-tab="create">➕ Create User</button>';
        echo '<button class="um-tab ' . ($activeTab === 'list' ? 'active' : '') . '" data-tab="list">📋 User List</button>';
        echo '</div>';
        
        // Tab Contents
        echo '<div id="um-tab-create" class="um-tab-content ' . ($activeTab === 'create' ? 'active' : '') . '">';
        $this->renderForm();
        echo '</div>';
        
        echo '<div id="um-tab-list" class="um-tab-content ' . ($activeTab === 'list' ? 'active' : '') . '">';
        $this->renderUserList();
        echo '</div>';
        
        echo '</div>';
        
        // Tab switching script
        $nonce = \Adminer\nonce();
        echo '<script ' . $nonce . ' defer>';
        echo '
        document.addEventListener("DOMContentLoaded", function() {
            var tabButtons = document.querySelectorAll(".um-tab");
            tabButtons.forEach(function(button) {
                button.addEventListener("click", function(evt) {
                    var tabName = evt.currentTarget.getAttribute("data-tab");
                    var tabs = document.getElementsByClassName("um-tab");
                    for (var i = 0; i < tabs.length; i++) {
                        tabs[i].className = tabs[i].className.replace(" active", "");
                    }
                    var contents = document.getElementsByClassName("um-tab-content");
                    for (var i = 0; i < contents.length; i++) {
                        contents[i].className = contents[i].className.replace(" active", "");
                    }
                    evt.currentTarget.className += " active";
                    document.getElementById("um-tab-" + tabName).className += " active";
                });
            });
        });
        ';
        echo '</script>';
    }

    private function renderForm()
    {
        $objects = $this->loadGrantableObjects();

        $formAction = $this->buildPageUrl();

        echo '<div class="um-container">';
        echo '<form method="post" action="' . \Adminer\h($formAction) . '">';
        echo '<input type="hidden" name="um_action" value="create_user">';
        echo \Adminer\input_token();
 
        echo '<div>';
        echo '<label>Username</label>';
        echo '<input name="user" type="text" required autocomplete="off" value="' . \Adminer\h($_POST['user'] ?? '') . '">';
        echo '</div>';
 
        echo '<div>';
        echo '<label>Password</label>';
        echo '<input type="password" name="pass" required autocomplete="new-password">';
        echo '</div>';
 
        echo '<div>';
        echo '<label>Access Level</label>';
        echo '<select name="access">
                <option value="ro">Read Only</option>
                <option value="rw">Read / Write</option>
              </select>';
        echo '</div>';
 
        echo '<div>';
        echo '<label>Table / View Scope</label>';
        echo '<select name="scope" id="um-scope">
                <option value="all">All Tables and Views</option>
                <option value="selected">Selected Tables and Views</option>
              </select>';
        echo '</div>';
 
        echo '<div id="um-objects-wrap" style="display:none">';
        echo '<label>Tables / Views (Ctrl/Cmd for multi-select)</label>';
        echo '<select multiple name="um_objects[]" size="10">';
        foreach ($objects as $object) {
            $objectName = (string) ($object['object_name'] ?? '');
            $objectType = (string) ($object['object_type'] ?? 'table');
            echo '<option value="' . \Adminer\h($objectName) . '">' . \Adminer\h($objectName . ' (' . $objectType . ')') . '</option>';
        }
        echo '</select>';
        echo '</div>';
 
        echo '<br><button type="submit">Create User</button>';
        echo '</form>';
        echo '</div>';
 
        // Scope selector script
        $nonce = \Adminer\nonce();
        echo '<script ' . $nonce . '>';
        echo 'document.addEventListener("DOMContentLoaded", function() {
            var scope = document.getElementById("um-scope");
            var wrap  = document.getElementById("um-objects-wrap");
            if (scope && wrap) {
                scope.addEventListener("change", function() {
                    wrap.style.display = scope.value === "selected" ? "" : "none";
                });
            }
        });
        </script>';
    }

    private function buildPageUrl(array $params = []): string
    {
        return '?' . http_build_query(array_merge(
            array_filter(
                $_GET,
                fn($k) => in_array($k, ['pgsql', 'server', 'username', 'db', 'driver', 'ns']),
                ARRAY_FILTER_USE_KEY
            ),
            ['user_mgmt' => '1'],
            $params
        ));
    }

    private function handle()
    {
        // CSRF Check
        if (!\Adminer\verify_token()) {
            $this->message = "Invalid CSRF token.";
            $this->isError = true;
            return;
        }

        $user   = trim($_POST['user']   ?? '');
        $pass   = $_POST['pass']        ?? '';
        $access = $_POST['access']      ?? 'ro';
        $scope  = $_POST['scope']       ?? 'all';
        $objects = $_POST['um_objects'] ?? [];

        if (!$this->validIdent($user)) {
            $this->message = "Invalid username — letters, digits and underscores only, must start with a letter or underscore.";
            $this->isError = true;
            return;
        }

        if (strlen($pass) < 8) {
            $this->message = "Password must be at least 8 characters.";
            $this->isError = true;
            return;
        }

        // Ensure connection exists
        $connection = \Adminer\connection();
        if (!$connection) {
            $this->message = "Database connection not available.";
            $this->isError = true;
            return;
        }

        $escapedPass = \Adminer\q($pass);
        $db          = $_GET['db'];

        if (!$this->exec("CREATE USER \"$user\" WITH PASSWORD $escapedPass")) {
            $this->message = "Error while creating user.";
            $this->isError = true;
            return;
        }
        // if (!$this->exec("GRANT ALL PRIVILEGES ON DATABASE \"$db\" TO \"$user\"")) return;
        // if (!$this->exec("GRANT USAGE ON SCHEMA \"$this->schema\" TO \"$user\"")) return;

        $permList = ($access === 'rw') ? 'SELECT, INSERT, UPDATE, DELETE' : 'SELECT';

        if ($scope === 'all') {
            if (!$this->exec("GRANT $permList ON ALL TABLES IN SCHEMA \"$this->schema\" TO \"$user\"")) return;
            if ($access === 'rw') {
                $this->exec("GRANT ALL PRIVILEGES ON DATABASE \"$db\" TO \"$user\"");
            }
        } else {
            foreach ($objects as $objectName) {
                $objectName = trim($objectName);
                if (!$this->validIdent($objectName)) continue;
                if (!$this->exec("GRANT $permList ON TABLE \"$this->schema\".\"$objectName\" TO \"$user\"")) {
                    $this->message .= "Error granting permissions on $objectName to user.";
                    $this->isError = true;
                    return;
                }
            }
        }

        $this->message = "✅ User <strong>" . \Adminer\h($user) . "</strong> created successfully.";
        $this->isError = false;
    }

    private function deleteUser(): void
    {
        if (!\Adminer\verify_token()) {
            $this->message = "Invalid CSRF token.";
            $this->isError = true;
            return;
        }

        $user = trim($_POST['delete_user'] ?? '');
        if (!$this->validIdent($user)) {
            $this->message = "Invalid username.";
            $this->isError = true;
            return;
        }

        $currentUser = $this->getCurrentUser();
        if ($currentUser !== '' && $user === $currentUser) {
            $this->message = "You cannot delete the current user.";
            $this->isError = true;
            return;
        }

        if (!$this->exec("REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA \"$this->schema\" FROM \"$user\"")) {
            $this->message = "Error deleting user.";
            return;
        }

        if (!$this->exec("REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA \"$this->schema\" FROM \"$user\"")) {
            $this->message = "Error deleting user.";
            return;
        }

        if (!$this->exec("REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA \"$this->schema\" FROM \"$user\"")) {
            $this->message = "Error deleting user.";
            return;
        }

        if (!$this->exec("REVOKE USAGE ON SCHEMA \"$this->schema\" FROM \"$user\"")) {
            $this->message = "Error deleting user.";
            return;
        }

        if (!$this->exec("DROP USER \"$user\"")) {
            $this->message = "Error deleting user.";
            return;
        }

        $this->message = "✅ User <strong>" . \Adminer\h($user) . "</strong> deleted successfully.";
        $this->isError = false;
    }

    private function exec(string $sql): bool
    {
        $res = \Adminer\queries($sql);
        if ($res === false) {
            $conn = \Adminer\connection();
            $err  = $conn ? $conn->error : 'Unknown error';
            $this->message = "SQL error: " . \Adminer\h((string) $err);
            $this->isError = true;
            return false;
        }
        return true;
    }

    private function loadGrantableObjects(): array
    {
        if (!\Adminer\connection()) return [];
        $rows = \Adminer\get_rows(
            "SELECT table_name AS object_name,\n"
            . "       CASE WHEN table_type = 'VIEW' THEN 'view' ELSE 'table' END AS object_type\n"
            . "FROM information_schema.tables\n"
            . "WHERE table_schema = " . \Adminer\q($this->schema) . "\n"
            . "  AND table_type IN ('BASE TABLE', 'VIEW')\n"
            . "ORDER BY object_type, object_name"
        );
        return $rows ?: [];
    }

    private function validIdent(string $str): bool
    {
        return (bool) preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $str);
    }

    private function getCurrentUser(): string
    {
        try {
            return \Adminer\logged_user();
            $rows = \Adminer\get_rows("SELECT current_user AS current_user");
            if (!empty($rows[0]['current_user'])) {
                return (string) $rows[0]['current_user'];
            }
        } catch (\Exception $e) {
        }

        return (string) ($_GET['username'] ?? '');
    }

    private function renderUserList()
    {
        $users = $this->loadUsers();
        $currentUser = $this->getCurrentUser();
        $deleteAction = $this->buildPageUrl(['um_tab' => 'list']);

        // Debug: show what we got
        if (empty($users)) {
            echo '<div class="um-no-users">';
            echo '<svg fill="currentColor" viewBox="0 0 24 24"><path d="M12 12c2.21 0 4-1.79 4-4s-1.79-4-4-4-4 1.79-4 4 1.79 4 4 4zm0 2c-2.67 0-8 1.34-8 4v2h16v-2c0-2.66-5.33-4-8-4z"/></svg>';
            echo '<p>No users found</p>';
            echo '<pre style="text-align:left;font-size:11px;">Debug: ' . \Adminer\h(print_r($users, true)) . '</pre>';
            echo '</div>';
            return;
        }

        echo '<table class="um-users-table">';
        echo '<thead>';
        echo '<tr>';
        echo '<th>Username</th>';
        echo '<th>Actions</th>';
        echo '</tr>';
        echo '</thead>';
        echo '<tbody>';

        foreach ($users as $user) {
            $username = (string) ($user['usename'] ?? '');
            echo '<tr>';
            echo '<td><strong>' . \Adminer\h($username) . '</strong></td>';
            echo '<td>';
            if ($username === $currentUser) {
                echo '<span>Current User</span>';
            } else {
                echo '<form method="post" action="' . \Adminer\h($deleteAction) . '" onsubmit="return confirm(\'Delete this user?\');">';
                echo '<input type="hidden" name="um_action" value="delete_user">';
                echo '<input type="hidden" name="delete_user" value="' . \Adminer\h($username) . '">';
                echo \Adminer\input_token();
                echo '<button type="submit">Delete</button>';
                echo '</form>';
            }
            echo '</td>';
            echo '</tr>';
        }

        echo '</tbody>';
        echo '</table>';
    }

    private function loadUsers(): array
    {
        $conn = \Adminer\connection();
        if (!$conn) {
            error_log("No database connection");
            return [];
        }

        $sql = "SELECT usename
                FROM pg_user
                ORDER BY usename";
        try {
            $rows = \Adminer\get_rows($sql);
            error_log("Query returned " . count($rows ?: []) . " rows");
            return $rows ?: [];
        } catch (\Exception $e) {
            error_log("Query error: " . $e->getMessage());
            return [];
        }
    }
}

return new PostgresUserManagementPlugin();

Create users, assign access to tables, and delete users through this Adminer plugin for Postgres Databases.