데이터베이스에 연결 한 후 역할 전환

데이터베이스에 연결 한 후 역할 전환

---

초기 연결 후 postgres와 상호 작용할 때 사용자가 사용하는 postgresql 역할을 변경할 수 있습니까?

데이터베이스는 웹 애플리케이션에서 사용되며 연결 풀링을 사용하여 테이블과 스키마에 데이터베이스 수준 규칙을 적용하고 싶습니다. postgresql 문서를 읽으면 원래 수퍼 유저 역할을 가진 사용자로 연결하면 역할을 전환 할 수있는 것처럼 보이지만 처음에는 최소한의 권한을 가진 사용자로 연결하고 필요에 따라 전환하는 것을 선호합니다. 전환 할 때 사용자의 암호를 지정해야하는 것은 괜찮습니다 (사실 나는 그것을 선호합니다).

내가 무엇을 놓치고 있습니까?

업데이트 : 난 둘 다 해봤 SET ROLE및 SET SESSION AUTHORIZATION@Milen에 의해 제안 사용자가 수퍼 유저가 아닌 경우 그러나 어느 명령이 작동하는 것 같다 :

$ psql -U test
psql (8.4.4)
Type "help" for help.

test=> \du test
          List of roles
 Role name | Attributes |   Member of    
-----------+------------+----------------
 test      |            | {connect_only}

test=> \du test2
          List of roles
 Role name | Attributes |   Member of    
-----------+------------+----------------
 test2     |            | {connect_only}

test=> set role test2;
ERROR:  permission denied to set role "test2"
test=> \q

---

--create a user that you want to use the database as:

create role neil;

--create the user for the web server to connect as:

create role webgui noinherit login password 's3cr3t';

--let webgui set role to neil:

grant neil to webgui; --this looks backwards but is correct.

webgui지금 neil그룹에 있으므로을 ( webgui를) 호출 할 수 있습니다 set role neil. 그러나 의 권한을 webgui상속하지 않았습니다 neil.

나중에 webgui로 로그인하십시오.

psql -d some_database -U webgui
(enter s3cr3t as password)

set role neil;

webgui이에 대한 superuser권한이 필요하지 않습니다 .

당신은 원하는 set role데이터베이스 세션의 시작 부분에서 세션의 마지막 재설정. 웹 앱에서 이는 각각 데이터베이스 연결 풀에서 연결을 가져 와서 해제하는 것에 해당합니다. 다음은 Tomcat의 연결 풀과 Spring Security를 ​​사용하는 예입니다.

public class SetRoleJdbcInterceptor extends JdbcInterceptor {

    @Override
    public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) {

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();

        if(authentication != null) {
            try {

                /* 
                  use OWASP's ESAPI to encode the username to avoid SQL Injection. Can't use parameters with SET ROLE. Need to write PG codec.

                  Or use a whitelist-map approach
                */
                String username = ESAPI.encoder().encodeForSQL(MY_CODEC, authentication.getName());

                Statement statement = pooledConnection.getConnection().createStatement();
                statement.execute("set role \"" + username + "\"");
                statement.close();
            } catch(SQLException exp){
                throw new RuntimeException(exp);
            }
        }
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

        if("close".equals(method.getName())){
            Statement statement = ((Connection)proxy).createStatement();
            statement.execute("reset role");
            statement.close();
        }

        return super.invoke(proxy, method, args);
    }
}

---

"SET ROLE" 및 "SET SESSION AUTHORIZATION"을 살펴보십시오 .

---

누군가가 여전히 그것을 필요로한다면 (나처럼).

The specified role_name must be a role that the current session user is a member of. https://www.postgresql.org/docs/10/sql-set-role.html

We need to make the current session user a member of the role:

create role myrole;
set role myrole;
grant myrole to myuser;
set role myrole;

produces:

Role ROLE created.


Error starting at line : 4 in command -
set role myrole
Error report -
ERROR: permission denied to set role "myrole"

Grant succeeded.


Role SET succeeded.

ReferenceURL : https://stackoverflow.com/questions/2998597/switch-role-after-connecting-to-database