0

Given the following SQL script for PostgreSQL...

CREATE TABLE IF NOT EXISTS my_table (

    id          UUID PRIMARY KEY,
    name        VARCHAR(255) NOT NULL, 
    create_time TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

    acl_read    UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL,
    acl_write   UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL
);

CREATE INDEX ON my_table USING gin(acl_read);
CREATE INDEX ON my_table USING gin(acl_write);

CREATE POLICY my_table_policy ON my_table
USING (
    acl_read @> array[current_setting('subject')]::uuid[]
    OR
    acl_write @> array[current_setting('subject')]::uuid[]
)
WITH CHECK (
    acl_write @> array[current_setting('subject')]::uuid[]
);

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

... I'd like to move the POLICY section into another file and pass the name of the table to create the policy for:

CREATE TABLE IF NOT EXISTS my_table (

    id          UUID PRIMARY KEY,
    name        VARCHAR(255) NOT NULL, 
    create_time TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

    acl_read    UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL,
    acl_write   UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL
);

\i policy.sql 'my_table'

Here is policy.sql, which should take the name of the table as the input parameter:

CREATE INDEX ON PARAM_VALUE USING gin(acl_read);
CREATE INDEX ON PARAM_VALUE USING gin(acl_write);

CREATE POLICY my_table_policy ON PARAM_VALUE
USING (
    acl_read @> array[current_setting('subject')]::uuid[]
    OR
    acl_write @> array[current_setting('subject')]::uuid[]
)
WITH CHECK (
    acl_write @> array[current_setting('subject')]::uuid[]
);

ALTER TABLE PARAM_VALUE ENABLE ROW LEVEL SECURITY;

So the question is: how do I pass the table name to policy.sql? And then how do I get the value of the parameter (PARAM_VALUE) in policy.sql?

j3d
  • 9,492
  • 22
  • 88
  • 172
  • I'm not a super heavy PSQL user, but can you use metadata? Like, create a temp table in one script and query that from another for your parameter? – Mark Moretto Jun 07 '20 at 14:29
  • I'm using these script to manage database evolutions... so they are used to setup the db when i stautup the project. – j3d Jun 07 '20 at 14:32
  • How do you execute the script(s)? – sticky bit Jun 07 '20 at 14:34
  • I run the scripts in a docker container, where I run a tool called `go migrate`. This tool executes the scripts in the right order (some scripts might just contain delta changes). – j3d Jun 07 '20 at 15:00

0 Answers0