LOAD 'chdb_hook';
CREATE TABLE secrets (
    id     INT  PRIMARY KEY,
    name   TEXT NOT NULL,
    secret TEXT NOT NULL
);
INSERT INTO secrets VALUES (1, 'alice', 'hunter2'), (2, 'bob', 'letmein');
CREATE TABLE names (
    id   INT  PRIMARY KEY,
    name TEXT NOT NULL,
    who  TEXT NOT NULL DEFAULT current_user
);
-- Clean up after any previous failed run.
SET client_min_messages = error;
DROP ROLE IF EXISTS chdb_none;
DROP ROLE IF EXISTS chdb_reader;
RESET client_min_messages;
CREATE ROLE chdb_none;
CREATE ROLE chdb_reader;
GRANT SELECT (id, name) ON secrets TO chdb_reader;
GRANT INSERT (id, name) ON names TO chdb_reader;
-- Disable quiet to emit COPY numbers
\set QUIET false
-- Copy through /tmp, which the server can always read and write.
\set names_out file:///tmp/permissions.tmp/names.tsv
-- Copying a server file requires the same role membership as a Postgres COPY.
SET ROLE chdb_reader;
SET
COPY secrets (id, name) TO :'names_out';
ERROR:  chdb: permission denied to COPY to a file
DETAIL:  Only roles with privileges of the "pg_write_server_files" role may COPY to a file.
COPY names (id, name) FROM :'names_out';
ERROR:  chdb: permission denied to COPY from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
RESET ROLE;
RESET
GRANT pg_read_server_files, pg_write_server_files TO chdb_none, chdb_reader;
GRANT ROLE
-- A role without privileges cannot copy the relation.
SET ROLE chdb_none;
SET
COPY secrets TO :'names_out';
ERROR:  permission denied for table secrets
COPY secrets FROM :'names_out';
ERROR:  permission denied for table secrets
-- Column privileges cover only the copied columns.
SET ROLE chdb_reader;
SET
COPY secrets TO :'names_out';
ERROR:  permission denied for table secrets
COPY secrets (id, secret) TO :'names_out';
ERROR:  permission denied for table secrets
COPY secrets (id, name) TO :'names_out';
COPY 2
-- The copy runs as the role that ran the COPY.
COPY names (id, name) FROM :'names_out';
COPY 2
RESET ROLE;
RESET
SELECT * FROM names ORDER BY id;
 id | name  |     who     
----+-------+-------------
  1 | alice | chdb_reader
  2 | bob   | chdb_reader
(2 rows)

-- COPY TO PROGRAM remains Postgres's business.
SET ROLE chdb_reader;
SET
COPY names TO PROGRAM 'file:///bin/cat';
ERROR:  permission denied to COPY to or from an external program
DETAIL:  Only roles with privileges of the "pg_execute_server_program" role may COPY to or from an external program.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
RESET ROLE;
RESET
-- chDB copies every row, so row-level security cannot be applied.
ALTER TABLE secrets ENABLE ROW LEVEL SECURITY;
ALTER TABLE
CREATE POLICY own_secret ON secrets FOR SELECT TO chdb_reader USING (name = current_user);
CREATE POLICY
SET ROLE chdb_reader;
SET
COPY secrets (id, name) TO :'names_out';
ERROR:  chdb: COPY TO not supported with row-level security
DETAIL:  Row-level security policies apply to relation "secrets" for this role.
SET row_security = off;
SET
COPY secrets (id, name) TO :'names_out';
ERROR:  query would be affected by row-level security policy for table "secrets"
RESET row_security;
RESET
RESET ROLE;
RESET
-- The owner bypasses row-level security, so exports every row.
COPY secrets TO :'names_out';
COPY 2
CREATE TABLE exported (LIKE secrets);
CREATE TABLE
COPY exported FROM :'names_out';
COPY 2
SELECT * FROM exported ORDER BY id;
 id | name  | secret  
----+-------+---------
  1 | alice | hunter2
  2 | bob   | letmein
(2 rows)

-- COPY FROM writes, so a read-only transaction rejects it.
BEGIN READ ONLY;
BEGIN
COPY names FROM :'names_out';
ERROR:  cannot execute COPY FROM in a read-only transaction
ROLLBACK;
ROLLBACK
-- The copy runs in this session, so it sees its temporary relations.
CREATE TEMP TABLE tmp_names (id INT, name TEXT);
CREATE TABLE
COPY names (id, name) TO :'names_out';
COPY 2
COPY tmp_names FROM :'names_out';
COPY 2
SELECT * FROM tmp_names ORDER BY id;
 id | name  
----+-------
  1 | alice
  2 | bob
(2 rows)

-- The copy joins this transaction, so a rollback takes its rows with it.
BEGIN;
BEGIN
COPY tmp_names FROM :'names_out';
COPY 2
SELECT count(*) FROM tmp_names;
 count 
-------
     4
(1 row)

ROLLBACK;
ROLLBACK
SELECT count(*) FROM tmp_names;
 count 
-------
     2
(1 row)

DROP TABLE secrets, names, exported;
DROP TABLE
DROP ROLE chdb_none, chdb_reader;
DROP ROLE
-- Files belong to the server user, so ignore failure to remove them.
\! rm -rf /tmp/permissions.tmp 2>/dev/null