{"action":"created","comment":{"url":"https://api.github.com/repos/begriffs/postgrest/pulls/comments/44495581","id":44495581,"diff_hunk":"@@ -0,0 +1,389 @@\n+-------------------------------------------------------------------------------\n+-- Adapted from https://github.com/robconery/pg-auth\n+\n+begin;\n+\n+create role anon noinherit;\n+create role author;\n+\n+create extension if not exists pgcrypto;\n+create extension if not exists \"uuid-ossp\";\n+\n+-- We put things inside the basic_auth schema to hide\n+-- them from public view. Certain public procs/views will\n+-- refer to helpers and tables inside.\n+create schema if not exists basic_auth;\n+\n+-------------------------------------------------------------------------------\n+-- Utility functions\n+\n+create or replace function\n+basic_auth.clearance_for_role(u name) returns void as\n+$$\n+declare\n+  ok boolean;\n+begin\n+  select exists (\n+    select rolname\n+      from pg_authid\n+     where pg_has_role(current_user, oid, 'member')\n+       and rolname = u\n+  ) into ok;\n+  if not ok then\n+    raise invalid_password using message =\n+      'current user not member of role ' || u;\n+  end if;\n+end\n+$$ LANGUAGE plpgsql;\n+\n+-------------------------------------------------------------------------------\n+-- Users storage and constraints\n+\n+create table if not exists\n+basic_auth.users (\n+  email  text not null,\n+  pass   text not null,\n+  role   name not null,\n+  active boolean not null default false,\n+  -- If you like add more columns, or a json column\n+  constraint user_pkey primary key (email),\n+  constraint user_field_length_limits check (\n+    length(pass) < 512\n+    AND length(email::text) < 512)\n+);\n+\n+create or replace function\n+basic_auth.check_role_exists() returns trigger\n+  language plpgsql\n+  as $$\n+begin\n+  if not exists (select 1 from pg_roles as r where r.rolname = new.role) then\n+    raise foreign_key_violation using message =\n+      'unknown database role: ' || new.role;\n+    return null;\n+  end if;\n+  return new;\n+end\n+$$;\n+\n+drop trigger if exists ensure_user_role_exists on basic_auth.users;\n+create constraint trigger ensure_user_role_exists\n+  after insert or update on basic_auth.users\n+  for each row\n+  execute procedure basic_auth.check_role_exists();\n+\n+create or replace function\n+basic_auth.encrypt_pass() returns trigger\n+  language plpgsql\n+  as $$\n+begin\n+  if tg_op = 'INSERT' or new.pass <> old.pass then\n+    new.pass = crypt(new.pass, gen_salt('bf'));\n+  end if;\n+  return new;\n+end\n+$$;\n+\n+drop trigger if exists protect_passwords on basic_auth.users;\n+create trigger protect_passwords","path":"schema-templates/blog.sql","position":88,"original_position":88,"commit_id":"935d7bb092f3e0b8259ff5ce791cfaf80ff192c8","original_commit_id":"935d7bb092f3e0b8259ff5ce791cfaf80ff192c8","user":{"login":"diogob","id":20662,"avatar_url":"https://avatars.githubusercontent.com/u/20662?v=3","gravatar_id":"","url":"https://api.github.com/users/diogob","html_url":"https://github.com/diogob","followers_url":"https://api.github.com/users/diogob/followers","following_url":"https://api.github.com/users/diogob/following{/other_user}","gists_url":"https://api.github.com/users/diogob/gists{/gist_id}","starred_url":"https://api.github.com/users/diogob/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/diogob/subscriptions","organizations_url":"https://api.github.com/users/diogob/orgs","repos_url":"https://api.github.com/users/diogob/repos","events_url":"https://api.github.com/users/diogob/events{/privacy}","received_events_url":"https://api.github.com/users/diogob/received_events","type":"User","site_admin":false},"body":"Just style nitpicking, but I like to always use triggers with the same name as the procedures they execute.","created_at":"2015-11-11T02:27:10Z","updated_at":"2015-11-11T02:27:10Z","html_url":"https://github.com/begriffs/postgrest/pull/347#discussion_r44495581","pull_request_url":"https://api.github.com/repos/begriffs/postgrest/pulls/347","_links":{"self":{"href":"https://api.github.com/repos/begriffs/postgrest/pulls/comments/44495581"},"html":{"href":"https://github.com/begriffs/postgrest/pull/347#discussion_r44495581"},"pull_request":{"href":"https://api.github.com/repos/begriffs/postgrest/pulls/347"}}},"pull_request":{"url":"https://api.github.com/repos/begriffs/postgrest/pulls/347","id":49911403,"html_url":"https://github.com/begriffs/postgrest/pull/347","diff_url":"https://github.com/begriffs/postgrest/pull/347.diff","patch_url":"https://github.com/begriffs/postgrest/pull/347.patch","issue_url":"https://api.github.com/repos/begriffs/postgrest/issues/347","number":347,"state":"open","locked":false,"title":"Basic tables/procs for blog demo","user":{"login":"begriffs","id":911911,"avatar_url":"https://avatars.githubusercontent.com/u/911911?v=3","gravatar_id":"","url":"https://api.github.com/users/begriffs","html_url":"https://github.com/begriffs","followers_url":"https://api.github.com/users/begriffs/followers","following_url":"https://api.github.com/users/begriffs/following{/other_user}","gists_url":"https://api.github.com/users/begriffs/gists{/gist_id}","starred_url":"https://api.github.com/users/begriffs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/begriffs/subscriptions","organizations_url":"https://api.github.com/users/begriffs/orgs","repos_url":"https://api.github.com/users/begriffs/repos","events_url":"https://api.github.com/users/begriffs/events{/privacy}","received_events_url":"https://api.github.com/users/begriffs/received_events","type":"User","site_admin":false},"body":"Still need the user management views.","created_at":"2015-11-06T03:08:26Z","updated_at":"2015-11-11T02:27:10Z","closed_at":null,"merged_at":null,"merge_commit_sha":"519bd874a92b5da2860810fb4b92d97fca796df4","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/begriffs/postgrest/pulls/347/commits","review_comments_url":"https://api.github.com/repos/begriffs/postgrest/pulls/347/comments","review_comment_url":"https://api.github.com/repos/begriffs/postgrest/pulls/comments{/number}","comments_url":"https://api.github.com/repos/begriffs/postgrest/issues/347/comments","statuses_url":"https://api.github.com/repos/begriffs/postgrest/statuses/935d7bb092f3e0b8259ff5ce791cfaf80ff192c8","head":{"label":"begriffs:schema-templates","ref":"schema-templates","sha":"935d7bb092f3e0b8259ff5ce791cfaf80ff192c8","user":{"login":"begriffs","id":911911,"avatar_url":"https://avatars.githubusercontent.com/u/911911?v=3","gravatar_id":"","url":"https://api.github.com/users/begriffs","html_url":"https://github.com/begriffs","followers_url":"https://api.github.com/users/begriffs/followers","following_url":"https://api.github.com/users/begriffs/following{/other_user}","gists_url":"https://api.github.com/users/begriffs/gists{/gist_id}","starred_url":"https://api.github.com/users/begriffs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/begriffs/subscriptions","organizations_url":"https://api.github.com/users/begriffs/orgs","repos_url":"https://api.github.com/users/begriffs/repos","events_url":"https://api.github.com/users/begriffs/events{/privacy}","received_events_url":"https://api.github.com/users/begriffs/received_events","type":"User","site_admin":false},"repo":{"id":20787122,"name":"postgrest","full_name":"begriffs/postgrest","owner":{"login":"begriffs","id":911911,"avatar_url":"https://avatars.githubusercontent.com/u/911911?v=3","gravatar_id":"","url":"https://api.github.com/users/begriffs","html_url":"https://github.com/begriffs","followers_url":"https://api.github.com/users/begriffs/followers","following_url":"https://api.github.com/users/begriffs/following{/other_user}","gists_url":"https://api.github.com/users/begriffs/gists{/gist_id}","starred_url":"https://api.github.com/users/begriffs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/begriffs/subscriptions","organizations_url":"https://api.github.com/users/begriffs/orgs","repos_url":"https://api.github.com/users/begriffs/repos","events_url":"https://api.github.com/users/begriffs/events{/privacy}","received_events_url":"https://api.github.com/users/begriffs/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/begriffs/postgrest","description":"REST API for any Postgres database","fork":false,"url":"https://api.github.com/repos/begriffs/postgrest","forks_url":"https://api.github.com/repos/begriffs/postgrest/forks","keys_url":"https://api.github.com/repos/begriffs/postgrest/keys{/key_id}","collaborators_url":"https://api.github.com/repos/begriffs/postgrest/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/begriffs/postgrest/teams","hooks_url":"https://api.github.com/repos/begriffs/postgrest/hooks","issue_events_url":"https://api.github.com/repos/begriffs/postgrest/issues/events{/number}","events_url":"https://api.github.com/repos/begriffs/postgrest/events","assignees_url":"https://api.github.com/repos/begriffs/postgrest/assignees{/user}","branches_url":"https://api.github.com/repos/begriffs/postgrest/branches{/branch}","tags_url":"https://api.github.com/repos/begriffs/postgrest/tags","blobs_url":"https://api.github.com/repos/begriffs/postgrest/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/begriffs/postgrest/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/begriffs/postgrest/git/refs{/sha}","trees_url":"https://api.github.com/repos/begriffs/postgrest/git/trees{/sha}","statuses_url":"https://api.github.com/repos/begriffs/postgrest/statuses/{sha}","languages_url":"https://api.github.com/repos/begriffs/postgrest/languages","stargazers_url":"https://api.github.com/repos/begriffs/postgrest/stargazers","contributors_url":"https://api.github.com/repos/begriffs/postgrest/contributors","subscribers_url":"https://api.github.com/repos/begriffs/postgrest/subscribers","subscription_url":"https://api.github.com/repos/begriffs/postgrest/subscription","commits_url":"https://api.github.com/repos/begriffs/postgrest/commits{/sha}","git_commits_url":"https://api.github.com/repos/begriffs/postgrest/git/commits{/sha}","comments_url":"https://api.github.com/repos/begriffs/postgrest/comments{/number}","issue_comment_url":"https://api.github.com/repos/begriffs/postgrest/issues/comments{/number}","contents_url":"https://api.github.com/repos/begriffs/postgrest/contents/{+path}","compare_url":"https://api.github.com/repos/begriffs/postgrest/compare/{base}...{head}","merges_url":"https://api.github.com/repos/begriffs/postgrest/merges","archive_url":"https://api.github.com/repos/begriffs/postgrest/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/begriffs/postgrest/downloads","issues_url":"https://api.github.com/repos/begriffs/postgrest/issues{/number}","pulls_url":"https://api.github.com/repos/begriffs/postgrest/pulls{/number}","milestones_url":"https://api.github.com/repos/begriffs/postgrest/milestones{/number}","notifications_url":"https://api.github.com/repos/begriffs/postgrest/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/begriffs/postgrest/labels{/name}","releases_url":"https://api.github.com/repos/begriffs/postgrest/releases{/id}","created_at":"2014-06-13T00:23:00Z","updated_at":"2015-11-10T22:22:25Z","pushed_at":"2015-11-10T23:13:02Z","git_url":"git://github.com/begriffs/postgrest.git","ssh_url":"git@github.com:begriffs/postgrest.git","clone_url":"https://github.com/begriffs/postgrest.git","svn_url":"https://github.com/begriffs/postgrest","homepage":"","size":4200,"stargazers_count":5748,"watchers_count":5748,"language":"Haskell","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":178,"mirror_url":null,"open_issues_count":50,"forks":178,"open_issues":50,"watchers":5748,"default_branch":"master"}},"base":{"label":"begriffs:v3","ref":"v3","sha":"368bf34842e7a011240fffd991f32a44a0b69b24","user":{"login":"begriffs","id":911911,"avatar_url":"https://avatars.githubusercontent.com/u/911911?v=3","gravatar_id":"","url":"https://api.github.com/users/begriffs","html_url":"https://github.com/begriffs","followers_url":"https://api.github.com/users/begriffs/followers","following_url":"https://api.github.com/users/begriffs/following{/other_user}","gists_url":"https://api.github.com/users/begriffs/gists{/gist_id}","starred_url":"https://api.github.com/users/begriffs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/begriffs/subscriptions","organizations_url":"https://api.github.com/users/begriffs/orgs","repos_url":"https://api.github.com/users/begriffs/repos","events_url":"https://api.github.com/users/begriffs/events{/privacy}","received_events_url":"https://api.github.com/users/begriffs/received_events","type":"User","site_admin":false},"repo":{"id":20787122,"name":"postgrest","full_name":"begriffs/postgrest","owner":{"login":"begriffs","id":911911,"avatar_url":"https://avatars.githubusercontent.com/u/911911?v=3","gravatar_id":"","url":"https://api.github.com/users/begriffs","html_url":"https://github.com/begriffs","followers_url":"https://api.github.com/users/begriffs/followers","following_url":"https://api.github.com/users/begriffs/following{/other_user}","gists_url":"https://api.github.com/users/begriffs/gists{/gist_id}","starred_url":"https://api.github.com/users/begriffs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/begriffs/subscriptions","organizations_url":"https://api.github.com/users/begriffs/orgs","repos_url":"https://api.github.com/users/begriffs/repos","events_url":"https://api.github.com/users/begriffs/events{/privacy}","received_events_url":"https://api.github.com/users/begriffs/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/begriffs/postgrest","description":"REST API for any Postgres database","fork":false,"url":"https://api.github.com/repos/begriffs/postgrest","forks_url":"https://api.github.com/repos/begriffs/postgrest/forks","keys_url":"https://api.github.com/repos/begriffs/postgrest/keys{/key_id}","collaborators_url":"https://api.github.com/repos/begriffs/postgrest/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/begriffs/postgrest/teams","hooks_url":"https://api.github.com/repos/begriffs/postgrest/hooks","issue_events_url":"https://api.github.com/repos/begriffs/postgrest/issues/events{/number}","events_url":"https://api.github.com/repos/begriffs/postgrest/events","assignees_url":"https://api.github.com/repos/begriffs/postgrest/assignees{/user}","branches_url":"https://api.github.com/repos/begriffs/postgrest/branches{/branch}","tags_url":"https://api.github.com/repos/begriffs/postgrest/tags","blobs_url":"https://api.github.com/repos/begriffs/postgrest/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/begriffs/postgrest/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/begriffs/postgrest/git/refs{/sha}","trees_url":"https://api.github.com/repos/begriffs/postgrest/git/trees{/sha}","statuses_url":"https://api.github.com/repos/begriffs/postgrest/statuses/{sha}","languages_url":"https://api.github.com/repos/begriffs/postgrest/languages","stargazers_url":"https://api.github.com/repos/begriffs/postgrest/stargazers","contributors_url":"https://api.github.com/repos/begriffs/postgrest/contributors","subscribers_url":"https://api.github.com/repos/begriffs/postgrest/subscribers","subscription_url":"https://api.github.com/repos/begriffs/postgrest/subscription","commits_url":"https://api.github.com/repos/begriffs/postgrest/commits{/sha}","git_commits_url":"https://api.github.com/repos/begriffs/postgrest/git/commits{/sha}","comments_url":"https://api.github.com/repos/begriffs/postgrest/comments{/number}","issue_comment_url":"https://api.github.com/repos/begriffs/postgrest/issues/comments{/number}","contents_url":"https://api.github.com/repos/begriffs/postgrest/contents/{+path}","compare_url":"https://api.github.com/repos/begriffs/postgrest/compare/{base}...{head}","merges_url":"https://api.github.com/repos/begriffs/postgrest/merges","archive_url":"https://api.github.com/repos/begriffs/postgrest/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/begriffs/postgrest/downloads","issues_url":"https://api.github.com/repos/begriffs/postgrest/issues{/number}","pulls_url":"https://api.github.com/repos/begriffs/postgrest/pulls{/number}","milestones_url":"https://api.github.com/repos/begriffs/postgrest/milestones{/number}","notifications_url":"https://api.github.com/repos/begriffs/postgrest/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/begriffs/postgrest/labels{/name}","releases_url":"https://api.github.com/repos/begriffs/postgrest/releases{/id}","created_at":"2014-06-13T00:23:00Z","updated_at":"2015-11-10T22:22:25Z","pushed_at":"2015-11-10T23:13:02Z","git_url":"git://github.com/begriffs/postgrest.git","ssh_url":"git@github.com:begriffs/postgrest.git","clone_url":"https://github.com/begriffs/postgrest.git","svn_url":"https://github.com/begriffs/postgrest","homepage":"","size":4200,"stargazers_count":5748,"watchers_count":5748,"language":"Haskell","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":178,"mirror_url":null,"open_issues_count":50,"forks":178,"open_issues":50,"watchers":5748,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/begriffs/postgrest/pulls/347"},"html":{"href":"https://github.com/begriffs/postgrest/pull/347"},"issue":{"href":"https://api.github.com/repos/begriffs/postgrest/issues/347"},"comments":{"href":"https://api.github.com/repos/begriffs/postgrest/issues/347/comments"},"review_comments":{"href":"https://api.github.com/repos/begriffs/postgrest/pulls/347/comments"},"review_comment":{"href":"https://api.github.com/repos/begriffs/postgrest/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/begriffs/postgrest/pulls/347/commits"},"statuses":{"href":"https://api.github.com/repos/begriffs/postgrest/statuses/935d7bb092f3e0b8259ff5ce791cfaf80ff192c8"}}}}