Switch role after connecting to database

by 
--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 is now in the neil group, so webgui can call set role neil . However, webgui did not inherit neil‘s permissions.

Later, login as webgui:

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

set role neil;

webgui does not need superuser permission for this.

You want to set role at the beginning of a database session and reset it at the end of the session. In a web app, this corresponds to getting a connection from your database connection pool and releasing it, respectively. Here’s an example using Tomcat’s connection pool and 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);
    }
}

More Related Contents:

  1. Insert, on duplicate update in PostgreSQL?
  2. Run batch file with psql command without password
  3. invalid byte sequence for encoding “UTF8”
  4. Postgresql: Conditionally unique constraint
  5. How to make “case-insensitive” query in Postgresql?
  6. Rownum in postgresql
  7. Postgresql: Scripting psql execution with password
  8. dynamic sql query in postgres
  9. Cannot create a database table named ‘user’ in PostgreSQL
  10. Is there a timeout for idle PostgreSQL connections?
  11. How to make connection to Postgres via Node.js
  12. Postgresql Truncation speed
  13. Buffers (Circle) in PostGIS
  14. SELECT raises exception in PL/pgSQL function
  15. Heroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”
  16. Changing primary key int type to serial
  17. Return SETOF rows from PostgreSQL function
  18. PSQLException: current transaction is aborted, commands ignored until end of transaction block
  19. SQL Sub queries in check constraint
  20. Postgresql enforce unique two-way combination of columns
  21. When do Postgres column or table names need quotes and when don’t they?
  22. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  23. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  24. how to store PostgreSQL jsonb using SpringBoot + JPA?
  25. PostgreSQL how to concat interval value ‘2 days’
  26. Displaying image in Ireports using PostgreSql
  27. Heroku database restore issue
  28. user postgres launches process that takes all CPUs 100% usage
  29. Postgres table column name restrictions?
  30. How do I get min, median and max from my query in postgresql?

Leave a Comment