Troubleshooting Database Permissions

After successfully authenticating to your database, Portable verifies that your user has the necessary permissions to perform the required operations. If this step fails, you'll see a "Database Permissions" or "User Privileges" error in the diagnostic checks.

What This Means

Portable can connect and authenticate to your database, but the user lacks the permissions needed for the operation. Sources need read permissions; destinations need write permissions.

Required Permissions by Role

Sources (Read-Only)

Sources need to read data and schema metadata from your database.

Database	Required Permissions
PostgreSQL	USAGE on schema, SELECT on tables, access to pg_catalog
MySQL	SELECT on database/tables, access to information_schema

Destinations (Read-Write)

Destinations need to create tables and write data.

Database	Required Permissions
PostgreSQL	USAGE and CREATE on schema, SELECT, INSERT, UPDATE, DELETE on tables
MySQL	SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP on database

Common Causes

1. User Has No Permissions

The user was created but never granted any permissions.

PostgreSQL:

-- Check user's permissions
SELECT grantee, table_schema, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'myuser';

MySQL:

-- Check user's permissions
SHOW GRANTS FOR 'myuser'@'%';

2. Permissions on Wrong Schema/Database

Permissions were granted on a different schema or database than the one configured in Portable.

PostgreSQL - Permissions are schema-specific:

-- Grants on 'public' schema don't apply to 'myschema'
GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;

MySQL - Permissions are database-specific:

-- Grants on 'testdb' don't apply to 'proddb'
GRANT SELECT ON proddb.* TO 'myuser'@'%';

3. Table-Level vs Schema/Database-Level Permissions

Permissions may have been granted on specific tables rather than the entire schema/database. This can cause issues when new tables are added.

PostgreSQL:

-- This only grants on EXISTING tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

-- This grants on FUTURE tables too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO myuser;

MySQL:

-- Grant on all tables in database
GRANT SELECT ON mydb.* TO 'myuser'@'%';

-- vs grant on specific table only
GRANT SELECT ON mydb.specific_table TO 'myuser'@'%';

4. Missing Schema USAGE Permission (PostgreSQL)

In PostgreSQL, even if you have SELECT on tables, you also need USAGE on the schema to access them.

-- Both are required
GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;

5. Missing Metadata Access

Portable needs to read table metadata (column names, types, etc.) to function properly.

PostgreSQL requires access to pg_catalog:

-- Usually granted by default, but verify
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO myuser;

MySQL requires access to information_schema:

-- Usually accessible by default if user has any grants

6. Role Inheritance Issues (PostgreSQL)

If permissions are granted to a role, ensure your user is a member of that role.

-- Check role membership
SELECT r.rolname as user, r2.rolname as member_of
FROM pg_roles r
JOIN pg_auth_members m ON r.oid = m.member
JOIN pg_roles r2 ON m.roleid = r2.oid
WHERE r.rolname = 'myuser';

-- Grant role membership
GRANT readonly_role TO myuser;

How to Fix

PostgreSQL Source (Read-Only)

-- Option 1: Grant on specific schema
GRANT USAGE ON SCHEMA public TO myuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO myuser;

-- Option 2: Grant on all schemas (superuser required)
GRANT pg_read_all_data TO myuser;  -- PostgreSQL 14+

PostgreSQL Destination (Read-Write)

GRANT USAGE, CREATE ON SCHEMA public TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

-- For creating sequences (if needed)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO myuser;

MySQL Source (Read-Only)

GRANT SELECT ON mydb.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

MySQL Destination (Read-Write)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON mydb.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

Cloud-Specific Instructions

AWS RDS

RDS uses standard PostgreSQL/MySQL permissions. Connect as the master user to grant permissions:

-- PostgreSQL
GRANT rds_superuser TO myuser;  -- Full access (use cautiously)
-- Or grant specific permissions as shown above

-- MySQL
GRANT SELECT ON mydb.* TO 'myuser'@'%';

Google Cloud SQL

Cloud SQL uses standard permissions. The default admin user can grant permissions:

-- PostgreSQL
GRANT cloudsqlsuperuser TO myuser;  -- Full access
-- Or grant specific permissions

-- MySQL
GRANT SELECT ON mydb.* TO 'myuser'@'%';

Azure Database

Azure uses standard permissions with some restrictions on superuser access:

-- PostgreSQL (Azure)
-- azure_pg_admin role has elevated permissions
GRANT azure_pg_admin TO myuser;  -- If available
-- Or grant specific permissions

-- MySQL (Azure)
GRANT SELECT ON mydb.* TO 'myuser'@'%';

Verifying Permissions

PostgreSQL

-- Check schema permissions
SELECT has_schema_privilege('myuser', 'public', 'USAGE') as has_usage,
       has_schema_privilege('myuser', 'public', 'CREATE') as has_create;

-- Check table permissions
SELECT has_table_privilege('myuser', 'public.mytable', 'SELECT') as can_select,
       has_table_privilege('myuser', 'public.mytable', 'INSERT') as can_insert;

-- List all grants
SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'myuser';

MySQL

-- Show all grants for user
SHOW GRANTS FOR 'myuser'@'%';

-- Check specific privilege
SELECT * FROM information_schema.user_privileges
WHERE grantee LIKE "'myuser'%";

SELECT * FROM information_schema.schema_privileges
WHERE grantee LIKE "'myuser'%" AND table_schema = 'mydb';

Checklist

Before contacting support, verify:

• User has the correct permission level for the role (source vs destination)
• Permissions are on the correct schema (PostgreSQL) or database (MySQL)
• For PostgreSQL: User has USAGE on the schema
• For new tables: Default privileges are set (PostgreSQL) or database-wide grants exist (MySQL)
• You've run FLUSH PRIVILEGES after granting (MySQL)
• Run the verification queries above to confirm permissions

Still Stuck?

If you've granted permissions but Portable still reports an error, contact support with:

• Your database type (PostgreSQL, MySQL)
• Whether this is a source or destination
• The output of SHOW GRANTS (MySQL) or the permission check queries (PostgreSQL)
• The schema/database name configured in Portable
• Your source/destination ID from Portable