47 lines
2.7 KiB
SQL
47 lines
2.7 KiB
SQL
-- SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
|
|
-- The three roles a linked managed Postgres exposes, created the way the platform creates them
|
|
-- (docs/architecture/managed-postgres-roles.md, data-services DS21). Development only: on the
|
|
-- platform these statements are the provider's, and the passwords are derived, not written down.
|
|
--
|
|
-- Owner appdev_owner owns the database and schema public; the migrate job's role
|
|
-- Runtime appdev_app owns nothing, so row-level security binds it; web and worker
|
|
-- Reader appdev_reader holds no table privilege until this app's own migrations grant one
|
|
|
|
CREATE ROLE appdev_owner LOGIN PASSWORD 'appdev_owner' NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS NOINHERIT NOREPLICATION;
|
|
CREATE ROLE appdev_app LOGIN PASSWORD 'appdev_app' NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS NOINHERIT NOREPLICATION;
|
|
CREATE ROLE appdev_reader LOGIN PASSWORD 'appdev_reader' NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS NOINHERIT NOREPLICATION;
|
|
|
|
ALTER DATABASE "appdev" OWNER TO "appdev_owner";
|
|
REVOKE ALL ON DATABASE "appdev" FROM PUBLIC;
|
|
GRANT CONNECT, TEMPORARY ON DATABASE "appdev" TO "appdev_owner";
|
|
GRANT CONNECT ON DATABASE "appdev" TO "appdev_app";
|
|
GRANT CONNECT ON DATABASE "appdev" TO "appdev_reader";
|
|
|
|
\connect appdev
|
|
|
|
-- pgvector is not a trusted extension, so the owner role cannot create it; the platform's provider
|
|
-- creates it as the administrator where the server offers it, and so does this script. The
|
|
-- knowledge module's `create extension if not exists vector` is then a no-op.
|
|
DO $$ BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name = 'vector') THEN
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER SCHEMA public OWNER TO "appdev_owner";
|
|
REVOKE ALL ON SCHEMA public FROM PUBLIC;
|
|
GRANT ALL ON SCHEMA public TO "appdev_owner";
|
|
GRANT USAGE ON SCHEMA public TO "appdev_app";
|
|
GRANT USAGE ON SCHEMA public TO "appdev_reader";
|
|
|
|
-- No IN SCHEMA clause: every table the owner creates, in any schema a migration adds, is granted
|
|
-- to the runtime role as it is created. There is no grant pass left to run after a migration.
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "appdev_owner" GRANT USAGE ON SCHEMAS TO "appdev_app";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "appdev_owner" GRANT USAGE ON SCHEMAS TO "appdev_reader";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "appdev_owner" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "appdev_app";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "appdev_owner" GRANT USAGE, SELECT ON SEQUENCES TO "appdev_app";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "appdev_owner" GRANT EXECUTE ON FUNCTIONS TO "appdev_app";
|
|
|
|
-- The reader gets nothing here. What it may read is this app's decision, and it is a GRANT in
|
|
-- migrations/postgres/0002_reader_grants (apps/worker/src/migrations.ts).
|