Подтвердить что ты не робот

Переключить роль после подключения к базе данных

Можно ли изменить роль postgresql, которую пользователь использует при взаимодействии с postgres после первоначального соединения?

База данных (ов) будет использоваться в веб-приложении, и я бы хотел использовать правила уровня базы данных для таблиц и схем с пулом соединений. Из чтения документации 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
4b9b3361

ОТВЕТЫ

Ответ 1

--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 в начале сеанса базы данных и reset в конце сеанса. В веб-приложении это соответствует подключению вашего пула подключения к базе данных и его освобождению соответственно. Вот пример использования пула соединений Tomcat и Spring Безопасность:

public class SetRoleJdbcInterceptor extends JdbcInterceptor {

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

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

        if(authentication != null) {
            try {

                /* 
                  use OWASP 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);
    }
}

Ответ 3

Если кому-то это все еще нужно (как и мне).

Указанное имя_роли должно быть ролью, членом которой является текущий пользователь сеанса. https://www.postgresql.org/docs/10/sql-set-role.html

Нам нужно сделать текущего пользователя сессии членом роли:

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

производит:

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.