https://postgraphile.org/assets/js/bd86b945.dcca415c.js

1"use strict";(globalThis.webpackChunk_localrepo_postgraphile_website=globalThis.webpackChunk_localrepo_postgraphile_website||[]).push([[5325],{46524(e,n,t){t.r(n),t.d(n,{assets:()=>c,contentTitle:()=>o,default:()=>h,frontMatter:()=>a,metadata:()=>s,toc:()=>l});const s=JSON.parse('{"id":"security","title":"Security","description":"Traditionally in web application architectures the security is implemented in","source":"@site/postgraphile/security.md","sourceDirName":".","slug":"/security","permalink":"/postgraphile/next/security","draft":false,"unlisted":false,"editUrl":"https://github.com/graphile/crystal/tree/main/postgraphile/website/postgraphile/security.md","tags":[],"version":"current","lastUpdatedAt":1766160712000,"frontMatter":{"title":"Security"},"sidebar":"docs","previous":{"title":"Behavior","permalink":"/postgraphile/next/behavior"},"next":{"title":"Realtime","permalink":"/postgraphile/next/realtime"}}');var i=t(31085),r=t(71184);const a={title:"Security"},o=void 0,c={},l=[{value:"Authentication strategies",id:"authentication-strategies",level:2},{value:"Feeding identity into PostgreSQL",id:"feeding-identity-into-postgresql",level:2},{value:"Issuing JWTs from PostgreSQL",id:"issuing-jwts-from-postgresql",level:2},{value:"Using values inside PostgreSQL",id:"using-values-inside-postgresql",level:2}];function d(e){const n={a:"a",admonition:"admonition",code:"code",h2:"h2",li:"li",mdxAdmonitionTitle:"mdxAdmonitionTitle",p:"p",pre:"pre",strong:"strong",ul:"ul",...(0,r.R)(),...e.components};return(0,i.jsxs)(i.Fragment,{children:[(0,i.jsx)(n.p,{children:"Traditionally in web application architectures the security is implemented in\nthe server layer and the database is treated as a simple store of data. Partly\nthis was due to necessity (the security policies offered by databases such as\nPostgreSQL were simply not granular enough), and partly this was people figuring\nit would reduce the workload on the database thus increases scalability.\nHowever, as applications grow, they start needing more advanced features or\nadditional services to interact with the database. There's a couple options they\nhave here: duplicate the authentication/authorization logic in multiple places\n(which can lead to discrepancies and increases the surface area for potential\nissues), or make sure everything goes through the original application layer\n(which then becomes both the development and performance bottleneck)."}),"\n",(0,i.jsxs)(n.p,{children:["However, this is no longer necessary since PostgreSQL introduced much more\ngranular permissions in the form of\n",(0,i.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/ddl-rowsecurity.html",children:"Row-Level Security (RLS) policies"})," in PostgreSQL 9.5 back at the\nbeginning of 2016. Now you can combine this with\nPostgreSQL established permissions system (based on roles) allowing your\napplication to be considerably more specific about permissions: adding row-level\npermission constraints to the existing table- and column-based permissions."]}),"\n",(0,i.jsx)(n.p,{children:"Now that this functionality is stable and proven (and especially with the\nperformance improvements in the latest PostgreSQL releases), we advise that you\nprotect your lowest level \u2014 the data itself. By doing so you can be sure that no\nmatter how many services interact with your database they will all be protected\nby the same underlying permissions logic, which you only need to maintain in one\nplace. You can add as many microservices as you like, and they can talk to the\ndatabase directly!"}),"\n",(0,i.jsx)(n.p,{children:"When Row Level Security (RLS) is enabled, all rows are by default not visible to\nany roles (except database administration roles and the role who created the\ndatabase/table); and permission is selectively granted with the use of policies."}),"\n",(0,i.jsxs)(n.p,{children:["If you already have a secure database schema that implements these technologies\nto protect your data at the lowest levels then you can leverage ",(0,i.jsx)(n.code,{children:"postgraphile"}),"\nto generate a powerful, secure and fast API very rapidly. PostGraphile simply\nneeds enough context (via ",(0,i.jsx)(n.a,{href:"./config/overview#pgsettings",children:(0,i.jsx)(n.code,{children:"pgSettings"})}),") to understand\nwho is making the current request."]}),"\n",(0,i.jsx)(n.h2,{id:"authentication-strategies",children:"Authentication strategies"}),"\n",(0,i.jsxs)(n.ul,{children:["\n",(0,i.jsxs)(n.li,{children:[(0,i.jsx)(n.strong,{children:"Sessions"}),": Use your framework\u2019s existing session middleware (e.g.\n",(0,i.jsx)(n.code,{children:"express-session"}),", ",(0,i.jsx)(n.code,{children:"@fastify/session"}),"). After the session has been validated\nyou can copy the user identifier and any relevant flags into ",(0,i.jsx)(n.code,{children:"pgSettings"}),"."]}),"\n",(0,i.jsxs)(n.li,{children:[(0,i.jsx)(n.strong,{children:"JWTs"}),": Verify the token in your middleware of choice, then map whichever\nclaims you require into PostgreSQL. The ",(0,i.jsx)(n.a,{href:"/postgraphile/next/jwt-guide",children:"JWT guide"})," walks\nthrough that process and links to the\n",(0,i.jsx)(n.a,{href:"/postgraphile/next/jwt-specification",children:"PostgreSQL JWT specification"})," that PostGraphile\nfollows."]}),"\n",(0,i.jsxs)(n.li,{children:[(0,i.jsx)(n.strong,{children:"Other tokens"}),": API keys, mTLS attributes, OAuth access tokens, or other\ncredentials can authenticate the caller; convert whatever identity or policy\ndata you need into values for ",(0,i.jsx)(n.code,{children:"pgSettings"}),"."]}),"\n"]}),"\n",(0,i.jsx)(n.p,{children:"PostGraphile does not recommend one approach over another;

1 pick whatever fits\nthe rest of your infrastructure and long-term maintenance plans."}),"\n",(0,i.jsxs)(n.admonition,{type:"warning",children:[(0,i.jsxs)(n.mdxAdmonitionTitle,{children:[(0,i.jsx)(n.code,{children:"lazy-jwt"})," is a stopgap"]}),(0,i.jsxs)(n.p,{children:["The ",(0,i.jsx)(n.code,{children:"postgraphile/presets/lazy-jwt"})," preset can decode simple Bearer tokens, but\nit deliberately does not address refresh tokens, revocation, or custom claim\nmapping. It can be helpful to get you started, but do not use it as a permanent\nsolution."]})]}),"\n",(0,i.jsx)(n.h2,{id:"feeding-identity-into-postgresql",children:"Feeding identity into PostgreSQL"}),"\n",(0,i.jsxs)(n.p,{children:["Your authentication layer runs inside your web framework; PostgreSQL only sees\nthe values you place into ",(0,i.jsx)(n.code,{children:"pgSettings"}),". A common pattern is to copy data from\nthe framework\u2019s request object inside ",(0,i.jsx)(n.code,{children:"preset.grafast.context"}),":"]}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-ts",metastring:'title="graphile.config.ts"',children:'export default {\n  grafast: {\n    async context(requestContext, args) {\n      const req = requestContext.expressv4?.req;\n      const pgSettings = {\n        ...args.contextValue?.pgSettings,\n      } as Record<string, string>;\n\n      if (req?.user?.id) {\n        pgSettings["myapp.user_id"] = String(req.user.id);\n      }\n      if (req?.user?.is_admin) {\n        pgSettings["myapp.is_admin"] = "true";\n      }\n\n      return {\n        ...args.contextValue,\n        pgSettings,\n      };\n    },\n  },\n};\n'})}),"\n",(0,i.jsxs)(n.p,{children:["Inside PostgreSQL you can read these values with ",(0,i.jsx)(n.code,{children:"current_setting"}),":"]}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-sql",children:"create function myapp.current_user_id() returns uuid as $$\n  select nullif(current_setting('myapp.user_id', true), '')::uuid;\n$$ language sql stable;\n"})}),"\n",(0,i.jsxs)(n.p,{children:["Apply the function (or the ",(0,i.jsx)(n.code,{children:"current_setting"})," call directly) inside Row Level\nSecurity policies to enforce your rules."]}),"\n",(0,i.jsxs)(n.p,{children:["The ",(0,i.jsx)(n.a,{href:"./config#pgsettings",children:"configuration docs"})," contain more variations on this\npattern, including how to expose HTTP headers or other request metadata."]}),"\n",(0,i.jsx)(n.h2,{id:"issuing-jwts-from-postgresql",children:"Issuing JWTs from PostgreSQL"}),"\n",(0,i.jsx)(n.p,{children:"PostGraphile also has support for generating JWTs easily from inside your\nPostgreSQL schema."}),"\n",(0,i.jsxs)(n.p,{children:["To do so we will take a composite type that you specify via\n",(0,i.jsx)(n.code,{children:"preset.gather.pgJwtTypes"}),":"]}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-js",metastring:'title="graphile.config.mjs"',children:'export default {\n  gather: {\n    pgJwtTypes: "jwt_token",\n  },\n  //...\n};\n'})}),"\n",(0,i.jsx)(n.p,{children:"The value of this setting is a schema-name, type-name tuple. Whenever a value\nof the type identified by this tuple is returned from a PostgreSQL function we\nwill instead sign it with your JWT secret and return it as a string JWT token\nas part of your GraphQL response payload."}),"\n",(0,i.jsx)(n.p,{children:"For example, you might define a composite type such as this in PostgreSQL:"}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-sql",children:"create type my_public_schema.jwt_token as (\n  role text,\n  exp integer,\n  person_id integer,\n  is_admin boolean,\n  username varchar\n);\n"})}),"\n",(0,i.jsx)(n.p,{children:"Then run PostGraphile with this configuration"}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-js",metastring:'title="graphile.config.mjs"',children:'import { PostGraphileAmberPreset } from "postgraphile/presets/amber";\n\nexport default {\n  extends: [PostGraphileAmberPreset],\n  gather: {\n    // highlight-next-line\n    pgJwtTypes: "my_public_schema.jwt_token",\n  },\n  schema: {\n    // highlight-next-line\n    pgJwtSecret: process.env.JWT_SECRET,\n  },\n};\n'})}),"\n",(0,i.jsx)(n.p,{children:"And finally you might add a PostgreSQL function such as:"}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-sql",metastring:"{5}",children:"create function my_public_schema.authenticate(\n  email text,\n  password text\n)\nreturns my_public_schema.jwt_token\nas $$\ndeclare\n  account my_private_schema.person_account;\nbegin\n  select a.* into account\n    from my_private_schema.person_account as a\n    where a.email = authenticate.email;\n\n  if account.password_hash = crypt(password, account.password_hash) then\n    return (\n      'person_role',\n      extract(epoch from now() + interval '7 days'),\n      account.person_id,\n      account.is_admin,\n      account.username\n    )::my_public_schema.jwt_token;\n  else\n    return null;\n  end if;\nend;\n$$ language plpgsql strict security definer;\n"})}),"\n",(0,i.jsxs)(n.p,{children:["Which would give you an ",(0,i.jsx)(n.code,{children:"authenticate"})," mutation with which you can extract the\n",(0,i.jsx)(n.code,{children:"jwtToken"})," from the response payload."]}),"\n",(0,i.jsxs)(n.p,{children:["Remember that the resulting token will be verified by whichever middleware you\nwrite (or by the ",(0,i.jsx)(n.code,{children:"lazy-jwt"})," preset if you are still using it). Review the\n",(0,i.jsx)(n.a,{href:"./jwt-specification",children:"PostgreSQL JWT specification"})," to ensure the fields you\nreturn map cleanly onto PostgreSQL session settings."]}),"\n",(0,i.jsx)(n.h2,{id:"using-values-inside-postgresql",children:"Using values inside PostgreSQL"}),"\n",(0,i.jsxs)(n.p,{children:["Whether you authenticate with sessions, JWTs, or another mechanism, PostGraphile\nultimately sets PostgreSQL parameters with the data you place into ",(0,i.jsx)(n.code,{children:"pgSettings"}),".\nThe ",(0,i.jsx)(n.a,{href:"./jwt-specification",children:"PostgreSQL JWT specification"})," documents the exact\n",(0,i.jsx)(n.code,{children:"set_config"})," calls used for JWT claims, but the same principles apply to any\ncustom prefix you choose."]}),"\n",(0,i.jsxs)(n.p,{children:["For example, if you push a user identifier through ",(0,i.jsx)(n.code,{children:"pgSettings"})," then the\ndatabase session might receive commands similar to:"]}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-sql",children:"begin;\nset local myapp.user_id to '2';\n\n-- PERFORM GRAPHQL QUERIES HERE\n\ncommit;\n"})}),"\n",(0,i.jsxs)(n.admonition,{type:"info",children:[(0,i.jsx)(n.p,{children:"To save round-trips, many adaptors perform just one query to set all configs\nvia:"}),(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-sql",children:"select set_config('role', 'app_user', true),\n       set_config('user_id', '2', true),\n       ...;\n"})}),(0,i.jsxs)(n.p,{children:["but showing ",(0,i.jsx)(n.code,{children:"set local"})," is simpler to understand."]})]}),"\n",(0,i.jsxs)(n.p,{children:["You can then access the data via ",(0,i.jsx)(n.code,{children:"current_setting(name, true)"})," (the second\nargument says it is okay for the property to be missing). A helper function such\nas the following can keep row level policies tidy:"]}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-sql",children:"create function current_user_id() returns integer as $$\n  select nullif(current_setting('myapp.user_id', true), '')::integer;\n$$ language sql stable;\n"})}

1),"\n",(0,i.jsx)(n.p,{children:"and you can rely on it inside policies:"}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-sql",children:'create policy update_if_author\n  on comments\n  for update\n  using ("userId" = current_user_id())\n  with check ("userId" = current_user_id());\n'})})]})}function h(e={}){const{wrapper:n}={...(0,r.R)(),...e.components};return n?(0,i.jsx)(n,{...e,children:(0,i.jsx)(d,{...e})}):d(e)}},71184(e,n,t){t.d(n,{R:()=>a,x:()=>o});var s=t(14041);const i={},r=s.createContext(i);function a(e){const n=s.useContext(r);return s.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function o(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(i):e.components||i:a(e.components),s.createElement(r.Provider,{value:n},e.children)}}}]);