Skip to content

Instantly share code, notes, and snippets.

@DoumanAsh
Created July 18, 2025 06:15
Show Gist options
  • Save DoumanAsh/9ea31bdd8680d9399ffa455f8bc5bea1 to your computer and use it in GitHub Desktop.
Save DoumanAsh/9ea31bdd8680d9399ffa455f8bc5bea1 to your computer and use it in GitHub Desktop.
Create read only user once PostgresSQL procedure
CREATE OR REPLACE PROCEDURE create_temp_user(user_id VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_user WHERE usename = user_id) THEN
EXECUTE 'CREATE USER ' || user_id || ' WITH PASSWORD DISABLE';
EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO ' || user_id;
EXECUTE 'GRANT USAGE ON SCHEMA public TO ' || user_id;
EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ' || user_id;
END IF;
END;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment