Re: pgsql: Regression tests for security_barrier views.

On Fri, Jan 20, 2012 at 2:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <rhaas@postgresql.org> writes:
>> Regression tests for security_barrier views.
>
> This patch broke the regression tests for any scenario where you run the
> tests multiple times without an initdb, because it creates a role and
> fails to drop it.

Argh, sorry.

> More generally, please do not use role names as generic as "alice" in
> regression tests, as that could have disastrous effects if someone
> were to run the regression tests in a live installation.  We have a
> convention of using "regressuser1" and so forth.

A quick grep suggests that we have quite a number of violations of
that convention, some of them dating back more than 5 years:

src/test/regress/expected/cluster.out:CREATE USER clstr_user;
src/test/regress/expected/collate.linux.utf8.out:CREATE ROLE regress_test_role;
src/test/regress/expected/conversion.out:CREATE USER
conversion_test_user WITH NOCREATEDB NOCREATEUSER;
src/test/regress/expected/dependency.out:CREATE USER regression_user;
src/test/regress/expected/dependency.out:CREATE USER regression_user2;
src/test/regress/expected/dependency.out:CREATE USER regression_user3;
src/test/regress/expected/dependency.out:CREATE GROUP regression_group;
src/test/regress/expected/dependency.out:CREATE USER regression_user0;
src/test/regress/expected/dependency.out:CREATE USER regression_user1;
src/test/regress/expected/dependency.out:CREATE USER regression_user2;
src/test/regress/expected/drop_if_exists.out:CREATE USER tu1;
src/test/regress/expected/drop_if_exists.out:CREATE ROLE tr1;
src/test/regress/expected/drop_if_exists.out:CREATE GROUP tg1;
src/test/regress/expected/foreign_data.out:CREATE ROLE
foreign_data_user LOGIN SUPERUSER;
src/test/regress/expected/foreign_data.out:CREATE ROLE regress_test_role;
src/test/regress/expected/foreign_data.out:CREATE ROLE regress_test_role2;
src/test/regress/expected/foreign_data.out:CREATE ROLE
regress_test_role_super SUPERUSER;
src/test/regress/expected/foreign_data.out:CREATE ROLE regress_test_indirect;
src/test/regress/expected/foreign_data.out:CREATE ROLE unprivileged_role;
src/test/regress/expected/guc.out:CREATE ROLE temp_reset_user;
src/test/regress/expected/privileges.out:CREATE USER regressuser1;
src/test/regress/expected/privileges.out:CREATE USER regressuser2;
src/test/regress/expected/privileges.out:CREATE USER regressuser3;
src/test/regress/expected/privileges.out:CREATE USER regressuser4;
src/test/regress/expected/privileges.out:CREATE USER regressuser5;
src/test/regress/expected/privileges.out:CREATE USER regressuser5;    -- duplicate
src/test/regress/expected/privileges.out:CREATE GROUP regressgroup1;
src/test/regress/expected/privileges.out:CREATE GROUP regressgroup2
WITH USER regressuser1, regressuser2;
src/test/regress/expected/select_into.out:CREATE USER selinto_user;
src/test/regress/expected/select_views.out:CREATE USER alice;
src/test/regress/expected/select_views_1.out:CREATE USER alice;
src/test/regress/expected/sequence.out:CREATE USER seq_user;
src/test/regress/expected/sequence_1.out:CREATE USER seq_user;
src/test/regress/input/security_label.source:CREATE USER
seclabel_user1 WITH CREATEROLE;
src/test/regress/input/security_label.source:CREATE USER seclabel_user2;
src/test/regress/output/security_label.source:CREATE USER
seclabel_user1 WITH CREATEROLE;
src/test/regress/output/security_label.source:CREATE USER seclabel_user2;
src/test/regress/pg_regress.c:    psql_command("postgres", "CREATE ROLE
\"%s\" WITH LOGIN", rolename);
src/test/regress/sql/cluster.sql:CREATE USER clstr_user;
src/test/regress/sql/collate.linux.utf8.sql:CREATE ROLE regress_test_role;
src/test/regress/sql/conversion.sql:CREATE USER conversion_test_user
WITH NOCREATEDB NOCREATEUSER;
src/test/regress/sql/dependency.sql:CREATE USER regression_user;
src/test/regress/sql/dependency.sql:CREATE USER regression_user2;
src/test/regress/sql/dependency.sql:CREATE USER regression_user3;
src/test/regress/sql/dependency.sql:CREATE GROUP regression_group;
src/test/regress/sql/dependency.sql:CREATE USER regression_user0;
src/test/regress/sql/dependency.sql:CREATE USER regression_user1;
src/test/regress/sql/dependency.sql:CREATE USER regression_user2;
src/test/regress/sql/drop_if_exists.sql:CREATE USER tu1;
src/test/regress/sql/drop_if_exists.sql:CREATE ROLE tr1;
src/test/regress/sql/drop_if_exists.sql:CREATE GROUP tg1;
src/test/regress/sql/foreign_data.sql:CREATE ROLE foreign_data_user
LOGIN SUPERUSER;
src/test/regress/sql/foreign_data.sql:CREATE ROLE regress_test_role;
src/test/regress/sql/foreign_data.sql:CREATE ROLE regress_test_role2;
src/test/regress/sql/foreign_data.sql:CREATE ROLE
regress_test_role_super SUPERUSER;
src/test/regress/sql/foreign_data.sql:CREATE ROLE regress_test_indirect;
src/test/regress/sql/foreign_data.sql:CREATE ROLE unprivileged_role;
src/test/regress/sql/guc.sql:CREATE ROLE temp_reset_user;
src/test/regress/sql/privileges.sql:CREATE USER regressuser1;
src/test/regress/sql/privileges.sql:CREATE USER regressuser2;
src/test/regress/sql/privileges.sql:CREATE USER regressuser3;
src/test/regress/sql/privileges.sql:CREATE USER regressuser4;
src/test/regress/sql/privileges.sql:CREATE USER regressuser5;
src/test/regress/sql/privileges.sql:CREATE USER regressuser5;    -- duplicate
src/test/regress/sql/privileges.sql:CREATE GROUP regressgroup1;
src/test/regress/sql/privileges.sql:CREATE GROUP regressgroup2 WITH
USER regressuser1, regressuser2;
src/test/regress/sql/select_into.sql:CREATE USER selinto_user;
src/test/regress/sql/select_views.sql:CREATE USER alice;
src/test/regress/sql/sequence.sql:CREATE USER seq_user;

I can't help thinking we could probably manage an adequate set of
regression tests that involves a somewhat fewer CREATE USER/ROLE/GROUP
commands than the above list.  Surely some roles could be used by more
than one test, and dropped at the end?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company