How to fix "your account is locked. please contact admin" error in pgAdmin?

Question

I'm new to PostgreSQL.I'm using postgreSQL locally. I used it last time without any issues. Now, when I try login with my login credentials it shows the account is locked message all the time.

Answer 1

According to the pgadmin documentation there are two ways to unlock the user.

1. Updating SQLite DB (pgAdmin4.db):

Locate the pgAdmin4.db file and open it using any DB Browser (or DB Browser for SQLite)

After opening the DB file, head towards ‘Execute SQL’ section. Run below query :

UPDATE USER SET LOCKED = false, LOGIN_ATTEMPTS = 0 WHERE USERNAME = <YOUR_EMAIL_ID>

And make sure the query changes are committed.

2. Increase the MAX_LOGIN_ATTEMPTS in the pgadmin configuration file config.py or preferably add a line in config_local.py

MAX_LOGIN_ATTEMPTS = 5

Answer 2

As @alfredo138923 explains, you should find pgadmin4.db on your system and run a query to update the USER attempts and unlock the application for the specific user account.

But the correct SQL query must be fixed. The query parameter value must use single quotes, otherwise an error will be returned.

UPDATE USER SET LOCKED = false, LOGIN_ATTEMPTS = 0 WHERE USERNAME = '<YOUR_EMAIL>'

An example on the shell command line might be (i use Ubuntu 20.04)*:

sqlite3 pgadmin4.db "UPDATE USER SET LOCKED = false, LOGIN_ATTEMPTS = 0 WHERE USERNAME = 'user.name@domain.com';" ".exit"

*Before using sqlite3 from the command line, you need to install it as below.

apt-get install sqlite3

Answer 3

These solutions work very well, but if you've deployed pgadmin to a cluster or in a container, where you don't have sudo rights and the sqlite CLI tool is not installed, then you can also just use python to execute the query:

Back up your db before executing this! cp /var/lib/pgadmin/pgadmin4.db /var/lib/pgadmin/pgadmin4.db.backup

import sqlite3
db_path = '/var/lib/pgadmin/pgadmin4.db'
query = "update user set locked = false, login_attempts = 0 where username = '<admin_email>';"
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
print('User should be unlocked now. Changes commited to the DB.')

Note: this script doesn't have any error checking!

Tested this with https://artifacthub.io/packages/helm/runix/pgadmin4/1.2.20 Which is using image https://hub.docker.com/r/dpage/pgadmin4/

Answer 4

If you're running a docker-compose.yml like this ...

x-reporting-source:
  &reportingsource
  type: 'bind'
  source: /mnt/docker/reporting/reporting_source
  target: /share/reporting_source
  bind:
    create_host_path: true
x-postgresdata:
  &postgresdata
  type: 'bind'
  source: /mnt/docker/reporting/postgresdata
  target: /var/lib/postgresql/data
  bind:
    create_host_path: true
x-postgres-scripts:
  &postgresscripts
  type: 'bind'
  source: /mnt/docker/reporting/postgresscripts
  target: /docker-entrypoint-initdb.d
  bind:
    create_host_path: true
x-pgadmin-data:
  &pgadmindata
  type: 'bind'
  source: /mnt/docker/reporting/pgadmin
  target: /var/lib/pgadmin
  bind:
    create_host_path: true
services:
postgres:
    image: postgres
    restart: unless-stopped
    networks:
      - default
    # set shared memory limit when using docker-compose
    shm_size: 128mb
    volumes:
      - <<: postgresdata
      - <<: postgresscripts
      - <<: *reportingsource
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: yourdbuser
      POSTGRES_PASSWORD: yourp4ssw0rd
      PGDATA: '/var/lib/postgresql/data/pgdata'
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U yourdbuser -d yourdbuser"]
      interval: 10s
      timeout: 5s
      retries: 5
pgadmin:
    # https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html
    image: dpage/pgadmin4
    restart: unless-stopped
    networks:
      - default
    ports:
      - 8888:80
    volumes:
      - <<: *pgadmindata
    environment:
      PGADMIN_DEFAULT_EMAIL: your.admin@yoursite.com
      PGADMIN_DEFAULT_PASSWORD: yourp4ssw0rd
      PGADMIN_CONFIG_MAX_LOGIN_ATTEMPTS: 10
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:80"]
      interval: 60s
      timeout: 10s
      retries: 5
    depends_on:
      postgres:
        condition: service_healthy

You can just restart both containers and the lock will be undone. So a docker compose down --remove-orphans && docker compose up -d should do the trick.

Answer 5

Steps to Solve this Issue

First Locate the pgAdmin4.db in Ubuntu 20.04 you find following path

cd /var/lib/pgadmin

then run the following command

sqlite3 pgadmin4.db there might be two files but choose the file of heavy size

SQLite will be opened

now than run the command UPDATE USER SET LOCKED = false, LOGIN_ATTEMPTS = 0 WHERE USERNAME = '<YOUR_EMAIL>'

now you can login