PostgreSQL 데이터베이스 단위 유저 추가

PostgreSQL (14.3)에 새로운 Database를 만들고, Schema 도 추가했다. 

MySQL을 생각하고 상위 개체인 Database에 All 권한을 주었는데, 

GRANT ALL PRIVILEGES ON DATABASE {database_name} to {user_name};

테이블 조회는 되지만 데이터는 권한이 없어 조회가 되지 않았다. 맨 처음엔 MySQL 처럼 Flush 를 해주어야 하는가 싶어 찾아봤지만 없었다. 

Schema 도 별도로 줘야 하는 것 같아서 해당 권한을 설정해주었다. 

Table 및 Sequence 까지도 설정을 해주어야 함을 잊지 않아야 한다. 

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA {schema_name} to {user_name};
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA {schema_name} to {user_name};

이렇게 하면 정상적으로 동작한다. 

테이블 단위의 설정이 아니라서, 과거 default privileges( 새롭게 생성한 테이블에도 권한을 동일하게 받기 위한 )를 설정하는 것은 불필요하다. 

하지만 위의 구성은 새로운 스키마를 설정할때마다 새로운 권한을 매번 부여해주어야 한다. 

좀 더 심플하기를 원해 찾아보았더니, PostgreSQL 14버전 부터는 Predefined Roles 이 있어 좀 더 손쉽게 사용이 가능하다. 

다음과 같이 설정하면 스키마 생성시의 매번 권할 설정은 불필요하다. 

GRANT pg_write_all_data TO {user_name};

이렇게 설정하면 모두 해결될 것이라 생각했으나, pg_write_all_data 라는 이름에서 예상할 수 있듯 write 권한만 설정된다. 

pg_read_all_data 권한까지 주어야 예상했던대로 동작한다. 

GRANT pg_read_all_data TO {user_name};

---

결론

• MySQL 처럼 Flush Privileges 는 불필요하다. 
• Database 및 Schema ( MySQL의 Database 와 같은 레벨 ) 까지 권할 설정을 해주어야 한다. 
• PostgreSQL은 조금 더 편한 Predefined Roles 가 있다. 
• Write 권한 뿐만 아니라 Read 권한도 명시해주어야 한다. 
• Database의 Schema 생성권한은 Database 권한을 설정해야 가능하다. 

 

데이터 베이스의 모든 권한을 부여하려면 다음과 같이 한다. 

GRANT ALL PRIVILEGES ON DATABASE {database_name} to {user_name};
// 모든 스키마를 설정해주어야 한다. 
// 이 권한이 부여되지 않으면 테이블을 생성할 수 없다. 
GRANT ALL PRIVILEGES ON SCHEMA {schema_name} to {user_name};
GRANT pg_write_all_data TO {user_name};
GRANT pg_read_all_data TO {user_name};