Agnostic Identity and Access Management (IAM) library for PostgreSQL (PL/pgSQL)

IAM lets you grant granular access to specific resources and helps prevent access to other resources. IAM lets you adopt the security principle of least privilege, which states that nobody should have more permissions than they actually need.

Table of Contents

• Features
• Why put authorizations at the database level?
• How IAM works
• Concepts related to identity
  • User Account
  • Service account
  • All authenticated users
  • All users
• Concepts related to access management
  • Resource
  • Permissions
  • Roles
  • Policy
• Resource hierarchy
• Tests
• SQL Conventions
• Alternatives

Features

Implemented:

• Role Based Access Control (RBAC)

• Permissive resource level Access Control Lists (ACL)

Not implemented "would be nice to have":

• Role hierarchy. Roles are flat

• Folder/Organization level ACLs

• Restrictive ACLs

Why put authorizations at the database level?

• No need to fully open the database to the API service, structurally higher security approach

• Unlike the API service framework approach "allow-all (full database access from the API service) then disallow through rules at the service level". The database level authorizations has a firewall approach (the best known approach to authz), where everything is locked by the database unless rules (row level security) are defined to open read/write access.

• Even if the higher layers are compromised (e.g. at the api service level), the data consistency and isolation stay safe as long as there are no security flaw at the database level.

How IAM works

Figure 1. Entities inside an Identity and Access Management

With IAM, you manage access control by defining who (identity) has what access (role) for which resource. For example, if your project is a chat application, the IAM will manage access to channels, messages, comments, files, and your application resources. The organizations, folders that you use to organize your resource are also resources.

In IAM, permission to access a resource isn’t granted directly to the end user. Instead, permissions are grouped into roles, and roles are granted to authenticated members. An IAM policy defines and enforces what roles are granted to which members, and this policy is attached to a resource. When an authenticated member attempts to access a resource, IAM checks the resource’s policy to determine whether the action is permitted.

This model for access management has three main parts:

• Member. A member can be an Account (for end users), a service account (for apps and virtual machines) that can access a resource. The identity of a member is an email address associated with a user, service account.

• Role. A role is a collection of permissions. Permissions determine what operations are allowed on a resource. When you grant a role to a member, you grant all the permissions that the role contains.

• Policy. The IAM policy binds one or more members to a role. When you want to define who (member) has what type of access (role) on a resource, you create a policy and attach it to the resource.

The IAM policy binds members, such as userid@gmail.com, to roles, such as the Ticket Manager Editor role (ticket_manager.editor). If the policy is attached to a folder, the members gain the specified roles within the folder.

The rest of this page describes these concepts in greater detail.

Concepts related to identity

In IAM, you grant access to members. Members can be of the following types:

• User Account

• Service account

• All authenticated users

• All users

User Account

A User Account represents a user, developer, an administrator, or any other person who interacts with your application. Any email address that’s associated with a user account can be an identity. New users can sign up for a User Account by going to your application signup page that will call lib_iam.user_create() function.

User accounts are stored in lib_iam.user table.

Service account

A service account is an account for an application instead of an individual end user. When you run code, the code runs as the account you specify. You can create as many service accounts as needed (lib_iam.service_account_create() function) to represent the different logical components of your application.

Service accounts are stored in lib_iam.service_account table.

All authenticated users

The value allAuthenticatedUsers is a special identifier that represents all service accounts and all users on the internet who have authenticated with a User Account. Users who aren’t authenticated, such as anonymous visitors, aren’t included.

All users

The value allUsers is a special identifier that represents anyone who is on the internet, including authenticated and unauthenticated users.

Concepts related to access management

When an authenticated member attempts to access a resource, IAM checks the resource’s IAM policy to determine whether the action is allowed.

This section describes the entities and concepts involved in the authorization process.

Resource

If a user needs access to a specific resource, you can grant the user a role for that resource. In the case of a ticket management application, some examples of resources are comments, files, and boards.

You can grant IAM permissions at the folder level. The permissions are then inherited by all resources within that folder.

For information on what roles can be granted on which resources, list every available roles with lib_iam.roles view.

Permissions

Permissions determine what operations are allowed on a resource. In the IAM world, permissions are represented in the form of service.resource_type.verb, for example, ticket_manager.comment.create.

Permissions often correspond one-to-one with REST API methods. That is, each of your application service has an associated set of permissions for each REST API method that it exposes. The caller of that method needs those permissions to call that method. For example, if you use the ticket manager application, and you need to call the comment.create() method, you must have the ticket_manager.comment.create permission for that application.

You don’t grant permissions to users directly. Instead, you identify role that contain the appropriate permissions, and then grant those roles to the user. For a list of all available permissions query the lib_iam.permissions view. For a list of all roles and their corresponding permissions query the lib_iam.roles view.

Conventions

A permission name is often a tuple of {service}.{resource_type}.{verb}. Some conventions do not include the {service} part but we think it’s better to namespace your resource_type by services instead of prefixing resource_type directly.

Examples of {service} names

• billing

• chat

• file

• iam

• logging

• monitoring

Examples of {resource_type} names

Note that resource_type is very often plural and camelCase:

• applications

• accounts

• agents

• apis

• assets

• attachments

• backups

• billingPlans

• brands

• buckets

• conversations

• credits

• databases

• devices

• documents

• events

• folders

• images

• licenses

• participants

• queues

• quotas

• settings

• sites

• users

Examples of {verb} names

• * (is special verb, even in lib_iam, that let specify wildcard permission in the form of service.resource_type.*)

• abort

• access

• actAs

• add

• attach

• bind

• calculate

• call

• cancel

• check

• cloneRules

• close

• connect

• consume

• create

• delete

• deploy

• destroy

• detachSubscription

• disable

• download

• drop

• enable

• escalate

• execute

• explain

• export

• failover

• get

• group

• import

• ingest

• install

• instantiate

• instantiateInline

• invoke

• list

• listActive

• listAll

• login

• lookup

• manage

• mirror

• move

• patch

• pause

• publish

• purge

• quota

• read

• reopen

• report

• reportStatus

• reset

• resetpassword

• resize

• resolve

• restart

• restore

• resume

• review

• run

• runDiscovery

• runtime

• sampleRowKeys

• search

• seek

• select

• sendCommand

• sendVerificationCode

• set

• setMetadata

• setState

• setTags

• start

• stop

• subscribe

• truncateLog

• undelete

• undeploy

• uninstall

• update

• use

• validate

• validateTrust

• verify

• view

• wait

• watch

• write

Roles

A role is a collection of permissions. You cannot grant a permission to the user directly. Instead, you grant them a role. When you grant a role to a user, you grant them all the permissions that the role contains.

lib_iam once executed in your database does not include default roles nor permissions. It’s your app, your specific problem domain. You will have to define them. However you might want to define:

• Basic roles: Roles that you will make available to your users. For instance Owner, Editor, and Viewer.

And then let your own application users define:

• Custom roles: Roles that your own user can create to tailor permissions to the needs of their organization when predefined roles don’t meet their needs.

Conventions

A role name is often a tuple of {service}.{role}. Some conventions do not include the {service} part but we think it’s better to namespace your role by services instead of prefixing role directly. Following this convention, Basic Roles are namespaced by {service}.

Examples of {role} full names ({service}.{role} format)

• accessapproval.approver

• accessapproval.viewer

• actions.Admin

• actions.Viewer

• apigateway.admin

• apigateway.viewer

• chat.owner

• chat.reader

• file.editor

• file.serviceAgent

• file.viewer

• iam.organizationRoleAdmin

• iam.organizationRoleViewer

• iam.roleAdmin

• iam.roleViewer

• iam.securityAdmin

• iam.securityReviewer

• iam.serviceAccountAdmin

• iam.serviceAccountCreator

• iam.serviceAccountDeleter

• iam.serviceAccountKeyAdmin

• iam.serviceAccountTokenCreator

• iam.serviceAccountUser

• logging.admin

• logging.bucketWriter

• logging.configWriter

• logging.logWriter

• logging.serviceAgent

• logging.viewAccessor

• logging.viewer

• monitoring.admin

• monitoring.alertPolicyEditor

• monitoring.alertPolicyViewer

• monitoring.dashboardEditor

• monitoring.dashboardViewer

• monitoring.editor

• monitoring.metricWriter

• monitoring.notificationChannelEditor

• monitoring.notificationChannelViewer

• monitoring.notificationServiceAgent

• monitoring.servicesEditor

• monitoring.servicesViewer

• monitoring.uptimeCheckConfigEditor

• monitoring.uptimeCheckConfigViewer

• monitoring.viewer

Examples of {role} names ({role} format without {service}. prefix)

• approver

• auditor

• builder

• connector

• consumer

• creator

• deployer

• developer

• editor

• enqueuer

• importer

• inframanager

• invoker

• manager

• migrator

• operator

• owner

• peer

• predictor

• provisioner

• publisher

• reader

• reporter

• runner

• signer

• subscriber

• tester

• user

• viewer

• worker

• writer

Policy

You can grant roles to users by creating an IAM policy, which is a collection of statements that define who has what type of access. A policy is attached to a resource and is used to enforce access control whenever that resource is accessed.

An IAM policy is represented by the IAM Policy object. An IAM Policy object consists of a list of bindings (query lib_iam.bindings view). A Binding binds a list of members to a role.

• role: The role you want to grant to the member. role is specified in the form of service.roleName. For example, our ticket application provides the roles ticket_manager.commenter, ticket_manager.editor, and ticket_manager.lead_dev, among others.

• members: A list of one or more identities as described in the Concepts related to identity section in this document. Each member type is identified with a prefix, such as:

• a User Account (user:)

• a Service account (serviceAccount:)

• All authenticated users (allAuthenticatedUsers)

• All users (allUsers)

In the following example code snippet, the storage.objectAdmin role is granted to the following members by using the appropriate prefix: user:ali@example.com, serviceAccount:my-other-app@appspot.gserviceaccount.com, group:admins@example.com, and domain:google.com. The objectViewer role is granted to user:maria@example.com.

List all defined IAM policies with lib_iam.policies view.

Resource hierarchy

lib_iam resources are organized hierarchically:

• The organization is the root node in the hierarchy. List all organizations with lib_iam.organizations view.

• Folders are children of the organization. List all organizations with lib_iam.folders view.

• Resources for each service are descendants of projects. Each resource has exactly one parent. List all organizations with lib_iam.resources view.

The following diagram is an example of a lib_iam resource hierarchy. Note that projects abstraction are not supported in lib_iam and can be emulated with folders instead.

You can set an IAM policy at any level in the resource hierarchy: the organizations level, the folders level, or the resource level. Resources inherit the policies of all of their parent resources. The effective policy for a resource is the union of the policy set on that resource and the policies inherited from higher up in the hierarchy.

This policy inheritance is transitive; in other words, resources inherit policies from folders, which inherit policies from the organization. Therefore, the organization-level policies also apply at the resource level.

For example: In the preceding diagram, topic_a is a Pub/Sub resource that lives under the project example-prod. If you grant the Editor role to micah@example.com for example-prod, and grant the Publisher role to song@example.com for topic_a, you effectively grant the Editor role for topic_a to micah@example.com and the Publisher role to song@example.com.

The policies for child resources inherit from the policies for their parent resources. For example, if you grant the Editor role to a user for a project, and grant the Viewer role to the same user for a child resource, then the user still has the Editor role grant for the child resource. If you change the resource hierarchy, the policy inheritance changes as well. For example, moving a project into an organization causes the project to inherit from the organization’s IAM policy.

Tests

See unit tests in *.test.sql files. They provide clear example of API usages.

SQL Conventions

_~~~~~

Code is written following standard SQL-convention.

Alternatives

• Cloud-IAM - Managed Keycloak clusters