def create_user(self, user, table_privileges=['ALL PRIVILEGES'],
                    schema_privileges=['ALL PRIVILEGES'],
                    row_limit=50000):
        con = self.connection or self._connect()
        cur = con.cursor()

        cur.execute('CREATE SCHEMA {0};'.format(user))
        # self._initialize(schema=schema_name)
        password = pwgen(8)
        cur.execute(
            "CREATE USER {user} with PASSWORD '{password}';"
            .format(user=user, password=password))

        """ Grant SELECT on public schema """
        cur.execute('GRANT USAGE ON SCHEMA public TO {user};'
                    .format(user=user))
        cur.execute(
            'GRANT SELECT ON ALL TABLES IN SCHEMA public TO {user};'
            .format(user=user))
        cur.execute(
            'ALTER ROLE {user} SET search_path TO {user};'
            .format(user=user))

        self.stdout.write(
            'CREATED USER {user} WITH PASSWORD {password}\n'
            .format(user=user, password=password))

        """ initialize user-schema """
        old_schema = self.schema
        self.initialized = False
        self.schema = user
        self._initialize(con)

        """ Privileges on user-schema"""
        cur.execute(
            'GRANT {privileges} ON SCHEMA {user} TO {user};'
            .format(privileges=', '.join(schema_privileges), user=user))
        cur.execute(
            'GRANT {privileges} ON ALL TABLES IN SCHEMA {user} TO {user};'
            .format(privileges=', '.join(table_privileges), user=user))
        cur.execute(
            'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA {user} TO {user};'
            .format(user=user))
        con.commit()

        if row_limit:
            """ Limit number of rows"""
            for table in ['reaction', 'publication', 'systems',
                          'reaction_system', 'publication_system',
                          'information']:
                table_factor = 1
                if table in ['reaction_system', 'publication_system']:
                    table_factor = 15
                elif table == 'publication':
                    table_factor = 1 / 100
                elif table == 'information':
                    table_factor = 1 / 100

                trigger_function = """
                CREATE OR REPLACE FUNCTION
                check_number_of_rows_{user}_{table}()
                RETURNS TRIGGER AS
                $BODY$
                BEGIN
                    IF (SELECT count(*) FROM {user}.{table}) > {row_limit}
                    THEN
                        RAISE EXCEPTION
                            'INSERT statement exceeding maximum number of rows';
                    END IF;
                    RETURN NEW;
                END;
                $BODY$
                LANGUAGE plpgsql""".format(user=user, table=table,
                                           row_limit=row_limit * table_factor)
                cur.execute(trigger_function)

                trigger = """
                DROP TRIGGER IF EXISTS tr_check_number_of_rows_{user}_{table}
                    on {user}.{table};
                CREATE TRIGGER tr_check_number_of_rows_{user}_{table}
                BEFORE INSERT ON {user}.systems
                FOR EACH ROW EXECUTE PROCEDURE check_number_of_rows_{user}_{table}();
                """.format(user=user, table=table)
                cur.execute(trigger)

        self.schema = old_schema
        set_schema = 'ALTER ROLE {user} SET search_path TO {schema};'\
                     .format(user=self.user, schema=self.schema)
        cur.execute(set_schema)

        if self.connection is None:
            con.commit()
            con.close()

        return password
def create_user ( self , user , table_privileges = [ 'ALL PRIVILEGES' ] , schema_privileges = [ 'ALL PRIVILEGES' ] , row_limit = 50000 ) : con = self . connection or self . _connect ( ) cur = con . cursor ( ) cur . execute ( 'CREATE SCHEMA {0};' . format ( user ) ) password = pwgen ( 8 ) cur . execute ( "CREATE USER {user} with PASSWORD '{password}';" . format ( user = user , password = password ) ) """ Grant SELECT on public schema """ cur . execute ( 'GRANT USAGE ON SCHEMA public TO {user};' . format ( user = user ) ) cur . execute ( 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO {user};' . format ( user = user ) ) cur . execute ( 'ALTER ROLE {user} SET search_path TO {user};' . format ( user = user ) ) self . stdout . write ( 'CREATED USER {user} WITH PASSWORD {password}\n' . format ( user = user , password = password ) ) """ initialize user-schema """ old_schema = self . schema self . initialized = False self . schema = user self . _initialize ( con ) """ Privileges on user-schema""" cur . execute ( 'GRANT {privileges} ON SCHEMA {user} TO {user};' . format ( privileges = ', ' . join ( schema_privileges ) , user = user ) ) cur . execute ( 'GRANT {privileges} ON ALL TABLES IN SCHEMA {user} TO {user};' . format ( privileges = ', ' . join ( table_privileges ) , user = user ) ) cur . execute ( 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA {user} TO {user};' . format ( user = user ) ) con . commit ( ) if row_limit : for table in [ 'reaction' , 'publication' , 'systems' , 'reaction_system' , 'publication_system' , 'information' ] : table_factor = 1 if table in [ 'reaction_system' , 'publication_system' ] : table_factor = 15 elif table == 'publication' : table_factor = 1 / 100 elif table == 'information' : table_factor = 1 / 100 trigger_function = """
                CREATE OR REPLACE FUNCTION
                check_number_of_rows_{user}_{table}()
                RETURNS TRIGGER AS
                $BODY$
                BEGIN
                    IF (SELECT count(*) FROM {user}.{table}) > {row_limit}
                    THEN
                        RAISE EXCEPTION
                            'INSERT statement exceeding maximum number of rows';
                    END IF;
                    RETURN NEW;
                END;
                $BODY$
                LANGUAGE plpgsql""" . format ( user = user , table = table , row_limit = row_limit * table_factor ) cur . execute ( trigger_function ) trigger = """
                DROP TRIGGER IF EXISTS tr_check_number_of_rows_{user}_{table}
                    on {user}.{table};
                CREATE TRIGGER tr_check_number_of_rows_{user}_{table}
                BEFORE INSERT ON {user}.systems
                FOR EACH ROW EXECUTE PROCEDURE check_number_of_rows_{user}_{table}();
                """ . format ( user = user , table = table ) cur . execute ( trigger ) self . schema = old_schema set_schema = 'ALTER ROLE {user} SET search_path TO {schema};' . format ( user = self . user , schema = self . schema ) cur . execute ( set_schema ) if self . connection is None : con . commit ( ) con . close ( ) return password
Grant SELECT on public schema