284 lines
7.5 KiB
Go
284 lines
7.5 KiB
Go
package db
|
|
|
|
import "database/sql"
|
|
import "errors"
|
|
import "strings"
|
|
import "time"
|
|
|
|
import "codit/internal/models"
|
|
|
|
func (s *Store) ListSubjectPermissions(permission string, subjectType string, subjectID string) ([]models.SubjectPermission, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var items []models.SubjectPermission
|
|
var item models.SubjectPermission
|
|
permission = strings.TrimSpace(permission)
|
|
subjectType = strings.ToLower(strings.TrimSpace(subjectType))
|
|
subjectID = strings.TrimSpace(subjectID)
|
|
rows, err = s.Query(`SELECT
|
|
sp.permission,
|
|
sp.subject_type,
|
|
sp.subject_public_id,
|
|
CASE
|
|
WHEN sp.subject_type = 'user' THEN
|
|
CASE
|
|
WHEN u.public_id IS NULL THEN sp.subject_public_id
|
|
WHEN u.display_name <> '' THEN u.display_name || ' (' || u.username || ')'
|
|
ELSE u.username
|
|
END
|
|
WHEN sp.subject_type = 'group' THEN COALESCE(g.name, sp.subject_public_id)
|
|
WHEN sp.subject_type = 'principal' THEN COALESCE(p.name, sp.subject_public_id)
|
|
ELSE sp.subject_public_id
|
|
END AS subject_name,
|
|
CASE
|
|
WHEN sp.subject_type = 'user' THEN CASE WHEN u.public_id IS NOT NULL AND u.disabled = 0 THEN 1 ELSE 0 END
|
|
WHEN sp.subject_type = 'group' THEN CASE WHEN g.public_id IS NOT NULL AND g.disabled = 0 THEN 1 ELSE 0 END
|
|
WHEN sp.subject_type = 'principal' THEN CASE WHEN p.public_id IS NOT NULL AND p.disabled = 0 THEN 1 ELSE 0 END
|
|
ELSE 0
|
|
END AS subject_active,
|
|
sp.created_at,
|
|
sp.updated_at
|
|
FROM subject_permissions sp
|
|
LEFT JOIN users u
|
|
ON sp.subject_type = 'user'
|
|
AND u.public_id = sp.subject_public_id
|
|
LEFT JOIN user_groups g
|
|
ON sp.subject_type = 'group'
|
|
AND g.public_id = sp.subject_public_id
|
|
LEFT JOIN service_principals p
|
|
ON sp.subject_type = 'principal'
|
|
AND p.public_id = sp.subject_public_id
|
|
WHERE (? = '' OR sp.permission = ?)
|
|
AND (? = '' OR sp.subject_type = ?)
|
|
AND (? = '' OR sp.subject_public_id = ?)
|
|
ORDER BY sp.permission, sp.subject_type, subject_name, sp.subject_public_id`,
|
|
permission,
|
|
permission,
|
|
subjectType,
|
|
subjectType,
|
|
subjectID,
|
|
subjectID,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(
|
|
&item.Permission,
|
|
&item.SubjectType,
|
|
&item.SubjectID,
|
|
&item.SubjectName,
|
|
&item.SubjectActive,
|
|
&item.CreatedAt,
|
|
&item.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, item)
|
|
}
|
|
err = rows.Err()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
func (s *Store) GetSubjectPermission(permission string, subjectType string, subjectID string) (models.SubjectPermission, error) {
|
|
var items []models.SubjectPermission
|
|
var item models.SubjectPermission
|
|
var err error
|
|
items, err = s.ListSubjectPermissions(permission, subjectType, subjectID)
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
if len(items) == 0 {
|
|
return item, sql.ErrNoRows
|
|
}
|
|
return items[0], nil
|
|
}
|
|
|
|
func (s *Store) UpsertSubjectPermission(item models.SubjectPermission) (models.SubjectPermission, error) {
|
|
var now int64
|
|
var err error
|
|
var result sql.Result
|
|
var rowsAffected int64
|
|
now = time.Now().UTC().Unix()
|
|
item.Permission = strings.TrimSpace(item.Permission)
|
|
item.SubjectType = strings.ToLower(strings.TrimSpace(item.SubjectType))
|
|
item.SubjectID = strings.TrimSpace(item.SubjectID)
|
|
item.CreatedAt = now
|
|
item.UpdatedAt = now
|
|
switch item.SubjectType {
|
|
case "user":
|
|
result, err = s.Exec(`INSERT INTO subject_permissions (permission, subject_type, subject_public_id, created_at, updated_at)
|
|
SELECT ?, 'user', u.public_id, ?, ?
|
|
FROM users u
|
|
WHERE u.public_id = ?
|
|
ON CONFLICT(permission, subject_type, subject_public_id)
|
|
DO UPDATE SET updated_at = excluded.updated_at`,
|
|
item.Permission,
|
|
item.CreatedAt,
|
|
item.UpdatedAt,
|
|
item.SubjectID,
|
|
)
|
|
case "group":
|
|
result, err = s.Exec(`INSERT INTO subject_permissions (permission, subject_type, subject_public_id, created_at, updated_at)
|
|
SELECT ?, 'group', g.public_id, ?, ?
|
|
FROM user_groups g
|
|
WHERE g.public_id = ?
|
|
ON CONFLICT(permission, subject_type, subject_public_id)
|
|
DO UPDATE SET updated_at = excluded.updated_at`,
|
|
item.Permission,
|
|
item.CreatedAt,
|
|
item.UpdatedAt,
|
|
item.SubjectID,
|
|
)
|
|
case "principal":
|
|
result, err = s.Exec(`INSERT INTO subject_permissions (permission, subject_type, subject_public_id, created_at, updated_at)
|
|
SELECT ?, 'principal', p.public_id, ?, ?
|
|
FROM service_principals p
|
|
WHERE p.public_id = ?
|
|
ON CONFLICT(permission, subject_type, subject_public_id)
|
|
DO UPDATE SET updated_at = excluded.updated_at`,
|
|
item.Permission,
|
|
item.CreatedAt,
|
|
item.UpdatedAt,
|
|
item.SubjectID,
|
|
)
|
|
default:
|
|
return item, errors.New("invalid subject_type")
|
|
}
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
rowsAffected, err = result.RowsAffected()
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
if rowsAffected <= 0 {
|
|
return item, errors.New("subject not found")
|
|
}
|
|
return s.GetSubjectPermission(item.Permission, item.SubjectType, item.SubjectID)
|
|
}
|
|
|
|
func (s *Store) DeleteSubjectPermission(permission string, subjectType string, subjectID string) error {
|
|
var err error
|
|
_, err = s.Exec(`DELETE FROM subject_permissions
|
|
WHERE permission = ?
|
|
AND subject_type = ?
|
|
AND subject_public_id = ?`,
|
|
strings.TrimSpace(permission),
|
|
strings.ToLower(strings.TrimSpace(subjectType)),
|
|
strings.TrimSpace(subjectID),
|
|
)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) ListUserPermissions(userID string) ([]string, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var items []string
|
|
var item string
|
|
userID = strings.TrimSpace(userID)
|
|
rows, err = s.Query(`SELECT DISTINCT sp.permission
|
|
FROM subject_permissions sp
|
|
WHERE (sp.subject_type = 'user' AND sp.subject_public_id = ?)
|
|
OR (
|
|
sp.subject_type = 'group'
|
|
AND sp.subject_public_id IN (
|
|
SELECT g.public_id
|
|
FROM user_group_members gm
|
|
JOIN users u ON u.id = gm.user_id
|
|
JOIN user_groups g ON g.id = gm.group_id
|
|
WHERE u.public_id = ?
|
|
AND g.disabled = 0
|
|
)
|
|
)
|
|
ORDER BY sp.permission`,
|
|
userID,
|
|
userID,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&item)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, item)
|
|
}
|
|
err = rows.Err()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
func (s *Store) UserHasPermission(userID string, permission string) (bool, error) {
|
|
var row *sql.Row
|
|
var value int
|
|
var err error
|
|
userID = strings.TrimSpace(userID)
|
|
permission = strings.TrimSpace(permission)
|
|
row = s.QueryRow(`SELECT 1
|
|
FROM subject_permissions sp
|
|
WHERE sp.permission = ?
|
|
AND (
|
|
(sp.subject_type = 'user' AND sp.subject_public_id = ?)
|
|
OR
|
|
(
|
|
sp.subject_type = 'group'
|
|
AND sp.subject_public_id IN (
|
|
SELECT g.public_id
|
|
FROM user_group_members gm
|
|
JOIN users u ON u.id = gm.user_id
|
|
JOIN user_groups g ON g.id = gm.group_id
|
|
WHERE u.public_id = ?
|
|
AND g.disabled = 0
|
|
)
|
|
)
|
|
)
|
|
LIMIT 1`,
|
|
permission,
|
|
userID,
|
|
userID,
|
|
)
|
|
err = row.Scan(&value)
|
|
if err == sql.ErrNoRows {
|
|
return false, nil
|
|
}
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return true, nil
|
|
}
|
|
|
|
func (s *Store) PrincipalHasPermission(principalID string, permission string) (bool, error) {
|
|
var row *sql.Row
|
|
var value int
|
|
var err error
|
|
principalID = strings.TrimSpace(principalID)
|
|
permission = strings.TrimSpace(permission)
|
|
row = s.QueryRow(`SELECT 1
|
|
FROM subject_permissions
|
|
WHERE permission = ?
|
|
AND subject_type = 'principal'
|
|
AND subject_public_id = ?
|
|
LIMIT 1`,
|
|
permission,
|
|
principalID,
|
|
)
|
|
err = row.Scan(&value)
|
|
if err == sql.ErrNoRows {
|
|
return false, nil
|
|
}
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return true, nil
|
|
}
|