Re: [OSSTEST PATCH 3/7] Schema: Remove SET OWNER and GRANT/REVOKE from schema/initial.sql

[Ian Campbell <ian.campbell@citrix.com>]

On Thu, 2015-12-10 at 13:51 +0000, Ian Jackson wrote:
> Really, we don't want the initial schema setup to mess about with
> permissions.  Instead, we simply expect to run the creation as the
> correct role user.
> 
> So:
>  - Remove the code in mg-schema-test-database to remove the
>    permission settings from initial.sql;
>  - Instead, run exactly that code on initial.sql and commit the
>    result.
> 
> Signed-off-by: Ian Jackson <Ian.Jackson@eu.citrix.com>

Acked-by: Ian Campbell <ian.campbell@citrix.com>

I suppose the resulting comments in initial.sql are useful even for
illustration (i.e. not much point in removing).

I noticed that owner is not always osstest, I see instances of iwj,
postgres and osstest_ro. I guess iwj is some historical baggage which the
expectation to run as the role user supercedes, but I'm not sure about
osstest_ro or postgres?

> ---
>  mg-schema-test-database |    9 +---
>  schema/initial.sql      |  132 +++++++++++++++++++++++----------------
> --------
>  2 files changed, 67 insertions(+), 74 deletions(-)
> 
> diff --git a/mg-schema-test-database b/mg-schema-test-database
> index 0c4dab7..c68b1d2 100755
> --- a/mg-schema-test-database
> +++ b/mg-schema-test-database
> @@ -386,19 +386,12 @@ END
>  
>  	tables=$(tsort <$t.tablesortlist)
>  
> -	# We don't want to set the permissions
> -	perl <schema/initial.sql >$t.new-schema -pe '
> -		s/^/--/ if
> -			m/^ALTER TABLE .* OWNER TO / ||
> -			m/^GRANT |^REVOKE /
> -	'
> -
>  	#---------- create test db ----------
>  
>  	psql_do <<END
>  		CREATE DATABASE $dbname;
>  END
> -	$(withtest get_psql_cmd) -q -f $t.new-schema
> +	$(withtest get_psql_cmd) -q -f schema/initial.sql
>  
>  	printf ".\n"
>  
> diff --git a/schema/initial.sql b/schema/initial.sql
> index 7bd6c55..4feeeb2 100644
> --- a/schema/initial.sql
> +++ b/schema/initial.sql
> @@ -28,7 +28,7 @@ CREATE TABLE flights (
>  );
>  
>  
> -ALTER TABLE public.flights OWNER TO osstest;
> +--ALTER TABLE public.flights OWNER TO osstest;
>  
>  --
>  -- Name: flights_flight_seq; Type: SEQUENCE; Schema: public; Owner:
> osstest
> @@ -42,7 +42,7 @@ CREATE SEQUENCE flights_flight_seq
>      CACHE 1;
>  
>  
> -ALTER TABLE public.flights_flight_seq OWNER TO osstest;
> +--ALTER TABLE public.flights_flight_seq OWNER TO osstest;
>  
>  --
>  -- Name: flights_flight_seq; Type: SEQUENCE OWNED BY; Schema: public;
> Owner: osstest
> @@ -61,7 +61,7 @@ CREATE TABLE flights_harness_touched (
>  );
>  
>  
> -ALTER TABLE public.flights_harness_touched OWNER TO osstest;
> +--ALTER TABLE public.flights_harness_touched OWNER TO osstest;
>  
>  --
>  -- Name: hostflags; Type: TABLE; Schema: public; Owner: osstest;
> Tablespace: 
> @@ -73,7 +73,7 @@ CREATE TABLE hostflags (
>  );
>  
>  
> -ALTER TABLE public.hostflags OWNER TO osstest;
> +--ALTER TABLE public.hostflags OWNER TO osstest;
>  
>  --
>  -- Name: jobs; Type: TABLE; Schema: public; Owner: osstest; Tablespace: 
> @@ -87,7 +87,7 @@ CREATE TABLE jobs (
>  );
>  
>  
> -ALTER TABLE public.jobs OWNER TO osstest;
> +--ALTER TABLE public.jobs OWNER TO osstest;
>  
>  --
>  -- Name: resource_log_evid_seq; Type: SEQUENCE; Schema: public; Owner:
> iwj
> @@ -101,7 +101,7 @@ CREATE SEQUENCE resource_log_evid_seq
>      CACHE 1;
>  
>  
> -ALTER TABLE public.resource_log_evid_seq OWNER TO iwj;
> +--ALTER TABLE public.resource_log_evid_seq OWNER TO iwj;
>  
>  --
>  -- Name: resource_log; Type: TABLE; Schema: public; Owner: iwj;
> Tablespace: 
> @@ -122,7 +122,7 @@ CREATE TABLE resource_log (
>  );
>  
>  
> -ALTER TABLE public.resource_log OWNER TO iwj;
> +--ALTER TABLE public.resource_log OWNER TO iwj;
>  
>  --
>  -- Name: resource_properties; Type: TABLE; Schema: public; Owner:
> osstest; Tablespace: 
> @@ -136,7 +136,7 @@ CREATE TABLE resource_properties (
>  );
>  
>  
> -ALTER TABLE public.resource_properties OWNER TO osstest;
> +--ALTER TABLE public.resource_properties OWNER TO osstest;
>  
>  --
>  -- Name: resource_sharing; Type: TABLE; Schema: public; Owner: osstest;
> Tablespace: 
> @@ -152,7 +152,7 @@ CREATE TABLE resource_sharing (
>  );
>  
>  
> -ALTER TABLE public.resource_sharing OWNER TO osstest;
> +--ALTER TABLE public.resource_sharing OWNER TO osstest;
>  
>  --
>  -- Name: resources; Type: TABLE; Schema: public; Owner: osstest;
> Tablespace: 
> @@ -168,7 +168,7 @@ CREATE TABLE resources (
>  );
>  
>  
> -ALTER TABLE public.resources OWNER TO osstest;
> +--ALTER TABLE public.resources OWNER TO osstest;
>  
>  --
>  -- Name: runvars; Type: TABLE; Schema: public; Owner: osstest;
> Tablespace: 
> @@ -183,7 +183,7 @@ CREATE TABLE runvars (
>  );
>  
>  
> -ALTER TABLE public.runvars OWNER TO osstest;
> +--ALTER TABLE public.runvars OWNER TO osstest;
>  
>  --
>  -- Name: steps; Type: TABLE; Schema: public; Owner: osstest; Tablespace:
> @@ -201,7 +201,7 @@ CREATE TABLE steps (
>  );
>  
>  
> -ALTER TABLE public.steps OWNER TO osstest;
> +--ALTER TABLE public.steps OWNER TO osstest;
>  
>  --
>  -- Name: tasks_taskid_seq; Type: SEQUENCE; Schema: public; Owner:
> osstest
> @@ -215,7 +215,7 @@ CREATE SEQUENCE tasks_taskid_seq
>      CACHE 1;
>  
>  
> -ALTER TABLE public.tasks_taskid_seq OWNER TO osstest;
> +--ALTER TABLE public.tasks_taskid_seq OWNER TO osstest;
>  
>  --
>  -- Name: tasks; Type: TABLE; Schema: public; Owner: osstest; Tablespace:
> @@ -232,7 +232,7 @@ CREATE TABLE tasks (
>  );
>  
>  
> -ALTER TABLE public.tasks OWNER TO osstest;
> +--ALTER TABLE public.tasks OWNER TO osstest;
>  
>  --
>  -- Name: flight; Type: DEFAULT; Schema: public; Owner: osstest
> @@ -409,130 +409,130 @@ ALTER TABLE ONLY steps
>  -- Name: public; Type: ACL; Schema: -; Owner: postgres
>  --
>  
> -REVOKE ALL ON SCHEMA public FROM PUBLIC;
> -REVOKE ALL ON SCHEMA public FROM postgres;
> -GRANT ALL ON SCHEMA public TO postgres;
> -GRANT ALL ON SCHEMA public TO PUBLIC;
> +--REVOKE ALL ON SCHEMA public FROM PUBLIC;
> +--REVOKE ALL ON SCHEMA public FROM postgres;
> +--GRANT ALL ON SCHEMA public TO postgres;
> +--GRANT ALL ON SCHEMA public TO PUBLIC;
>  
>  
>  --
>  -- Name: flights; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE flights FROM PUBLIC;
> -REVOKE ALL ON TABLE flights FROM osstest;
> -GRANT ALL ON TABLE flights TO osstest;
> -GRANT SELECT ON TABLE flights TO osstest_ro;
> +--REVOKE ALL ON TABLE flights FROM PUBLIC;
> +--REVOKE ALL ON TABLE flights FROM osstest;
> +--GRANT ALL ON TABLE flights TO osstest;
> +--GRANT SELECT ON TABLE flights TO osstest_ro;
>  
>  
>  --
>  -- Name: flights_flight_seq; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON SEQUENCE flights_flight_seq FROM PUBLIC;
> -REVOKE ALL ON SEQUENCE flights_flight_seq FROM osstest;
> -GRANT ALL ON SEQUENCE flights_flight_seq TO osstest;
> -GRANT SELECT ON SEQUENCE flights_flight_seq TO osstest_ro;
> +--REVOKE ALL ON SEQUENCE flights_flight_seq FROM PUBLIC;
> +--REVOKE ALL ON SEQUENCE flights_flight_seq FROM osstest;
> +--GRANT ALL ON SEQUENCE flights_flight_seq TO osstest;
> +--GRANT SELECT ON SEQUENCE flights_flight_seq TO osstest_ro;
>  
>  
>  --
>  -- Name: flights_harness_touched; Type: ACL; Schema: public; Owner:
> osstest
>  --
>  
> -REVOKE ALL ON TABLE flights_harness_touched FROM PUBLIC;
> -REVOKE ALL ON TABLE flights_harness_touched FROM osstest;
> -GRANT ALL ON TABLE flights_harness_touched TO osstest;
> -GRANT SELECT ON TABLE flights_harness_touched TO osstest_ro;
> +--REVOKE ALL ON TABLE flights_harness_touched FROM PUBLIC;
> +--REVOKE ALL ON TABLE flights_harness_touched FROM osstest;
> +--GRANT ALL ON TABLE flights_harness_touched TO osstest;
> +--GRANT SELECT ON TABLE flights_harness_touched TO osstest_ro;
>  
>  
>  --
>  -- Name: hostflags; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE hostflags FROM PUBLIC;
> -REVOKE ALL ON TABLE hostflags FROM osstest;
> -GRANT ALL ON TABLE hostflags TO osstest;
> -GRANT SELECT ON TABLE hostflags TO osstest_ro;
> +--REVOKE ALL ON TABLE hostflags FROM PUBLIC;
> +--REVOKE ALL ON TABLE hostflags FROM osstest;
> +--GRANT ALL ON TABLE hostflags TO osstest;
> +--GRANT SELECT ON TABLE hostflags TO osstest_ro;
>  
>  
>  --
>  -- Name: jobs; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE jobs FROM PUBLIC;
> -REVOKE ALL ON TABLE jobs FROM osstest;
> -GRANT ALL ON TABLE jobs TO osstest;
> -GRANT SELECT ON TABLE jobs TO osstest_ro;
> +--REVOKE ALL ON TABLE jobs FROM PUBLIC;
> +--REVOKE ALL ON TABLE jobs FROM osstest;
> +--GRANT ALL ON TABLE jobs TO osstest;
> +--GRANT SELECT ON TABLE jobs TO osstest_ro;
>  
>  
>  --
>  -- Name: resource_properties; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE resource_properties FROM PUBLIC;
> -REVOKE ALL ON TABLE resource_properties FROM osstest;
> -GRANT ALL ON TABLE resource_properties TO osstest;
> -GRANT SELECT ON TABLE resource_properties TO osstest_ro;
> +--REVOKE ALL ON TABLE resource_properties FROM PUBLIC;
> +--REVOKE ALL ON TABLE resource_properties FROM osstest;
> +--GRANT ALL ON TABLE resource_properties TO osstest;
> +--GRANT SELECT ON TABLE resource_properties TO osstest_ro;
>  
>  
>  --
>  -- Name: resource_sharing; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE resource_sharing FROM PUBLIC;
> -REVOKE ALL ON TABLE resource_sharing FROM osstest;
> -GRANT ALL ON TABLE resource_sharing TO osstest;
> -GRANT SELECT ON TABLE resource_sharing TO osstest_ro;
> +--REVOKE ALL ON TABLE resource_sharing FROM PUBLIC;
> +--REVOKE ALL ON TABLE resource_sharing FROM osstest;
> +--GRANT ALL ON TABLE resource_sharing TO osstest;
> +--GRANT SELECT ON TABLE resource_sharing TO osstest_ro;
>  
>  
>  --
>  -- Name: resources; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE resources FROM PUBLIC;
> -REVOKE ALL ON TABLE resources FROM osstest;
> -GRANT ALL ON TABLE resources TO osstest;
> -GRANT SELECT ON TABLE resources TO osstest_ro;
> +--REVOKE ALL ON TABLE resources FROM PUBLIC;
> +--REVOKE ALL ON TABLE resources FROM osstest;
> +--GRANT ALL ON TABLE resources TO osstest;
> +--GRANT SELECT ON TABLE resources TO osstest_ro;
>  
>  
>  --
>  -- Name: runvars; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE runvars FROM PUBLIC;
> -REVOKE ALL ON TABLE runvars FROM osstest;
> -GRANT ALL ON TABLE runvars TO osstest;
> -GRANT SELECT ON TABLE runvars TO osstest_ro;
> +--REVOKE ALL ON TABLE runvars FROM PUBLIC;
> +--REVOKE ALL ON TABLE runvars FROM osstest;
> +--GRANT ALL ON TABLE runvars TO osstest;
> +--GRANT SELECT ON TABLE runvars TO osstest_ro;
>  
>  
>  --
>  -- Name: steps; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE steps FROM PUBLIC;
> -REVOKE ALL ON TABLE steps FROM osstest;
> -GRANT ALL ON TABLE steps TO osstest;
> -GRANT SELECT ON TABLE steps TO osstest_ro;
> +--REVOKE ALL ON TABLE steps FROM PUBLIC;
> +--REVOKE ALL ON TABLE steps FROM osstest;
> +--GRANT ALL ON TABLE steps TO osstest;
> +--GRANT SELECT ON TABLE steps TO osstest_ro;
>  
>  
>  --
>  -- Name: tasks_taskid_seq; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON SEQUENCE tasks_taskid_seq FROM PUBLIC;
> -REVOKE ALL ON SEQUENCE tasks_taskid_seq FROM osstest;
> -GRANT ALL ON SEQUENCE tasks_taskid_seq TO osstest;
> -GRANT SELECT ON SEQUENCE tasks_taskid_seq TO osstest_ro;
> +--REVOKE ALL ON SEQUENCE tasks_taskid_seq FROM PUBLIC;
> +--REVOKE ALL ON SEQUENCE tasks_taskid_seq FROM osstest;
> +--GRANT ALL ON SEQUENCE tasks_taskid_seq TO osstest;
> +--GRANT SELECT ON SEQUENCE tasks_taskid_seq TO osstest_ro;
>  
>  
>  --
>  -- Name: tasks; Type: ACL; Schema: public; Owner: osstest
>  --
>  
> -REVOKE ALL ON TABLE tasks FROM PUBLIC;
> -REVOKE ALL ON TABLE tasks FROM osstest;
> -GRANT ALL ON TABLE tasks TO osstest;
> -GRANT SELECT ON TABLE tasks TO osstest_ro;
> +--REVOKE ALL ON TABLE tasks FROM PUBLIC;
> +--REVOKE ALL ON TABLE tasks FROM osstest;
> +--GRANT ALL ON TABLE tasks TO osstest;
> +--GRANT SELECT ON TABLE tasks TO osstest_ro;
>  
>  
>  --

_______________________________________________
Xen-devel mailing list
Xen-devel@lists.xen.org
http://lists.xen.org/xen-devel