1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
-- pgcrypto is needed for UUIDs.
create extension pgcrypto;
-- A table with defaults and constraints.
create table bugs (
id uuid not null primary key default gen_random_uuid(),
reported timestamp with time zone not null default now(),
reporter varchar not null default current_user,
project varchar(256) not null check (project <> ''),
description varchar(10240) not null check (description <> '')
);
-- No indexes, since they are irrelevant to this example (but normally
-- they should be there).
-- Additional restrictions.
create policy bugs_select_policy on bugs for select using (true);
create policy bugs_insert_policy on bugs for insert
with check (reported = now() and reporter = current_user);
-- Update and delete policies can be added later.
alter table bugs enable row level security;
-- A search function for convenience.
create or replace function bug_search (proj varchar, descr varchar, lim int, offs int)
returns xml
as $$
select query_to_xml(
'select id, date_trunc(''second'', reported) as reported, reporter, '
|| 'project, substring(description from ''[^\n\r]+'') as summary from bugs '
|| 'where (project like ' || quote_literal('%' || proj || '%')
|| ') and (description like ' || quote_literal('%' || descr || '%')
|| ') order by reported desc limit ' || lim || ' offset ' || offs,
false, false, 'bugs')
$$ language sql;
-- Now users can be added with select and/or insert privileges,
-- including a guest user for unauthenticated requests.
|
