-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathsuccess-postgresql.html
More file actions
349 lines (302 loc) · 31.4 KB
/
Copy pathsuccess-postgresql.html
File metadata and controls
349 lines (302 loc) · 31.4 KB
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
<!DOCTYPE HTML>
<html lang="en" class="light" dir="ltr">
<head>
<!-- Book generated using mdBook -->
<meta charset="UTF-8">
<title>Success with PostgreSQL</title>
<!-- Custom HTML head -->
<meta name="description" content="">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="theme-color" content="#ffffff">
<link rel="shortcut icon" href="favicon.png">
<link rel="stylesheet" href="css/variables.css">
<link rel="stylesheet" href="css/general.css">
<link rel="stylesheet" href="css/chrome.css">
<link rel="stylesheet" href="css/print.css" media="print">
<!-- Fonts -->
<link rel="stylesheet" href="FontAwesome/css/font-awesome.css">
<link rel="stylesheet" href="fonts/fonts.css">
<!-- Highlight.js Stylesheets -->
<link rel="stylesheet" href="highlight.css">
<link rel="stylesheet" href="tomorrow-night.css">
<link rel="stylesheet" href="ayu-highlight.css">
<!-- Custom theme stylesheets -->
<script data-host="https://app.microanalytics.io" data-dnt="false" src="https://app.microanalytics.io/js/script.js" id="ZwSg9rf6GA" async defer></script>
</head>
<body class="sidebar-visible no-js">
<div id="body-container">
<!-- Provide site root to javascript -->
<script>
var path_to_root = "";
var default_theme = window.matchMedia("(prefers-color-scheme: dark)").matches ? "navy" : "light";
</script>
<!-- Work around some values being stored in localStorage wrapped in quotes -->
<script>
try {
var theme = localStorage.getItem('mdbook-theme');
var sidebar = localStorage.getItem('mdbook-sidebar');
if (theme.startsWith('"') && theme.endsWith('"')) {
localStorage.setItem('mdbook-theme', theme.slice(1, theme.length - 1));
}
if (sidebar.startsWith('"') && sidebar.endsWith('"')) {
localStorage.setItem('mdbook-sidebar', sidebar.slice(1, sidebar.length - 1));
}
} catch (e) { }
</script>
<!-- Set the theme before any content is loaded, prevents flash -->
<script>
var theme;
try { theme = localStorage.getItem('mdbook-theme'); } catch(e) { }
if (theme === null || theme === undefined) { theme = default_theme; }
var html = document.querySelector('html');
html.classList.remove('light')
html.classList.add(theme);
var body = document.querySelector('body');
body.classList.remove('no-js')
body.classList.add('js');
</script>
<input type="checkbox" id="sidebar-toggle-anchor" class="hidden">
<!-- Hide / unhide sidebar before it is displayed -->
<script>
var body = document.querySelector('body');
var sidebar = null;
var sidebar_toggle = document.getElementById("sidebar-toggle-anchor");
if (document.body.clientWidth >= 1080) {
try { sidebar = localStorage.getItem('mdbook-sidebar'); } catch(e) { }
sidebar = sidebar || 'visible';
} else {
sidebar = 'hidden';
}
sidebar_toggle.checked = sidebar === 'visible';
body.classList.remove('sidebar-visible');
body.classList.add("sidebar-" + sidebar);
</script>
<nav id="sidebar" class="sidebar" aria-label="Table of contents">
<div class="sidebar-scrollbox">
<ul class="chapter">
<li><a class="active" href="../">chuck-stack Home</a></li> <!--chuboe changed-->
</ul>
<ol class="chapter"><li class="chapter-item expanded "><a href="introduction.html"><strong aria-hidden="true">1.</strong> Introduction</a></li><li class="chapter-item expanded "><a href="picture-success.html"><strong aria-hidden="true">2.</strong> Picture of Success</a></li><li class="chapter-item expanded "><a href="success-factor.html"><strong aria-hidden="true">3.</strong> Success Factors</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="success-hr.html"><strong aria-hidden="true">3.1.</strong> Success with HR</a></li><li class="chapter-item expanded "><a href="success-ai.html"><strong aria-hidden="true">3.2.</strong> Success with AI</a></li><li class="chapter-item expanded "><a href="success-cli.html"><strong aria-hidden="true">3.3.</strong> Success with CLI</a></li><li class="chapter-item expanded "><a href="success-linux.html"><strong aria-hidden="true">3.4.</strong> Success with Linux</a></li><li class="chapter-item expanded "><a href="success-postgresql.html" class="active"><strong aria-hidden="true">3.5.</strong> Success with PostgreSQL</a></li><li class="chapter-item expanded "><a href="success-api.html"><strong aria-hidden="true">3.6.</strong> Success with API</a></li></ol></li><li class="chapter-item expanded "><a href="getting-started.html"><strong aria-hidden="true">4.</strong> Getting Started</a></li><li class="chapter-item expanded "><a href="stack-faq.html"><strong aria-hidden="true">5.</strong> Stack FAQ</a></li><li class="chapter-item expanded "><a href="stack-tools.html"><strong aria-hidden="true">6.</strong> Stack Tools</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="tool-incus.html"><strong aria-hidden="true">6.1.</strong> Incus</a></li><li class="chapter-item expanded "><a href="tool-linux.html"><strong aria-hidden="true">6.2.</strong> Linux</a></li><li class="chapter-item expanded "><a href="tool-postgresql.html"><strong aria-hidden="true">6.3.</strong> PostgreSQL</a></li><li class="chapter-item expanded "><a href="tool-postgrest.html"><strong aria-hidden="true">6.4.</strong> PostgREST</a></li><li class="chapter-item expanded "><a href="tool-aichat.html"><strong aria-hidden="true">6.5.</strong> AIChat</a></li><li class="chapter-item expanded "><a href="tool-nushell.html"><strong aria-hidden="true">6.6.</strong> Nushell</a></li><li class="chapter-item expanded "><a href="tool-zellij.html"><strong aria-hidden="true">6.7.</strong> Zellij</a></li><li class="chapter-item expanded "><a href="tool-git.html"><strong aria-hidden="true">6.8.</strong> Git</a></li><li class="chapter-item expanded "><a href="tool-obsidian.html"><strong aria-hidden="true">6.9.</strong> Obsidian</a></li><li class="chapter-item expanded "><a href="tool-netbird.html"><strong aria-hidden="true">6.10.</strong> Netbird</a></li><li class="chapter-item expanded "><a href="tool-idempiere.html"><strong aria-hidden="true">6.11.</strong> iDempiere</a></li><li class="chapter-item expanded "><a href="tool-htmx.html"><strong aria-hidden="true">6.12.</strong> htmx</a></li><li class="chapter-item expanded "><a href="tool-discourse.html"><strong aria-hidden="true">6.13.</strong> Discourse</a></li><li class="chapter-item expanded "><a href="tool-mdbook.html"><strong aria-hidden="true">6.14.</strong> mdBook</a></li><li class="chapter-item expanded "><a href="tool-others.html"><strong aria-hidden="true">6.15.</strong> Others</a></li></ol></li><li class="chapter-item expanded "><a href="stack-architecture.html"><strong aria-hidden="true">7.</strong> Stack Architecture</a></li><li class="chapter-item expanded "><a href="stack-application.html"><strong aria-hidden="true">8.</strong> Stack Application</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="application-deploy.html"><strong aria-hidden="true">8.1.</strong> Deployment</a></li><li class="chapter-item expanded "><a href="application-workflow.html"><strong aria-hidden="true">8.2.</strong> Workflow</a></li><li class="chapter-item expanded "><a href="application-attribute.html"><strong aria-hidden="true">8.3.</strong> Attribute Tagging</a></li></ol></li><li class="chapter-item expanded "><a href="best-practices.html"><strong aria-hidden="true">9.</strong> Stack Best Practices</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="best-practices-operation.html"><strong aria-hidden="true">9.1.</strong> Operation Best Practices</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="best-practices-operation-say.html"><strong aria-hidden="true">9.1.1.</strong> Say What We Do</a></li><li class="chapter-item expanded "><a href="best-practices-operation-do.html"><strong aria-hidden="true">9.1.2.</strong> Do What We Said</a></li><li class="chapter-item expanded "><a href="best-practices-operation-prove.html"><strong aria-hidden="true">9.1.3.</strong> Prove We Did It</a></li><li class="chapter-item expanded "><a href="best-practices-operation-feedback.html"><strong aria-hidden="true">9.1.4.</strong> Welcome Feedback</a></li><li class="chapter-item expanded "><a href="best-practices-operation-improve.html"><strong aria-hidden="true">9.1.5.</strong> Continuously Improve</a></li></ol></li><li class="chapter-item expanded "><a href="data-management-philosophy.html"><strong aria-hidden="true">9.2.</strong> Data Management Philosophy</a></li><li class="chapter-item expanded "><a href="postgres-conventions.html"><strong aria-hidden="true">9.3.</strong> Postgres Conventions</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="postgres-convention/schema.html"><strong aria-hidden="true">9.3.1.</strong> Private and API Schema</a></li><li class="chapter-item expanded "><a href="postgres-convention/abbreviation.html"><strong aria-hidden="true">9.3.2.</strong> Abbreviation</a></li><li class="chapter-item expanded "><a href="postgres-convention/table-convention.html"><strong aria-hidden="true">9.3.3.</strong> Table Convention</a></li><li class="chapter-item expanded "><a href="postgres-convention/column-convention.html"><strong aria-hidden="true">9.3.4.</strong> Column Convention</a></li><li class="chapter-item expanded "><a href="postgres-convention/table-record-convention.html"><strong aria-hidden="true">9.3.5.</strong> Table and Record</a></li><li class="chapter-item expanded "><a href="postgres-convention/uuid.html"><strong aria-hidden="true">9.3.6.</strong> UUID</a></li><li class="chapter-item expanded "><a href="postgres-convention/json-array-table-column.html"><strong aria-hidden="true">9.3.7.</strong> JSON vs Array vs Table</a></li><li class="chapter-item expanded "><a href="postgres-convention/enum-type-convention.html"><strong aria-hidden="true">9.3.8.</strong> enum and Type</a></li><li class="chapter-item expanded "><a href="postgres-convention/partition-convention.html"><strong aria-hidden="true">9.3.9.</strong> Partition</a></li><li class="chapter-item expanded "><a href="postgres-convention/function-convention.html"><strong aria-hidden="true">9.3.10.</strong> Function</a></li><li class="chapter-item expanded "><a href="postgres-convention/trigger-convention.html"><strong aria-hidden="true">9.3.11.</strong> Trigger</a></li><li class="chapter-item expanded "><a href="postgres-convention/comment.html"><strong aria-hidden="true">9.3.12.</strong> Comments</a></li><li class="chapter-item expanded "><a href="postgres-convention/sample-table-convention.html"><strong aria-hidden="true">9.3.13.</strong> Sample Table</a></li><li class="chapter-item expanded "><a href="postgres-convention/scalability-convention.html"><strong aria-hidden="true">9.3.14.</strong> Scalability</a></li><li class="chapter-item expanded "><a href="postgres-convention/nushell.html"><strong aria-hidden="true">9.3.15.</strong> Nushell psql</a></li><li class="chapter-item expanded "><a href="postgres-convention/to-be-resolved-convention.html"><strong aria-hidden="true">9.3.16.</strong> To Be Resolved</a></li></ol></li><li class="chapter-item expanded "><a href="postgres-services.html"><strong aria-hidden="true">9.4.</strong> Postgres Services</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="postgres-convention/async.html"><strong aria-hidden="true">9.4.1.</strong> Async</a></li><li class="chapter-item expanded "><a href="postgres-convention/attribute-tag.html"><strong aria-hidden="true">9.4.2.</strong> Attribute Tagging</a></li><li class="chapter-item expanded "><a href="postgres-convention/change-log.html"><strong aria-hidden="true">9.4.3.</strong> Change Log</a></li><li class="chapter-item expanded "><a href="postgres-convention/event.html"><strong aria-hidden="true">9.4.4.</strong> Event</a></li><li class="chapter-item expanded "><a href="postgres-convention/notify.html"><strong aria-hidden="true">9.4.5.</strong> Notify</a></li><li class="chapter-item expanded "><a href="postgres-convention/request.html"><strong aria-hidden="true">9.4.6.</strong> Request Workflow</a></li><li class="chapter-item expanded "><a href="postgres-convention/statistics-convention.html"><strong aria-hidden="true">9.4.7.</strong> Statistics</a></li><li class="chapter-item expanded "><a href="postgres-convention/system-configuration-convention.html"><strong aria-hidden="true">9.4.8.</strong> System Configuration</a></li></ol></li></ol></li><li class="chapter-item expanded "><a href="cli-tutor.html"><strong aria-hidden="true">10.</strong> Stack Tutor</a></li><li class="chapter-item expanded "><a href="stack-academy.html"><strong aria-hidden="true">11.</strong> Stack Academy</a></li><li class="chapter-item expanded "><a href="stack-local.html"><strong aria-hidden="true">12.</strong> Local Community Support</a></li><li class="chapter-item expanded "><a href="terminology.html"><strong aria-hidden="true">13.</strong> Terminology</a></li><li class="chapter-item expanded "><a href="roadmap.html"><strong aria-hidden="true">14.</strong> Roadmap</a></li><li class="chapter-item expanded "><a href="project-history.html"><strong aria-hidden="true">15.</strong> Project History</a></li><li class="chapter-item expanded "><a href="reference.html"><strong aria-hidden="true">16.</strong> References</a></li><li class="chapter-item expanded affix "><li class="spacer"></li><li class="chapter-item expanded "><a href="code-of-conduct.html"><strong aria-hidden="true">17.</strong> Code of Conduct</a></li><li class="chapter-item expanded "><a href="privacy-policy.html"><strong aria-hidden="true">18.</strong> Privacy Policy</a></li><li class="chapter-item expanded affix "><li class="spacer"></li><li class="chapter-item expanded "><a href="blog.html"><strong aria-hidden="true">19.</strong> Blog</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="blog-postgresql-json-show-sales-order.html"><strong aria-hidden="true">19.1.</strong> How to Extract JSON from PostgreSQL</a></li><li class="chapter-item expanded "><a href="blog-incus-netbird-phoenixnap-isolated.html"><strong aria-hidden="true">19.2.</strong> Isolated Public-Facing Services in Incus</a></li><li class="chapter-item expanded "><a href="blog-local-co-op-funding-help.html"><strong aria-hidden="true">19.3.</strong> Local AI Funding - Time to Bring Back the Co-op</a></li><li class="chapter-item expanded "><a href="blog-llm-ai-production-deploy.html"><strong aria-hidden="true">19.4.</strong> Production Open Source Chat with Work Instructions</a></li><li class="chapter-item expanded "><a href="blog-chuck-stack-it-job-description-assessment.html"><strong aria-hidden="true">19.5.</strong> chuck-stack IT Job Description</a></li><li class="chapter-item expanded "><a href="blog-rsync-net.html"><strong aria-hidden="true">19.6.</strong> Immutable Off-site Backups are Imperative</a></li><li class="chapter-item expanded "><a href="blog-llm-ai-operations-automation.html"><strong aria-hidden="true">19.7.</strong> Operations AI LLM Automation in 2025</a></li><li class="chapter-item expanded "><a href="blog-incus-netbird-phoenixnap.html"><strong aria-hidden="true">19.8.</strong> Hybrid Cloud Strategy: Incus + Netbird + PhoenixNAP</a></li><li class="chapter-item expanded "><a href="blog-letter-ceo.html"><strong aria-hidden="true">19.9.</strong> Letter to the CEO</a></li><li class="chapter-item expanded "><a href="blog-cli-better.html"><strong aria-hidden="true">19.10.</strong> The CLI is better than...</a></li><li class="chapter-item expanded "><a href="blog-conversational-enterprise-computing.html"><strong aria-hidden="true">19.11.</strong> Why Conversational Enterprise Computing Matters</a></li><li class="chapter-item expanded "><a href="blog-work-instruction-sexy.html"><strong aria-hidden="true">19.12.</strong> Work Instructions Make your Organization Sexy</a></li><li class="chapter-item expanded "><a href="blog-live-markdown-world.html"><strong aria-hidden="true">19.13.</strong> We Live in a Markdown World</a></li></ol></li></ol>
</div>
<div id="sidebar-resize-handle" class="sidebar-resize-handle">
<div class="sidebar-resize-indicator"></div>
</div>
</nav>
<!-- Track and set sidebar scroll position -->
<script>
var sidebarScrollbox = document.querySelector('#sidebar .sidebar-scrollbox');
sidebarScrollbox.addEventListener('click', function(e) {
if (e.target.tagName === 'A') {
sessionStorage.setItem('sidebar-scroll', sidebarScrollbox.scrollTop);
}
}, { passive: true });
var sidebarScrollTop = sessionStorage.getItem('sidebar-scroll');
sessionStorage.removeItem('sidebar-scroll');
if (sidebarScrollTop) {
// preserve sidebar scroll position when navigating via links within sidebar
sidebarScrollbox.scrollTop = sidebarScrollTop;
} else {
// scroll sidebar to current active section when navigating via "next/previous chapter" buttons
var activeSection = document.querySelector('#sidebar .active');
if (activeSection) {
activeSection.scrollIntoView({ block: 'center' });
}
}
</script>
<div id="page-wrapper" class="page-wrapper">
<div class="page">
<!-- add things here that will go above the body navigation -->
<div id="menu-bar-hover-placeholder"></div>
<div id="menu-bar" class="menu-bar sticky">
<div class="left-buttons">
<label id="sidebar-toggle" class="icon-button" for="sidebar-toggle-anchor" title="Toggle Table of Contents" aria-label="Toggle Table of Contents" aria-controls="sidebar">
<i class="fa fa-bars"></i>
</label>
<button id="theme-toggle" class="icon-button" type="button" title="Change theme" aria-label="Change theme" aria-haspopup="true" aria-expanded="false" aria-controls="theme-list">
<i class="fa fa-paint-brush"></i>
</button>
<ul id="theme-list" class="theme-popup" aria-label="Themes" role="menu">
<li role="none"><button role="menuitem" class="theme" id="light">Light</button></li>
<li role="none"><button role="menuitem" class="theme" id="rust">Rust</button></li>
<li role="none"><button role="menuitem" class="theme" id="coal">Coal</button></li>
<li role="none"><button role="menuitem" class="theme" id="navy">Navy</button></li>
<li role="none"><button role="menuitem" class="theme" id="ayu">Ayu</button></li>
</ul>
<button id="search-toggle" class="icon-button" type="button" title="Search. (Shortkey: s)" aria-label="Toggle Searchbar" aria-expanded="false" aria-keyshortcuts="S" aria-controls="searchbar">
<i class="fa fa-search"></i>
</button>
</div>
<h1 class="menu-title"></h1>
<div class="right-buttons">
<a href="print.html" title="Print this book" aria-label="Print this book">
<i id="print-button" class="fa fa-print"></i>
</a>
</div>
</div>
<div id="search-wrapper" class="hidden">
<form id="searchbar-outer" class="searchbar-outer">
<input type="search" id="searchbar" name="searchbar" placeholder="Search this book ..." aria-controls="searchresults-outer" aria-describedby="searchresults-header">
</form>
<div id="searchresults-outer" class="searchresults-outer hidden">
<div id="searchresults-header" class="searchresults-header"></div>
<ul id="searchresults">
</ul>
</div>
</div>
<!-- Apply ARIA attributes after the sidebar and the sidebar toggle button are added to the DOM -->
<script>
document.getElementById('sidebar-toggle').setAttribute('aria-expanded', sidebar === 'visible');
document.getElementById('sidebar').setAttribute('aria-hidden', sidebar !== 'visible');
Array.from(document.querySelectorAll('#sidebar a')).forEach(function(link) {
link.setAttribute('tabIndex', sidebar === 'visible' ? 0 : -1);
});
</script>
<div id="content" class="content">
<main>
<h1 id="success-using-postgresql"><a class="header" href="#success-using-postgresql">Success Using PostgreSQL</a></h1>
<p>PostgreSQL plays a critical role in our success. How we plan to create that success is largely attributable to the documentation and practices of <a href="./tool-postgrest.html">PostgREST.org</a>. They paved a way to move user and organization logic previously trapped in an application server to the database itself. They proved the concept was more than possible, it was actually advantageous.</p>
<p>When the concepts in this page are fully implemented in the chuck-stack, this page will go away, and its contents will be move to other functional areas. Until then, this information represents our plan and our partial implementation.</p>
<h2 id="toc"><a class="header" href="#toc">TOC</a></h2>
<ul>
<li><a href="#user-and-role-management">User and Role Management</a></li>
<li><a href="#session-variables">Session Variables</a></li>
<li><a href="#sql-plus-psql">SQL plus psql</a></li>
<li><a href="#case-for-psql">Case for psql</a></li>
<li><a href="#sql-encapsulation">SQL Encapsulation</a></li>
<li><a href="#configuration-management">Configuration Management</a></li>
<li><a href="#technical-details-and-examples">Technical Details and Examples</a></li>
</ul>
<h2 id="user-and-role-management"><a class="header" href="#user-and-role-management">User and Role Management</a></h2>
<p>User management plays a small part is how the chuck-stack operates. Almost all logic is tied to a role. PostgREST gives us a great roadmap for accomplishing this task.</p>
<p>Here is a quick description of how chuck-stack manages users and roles:</p>
<ul>
<li>Users will be created in PostgreSQL with no privileges.</li>
<li>Users will be granted access to a PostgreSQL role.</li>
<li>This concept is called impersonation where the user logs in and quickly moved to a role.</li>
<li>After the login is complete, all authorization is performed at the role level.</li>
</ul>
<p>Here is an example of impersonation:</p>
<pre><code class="language-sql">create role some_role nologin;
grant usage on schema private to some_role;
grant select on private.stk_business_partner to some_role;
create role some_user_auth noinherit login password 'some_password';
grant some_role to some_user_auth;
psql -d some_db -U some_user_auth -c "set SESSION ROLE some_role" -c "select * from adempiere.c_bpartner limit 1"
</code></pre>
<p>If you remove the "-c ... set ROLE ..." command, the psql statement will fail with a "permission denied" error because the some_user_auth role does not have any privileges.</p>
<p>This example shows just how easily we can provide user management yet ensure that no user capabilities spill into role-based organization logic.</p>
<!-- the following is duplicated in multiple places including success-postgresql -->
<p>It is worth noting that user and role management is shared in both Linux and PostgreSQL. While these responsibilities exist in both planes, they are not duplicated. Linux dictates role-based tool access and how the user connects to the database. PostgreSQL dictates data access inside the database.</p>
<p>Reference: <a href="https://docs.postgrest.org/en/v12/references/auth.html">PostgREST Authentication</a></p>
<h2 id="session-variables"><a class="header" href="#session-variables">Session Variables</a></h2>
<p>A big requirement of enterprise systems is managing a user's context inside the application. The above example of switching from the user to the role is great for simplifying organization logic; however, the system still needs to know what user performed what action.</p>
<p>The chuck-stack uses another concept from PostgREST to set session variables (context) using PostgreSQL's configuration settings. Here is an example where any organization logic can check session variable details during a process.</p>
<pre><code class="language-sql">CREATE OR REPLACE FUNCTION check_user() RETURNS void AS $$
DECLARE
email text := current_setting('stk.variable', true)::json->>'email';
BEGIN
IF email = 'some_joe@bad.com' THEN
RAISE EXCEPTION 'No, you are not allowed'
USING HINT = 'Some additional details about the situation';
END IF;
END
$$ LANGUAGE plpgsql;
</code></pre>
<p>Setting these variables during psql execution is easy (<a href="./success-linux.html">see success with Linux</a>). Checking them during program execution is easy as demonstrated above.</p>
<h2 id="sql-plus-psql"><a class="header" href="#sql-plus-psql">SQL plus psql</a></h2>
<p>The purpose if this section is to highlight that chuck-stack not only benefits from SQL (one of the technical world's most well understood languages), but it also benefit from the psql application. psql is powerful SQL application in its own right that has additional features for a) helping users do their jobs and b) helping the stack protect its data and processes from attach (like sql injection).</p>
<h2 id="case-for-psql"><a class="header" href="#case-for-psql">Case for psql</a></h2>
<p>If we can use the psql PostgreSQL CLI tool to access the database, we get the following advantages:</p>
<ul>
<li>We can use environment variables and .pgpass to specify database connection details</li>
<li>We can use psql's variable capabilities to maintain and execute SQL templates (as well as the many, many other psql capabilities)</li>
<li>We prevent needing to code our own connection tool</li>
<li>We create a consistent way to connect to the database across many scenarios</li>
</ul>
<h2 id="sql-encapsulation"><a class="header" href="#sql-encapsulation">SQL Encapsulation</a></h2>
<p>The <a href="./postgres-conventions.html#schema">PostgreSQL Convention</a> highlights how we use two schemas to provide :</p>
<ul>
<li>a public (<code>api</code>) facing schema to allow for direct interactions via the CLI and REST and</li>
<li>a private (<code>private</code>) facing schema to allow to data representation encapsulation.</li>
</ul>
<h2 id="configuration-management"><a class="header" href="#configuration-management">Configuration Management</a></h2>
<p>The chuck-stack uses a <a href="https://github.com/chuckstack/chuck-stack-nushell-psql-migration">SQL migration tool</a> to track and deploy SQL migration scripts. We use a NixOS service to manage and automatically deploy migrations when needed.</p>
<h2 id="technical-details-and-examples"><a class="header" href="#technical-details-and-examples">Technical Details and Examples</a></h2>
<p>The purpose of this section is to highlight simple examples that demonstrate the concepts described above.</p>
<p>References:</p>
<ul>
<li><a href="https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET">PostgreSQL docs => Configuration Setting Functions</a></li>
<li><a href="https://www.depesz.com/2023/05/28/variables-in-psql-how-to-use-them/">psql variables demo</a></li>
</ul>
<p>From inside psql without modifying postgresql.conf:</p>
<pre><code class="language-sql">select set_config('test.test.test.one','truly',false);
select current_setting('test.test.test.one',true);
current_setting
-----------------
truly
</code></pre>
<p>From inside psql after modifying postgresql.conf to set test.chuboe='init':</p>
<pre><code class="language-sql">select current_setting('test.chuboe',true);
current_setting
-----------------
init
</code></pre>
<pre><code class="language-sql">select set_config('test.chuboe','post-set',false); --false says available outside of trx but only in session --true says only in trx
set_config
------------
post-set
</code></pre>
<pre><code class="language-sql">select current_setting('test.chuboe',true);
current_setting
-----------------
post-set
</code></pre>
<p>From command line - set temporarily after postgresql.conf updated (but postgresql.conf edit not needed)</p>
<pre><code class="language-bash">psqli -c "SET test.chuboe = 'some-other'" -c "select current_setting('test.chuboe',true)"
current_setting
-----------------
some-other
</code></pre>
<p>From command line - random parameter - not set in postgresql.conf</p>
<pre><code class="language-bash">psqli -c "SET gabe.chuboe1 = 'some-other1'" -c "select current_setting('gabe.chuboe1',true)"
current_setting
-----------------
some-other1
</code></pre>
<br>
<p>Copyright © 2024-<script>document.write(new Date().getFullYear())</script>, CHUBOE LLC. All rights reserved.</p> <!--chuboe changed-->
</main>
<nav class="nav-wrapper" aria-label="Page navigation">
<!-- Mobile navigation buttons -->
<a rel="prev" href="success-linux.html" class="mobile-nav-chapters previous" title="Previous chapter" aria-label="Previous chapter" aria-keyshortcuts="Left">
<i class="fa fa-angle-left"></i>
</a>
<a rel="next prefetch" href="success-api.html" class="mobile-nav-chapters next" title="Next chapter" aria-label="Next chapter" aria-keyshortcuts="Right">
<i class="fa fa-angle-right"></i>
</a>
<div style="clear: both"></div>
</nav>
</div>
</div>
<nav class="nav-wide-wrapper" aria-label="Page navigation">
<a rel="prev" href="success-linux.html" class="nav-chapters previous" title="Previous chapter" aria-label="Previous chapter" aria-keyshortcuts="Left">
<i class="fa fa-angle-left"></i>
</a>
<a rel="next prefetch" href="success-api.html" class="nav-chapters next" title="Next chapter" aria-label="Next chapter" aria-keyshortcuts="Right">
<i class="fa fa-angle-right"></i>
</a>
</nav>
</div>
<script>
window.playground_copyable = true;
</script>
<script src="elasticlunr.min.js"></script>
<script src="mark.min.js"></script>
<script src="searcher.js"></script>
<script src="clipboard.min.js"></script>
<script src="highlight.js"></script>
<script src="book.js"></script>
<!-- Custom JS scripts -->
</div>
</body>
</html>