How to fix permission denied for schema in PostgreSQL

-- Check current user and database:
SELECT current_user, current_database();

-- List all schemas:
\dn
-- or:
SELECT schema_name FROM information_schema.schemata;

-- Check which schema the user is trying to access:
SHOW search_path;  -- Shows the default schema search order

# Connect as superuser (postgres user is typically the default superuser)
psql -U postgres -d your_database

# Or if using a connection string:
psql postgresql://admin@localhost/your_database

-- Grant USAGE privilege on the public schema:
GRANT USAGE ON SCHEMA public TO your_username;

-- Or for a custom schema:
GRANT USAGE ON SCHEMA your_schema TO your_username;

-- Grant to a role (if the user is part of a role):
GRANT USAGE ON SCHEMA public TO your_role;

-- Grant CREATE privilege on the public schema:
GRANT CREATE ON SCHEMA public TO your_username;

-- Grant both USAGE and CREATE together:
GRANT USAGE, CREATE ON SCHEMA public TO your_username;

-- Or grant ALL privileges (USAGE + CREATE + other administrative privileges):
GRANT ALL ON SCHEMA public TO your_username;

-- Grant SELECT, INSERT, UPDATE, DELETE on all existing tables:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_username;

-- Or for read-only access:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_username;

-- Grant usage on sequences (required for auto-increment columns):
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO your_username;

-- Grant execute on functions:
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO your_username;

-- As the schema owner or superuser, set defaults:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_username;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO your_username;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO your_username;

-- Check schema privileges:
SELECT grantee, privilege_type
FROM information_schema.role_schema_grants
WHERE schema_name = 'public' AND grantee = 'your_username'
ORDER BY privilege_type;

-- Check table privileges:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public' AND grantee = 'your_username'
ORDER BY table_name, privilege_type;

-- For a comprehensive view of what the user can do:
\dp public.*

# Connect as the user that had permission errors:
psql -U your_username -d your_database

-- Test schema access:
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';

-- Test creating a table (if CREATE was granted):
CREATE TABLE test_table (id INT, name VARCHAR(100));

-- Test inserting data:
INSERT INTO test_table VALUES (1, 'test');

-- Clean up:
DROP TABLE test_table;