Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

2.0 KB
Raw Blame
package pg

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/db/util"
	pgparser "github.com/bytebase/bytebase/backend/plugin/parser/pg"
)

func (d *Driver) getInstanceRoles(ctx context.Context) ([]*storepb.InstanceRole, error) {
	query := `
		SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolreplication, r.rolvaliduntil, r.rolbypassrls
		FROM pg_catalog.pg_roles r
		WHERE r.rolname !~ '^pg_';
	`
	var instanceRoles []*storepb.InstanceRole
	rows, err := d.db.QueryContext(ctx, query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()
	for rows.Next() {
		var role string
		var super, inherit, createRole, createDB, canLogin, replication, bypassRLS bool
		var rolValidUntil sql.NullString
		if err := rows.Scan(
			&role,
			&super,
			&inherit,
			&createRole,
			&createDB,
			&canLogin,
			&replication,
			&rolValidUntil,
			&bypassRLS,
		); err != nil {
			return nil, err
		}
		if pgparser.IsSystemUser(role) {
			continue
		}

		var attributes []string
		if super {
			attributes = append(attributes, "Superuser")
		}
		if !inherit {
			attributes = append(attributes, "No inheritance")
		}
		if createRole {
			attributes = append(attributes, "Create role")
		}
		if createDB {
			attributes = append(attributes, "Create DB")
		}
		if !canLogin {
			attributes = append(attributes, "Cannot login")
		}
		if replication {
			attributes = append(attributes, "Replication")
		}
		if rolValidUntil.Valid {
			attributes = append(attributes, fmt.Sprintf("Password valid until %s", rolValidUntil.String))
		}
		if bypassRLS {
			attributes = append(attributes, "Bypass RLS+")
		}
		attribute := strings.Join(attributes, " ")
		instanceRoles = append(instanceRoles, &storepb.InstanceRole{
			Name:      role,
			Attribute: &attribute,
		})
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return instanceRoles, nil
}