After creating a new user in PostgreSQL, the user does not have access to run any queries on existing databases or create any new databases. This can lead to confusion — especially when you see your user listed with \du but can’t actually connect or modify data.

Why do we need another user?

postgres Users with superuser permissions can lead to serious security issues, including unauthorized access, data leaks, or accidental data loss. A superuser can bypass all permission checks, modify system catalogs, drop databases, or execute arbitrary code.
Always reserve superuser access for administrative tasks only and use least-privilege roles for applications or developers.

🔍 The Problem

After creating a user:

CREATE USER thor WITH PASSWORD 'choti-master';

Running \du shows:

Role name | Attributes
-----------+------------
 thor      |

But thor can’t access databases or perform any operations.

✅ The Fix

You need to grant privileges manually.

  1. Log in as superuser:
psql -U postgres

2. Grant privileges on all existing databases:

DO $$
DECLARE
    db RECORD;
BEGIN
    FOR db IN SELECT datname FROM pg_database WHERE datistemplate = false LOOP
        EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO thor;', db.datname);
    END LOOP;
END
$$;

3. Optionally, allow thor to create new databases:

ALTER USER thor CREATEDB;

4. Inside each database, grant schema-level privileges:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO thor; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO thor; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO thor;

Result

Now thor can connect, query, and manage data across all your databases — without hitting those frustrating “permission denied” errors.