relation "users" does not exist 해결 방법, psql에서는 보이는 테이블을 못 찾는 이유
문제 발생
로컬에서 잘 돌던 서비스를 새 DB에 붙였더니 조회가 전부 실패했습니다.
ERROR: relation "users" does not exist
LINE 1: SELECT * FROM Users;
^이상한 건 psql로 들어가 \dt를 치면 Users 테이블이 멀쩡히 보인다는 점이었습니다. 테이블이 있는데 없다고 합니다. 더 헷갈린 건 에러 메시지였습니다. 저는 Users라고 썼는데 에러는 소문자 "users"를 찾을 수 없다고 합니다. 제가 쓴 적 없는 이름입니다.
며칠 뒤에는 다른 동료가 같은 메시지를 다른 이유로 만났습니다. 그쪽은 테이블 이름에 대문자가 하나도 없었고, 운영 DB에서만 안 됐습니다. 그리고 세 번째로는 비슷하게 생긴 이 메시지가 나왔습니다.
ERROR: permission denied for schema public
ERROR: permission denied for table orders세 가지가 전부 "테이블을 못 쓴다"로 보이는데 원인은 전혀 달랐습니다. 이 글은 PostgreSQL 16.15에서 직접 재현했습니다.
원인 분석
따옴표 없는 이름은 전부 소문자로 접힙니다. PostgreSQL 문서의 식별자 설명에 그대로 적혀 있습니다. 식별자를 따옴표로 감싸면 대소문자를 구분하게 되지만, 따옴표 없는 이름은 항상 소문자로 접힌다고요. 그래서 제가 쓴 SELECT * FROM Users는 서버에 도착하기 전에 users를 찾는 쿼리가 됩니다. 에러 메시지에 소문자가 찍힌 게 그 증거였습니다. 반대로 따옴표 없이 만든 테이블은 어떻게 써도 됩니다.
CREATE TABLE Orders (id int); -- 실제로 저장된 이름은 orders
SELECT count(*) FROM ORDERS; -- 된다
SELECT count(*) FROM "orders"; -- 된다같은 문서는 키워드와 따옴표 없는 식별자는 대소문자를 구분하지 않는다고도 적습니다. 평소에 SELECT든 select든 되는 이유와 같은 규칙입니다.
문제는 따옴표로 만든 이름입니다. 그 순간부터 따옴표가 평생 따라붙습니다. ORM이나 GUI 도구가 생성한 DDL이 CREATE TABLE "Users"처럼 따옴표를 붙여 두면, 저장된 이름이 정말로 Users가 됩니다. 재현 결과입니다.
CREATE TABLE "Users" (id int, "userName" text);
SELECT * FROM Users; → ERROR: relation "users" does not exist
SELECT * FROM users; → ERROR: relation "users" does not exist
SELECT count(*) FROM "Users"; → 정상컬럼도 똑같습니다. 그리고 컬럼 쪽은 PostgreSQL이 친절하게 힌트를 줍니다.
SELECT userName FROM "Users";
ERROR: column "username" does not exist
HINT: Perhaps you meant to reference the column "Users.userName".이 힌트가 나오면 범인은 따옴표입니다. 참고로 같은 문서는 따옴표 없는 이름을 소문자로 접는 것이 SQL 표준과 호환되지 않으며, 표준은 대문자로 접으라고 한다고 밝힌 뒤, 이식성 있는 애플리케이션을 쓰려면 특정 이름을 항상 따옴표로 쓰거나 아예 쓰지 말라고 권합니다. 섞어 쓰는 게 가장 나쁩니다.
대문자가 없는데도 같은 에러가 난다면 search_path입니다. 두 번째 사례가 이쪽이었습니다. 테이블은 shop 스키마에 있고, 쿼리는 스키마를 붙이지 않았습니다.
CREATE SCHEMA shop;
CREATE TABLE shop.items (id int);
SELECT count(*) FROM items; → ERROR: relation "items" does not exist
SELECT count(*) FROM shop.items; → 정상
SET search_path TO shop, public;
SELECT count(*) FROM items; → 정상문서의 설명이 이 상황을 정확히 적어 둡니다. 검색 경로에서 처음 일치하는 테이블이 찾던 것으로 간주되고, 검색 경로에 일치하는 것이 없으면 데이터베이스의 다른 스키마에 같은 이름의 테이블이 있어도 에러가 보고된다고요. 기본값은 "$user", public인데, 첫 항목은 현재 사용자 이름과 같은 스키마를 찾으라는 뜻이고 그런 스키마가 없으면 무시된다고 설명합니다. 즉 기본 상태에서 실제로 쓰이는 건 public 하나입니다. 로컬에서 전부 public에 만들어 두고 운영만 스키마를 나눠 뒀다면, 로컬에서는 절대 재현되지 않는 에러가 됩니다.
같은 이름이 두 스키마에 있으면 순서가 결과를 바꿉니다. 더 고약한 경우입니다. 에러도 안 나고 조용히 다른 테이블을 읽습니다.
SET search_path TO public, shop; SELECT src FROM items; → public
SET search_path TO shop, public; SELECT count(*) FROM items; → shop 쪽 1건비슷해 보이는 권한 에러는 다른 문제입니다. 세 번째 사례였습니다. 일반 역할로 접속해 테이블을 만들려니 이렇게 막혔습니다.
CREATE TABLE mine (id int); → ERROR: permission denied for schema public
SELECT count(*) FROM orders; → ERROR: permission denied for table orders앞쪽은 PostgreSQL 15에서 바뀐 기본값 때문입니다. 15 릴리스 노트의 이전 버전 마이그레이션 항목에 public 스키마의 PUBLIC 생성 권한 제거가 올라와 있고, 기존 데이터베이스, 특히 사용자가 여럿인 경우에는 이 새 기본값을 따르도록 public 스키마의 CREATE 권한을 회수하는 것을 고려하라고 적혀 있습니다. 그래서 14까지 되던 "아무 역할로나 public에 테이블 만들기"가 15부터 막힙니다. 뒤쪽은 테이블은 찾았지만 SELECT 권한이 없다는 뜻입니다. 없다(does not exist)와 권한이 없다(permission denied)는 서로 다른 조사라는 걸 기억해 두면 시간을 아낍니다.
해결 방안
- 추측하지 말고 저장된 이름과 스키마를 먼저 봅니다. 이 한 줄이면 대소문자와 스키마가 동시에 확인됩니다.
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name ILIKE '%user%';ILIKE로 찾는 게 중요합니다. =로 비교하면 내가 틀린 그 이름으로 찾게 되니 또 빈 결과가 나옵니다. psql에서는 \dt *.*로 모든 스키마의 테이블을 볼 수 있고, SHOW search_path로 지금 경로를 확인합니다. 결과에 대문자가 섞여 있으면 따옴표 문제, 스키마가 public이 아니면 경로 문제입니다.
-
새로 만들 때는 따옴표 없이 snake_case로 통일합니다.
user_name처럼 쓰면 따옴표를 쓸 일이 없고, 어디서 어떻게 호출해도 같은 테이블을 가리킵니다. ORM을 쓴다면 테이블·컬럼 이름 전략을 snake_case로 맞춰 두는 게 이 문제를 처음부터 없앱니다. 문서의 권고도 "항상 따옴표" 아니면 "절대 따옴표"였습니다. 팀에서는 후자가 훨씬 지키기 쉽습니다. -
이미 대문자로 만들어졌다면 이름을 바꾸는 쪽을 먼저 검토합니다. 모든 쿼리에 따옴표를 붙여 사는 것도 방법이지만, 손으로 쓰는 쿼리, 마이그레이션, 대시보드, 스크립트까지 전부 규칙을 지켜야 합니다. 한 군데라도 빠지면 같은 에러가 다시 납니다.
ALTER TABLE "Users" RENAME TO users;
ALTER TABLE users RENAME COLUMN "userName" TO user_name;여기서도 규칙은 같습니다. 바꿀 이름을 따옴표 없이 쓰면 소문자로 접혀 저장됩니다. 재현에서 ALTER TABLE "Users" RENAME TO users_old를 실행하자 저장된 이름이 users_old가 됐습니다. 물론 운영 중인 테이블 이름을 바꾸는 건 배포와 함께 계획해야 합니다. 뷰나 함수가 옛 이름을 참조하고 있을 수 있으니 바꾸기 전에 의존 객체를 확인합니다.
search_path에 기대지 말고 명시합니다. 가장 안전한 건 쿼리에 스키마를 붙이는 겁니다. 매번 붙이기 어렵다면 역할에 고정해 두면 접속할 때마다 적용됩니다.
ALTER ROLE appuser SET search_path TO shop, public;세션에서 SET search_path로 바꾼 값은 그 연결에서만 유효합니다. 커넥션 풀을 쓰는 애플리케이션이라면 어떤 연결을 잡느냐에 따라 결과가 달라질 수 있으니, 연결마다 설정하는 코드보다 역할이나 접속 문자열에 못 박아 두는 쪽이 낫습니다. 마이그레이션 스크립트도 첫 줄에 경로를 정해 두면 실행 환경에 따라 다른 스키마를 건드리는 사고를 막습니다.
- 권한 에러는 권한으로 해결합니다.
permission denied for schema public이 났다면 PostgreSQL 15 이후의 기본값에 부딪힌 겁니다. 애플리케이션 전용 스키마를 만들어 그 소유자로 지정하는 쪽이 깔끔합니다.
CREATE SCHEMA app AUTHORIZATION appuser;
ALTER ROLE appuser SET search_path TO app;읽기 전용 계정이라면 테이블 권한을 따로 줘야 합니다. 앞으로 만들어질 테이블까지 포함하려면 기본 권한도 같이 설정합니다.
GRANT USAGE ON SCHEMA app TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO reader;마지막 줄이 없으면 다음 마이그레이션에서 추가된 테이블에서만 permission denied for table이 나는, 찾기 귀찮은 상태가 됩니다.
댓글0
아직 댓글이 없어요. 첫 댓글을 남겨 보세요.
댓글을 남기려면 로그인이 필요해요. 로그인