Postgres新手求助：pgAdmin4添加新服务器遇FATAL: role "postgres"不存在报错

Hey Aisha, let's work through this step by step—this is a super common gotcha when you're starting out with Postgres, so don’t feel frustrated! The error pops up because Postgres doesn’t always create a postgres role by default (it depends on how you installed the database). Let’s get this sorted:

Step 1: Identify your default Postgres superuser #

First, let’s figure out which user has full access to your Postgres instance. Open your terminal and try running:

psql

If this opens a Postgres prompt (it’ll look like yourusername=#), your operating system username is the default superuser. This is typical for installations like Homebrew (macOS) or some Linux packages.

If that fails with an error like psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "yourusername" does not exist, try specifying the user created during installation. For Windows, try:

psql -U postgres

If that still throws the same "role doesn't exist" error, jump to Step 2b.

Step 2: Create the postgres role (if you can access psql) #

Once you’re in the psql prompt, run these commands to create the postgres role with full superuser privileges and a secure password:

-- Create the postgres role with login access
CREATE ROLE postgres WITH LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;

-- Set a password (replace 'your-strong-password' with something you’ll remember)
ALTER ROLE postgres WITH PASSWORD 'your-strong-password';

Type \q to exit psql after running these.

Step 2b: If you can’t access psql at all #

If you can’t get into psql with any user, you’ll need to start Postgres in single-user mode to create the role:

1. Stop your Postgres server first (method varies by OS: brew services stop postgresql on macOS, sudo systemctl stop postgresql on Linux, or use the Postgres service manager on Windows).
2. Start Postgres in single-user mode. For most systems, run:

   postgres --single -D /path/to/your/postgres/data/directory
   

   You can find the data directory by checking your postgresql.conf file or running psql -c 'SHOW data_directory;' if you ever connected successfully before.
3. Once in single-user mode, run the same SQL commands from Step 2 to create the postgres role.
4. Exit single-user mode (Ctrl+D) and restart your Postgres server.

Step 3: Connect from pgAdmin4 #

Now head back to pgAdmin4 and set up your server again:

• In the "General" tab, give your server a custom name (anything you like).
• In the "Connection" tab:
  • Host name/address: localhost (unless your Postgres is on a remote server).
  • Port: 5432 (default Postgres port).
  • Maintenance database: postgres (this should exist by default; if not, you can create it later).
  • Username: postgres
  • Password: The password you set in Step 2.
• Click "Save"—it should connect without issues now!

Quick Troubleshooting Notes #

• If you installed Postgres via Docker: Double-check your container’s environment variables. If you set POSTGRES_USER to something else during setup, that’s your superuser, not postgres. You can either use that user in pgAdmin or create the postgres role inside the container (run docker exec -it your-container-name psql -U your-db-user to access it).
• If you’re on Windows: Sometimes the Postgres installer skips creating the postgres user if you don’t select the right options. Re-running the installer and enabling the "Create superuser account" option works, but the steps above fix it without reinstalling.

Let me know if any of these steps give you trouble—I’m happy to help refine things further!

内容的提问来源于stack exchange，提问作者aishazafar