Обсуждение: Regression tests vs existing users in an installation
We've talked before about how the regression tests should be circumspect
about what role names they create/drop, so as to avoid possibly blowing
up an installation's existing users during "make installcheck". In
particular I believe there was consensus that such names should begin
with, or at least include, "regress". I got around today to instrumenting
CreateRole to see what names we were actually creating, and was quite
depressed as to how thoroughly that guideline is being ignored (see
attached).
I propose to go through the regression tests and fix this (in HEAD only).
However, there's one place where it's not so easy to just substitute a
different name, because rolenames.sql is intentionally doing this:
CREATE ROLE "Public";
CREATE ROLE "None";
CREATE ROLE "current_user";
CREATE ROLE "session_user";
CREATE ROLE "user";
in order to test whether we properly distinguish role-related keywords
from quoted identifiers. Obviously, modifying these would defeat the
point of the test.
One could certainly argue that these are safe enough because nobody would
ever create real roles by those names anyway. I'm not very comfortable
with that though; if we believe that, why did we go to the trouble of
making sure these cases work?
What I'm inclined to do with this is to reduce the test to be something
like
BEGIN;
CREATE ROLE "Public";
CREATE ROLE "None";
CREATE ROLE "current_user";
CREATE ROLE "session_user";
CREATE ROLE "user";
ROLLBACK;
with maybe a couple of ALTERs and GRANTs inside the transaction to verify
that the names can be referenced as well as created. This would be safe
against modifying any conflicting existing users; the only bad consequence
would be a phony failure of the test.
I thought about trying to preserve all the existing test cases while still
keeping these roles inside a transaction, by inserting savepoints around
the intentional failures. But there are enough intentional failures in
rolenames.sql that that would be really tedious. The existing test cases
seem enormously duplicative to me anyway, so I think a fairly short
transaction with a few tests would be sufficient to cover this territory.
A more aggressive answer would be to decide we don't need these test cases
at all and drop them. An advantage of that is that then we could
configure some buildfarm animal to fail the next time somebody ignores
the "test role names should contain 'regress'" rule.
Comments?
regards, tom lane
LOG: created role tablespace_testuser1
LOG: created role tablespace_testuser2
LOG: created role regtestrole
LOG: created role regress_rol_op1
LOG: created role regress_rol_op3
LOG: created role regress_rol_op4
LOG: created role regress_rol_op5
LOG: created role regress_rol_op6
LOG: created role regression_reindexuser
LOG: created role regtest_unpriv_user
LOG: created role test_def_superuser
LOG: created role test_superuser
LOG: created role Public
LOG: created role None
LOG: created role current_user
LOG: created role session_user
LOG: created role user
LOG: created role testrol0
LOG: created role testrolx
LOG: created role testrol2
LOG: created role testrol1
LOG: created role test_def_inherit
LOG: created role test_inherit
LOG: created role test_def_createrole
LOG: created role test_createrole
LOG: created role test_def_createdb
LOG: created role test_createdb
LOG: created role test_def_role_canlogin
LOG: created role test_role_canlogin
LOG: created role test_def_user_canlogin
LOG: created role test_user_canlogin
LOG: created role test_def_replication
LOG: created role test_replication
LOG: created role tu1
LOG: created role tr1
LOG: created role tg1
LOG: created role test_def_bypassrls
LOG: created role test_bypassrls
LOG: created role view_user1
LOG: created role view_user2
LOG: created role selinto_user
LOG: created role regtest_addr_user
LOG: created role regress_rls_alice
LOG: created role regress_rls_bob
LOG: created role regress_rls_carol
LOG: created role regress_rls_exempt_user
LOG: created role regress_rls_group1
LOG: created role regress_rls_group2
LOG: created role regress_rol_lock1
LOG: created role regressuser1
LOG: created role regressuser2
LOG: created role regressuser3
LOG: created role regressuser4
LOG: created role regressuser5
LOG: created role regressgroup1
LOG: created role regressgroup2
LOG: created role seclabel_user1
LOG: created role seclabel_user2
LOG: created role schemauser1
LOG: created role schemauser2
LOG: renamed role schemauser2 to schemauser_renamed
LOG: created role locktable_user
LOG: created role regress_rls_eve
LOG: created role regress_rls_frank
LOG: created role regress_rls_dob_role1
LOG: created role regress_rls_dob_role2
LOG: created role regress_user_mvtest
LOG: created role regtest_alter_user3
LOG: created role regtest_alter_user2
LOG: created role regtest_alter_user1
LOG: created role regtest_alter_user
LOG: created role regtest_alter_user5
LOG: created role regtest_alter_user6
LOG: created role regression_user
LOG: created role regression_user2
LOG: created role regression_user3
LOG: created role regression_group
LOG: created role foreign_data_user
LOG: created role regress_test_role
LOG: created role regress_test_role2
LOG: created role regress_test_role_super
LOG: created role regress_test_indirect
LOG: created role unprivileged_role
LOG: created role regression_user0
LOG: created role regression_user1
LOG: created role regression_user2
LOG: created role temp_reset_user
LOG: created role clstr_user
LOG: created role regress_alice
LOG: created role conversion_test_user
LOG: created role regresslo
LOG: created role seq_user
LOG: created role regression_bob
LOG: created role dblink_regression_test
LOG: created role file_fdw_superuser
LOG: created role file_fdw_user
LOG: created role no_priv_user
LOG: created role regress_view_owner
Tom Lane wrote: > We've talked before about how the regression tests should be circumspect > about what role names they create/drop, so as to avoid possibly blowing > up an installation's existing users during "make installcheck". In > particular I believe there was consensus that such names should begin > with, or at least include, "regress". I got around today to instrumenting > CreateRole to see what names we were actually creating, and was quite > depressed as to how thoroughly that guideline is being ignored (see > attached). > > I propose to go through the regression tests and fix this (in HEAD only). I would propose that we have one test run near the beginning or right at the beginning of the serial schedule that sets up a small number of roles to cover most of the needs of every other test, so that most such other tests do not need to create any roles at all. (Of course, there would be a few cases where this would defeat the purpose of the test because creating or dropping the role is precisely what is being created; those cases would have additional roles, with the proposed prefix.) So currently we have 97 roles? Probably we can get away with a dozen or so, maybe even less than that. > What I'm inclined to do with this is to reduce the test to be something > like > > BEGIN; > CREATE ROLE "Public"; > CREATE ROLE "None"; > CREATE ROLE "current_user"; > CREATE ROLE "session_user"; > CREATE ROLE "user"; > ROLLBACK; > > with maybe a couple of ALTERs and GRANTs inside the transaction to verify > that the names can be referenced as well as created. This would be safe > against modifying any conflicting existing users; the only bad consequence > would be a phony failure of the test. > > I thought about trying to preserve all the existing test cases while still > keeping these roles inside a transaction, by inserting savepoints around > the intentional failures. But there are enough intentional failures in > rolenames.sql that that would be really tedious. The existing test cases > seem enormously duplicative to me anyway, so I think a fairly short > transaction with a few tests would be sufficient to cover this territory. > A more aggressive answer would be to decide we don't need these test cases > at all and drop them. Hmm ... I think a blanket removal would go against generally accepted principle that our tests need to cover more functionality. Maybe we did go overboard on that one and the rolled-back creation is enough test for that functionality. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sat, Jul 16, 2016 at 7:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > We've talked before about how the regression tests should be circumspect > about what role names they create/drop, so as to avoid possibly blowing > up an installation's existing users during "make installcheck". In > particular I believe there was consensus that such names should begin > with, or at least include, "regress". I got around today to instrumenting > CreateRole to see what names we were actually creating, and was quite > depressed as to how thoroughly that guideline is being ignored (see > attached). Thanks for doing this. > A more aggressive answer would be to decide we don't need these test cases > at all and drop them. An advantage of that is that then we could > configure some buildfarm animal to fail the next time somebody ignores > the "test role names should contain 'regress'" rule. I am -1 for dropping the tests. We could just have a CFLAGS that adds an elog(ERROR) in CreateRole and checks that the created role has a wanted prefix, or have a plugin that uses the utility hook to do this filtering. -- Michael
<p dir="ltr"><br /> On 16 Jul 2016 12:59 pm, "Michael Paquier" <<a href="mailto:michael.paquier@gmail.com">michael.paquier@gmail.com</a>>wrote:<br /> ><p dir="ltr">> Thanks for doingthis.<p dir="ltr">+1<p dir="ltr">Though I might highlight this as the kind of issue that a bug tracker would help avoidfalling through the cracks and make visible to newcomers.<br /><p dir="ltr">> I am -1 for dropping the tests. Wecould just have a CFLAGS that adds<br /> > an elog(ERROR) in CreateRole and checks that the created role has a<br />> wanted prefix, or have a plugin that uses the utility hook to do this<br /> > filtering.<p dir="ltr">If we makea hidden regression_test_safety GUC then we could have pg_regress enable it and have these specific tests disable itexplicitly with comments on why it's safe.<p dir="ltr">It might even be handy for other people writing application regressiontests depending on what other things it blocked.<p dir="ltr">A hook might even be possible to use the same way.pg_regress would have to build and install a .so which might be tricky.
Tom, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > We've talked before about how the regression tests should be circumspect > about what role names they create/drop, so as to avoid possibly blowing > up an installation's existing users during "make installcheck". In > particular I believe there was consensus that such names should begin > with, or at least include, "regress". I got around today to instrumenting > CreateRole to see what names we were actually creating, and was quite > depressed as to how thoroughly that guideline is being ignored (see > attached). I had started in on this but hadn't made as much progress as I had hoped, so I'm glad to see that you're taking a look at it. > I propose to go through the regression tests and fix this (in HEAD only). > However, there's one place where it's not so easy to just substitute a > different name, because rolenames.sql is intentionally doing this: > > CREATE ROLE "Public"; > CREATE ROLE "None"; > CREATE ROLE "current_user"; > CREATE ROLE "session_user"; > CREATE ROLE "user"; > > in order to test whether we properly distinguish role-related keywords > from quoted identifiers. Obviously, modifying these would defeat the > point of the test. > > One could certainly argue that these are safe enough because nobody would > ever create real roles by those names anyway. I'm not very comfortable > with that though; if we believe that, why did we go to the trouble of > making sure these cases work? Sadly, it's not quite so simple: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=831234 Note that Christoph went ahead and closed out the bug report as it was just an issue in the regression test and not unexpected behavior, but if we're going to do something in this area then we may wish to consider fixing the case that caused that bug report. > What I'm inclined to do with this is to reduce the test to be something > like > > BEGIN; > CREATE ROLE "Public"; > CREATE ROLE "None"; > CREATE ROLE "current_user"; > CREATE ROLE "session_user"; > CREATE ROLE "user"; > ROLLBACK; > > with maybe a couple of ALTERs and GRANTs inside the transaction to verify > that the names can be referenced as well as created. This would be safe > against modifying any conflicting existing users; the only bad consequence > would be a phony failure of the test. Unfortunately, the above wouldn't fix the case where someone attempts to run the regression tests as a Unix user named "user". One suggestion discussed on the bug report was to include different result files, but that does seem pretty special-cased and overkill, though if the overall set of tests in rolenames.sql is reduced then perhaps it isn't as much of an issue. Then again, how many different result files would be needed to cover all cases? Seems like there could be quite a few, though, with this specific case, it looks like we'd at least only have to deal with one for each role which is allowed to exist already (such as "user"), without any multiplicative factors (can't run the regression test as more than one Unix user at a time). > A more aggressive answer would be to decide we don't need these test cases > at all and drop them. An advantage of that is that then we could > configure some buildfarm animal to fail the next time somebody ignores > the "test role names should contain 'regress'" rule. I'd really like to have more test coverage rather than less, so I'd find this a bit hard to swallow, but it might still be better than the alternatives. Thanks! Stephen
Stephen Frost <sfrost@snowman.net> writes: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> One could certainly argue that these are safe enough because nobody would >> ever create real roles by those names anyway. I'm not very comfortable >> with that though; if we believe that, why did we go to the trouble of >> making sure these cases work? > Sadly, it's not quite so simple: > https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=831234 Hah. Well, I have zero interest in supporting "user" as the name of the bootstrap superuser. Even if that seemed like a good idea, why not also current_user, session_user, Public, or any other name we might want to use in the tests? The variant-output-files answer definitely doesn't scale. What seems a more useful approach is for packagers to not allow the O/S username to affect the results of "make check". initdb already has the --username switch to override its choice of the bootstrap superuser name, and pg_regress has a --user switch, so in principle it should be possible for a package to ensure that its build tests are run with the standard superuser name rather than something dependent on the environment. I'm not sure how *easy* it is, mind you. We might want to add some Makefile plumbing to make it easier to do that. But that's not what I'm on about today ... >> A more aggressive answer would be to decide we don't need these test cases >> at all and drop them. An advantage of that is that then we could >> configure some buildfarm animal to fail the next time somebody ignores >> the "test role names should contain 'regress'" rule. > I'd really like to have more test coverage rather than less, so I'd find > this a bit hard to swallow, but it might still be better than the > alternatives. As Greg mentioned, we could improve things if we were willing to invent something like a "regression_test_mode" GUC. The rough sketch would be: 1. pg_regress adds "regression_test_mode = on" to the ALTER DATABASE SET settings it already applies to created databases. 2. One of the effects of the GUC would be to throw an error (or warning?) for creation of disallowed-by-convention role names. 3. The rolenames test could turn off the GUC during creation of these specific test names. Or if we make it report WARNING not ERROR, we don't even have to do that, just include the warnings in the expected output. I find myself liking this idea, because there would be other uses for such a GUC. One thing that is awful darn tempting right now is to get rid of the "force_parallel_mode = regress" wart, making that variable back into a plain bool, and instead have that behavior emerge from having both force_parallel_mode and regression_test_mode turned on. We'd still want creation of these specific role names to be wrapped in a rolled-back transaction, so that we could document that only role names beginning with "regress_" are at hazard from "make installcheck". But I don't think that doing that really represents any meaningful loss of coverage. regards, tom lane
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> We've talked before about how the regression tests should be circumspect
>> about what role names they create/drop, so as to avoid possibly blowing
>> up an installation's existing users during "make installcheck". In
>> particular I believe there was consensus that such names should begin
>> with, or at least include, "regress". I got around today to instrumenting
>> CreateRole to see what names we were actually creating, and was quite
>> depressed as to how thoroughly that guideline is being ignored (see
>> attached).
> I would propose that we have one test run near the beginning or right at
> the beginning of the serial schedule that sets up a small number of
> roles to cover most of the needs of every other test, so that most such
> other tests do not need to create any roles at all.
I don't think that's a very attractive idea. It would create hazards for
concurrent test cases, I fear. Moreover, an un-enforced convention of
"don't create roles" isn't really any safer than an un-enforced convention
of "only create roles named thus-and-such"; it just takes one person who
is not familiar with the convention, and one committer not paying close
attention, and we're right back where we started.
I'm coming to the conclusion that the only thing that will make this
materially better in the long run is automatic enforcement of a convention
about what role names may be created in the regression tests. See my
response to Stephen just now for a concrete proposal.
regards, tom lane
I've gone ahead and pushed a patch that does all of the cosmetic renamings
needed to clean up the global-object-names situation. I've not done
anything yet about those special cases in the rolenames test, since it's
open for discussion exactly what to do there. I figured that this patch
was bulky enough, and mechanical enough, that there wasn't much point in
putting it up for review; the buildfarm will do a lot better at finding
any mistakes I may have made.
regards, tom lane
On Sat, Jul 16, 2016 at 11:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I'm coming to the conclusion that the only thing that will make this > materially better in the long run is automatic enforcement of a convention > about what role names may be created in the regression tests. See my > response to Stephen just now for a concrete proposal. We could also do this by loading a C module during the regression tests, which seems maybe less ugly than adding a GUC. I don't particularly like your suggestion of spooky action at a distance between force_parallel_mode and regression_test_mode. That just seems kooky. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Jul 18, 2016 at 10:37 AM, Robert Haas <robertmhaas@gmail.com> wrote: > On Sat, Jul 16, 2016 at 11:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > We could also do this by loading a C module during the regression > tests, which seems maybe less ugly than adding a GUC. > I don't particularly like your suggestion of spooky action at a > distance between force_parallel_mode and regression_test_mode. That > just seems kooky. One downside of the plugin is that any users willing to do make installcheck would need to install it as well. -- Michael
Robert Haas <robertmhaas@gmail.com> writes: > On Sat, Jul 16, 2016 at 11:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I'm coming to the conclusion that the only thing that will make this >> materially better in the long run is automatic enforcement of a convention >> about what role names may be created in the regression tests. See my >> response to Stephen just now for a concrete proposal. > We could also do this by loading a C module during the regression > tests, which seems maybe less ugly than adding a GUC. Meh, I'm not convinced. As Michael points out, arranging for such a module to get loaded in an installcheck context would be difficult --- maybe not impossible, but complicated. Also, we'd have to add hook function calls in all the places it would need to get control; most of those places would probably be one-off hooks with no other conceivable use. And we'd still need to have a GUC, because I think it's inevitable that we'd need to be able to turn off the restrictions for specific tests. So that seems like a lot of work and complication just to make a GUC be custom to some undocumented extension rather than built-in. If we had no other debugging GUCs then there might be some point in rejecting this one, but we have a bunch: https://www.postgresql.org/docs/devel/static/runtime-config-developer.html > I don't particularly like your suggestion of spooky action at a > distance between force_parallel_mode and regression_test_mode. That > just seems kooky. It's certainly a judgment call as to which way is cleaner, but I don't understand your objection. There are plenty of ways in which multiple GUCs determine a given behavior already. Also, breaking this behavior into two variables would let us document the user-useful behavior (do this to test parallel safety of functions) in a different place from the developer-useful behavior (do this to make EXPLAIN lie to you, which surely has no possible use except for regression testing). Possibly a single "regression_test_mode" variable is a bad idea and we should instead have distinct developer-oriented GUCs for each special behavior we decide we need. I'm not particularly set on that, but to me it seems like less of a mess to have just one. regards, tom lane
On 7/15/16 6:13 PM, Tom Lane wrote: > We've talked before about how the regression tests should be circumspect > about what role names they create/drop, so as to avoid possibly blowing > up an installation's existing users during "make installcheck". I'm not particularly sure that that is a useful goal anymore. Setting up a new instance is cheap, so if users are concerned, they should not run the tests against their important instance. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 7/15/16 6:13 PM, Tom Lane wrote:
>> We've talked before about how the regression tests should be circumspect
>> about what role names they create/drop, so as to avoid possibly blowing
>> up an installation's existing users during "make installcheck".
> I'm not particularly sure that that is a useful goal anymore. Setting
> up a new instance is cheap, so if users are concerned, they should not
> run the tests against their important instance.
To my mind, the main point of "make installcheck" is to verify that
your actual installation, not some other instance, is working right.
This is far from a trivial issue; for instance on a SELinux machine,
you need to be able to verify that the installed policy allows the
DB to work, and that is very likely to be path-sensitive.
So this remains an important requirement to me. It's true that it might
be something you need to run only right after making the installation ---
but that doesn't mean the DB is empty. Consider wanting to test a freshly
pg_upgrade'd installation, for example.
regards, tom lane
On Mon, Jul 18, 2016 at 1:34 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > One downside of the plugin is that any users willing to do make > installcheck would need to install it as well. Not really. If the only purpose of the plugin is to verify that we're not creating regression users whose names don't start with "regress", it should be good enough to run it for "make check" but not for "make installcheck". It's not there to test functionality, just to verify that we've followed our own rules for regression tests. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Jul 18, 2016 at 11:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I don't particularly like your suggestion of spooky action at a >> distance between force_parallel_mode and regression_test_mode. That >> just seems kooky. > > It's certainly a judgment call as to which way is cleaner, but I don't > understand your objection. There are plenty of ways in which multiple > GUCs determine a given behavior already. Also, breaking this behavior > into two variables would let us document the user-useful behavior (do > this to test parallel safety of functions) in a different place from the > developer-useful behavior (do this to make EXPLAIN lie to you, which > surely has no possible use except for regression testing). There are certainly cases where the behavior that you get depends on multiple GUCs. For example, the vacuum cost limit stuff is like that, and so are the cost factors which control the query planner. But in each of those cases, each GUC has a function that is fully orthogonal to each other GUC. That doesn't seem to be the case here. You're saying that force_parallel_mode will decide whether we get behavior A, and regression_test_mode will decide whether we get behavior B, but if you ask for both A and B you will also get an additional behavior C which would not have been selected by either GUC taken alone. Because the different settings are now non-orthogonal, there's now no way to get A and B without C, or A and C without B. Moreover, I don't want to end up in a situation where regression_test_mode=on enables a score of minor behavior changes that can't be separated out and tested individually. It's true that checking the names of regression roles is such a very minor thing that a generic name like regression_test_mode might be OK, with the idea that anything else similarly minor that comes along later can be folded into that as well. But I fear that it will become a crutch: my code makes the regression test fail. Instead of writing better tests, add another thing that's conditional on regression_test_mode! Eventually, we'll have a bug that the regression tests fail to catch because regression_test_mode papers over the problem. We're some distance from such a situation now, of course, but I bet the temptation to be undisciplined about hooking more behavior into that GUC will be almost irresistible for new developers, and in some cases experienced ones, too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
[ blast from the past department ]
So, this thread about ensuring the regression tests don't create random
globally-visible names seems to have got lost in the weeds. I'm going
to resurrect it after noticing that two different places have grown
violations of the rule since I fixed things in 18555b132.
I think we were largely overdesigning the fix. The right thing is to do
something approximately as attached, and then make at least one buildfarm
critter build with -DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS.
This proposed patch intentionally emits only WARNINGs, not ERRORS. This
is so that TAP tests won't fail if the warnings fire. Since TAP tests
never run against an existing installation, there's no reason for them
to follow the restriction. Admittedly, this is a pretty ad-hoc way of
getting that end result, but I'm tired of waiting for somebody to
implement a less ad-hoc way.
There are still two things we'd have to argue about before committing
this. One is the already-discussed-upthread point that rolenames.sql
insists on creating and then deleting users with names like "user".
I remain of the opinion that that's just a damfool idea; there is nearly
zero chance that those test cases will ever catch a bug, and much more
than zero chance that they'll cause problems in some pre-existing
installation. So my vote is to take them out.
The other is that we've also grown a bunch of tests that create
subscriptions and replication origins with randomly-chosen names.
Since those objects also have globally-visible names, I think the
"name them regress_something" policy should apply to them too, and
the attached patch tries to enforce it. But of course that causes
a bunch of failures right now.
(While I'm looking at that, I wonder why we don't have a restriction
against "pg_xxx" names for those object types.)
Comments?
regards, tom lane
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index f13dce9..8cc7d2a 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -357,6 +357,11 @@ CreateSubscription(CreateSubscriptionStmt *stmt, bool isTopLevel)
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("must be superuser to create subscriptions"))));
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(stmt->subname, "regress_", 8) != 0)
+ elog(WARNING, "subscriptions created by regression test cases must be named regress_xxx");
+#endif
+
rel = table_open(SubscriptionRelationId, RowExclusiveLock);
/* Check if name is used */
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
index 5e43867..209b114 100644
--- a/src/backend/commands/tablespace.c
+++ b/src/backend/commands/tablespace.c
@@ -307,6 +307,11 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
stmt->tablespacename),
errdetail("The prefix \"pg_\" is reserved for system tablespaces.")));
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(stmt->tablespacename, "regress_", 8) != 0)
+ elog(WARNING, "tablespaces created by regression test cases must be named regress_xxx");
+#endif
+
/*
* Check that there is no other tablespace by this name. (The unique
* index would catch this anyway, but might as well give a friendlier
@@ -957,6 +962,11 @@ RenameTableSpace(const char *oldname, const char *newname)
errmsg("unacceptable tablespace name \"%s\"", newname),
errdetail("The prefix \"pg_\" is reserved for system tablespaces.")));
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(newname, "regress_", 8) != 0)
+ elog(WARNING, "tablespaces created by regression test cases must be named regress_xxx");
+#endif
+
/* Make sure the new name doesn't exist */
ScanKeyInit(&entry[0],
Anum_pg_tablespace_spcname,
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index ccc586d..db3bc63 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -326,6 +326,11 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
stmt->role),
errdetail("Role names starting with \"pg_\" are reserved.")));
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(stmt->role, "regress_", 8) != 0)
+ elog(WARNING, "roles created by regression test cases must be named regress_xxx");
+#endif
+
/*
* Check the pg_authid relation to be certain the role doesn't already
* exist.
@@ -1212,6 +1217,11 @@ RenameRole(const char *oldname, const char *newname)
newname),
errdetail("Role names starting with \"pg_\" are reserved.")));
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(newname, "regress_", 8) != 0)
+ elog(WARNING, "roles created by regression test cases must be named regress_xxx");
+#endif
+
/* make sure the new name doesn't exist */
if (SearchSysCacheExists1(AUTHNAME, CStringGetDatum(newname)))
ereport(ERROR,
diff --git a/src/backend/replication/logical/origin.c b/src/backend/replication/logical/origin.c
index ff4d54d..d24e59f 100644
--- a/src/backend/replication/logical/origin.c
+++ b/src/backend/replication/logical/origin.c
@@ -249,6 +249,11 @@ replorigin_create(char *roname)
SysScanDesc scan;
ScanKeyData key;
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(roname, "regress_", 8) != 0)
+ elog(WARNING, "replication origins created by regression test cases must be named regress_xxx");
+#endif
+
roname_d = CStringGetTextDatum(roname);
Assert(IsTransactionState());
On Tue, Jun 25, 2019 at 11:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Comments? LGTM. s/must/should/ ? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jun 25, 2019 at 11:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Comments?
> LGTM.
Thanks for looking!
> s/must/should/ ?
Sure, if you like.
Further on the rolenames test mess: I started to work on removing
that script's creation of out-of-spec user names, but my heart just
sank to the floor when I noticed that it was also doing stuff like
this:
ALTER USER ALL SET application_name to 'SLAP';
ALTER USER ALL RESET application_name;
The extent to which that's Not OK inside a production installation
is hard to overstate.
At the same time, I can see that we'd want to have some coverage
for that code path, so just deleting those tests isn't attractive.
So I think the only real solution is to cordon off this test script
in some environment where it won't get run by "make installcheck".
I thought briefly about recasting it as a TAP test, but that looked
like a huge amount of make-work.
What I propose that we do instead is invent an empty "module" under
src/test/modules/ and install rolenames as a test for that. We
already have this in src/test/modules/README:
src/test/modules contains PostgreSQL extensions that are primarily or
entirely intended for testing PostgreSQL and/or to serve as example
code. The extensions here aren't intended to be installed in a
production server and aren't suitable for "real work".
So I think we could just extend that verbiage to insist that neither "make
install" nor "make installcheck" are good ideas against production
servers. Perhaps like
Furthermore, while you can do "make install" and "make installcheck"
in this directory or its children, it is HIGHLY NOT ADVISED to do so
with a server containing valuable data. Some of these tests may have
undesirable side-effects on roles or other global objects within the
tested server.
Defining things this way also makes it a non-problem that
src/test/modules/test_pg_dump creates global objects and doesn't drop
them.
(src/test/Makefile is already on board with this definition.)
Now, this doesn't in itself fix the problem that my proposed patch will
emit warnings about the rolenames test script creating "Public" and so on.
We could fix that by maintaining a variant expected-file that includes
those warnings, but probably a less painful answer is just to jack
client_min_messages up to ERROR for that short segment of the test script.
We could make the new subdirectory be something specific like
"src/test/modules/test_rolenames", but I think very likely we'll be
wanting some additional test scripts that we likewise deem unsafe to
run during "installcheck". So I'd rather choose a more generic module
name, but I'm not sure what ... "unsafe_tests"?
Comments?
regards, tom lane
On 2019-Jun-27, Tom Lane wrote: > Further on the rolenames test mess: I started to work on removing > that script's creation of out-of-spec user names, but my heart just > sank to the floor when I noticed that it was also doing stuff like > this: > > ALTER USER ALL SET application_name to 'SLAP'; > ALTER USER ALL RESET application_name; > > The extent to which that's Not OK inside a production installation > is hard to overstate. Uh-oh. I don't remember doing that, but evidently I did :-( > At the same time, I can see that we'd want to have some coverage > for that code path, so just deleting those tests isn't attractive. Yeah ... > What I propose that we do instead is invent an empty "module" under > src/test/modules/ and install rolenames as a test for that. Hmm, that's an idea, yes. > Now, this doesn't in itself fix the problem that my proposed patch will > emit warnings about the rolenames test script creating "Public" and so on. > We could fix that by maintaining a variant expected-file that includes > those warnings, but probably a less painful answer is just to jack > client_min_messages up to ERROR for that short segment of the test script. +1 > We could make the new subdirectory be something specific like > "src/test/modules/test_rolenames", but I think very likely we'll be > wanting some additional test scripts that we likewise deem unsafe to > run during "installcheck". So I'd rather choose a more generic module > name, but I'm not sure what ... "unsafe_tests"? +0 for unsafe_tests, -1 for test_rolenames. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Furthermore, while you can do "make install" and "make installcheck" > in this directory or its children, it is HIGHLY NOT ADVISED to do so > with a server containing valuable data. Some of these tests may have > undesirable side-effects on roles or other global objects within the > tested server. > > Defining things this way also makes it a non-problem that > src/test/modules/test_pg_dump creates global objects and doesn't drop > them. Sounds like a good approach to me and I'm happy that it'd address the test_pg_dump case too. > Now, this doesn't in itself fix the problem that my proposed patch will > emit warnings about the rolenames test script creating "Public" and so on. > We could fix that by maintaining a variant expected-file that includes > those warnings, but probably a less painful answer is just to jack > client_min_messages up to ERROR for that short segment of the test script. Seems alright. > We could make the new subdirectory be something specific like > "src/test/modules/test_rolenames", but I think very likely we'll be > wanting some additional test scripts that we likewise deem unsafe to > run during "installcheck". So I'd rather choose a more generic module > name, but I'm not sure what ... "unsafe_tests"? Agreed but haven't got any particularly good suggestions on names.. Thanks, Stephen
Вложения
OK, here's a completed patch to add checking for naming-rule violations.
I updated regress.sgml to clarify the naming rules (and failed to
resist the temptation to update a lot of other somewhat-obsolete
statements there, too).
Also worth noting is that I added an IsReservedName check to
pg_replication_origin_create(), ie it will no longer allow
user-selected origin names starting with "pg_". This seems
like a good idea considering that we generate internal origin
names that look like that. I have not done anything equivalent
for subscription names, but should we consider doing so?
I touched the TAP tests only to the extent necessary to make them pass
cleanly --- mostly I had to fool with pg_dump/t/010_dump_connstr.pl
because it whines if the restore step emits any warnings. Perhaps
there's another way to address that with less invasive changes to that
test script; but I couldn't think of one other than ignoring warnings,
which didn't seem like a great idea.
This patch doesn't address the issue that we think rolenames.sql is
unsafe to run in "make installcheck" mode. That seems like a separable
problem, and we'd have to adjust that script as shown here anyway.
Barring objections, I'll push this shortly (to HEAD only) and turn on
the enabling switch on one or two of my buildfarm critters.
regards, tom lane
diff --git a/doc/src/sgml/regress.sgml b/doc/src/sgml/regress.sgml
index 448e2d1..7b68213 100644
--- a/doc/src/sgml/regress.sgml
+++ b/doc/src/sgml/regress.sgml
@@ -47,7 +47,7 @@ make check
<screen>
<computeroutput>
=======================
- All 115 tests passed.
+ All 193 tests passed.
=======================
</computeroutput>
</screen>
@@ -98,7 +98,7 @@ make MAX_CONNECTIONS=10 check
<para>
To run the tests after installation (see <xref linkend="installation"/>),
- initialize a data area and start the
+ initialize a data directory and start the
server as explained in <xref linkend="runtime"/>, then type:
<screen>
make installcheck
@@ -116,10 +116,10 @@ make installcheck-parallel
<para>
The tests will also transiently create some cluster-wide objects, such as
- roles and tablespaces. These objects will have names beginning with
- <literal>regress_</literal>. Beware of using <literal>installcheck</literal>
- mode in installations that have any actual users or tablespaces named
- that way.
+ roles, tablespaces, and subscriptions. These objects will have names
+ beginning with <literal>regress_</literal>. Beware of
+ using <literal>installcheck</literal> mode with an installation that has
+ any actual global objects named that way.
</para>
</sect2>
@@ -130,7 +130,7 @@ make installcheck-parallel
The <literal>make check</literal> and <literal>make installcheck</literal> commands
run only the <quote>core</quote> regression tests, which test built-in
functionality of the <productname>PostgreSQL</productname> server. The source
- distribution also contains additional test suites, most of them having
+ distribution contains many additional test suites, most of them having
to do with add-on functionality such as optional procedural languages.
</para>
@@ -146,9 +146,24 @@ make installcheck-world
already-installed server, respectively, just as previously explained
for <literal>make check</literal> and <literal>make installcheck</literal>. Other
considerations are the same as previously explained for each method.
- Note that <literal>make check-world</literal> builds a separate temporary
- installation tree for each tested module, so it requires a great deal
- more time and disk space than <literal>make installcheck-world</literal>.
+ Note that <literal>make check-world</literal> builds a separate instance
+ (temporary data directory) for each tested module, so it requires more
+ time and disk space than <literal>make installcheck-world</literal>.
+ </para>
+
+ <para>
+ On a modern machine with multiple CPU cores and no tight operating-system
+ limits, you can make things go substantially faster with parallelism.
+ The recipe that most PostgreSQL developers actually use for running all
+ tests is something like
+<screen>
+make check-world -j8 >/dev/null
+</screen>
+ with a <option>-j</option> limit near to or a bit more than the number
+ of available cores. Discarding <systemitem>stdout</systemitem>
+ eliminates chatter that's not interesting when you just want to verify
+ success. (In case of failure, the <systemitem>stderr</systemitem>
+ messages are usually enough to determine where to look closer.)
</para>
<para>
@@ -166,8 +181,7 @@ make installcheck-world
<itemizedlist>
<listitem>
<para>
- Regression tests for optional procedural languages (other than
- <application>PL/pgSQL</application>, which is tested by the core tests).
+ Regression tests for optional procedural languages.
These are located under <filename>src/pl</filename>.
</para>
</listitem>
@@ -186,27 +200,49 @@ make installcheck-world
</listitem>
<listitem>
<para>
+ Tests for core-supported authentication methods,
+ located in <filename>src/test/authentication</filename>.
+ (See below for additional authentication-related tests.)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
Tests stressing behavior of concurrent sessions,
located in <filename>src/test/isolation</filename>.
</para>
</listitem>
<listitem>
<para>
- Tests of client programs under <filename>src/bin</filename>. See
- also <xref linkend="regress-tap"/>.
+ Tests for crash recovery and physical replication,
+ located in <filename>src/test/recovery</filename>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Tests for logical replication,
+ located in <filename>src/test/subscription</filename>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Tests of client programs, located under <filename>src/bin</filename>.
</para>
</listitem>
</itemizedlist>
<para>
- When using <literal>installcheck</literal> mode, these tests will destroy any
- existing databases named <literal>pl_regression</literal>,
- <literal>contrib_regression</literal>, <literal>isolation_regression</literal>,
- <literal>ecpg1_regression</literal>, or <literal>ecpg2_regression</literal>, as well as
- <literal>regression</literal>.
+ When using <literal>installcheck</literal> mode, these tests will create
+ and destroy test databases whose names
+ include <literal>regression</literal>, for
+ example <literal>pl_regression</literal>
+ or <literal>contrib_regression</literal>. Beware of
+ using <literal>installcheck</literal> mode with an installation that has
+ any non-test databases named that way.
</para>
<para>
+ Some of these auxiliary test suites use the TAP infrastructure explained
+ in <xref linkend="regress-tap"/>.
The TAP-based tests are run only when PostgreSQL was configured with the
option <option>--enable-tap-tests</option>. This is recommended for
development, but can be omitted if there is no suitable Perl installation.
@@ -259,6 +295,17 @@ make check-world PG_TEST_EXTRA='kerberos ldap ssl'
configuration are not run even if they are mentioned in
<varname>PG_TEST_EXTRA</varname>.
</para>
+
+ <para>
+ In addition, there are tests in <filename>src/test/modules</filename>
+ which will be run by <literal>make check-world</literal> but not
+ by <literal>make installcheck-world</literal>. This is because they
+ install non-production extensions or have other side-effects that are
+ considered undesirable for a production installation. You can
+ use <literal>make install</literal> and <literal>make
+ installcheck</literal> in one of those subdirectories if you wish,
+ but it's not recommended to do so with a non-test server.
+ </para>
</sect2>
<sect2>
@@ -737,6 +784,26 @@ make check PROVE_TESTS='t/001_test1.pl t/003_test3.pl'
The TAP tests require the Perl module <literal>IPC::Run</literal>.
This module is available from CPAN or an operating system package.
</para>
+
+ <para>
+ Generically speaking, the TAP tests will test the executables in a
+ previously-installed installation tree if you say <literal>make
+ installcheck</literal>, or will build a new local installation tree from
+ current sources if you say <literal>make check</literal>. In either
+ case they will initialize a local instance (data directory) and
+ transiently run a server in it. Some of these tests run more than one
+ server. Thus, these tests can be fairly resource-intensive.
+ </para>
+
+ <para>
+ It's important to realize that the TAP tests will start test server(s)
+ even when you say <literal>make installcheck</literal>; this is unlike
+ the traditional non-TAP testing infrastructure, which expects to use an
+ already-running test server in that case. Some PostgreSQL
+ subdirectories contain both traditional-style and TAP-style tests,
+ meaning that <literal>make installcheck</literal> will produce a mix of
+ results from temporary servers and the already-running test server.
+ </para>
</sect1>
<sect1 id="regress-coverage">
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index 9229fe1..70dbcb0 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -274,6 +274,12 @@ AlterObjectRename_internal(Relation rel, Oid objectId, const char *new_name)
if (SearchSysCacheExists2(SUBSCRIPTIONNAME, MyDatabaseId,
CStringGetDatum(new_name)))
report_name_conflict(classId, new_name);
+
+ /* Also enforce regression testing naming rules, if enabled */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(new_name, "regress_", 8) != 0)
+ elog(WARNING, "subscriptions created by regression test cases should have names starting with
\"regress_\"");
+#endif
}
else if (nameCacheId >= 0)
{
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 15207bf..863f89f 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -471,6 +471,16 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
}
/*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for database names are violated. But don't complain during
+ * initdb.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (IsUnderPostmaster && strstr(dbname, "regression") == NULL)
+ elog(WARNING, "databases created by regression test cases should have names including \"regression\"");
+#endif
+
+ /*
* Check for db name conflict. This is just to give a more friendly error
* message than "unique index violation". There's a race condition but
* we're willing to accept the less friendly message in that case.
@@ -1009,6 +1019,15 @@ RenameDatabase(const char *oldname, const char *newname)
errmsg("permission denied to rename database")));
/*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for database names are violated.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strstr(newname, "regression") == NULL)
+ elog(WARNING, "databases created by regression test cases should have names including \"regression\"");
+#endif
+
+ /*
* Make sure the new name doesn't exist. See notes for same error in
* CREATE DATABASE.
*/
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index f13dce9..2e67a58 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -357,6 +357,15 @@ CreateSubscription(CreateSubscriptionStmt *stmt, bool isTopLevel)
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("must be superuser to create subscriptions"))));
+ /*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for subscription names are violated.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(stmt->subname, "regress_", 8) != 0)
+ elog(WARNING, "subscriptions created by regression test cases should have names starting with \"regress_\"");
+#endif
+
rel = table_open(SubscriptionRelationId, RowExclusiveLock);
/* Check if name is used */
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
index 5e43867..502736b 100644
--- a/src/backend/commands/tablespace.c
+++ b/src/backend/commands/tablespace.c
@@ -308,6 +308,15 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
errdetail("The prefix \"pg_\" is reserved for system tablespaces.")));
/*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for tablespace names are violated.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(stmt->tablespacename, "regress_", 8) != 0)
+ elog(WARNING, "tablespaces created by regression test cases should have names starting with \"regress_\"");
+#endif
+
+ /*
* Check that there is no other tablespace by this name. (The unique
* index would catch this anyway, but might as well give a friendlier
* message.)
@@ -957,6 +966,15 @@ RenameTableSpace(const char *oldname, const char *newname)
errmsg("unacceptable tablespace name \"%s\"", newname),
errdetail("The prefix \"pg_\" is reserved for system tablespaces.")));
+ /*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for tablespace names are violated.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(newname, "regress_", 8) != 0)
+ elog(WARNING, "tablespaces created by regression test cases should have names starting with \"regress_\"");
+#endif
+
/* Make sure the new name doesn't exist */
ScanKeyInit(&entry[0],
Anum_pg_tablespace_spcname,
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index ccc586d..aab5aa8 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -327,6 +327,15 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
errdetail("Role names starting with \"pg_\" are reserved.")));
/*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for role names are violated.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(stmt->role, "regress_", 8) != 0)
+ elog(WARNING, "roles created by regression test cases should have names starting with \"regress_\"");
+#endif
+
+ /*
* Check the pg_authid relation to be certain the role doesn't already
* exist.
*/
@@ -1212,6 +1221,15 @@ RenameRole(const char *oldname, const char *newname)
newname),
errdetail("Role names starting with \"pg_\" are reserved.")));
+ /*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for role names are violated.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(newname, "regress_", 8) != 0)
+ elog(WARNING, "roles created by regression test cases should have names starting with \"regress_\"");
+#endif
+
/* make sure the new name doesn't exist */
if (SearchSysCacheExists1(AUTHNAME, CStringGetDatum(newname)))
ereport(ERROR,
diff --git a/src/backend/replication/logical/origin.c b/src/backend/replication/logical/origin.c
index ff4d54d..681132c 100644
--- a/src/backend/replication/logical/origin.c
+++ b/src/backend/replication/logical/origin.c
@@ -78,6 +78,7 @@
#include "access/table.h"
#include "access/xact.h"
+#include "catalog/catalog.h"
#include "catalog/indexing.h"
#include "nodes/execnodes.h"
@@ -1228,6 +1229,24 @@ pg_replication_origin_create(PG_FUNCTION_ARGS)
replorigin_check_prerequisites(false, false);
name = text_to_cstring((text *) DatumGetPointer(PG_GETARG_DATUM(0)));
+
+ /* Replication origins "pg_xxx" are reserved for internal use */
+ if (IsReservedName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("replication origin name \"%s\" is reserved",
+ name),
+ errdetail("Origin names starting with \"pg_\" are reserved.")));
+
+ /*
+ * If built with appropriate switch, whine when regression-testing
+ * conventions for replication origin names are violated.
+ */
+#ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
+ if (strncmp(name, "regress_", 8) != 0)
+ elog(WARNING, "replication origins created by regression test cases should have names starting with
\"regress_\"");
+#endif
+
roident = replorigin_create(name);
pfree(name);
diff --git a/src/test/modules/README b/src/test/modules/README
index 99f921d..025ecac 100644
--- a/src/test/modules/README
+++ b/src/test/modules/README
@@ -6,6 +6,13 @@ intended for testing PostgreSQL and/or to serve as example code. The extensions
here aren't intended to be installed in a production server and aren't suitable
for "real work".
+Furthermore, while you can do "make install" and "make installcheck" in
+this directory or its children, it is NOT ADVISABLE to do so with a server
+containing valuable data. Some of these tests may have undesirable
+side-effects on roles or other global objects within the tested server.
+"make installcheck-world" at the top level does not recurse into this
+directory.
+
Most extensions have their own pg_regress tests or isolationtester specs. Some
are also used by tests elsewhere in the tree.
diff --git a/contrib/test_decoding/expected/replorigin.out b/contrib/test_decoding/expected/replorigin.out
index 8ea4ddd..3b249f4 100644
--- a/contrib/test_decoding/expected/replorigin.out
+++ b/contrib/test_decoding/expected/replorigin.out
@@ -2,38 +2,38 @@
SET synchronous_commit = on;
CREATE TABLE origin_tbl(id serial primary key, data text);
CREATE TABLE target_tbl(id serial primary key, data text);
-SELECT pg_replication_origin_create('test_decoding: regression_slot');
+SELECT pg_replication_origin_create('regress_test_decoding: regression_slot');
pg_replication_origin_create
------------------------------
1
(1 row)
-- ensure duplicate creations fail
-SELECT pg_replication_origin_create('test_decoding: regression_slot');
+SELECT pg_replication_origin_create('regress_test_decoding: regression_slot');
ERROR: duplicate key value violates unique constraint "pg_replication_origin_roname_index"
-DETAIL: Key (roname)=(test_decoding: regression_slot) already exists.
+DETAIL: Key (roname)=(regress_test_decoding: regression_slot) already exists.
--ensure deletions work (once)
-SELECT pg_replication_origin_create('test_decoding: temp');
+SELECT pg_replication_origin_create('regress_test_decoding: temp');
pg_replication_origin_create
------------------------------
2
(1 row)
-SELECT pg_replication_origin_drop('test_decoding: temp');
+SELECT pg_replication_origin_drop('regress_test_decoding: temp');
pg_replication_origin_drop
----------------------------
(1 row)
-SELECT pg_replication_origin_drop('test_decoding: temp');
-ERROR: replication origin "test_decoding: temp" does not exist
+SELECT pg_replication_origin_drop('regress_test_decoding: temp');
+ERROR: replication origin "regress_test_decoding: temp" does not exist
-- various failure checks for undefined slots
-select pg_replication_origin_advance('test_decoding: temp', '0/1');
-ERROR: replication origin "test_decoding: temp" does not exist
-select pg_replication_origin_session_setup('test_decoding: temp');
-ERROR: replication origin "test_decoding: temp" does not exist
-select pg_replication_origin_progress('test_decoding: temp', true);
-ERROR: replication origin "test_decoding: temp" does not exist
+select pg_replication_origin_advance('regress_test_decoding: temp', '0/1');
+ERROR: replication origin "regress_test_decoding: temp" does not exist
+select pg_replication_origin_session_setup('regress_test_decoding: temp');
+ERROR: replication origin "regress_test_decoding: temp" does not exist
+select pg_replication_origin_progress('regress_test_decoding: temp', true);
+ERROR: replication origin "regress_test_decoding: temp" does not exist
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
?column?
----------
@@ -57,14 +57,14 @@ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'inc
INSERT INTO origin_tbl(data) VALUES ('will be replicated, but not decoded again');
-- mark session as replaying
-SELECT pg_replication_origin_session_setup('test_decoding: regression_slot');
+SELECT pg_replication_origin_session_setup('regress_test_decoding: regression_slot');
pg_replication_origin_session_setup
-------------------------------------
(1 row)
-- ensure we prevent duplicate setup
-SELECT pg_replication_origin_session_setup('test_decoding: regression_slot');
+SELECT pg_replication_origin_session_setup('regress_test_decoding: regression_slot');
ERROR: cannot setup replication origin when one is already setup
SELECT '' FROM pg_logical_emit_message(false, 'test', 'this message will not be decoded');
?column?
@@ -103,19 +103,19 @@ SELECT pg_replication_origin_session_reset();
(1 row)
SELECT local_id, external_id, remote_lsn, local_lsn <> '0/0' FROM pg_replication_origin_status;
- local_id | external_id | remote_lsn | ?column?
-----------+--------------------------------+------------+----------
- 1 | test_decoding: regression_slot | 0/AABBCCDD | t
+ local_id | external_id | remote_lsn | ?column?
+----------+----------------------------------------+------------+----------
+ 1 | regress_test_decoding: regression_slot | 0/AABBCCDD | t
(1 row)
-- check replication progress identified by name is correct
-SELECT pg_replication_origin_progress('test_decoding: regression_slot', false);
+SELECT pg_replication_origin_progress('regress_test_decoding: regression_slot', false);
pg_replication_origin_progress
--------------------------------
0/AABBCCDD
(1 row)
-SELECT pg_replication_origin_progress('test_decoding: regression_slot', true);
+SELECT pg_replication_origin_progress('regress_test_decoding: regression_slot', true);
pg_replication_origin_progress
--------------------------------
0/AABBCCDD
@@ -146,7 +146,7 @@ SELECT pg_drop_replication_slot('regression_slot');
(1 row)
-SELECT pg_replication_origin_drop('test_decoding: regression_slot');
+SELECT pg_replication_origin_drop('regress_test_decoding: regression_slot');
pg_replication_origin_drop
----------------------------
diff --git a/contrib/test_decoding/expected/rewrite.out b/contrib/test_decoding/expected/rewrite.out
index 28998b8..b30999c 100644
--- a/contrib/test_decoding/expected/rewrite.out
+++ b/contrib/test_decoding/expected/rewrite.out
@@ -11,7 +11,7 @@ CREATE FUNCTION exec(text) returns void language plpgsql volatile
EXECUTE $1;
END;
$f$;
-CREATE ROLE justforcomments NOLOGIN;
+CREATE ROLE regress_justforcomments NOLOGIN;
SELECT exec(
format($outer$CREATE FUNCTION iamalongfunction() RETURNS TEXT IMMUTABLE LANGUAGE SQL AS $f$SELECT text
%L$f$$outer$,
(SELECT repeat(string_agg(to_char(g.i, 'FM0000'), ''), 50) FROM generate_series(1, 500) g(i))));
@@ -29,7 +29,7 @@ SELECT exec(
(1 row)
SELECT exec(
- format($outer$COMMENT ON ROLE JUSTFORCOMMENTS IS %L$outer$,
+ format($outer$COMMENT ON ROLE REGRESS_JUSTFORCOMMENTS IS %L$outer$,
iamalongfunction()));
exec
------
@@ -161,4 +161,4 @@ SELECT pg_drop_replication_slot('regression_slot');
DROP TABLE IF EXISTS replication_example;
DROP FUNCTION iamalongfunction();
DROP FUNCTION exec(text);
-DROP ROLE justforcomments;
+DROP ROLE regress_justforcomments;
diff --git a/contrib/test_decoding/sql/replorigin.sql b/contrib/test_decoding/sql/replorigin.sql
index 451cd4b..8979b30 100644
--- a/contrib/test_decoding/sql/replorigin.sql
+++ b/contrib/test_decoding/sql/replorigin.sql
@@ -4,19 +4,19 @@ SET synchronous_commit = on;
CREATE TABLE origin_tbl(id serial primary key, data text);
CREATE TABLE target_tbl(id serial primary key, data text);
-SELECT pg_replication_origin_create('test_decoding: regression_slot');
+SELECT pg_replication_origin_create('regress_test_decoding: regression_slot');
-- ensure duplicate creations fail
-SELECT pg_replication_origin_create('test_decoding: regression_slot');
+SELECT pg_replication_origin_create('regress_test_decoding: regression_slot');
--ensure deletions work (once)
-SELECT pg_replication_origin_create('test_decoding: temp');
-SELECT pg_replication_origin_drop('test_decoding: temp');
-SELECT pg_replication_origin_drop('test_decoding: temp');
+SELECT pg_replication_origin_create('regress_test_decoding: temp');
+SELECT pg_replication_origin_drop('regress_test_decoding: temp');
+SELECT pg_replication_origin_drop('regress_test_decoding: temp');
-- various failure checks for undefined slots
-select pg_replication_origin_advance('test_decoding: temp', '0/1');
-select pg_replication_origin_session_setup('test_decoding: temp');
-select pg_replication_origin_progress('test_decoding: temp', true);
+select pg_replication_origin_advance('regress_test_decoding: temp', '0/1');
+select pg_replication_origin_session_setup('regress_test_decoding: temp');
+select pg_replication_origin_progress('regress_test_decoding: temp', true);
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
@@ -31,10 +31,10 @@ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'inc
INSERT INTO origin_tbl(data) VALUES ('will be replicated, but not decoded again');
-- mark session as replaying
-SELECT pg_replication_origin_session_setup('test_decoding: regression_slot');
+SELECT pg_replication_origin_session_setup('regress_test_decoding: regression_slot');
-- ensure we prevent duplicate setup
-SELECT pg_replication_origin_session_setup('test_decoding: regression_slot');
+SELECT pg_replication_origin_session_setup('regress_test_decoding: regression_slot');
SELECT '' FROM pg_logical_emit_message(false, 'test', 'this message will not be decoded');
@@ -54,8 +54,8 @@ SELECT pg_replication_origin_session_reset();
SELECT local_id, external_id, remote_lsn, local_lsn <> '0/0' FROM pg_replication_origin_status;
-- check replication progress identified by name is correct
-SELECT pg_replication_origin_progress('test_decoding: regression_slot', false);
-SELECT pg_replication_origin_progress('test_decoding: regression_slot', true);
+SELECT pg_replication_origin_progress('regress_test_decoding: regression_slot', false);
+SELECT pg_replication_origin_progress('regress_test_decoding: regression_slot', true);
-- ensure reset requires previously setup state
SELECT pg_replication_origin_session_reset();
@@ -68,4 +68,4 @@ INSERT INTO origin_tbl(data) VALUES ('will be replicated');
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts',
'1', 'only-local', '1');
SELECT pg_drop_replication_slot('regression_slot');
-SELECT pg_replication_origin_drop('test_decoding: regression_slot');
+SELECT pg_replication_origin_drop('regress_test_decoding: regression_slot');
diff --git a/contrib/test_decoding/sql/rewrite.sql b/contrib/test_decoding/sql/rewrite.sql
index c9503a0..62dead3 100644
--- a/contrib/test_decoding/sql/rewrite.sql
+++ b/contrib/test_decoding/sql/rewrite.sql
@@ -13,7 +13,7 @@ CREATE FUNCTION exec(text) returns void language plpgsql volatile
EXECUTE $1;
END;
$f$;
-CREATE ROLE justforcomments NOLOGIN;
+CREATE ROLE regress_justforcomments NOLOGIN;
SELECT exec(
format($outer$CREATE FUNCTION iamalongfunction() RETURNS TEXT IMMUTABLE LANGUAGE SQL AS $f$SELECT text
%L$f$$outer$,
@@ -22,7 +22,7 @@ SELECT exec(
format($outer$COMMENT ON FUNCTION iamalongfunction() IS %L$outer$,
iamalongfunction()));
SELECT exec(
- format($outer$COMMENT ON ROLE JUSTFORCOMMENTS IS %L$outer$,
+ format($outer$COMMENT ON ROLE REGRESS_JUSTFORCOMMENTS IS %L$outer$,
iamalongfunction()));
CREATE TABLE iamalargetable AS SELECT iamalongfunction() longfunctionoutput;
@@ -104,4 +104,4 @@ SELECT pg_drop_replication_slot('regression_slot');
DROP TABLE IF EXISTS replication_example;
DROP FUNCTION iamalongfunction();
DROP FUNCTION exec(text);
-DROP ROLE justforcomments;
+DROP ROLE regress_justforcomments;
diff --git a/src/bin/pg_dump/t/010_dump_connstr.pl b/src/bin/pg_dump/t/010_dump_connstr.pl
index 28a9eb7..d221682 100644
--- a/src/bin/pg_dump/t/010_dump_connstr.pl
+++ b/src/bin/pg_dump/t/010_dump_connstr.pl
@@ -22,29 +22,42 @@ $ENV{PGCLIENTENCODING} = 'LATIN1';
# Create database and user names covering the range of LATIN1
# characters, for use in a connection string by pg_dumpall. Skip ','
# because of pg_regress --create-role, skip [\n\r] because pg_dumpall
-# does not allow them.
+# does not allow them. We also skip many ASCII letters, to keep the
+# total number of tested characters to what will fit in four names.
+# The odds of finding something interesting by testing all ASCII letters
+# seem too small to justify the cycles of testing a fifth name.
my $dbname1 =
- generate_ascii_string(1, 9)
+ 'regression'
+ . generate_ascii_string(1, 9)
. generate_ascii_string(11, 12)
. generate_ascii_string(14, 33)
- . ($TestLib::windows_os ? '' : '"x"')
- . # IPC::Run mishandles '"' on Windows
- generate_ascii_string(35, 43)
- . generate_ascii_string(45, 63); # contains '='
-my $dbname2 =
- generate_ascii_string(67, 129); # skip 64-66 to keep length to 62
-my $dbname3 = generate_ascii_string(130, 192);
-my $dbname4 = generate_ascii_string(193, 255);
+ . ($TestLib::windows_os ? '' : '"x"') # IPC::Run mishandles '"' on Windows
+ . generate_ascii_string(35, 43) # skip ','
+ . generate_ascii_string(45, 54);
+my $dbname2 = 'regression' . generate_ascii_string(55, 65) # skip 'B'-'W'
+ . generate_ascii_string(88, 99) # skip 'd'-'w'
+ . generate_ascii_string(120, 149);
+my $dbname3 = 'regression' . generate_ascii_string(150, 202);
+my $dbname4 = 'regression' . generate_ascii_string(203, 255);
+
+(my $username1 = $dbname1) =~ s/^regression/regress_/;
+(my $username2 = $dbname2) =~ s/^regression/regress_/;
+(my $username3 = $dbname3) =~ s/^regression/regress_/;
+(my $username4 = $dbname4) =~ s/^regression/regress_/;
+
+my $src_bootstrap_super = 'regress_postgres';
+my $dst_bootstrap_super = 'boot';
my $node = get_new_node('main');
-$node->init(extra => [ '--locale=C', '--encoding=LATIN1' ]);
+$node->init(extra =>
+ [ '-U', $src_bootstrap_super, '--locale=C', '--encoding=LATIN1' ]);
# prep pg_hba.conf and pg_ident.conf
$node->run_log(
[
$ENV{PG_REGRESS}, '--config-auth',
$node->data_dir, '--create-role',
- "$dbname1,$dbname2,$dbname3,$dbname4"
+ "$username1,$username2,$username3,$username4"
]);
$node->start;
@@ -53,11 +66,18 @@ my $discard = "$backupdir/discard.sql";
my $plain = "$backupdir/plain.sql";
my $dirfmt = "$backupdir/dirfmt";
-foreach my $dbname ($dbname1, $dbname2, $dbname3, $dbname4, 'CamelCase')
-{
- $node->run_log([ 'createdb', $dbname ]);
- $node->run_log([ 'createuser', '-s', $dbname ]);
-}
+$node->run_log([ 'createdb', '-U', $src_bootstrap_super, $dbname1 ]);
+$node->run_log(
+ [ 'createuser', '-U', $src_bootstrap_super, '-s', $username1 ]);
+$node->run_log([ 'createdb', '-U', $src_bootstrap_super, $dbname2 ]);
+$node->run_log(
+ [ 'createuser', '-U', $src_bootstrap_super, '-s', $username2 ]);
+$node->run_log([ 'createdb', '-U', $src_bootstrap_super, $dbname3 ]);
+$node->run_log(
+ [ 'createuser', '-U', $src_bootstrap_super, '-s', $username3 ]);
+$node->run_log([ 'createdb', '-U', $src_bootstrap_super, $dbname4 ]);
+$node->run_log(
+ [ 'createuser', '-U', $src_bootstrap_super, '-s', $username4 ]);
# For these tests, pg_dumpall -r is used because it produces a short
@@ -66,98 +86,109 @@ $node->command_ok(
[
'pg_dumpall', '-r', '-f', $discard, '--dbname',
$node->connstr($dbname1),
- '-U', $dbname4
+ '-U', $username4
],
'pg_dumpall with long ASCII name 1');
$node->command_ok(
[
'pg_dumpall', '--no-sync', '-r', '-f', $discard, '--dbname',
$node->connstr($dbname2),
- '-U', $dbname3
+ '-U', $username3
],
'pg_dumpall with long ASCII name 2');
$node->command_ok(
[
'pg_dumpall', '--no-sync', '-r', '-f', $discard, '--dbname',
$node->connstr($dbname3),
- '-U', $dbname2
+ '-U', $username2
],
'pg_dumpall with long ASCII name 3');
$node->command_ok(
[
'pg_dumpall', '--no-sync', '-r', '-f', $discard, '--dbname',
$node->connstr($dbname4),
- '-U', $dbname1
+ '-U', $username1
],
'pg_dumpall with long ASCII name 4');
$node->command_ok(
- [ 'pg_dumpall', '--no-sync', '-r', '-l', 'dbname=template1' ],
+ [
+ 'pg_dumpall', '-U',
+ $src_bootstrap_super, '--no-sync',
+ '-r', '-l',
+ 'dbname=template1'
+ ],
'pg_dumpall -l accepts connection string');
-$node->run_log([ 'createdb', "foo\n\rbar" ]);
+$node->run_log([ 'createdb', '-U', $src_bootstrap_super, "foo\n\rbar" ]);
# not sufficient to use -r here
$node->command_fails(
- [ 'pg_dumpall', '--no-sync', '-f', $discard ],
+ [ 'pg_dumpall', '-U', $src_bootstrap_super, '--no-sync', '-f', $discard ],
'pg_dumpall with \n\r in database name');
-$node->run_log([ 'dropdb', "foo\n\rbar" ]);
+$node->run_log([ 'dropdb', '-U', $src_bootstrap_super, "foo\n\rbar" ]);
# make a table, so the parallel worker has something to dump
-$node->safe_psql($dbname1, 'CREATE TABLE t0()');
+$node->safe_psql(
+ $dbname1,
+ 'CREATE TABLE t0()',
+ extra_params => [ '-U', $src_bootstrap_super ]);
# XXX no printed message when this fails, just SIGPIPE termination
$node->command_ok(
[
- 'pg_dump', '-Fd', '--no-sync', '-j2', '-f', $dirfmt, '-U', $dbname1,
+ 'pg_dump', '-Fd', '--no-sync', '-j2', '-f', $dirfmt, '-U', $username1,
$node->connstr($dbname1)
],
'parallel dump');
# recreate $dbname1 for restore test
-$node->run_log([ 'dropdb', $dbname1 ]);
-$node->run_log([ 'createdb', $dbname1 ]);
+$node->run_log([ 'dropdb', '-U', $src_bootstrap_super, $dbname1 ]);
+$node->run_log([ 'createdb', '-U', $src_bootstrap_super, $dbname1 ]);
$node->command_ok(
- [ 'pg_restore', '-v', '-d', 'template1', '-j2', '-U', $dbname1, $dirfmt ],
+ [
+ 'pg_restore', '-v', '-d', 'template1',
+ '-j2', '-U', $username1, $dirfmt
+ ],
'parallel restore');
-$node->run_log([ 'dropdb', $dbname1 ]);
+$node->run_log([ 'dropdb', '-U', $src_bootstrap_super, $dbname1 ]);
$node->command_ok(
[
'pg_restore', '-C', '-v', '-d',
- 'template1', '-j2', '-U', $dbname1,
+ 'template1', '-j2', '-U', $username1,
$dirfmt
],
'parallel restore with create');
-$node->command_ok([ 'pg_dumpall', '--no-sync', '-f', $plain, '-U', $dbname1 ],
+$node->command_ok(
+ [ 'pg_dumpall', '--no-sync', '-f', $plain, '-U', $username1 ],
'take full dump');
system_log('cat', $plain);
my ($stderr, $result);
-my $bootstrap_super = 'boot';
-my $restore_super = qq{a'b\\c=d\\ne"f};
+my $restore_super = qq{regress_a'b\\c=d\\ne"f};
# Restore full dump through psql using environment variables for
# dbname/user connection parameters
my $envar_node = get_new_node('destination_envar');
-$envar_node->init(
- extra => [ '-U', $bootstrap_super, '--locale=C', '--encoding=LATIN1' ]);
+$envar_node->init(extra =>
+ [ '-U', $dst_bootstrap_super, '--locale=C', '--encoding=LATIN1' ]);
$envar_node->run_log(
[
$ENV{PG_REGRESS}, '--config-auth',
$envar_node->data_dir, '--create-role',
- "$bootstrap_super,$restore_super"
+ "$dst_bootstrap_super,$restore_super"
]);
$envar_node->start;
# make superuser for restore
$envar_node->run_log(
- [ 'createuser', '-U', $bootstrap_super, '-s', $restore_super ]);
+ [ 'createuser', '-U', $dst_bootstrap_super, '-s', $restore_super ]);
{
local $ENV{PGPORT} = $envar_node->port;
@@ -177,17 +208,17 @@ is($stderr, '', 'no dump errors');
$restore_super =~ s/"//g
if $TestLib::windows_os; # IPC::Run mishandles '"' on Windows
my $cmdline_node = get_new_node('destination_cmdline');
-$cmdline_node->init(
- extra => [ '-U', $bootstrap_super, '--locale=C', '--encoding=LATIN1' ]);
+$cmdline_node->init(extra =>
+ [ '-U', $dst_bootstrap_super, '--locale=C', '--encoding=LATIN1' ]);
$cmdline_node->run_log(
[
$ENV{PG_REGRESS}, '--config-auth',
$cmdline_node->data_dir, '--create-role',
- "$bootstrap_super,$restore_super"
+ "$dst_bootstrap_super,$restore_super"
]);
$cmdline_node->start;
$cmdline_node->run_log(
- [ 'createuser', '-U', $bootstrap_super, '-s', $restore_super ]);
+ [ 'createuser', '-U', $dst_bootstrap_super, '-s', $restore_super ]);
{
$result = run_log(
[
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index d6d196a..7882024 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -159,9 +159,9 @@ dbname1=`awk 'BEGIN { for (i= 1; i < 46; i++)
dbname1='\"\'$dbname1'\\"\\\'
dbname2=`awk 'BEGIN { for (i = 46; i < 91; i++) printf "%c", i }' </dev/null`
dbname3=`awk 'BEGIN { for (i = 91; i < 128; i++) printf "%c", i }' </dev/null`
-createdb "$dbname1" || createdb_status=$?
-createdb "$dbname2" || createdb_status=$?
-createdb "$dbname3" || createdb_status=$?
+createdb "regression$dbname1" || createdb_status=$?
+createdb "regression$dbname2" || createdb_status=$?
+createdb "regression$dbname3" || createdb_status=$?
if "$MAKE" -C "$oldsrc" installcheck-parallel; then
oldpgversion=`psql -X -A -t -d regression -c "SHOW server_version_num"`
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 88ae9e7..de75b9a 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -43,7 +43,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
FROM SQL WITH FUNCTION prsd_lextype(internal),
TO SQL WITH FUNCTION int4recv(internal));
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
-CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
+CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the
tables
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
@@ -425,7 +425,7 @@ WITH objects (type, name, args) AS (VALUES
('access method', '{btree}', '{}'),
('publication', '{addr_pub}', '{}'),
('publication relation', '{addr_nsp, gentable}', '{addr_pub}'),
- ('subscription', '{addr_sub}', '{}'),
+ ('subscription', '{regress_addr_sub}', '{}'),
('statistics object', '{addr_nsp, gentable_stat}', '{}')
)
SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
@@ -484,7 +484,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
text search parser | addr_nsp | addr_ts_prs | addr_nsp.addr_ts_prs
| t
text search configuration | addr_nsp | addr_ts_conf | addr_nsp.addr_ts_conf
| t
text search template | addr_nsp | addr_ts_temp | addr_nsp.addr_ts_temp
| t
- subscription | | addr_sub | addr_sub
| t
+ subscription | | regress_addr_sub | regress_addr_sub
| t
publication | | addr_pub | addr_pub
| t
publication relation | | | addr_nsp.gentable in publication addr_pub
| t
(49 rows)
@@ -499,7 +499,7 @@ drop cascades to foreign table genftable
drop cascades to server integer
drop cascades to user mapping for regress_addr_user on server integer
DROP PUBLICATION addr_pub;
-DROP SUBSCRIPTION addr_sub;
+DROP SUBSCRIPTION regress_addr_sub;
DROP SCHEMA addr_nsp CASCADE;
NOTICE: drop cascades to 14 other objects
DETAIL: drop cascades to text search dictionary addr_ts_dict
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 6d2b524..03c1a25 100644
--- a/src/test/regress/expected/rolenames.out
+++ b/src/test/regress/expected/rolenames.out
@@ -37,11 +37,19 @@ SELECT r.rolname, s.srvname, m.umoptions
JOIN pg_foreign_server s ON (s.oid = m.umserver)
ORDER BY 2;
$$ LANGUAGE SQL;
+--
+-- We test creation and use of these role names to ensure that the server
+-- correctly distinguishes role keywords from quoted names that look like
+-- those keywords. In a test environment, creation of these roles may
+-- provoke warnings, so hide the warnings by raising client_min_messages.
+--
+SET client_min_messages = ERROR;
CREATE ROLE "Public";
CREATE ROLE "None";
CREATE ROLE "current_user";
CREATE ROLE "session_user";
CREATE ROLE "user";
+RESET client_min_messages;
CREATE ROLE current_user; -- error
ERROR: CURRENT_USER cannot be used as a role name here
LINE 1: CREATE ROLE current_user;
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 5ec3b40..e7add9d 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -6,31 +6,31 @@ CREATE ROLE regress_subscription_user2;
CREATE ROLE regress_subscription_user_dummy LOGIN NOSUPERUSER;
SET SESSION AUTHORIZATION 'regress_subscription_user';
-- fail - no publications
-CREATE SUBSCRIPTION testsub CONNECTION 'foo';
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'foo';
ERROR: syntax error at or near ";"
-LINE 1: CREATE SUBSCRIPTION testsub CONNECTION 'foo';
- ^
+LINE 1: CREATE SUBSCRIPTION regress_testsub CONNECTION 'foo';
+ ^
-- fail - no connection
-CREATE SUBSCRIPTION testsub PUBLICATION foo;
+CREATE SUBSCRIPTION regress_testsub PUBLICATION foo;
ERROR: syntax error at or near "PUBLICATION"
-LINE 1: CREATE SUBSCRIPTION testsub PUBLICATION foo;
- ^
+LINE 1: CREATE SUBSCRIPTION regress_testsub PUBLICATION foo;
+ ^
-- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block
BEGIN;
-CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot);
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot);
ERROR: CREATE SUBSCRIPTION ... WITH (create_slot = true) cannot run inside a transaction block
COMMIT;
-- fail - invalid connection string
-CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub;
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'testconn' PUBLICATION testpub;
ERROR: invalid connection string syntax: missing "=" after "testconn" in connection info string
-- fail - duplicate publications
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false);
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo, testpub, foo WITH
(connect= false);
ERROR: publication name "foo" used more than once
-- ok
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the
tables
-COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
+COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
obj_description
-------------------
@@ -38,123 +38,123 @@ SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
(1 row)
-- fail - name already exists
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
-ERROR: subscription "testsub" already exists
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false);
+ERROR: subscription "regress_testsub" already exists
-- fail - must be superuser
SET SESSION AUTHORIZATION 'regress_subscription_user2';
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (connect = false);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo WITH (connect = false);
ERROR: must be superuser to create subscriptions
SET SESSION AUTHORIZATION 'regress_subscription_user';
-- fail - invalid option combinations
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, copy_data =
true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false,copy_data = true);
ERROR: connect = false and copy_data = true are mutually exclusive options
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, enabled =
true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false,enabled = true);
ERROR: connect = false and enabled = true are mutually exclusive options
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot =
true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false,create_slot = true);
ERROR: connect = false and create_slot = true are mutually exclusive options
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled =
true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,enabled = true);
ERROR: slot_name = NONE and enabled = true are mutually exclusive options
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot
=true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,create_slot = true);
ERROR: slot_name = NONE and create_slot = true are mutually exclusive options
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE);
ERROR: subscription with slot_name = NONE must also set enabled = false
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled =
false);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,enabled = false);
ERROR: subscription with slot_name = NONE must also set create_slot = false
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot
=false);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,create_slot = false);
ERROR: subscription with slot_name = NONE must also set enabled = false
-- ok - with slot_name = NONE
-CREATE SUBSCRIPTION testsub3 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect =
false);
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,connect = false);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the
tables
-- fail
-ALTER SUBSCRIPTION testsub3 ENABLE;
+ALTER SUBSCRIPTION regress_testsub3 ENABLE;
ERROR: cannot enable subscription that does not have a slot name
-ALTER SUBSCRIPTION testsub3 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION;
ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptions
-DROP SUBSCRIPTION testsub3;
+DROP SUBSCRIPTION regress_testsub3;
-- fail - invalid connection string
-ALTER SUBSCRIPTION testsub CONNECTION 'foobar';
+ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar';
ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
----------+---------------------------+---------+-------------+--------------------+---------------------
- testsub | regress_subscription_user | f | {testpub} | off | dbname=doesnotexist
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
+-----------------+---------------------------+---------+-------------+--------------------+-----------------------------
+ regress_testsub | regress_subscription_user | f | {testpub} | off |
dbname=regress_doesnotexist
(1 row)
-ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
-ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2';
-ALTER SUBSCRIPTION testsub SET (slot_name = 'newname');
+ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
+ALTER SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist2';
+ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname');
-- fail
-ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2';
-ERROR: subscription "doesnotexist" does not exist
-ALTER SUBSCRIPTION testsub SET (create_slot = false);
+ALTER SUBSCRIPTION regress_doesnotexist CONNECTION 'dbname=regress_doesnotexist2';
+ERROR: subscription "regress_doesnotexist" does not exist
+ALTER SUBSCRIPTION regress_testsub SET (create_slot = false);
ERROR: unrecognized subscription parameter: "create_slot"
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
----------+---------------------------+---------+---------------------+--------------------+----------------------
- testsub | regress_subscription_user | f | {testpub2,testpub3} | off | dbname=doesnotexist2
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
+-----------------+---------------------------+---------+---------------------+--------------------+------------------------------
+ regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | off |
dbname=regress_doesnotexist2
(1 row)
BEGIN;
-ALTER SUBSCRIPTION testsub ENABLE;
+ALTER SUBSCRIPTION regress_testsub ENABLE;
\dRs
- List of subscriptions
- Name | Owner | Enabled | Publication
----------+---------------------------+---------+---------------------
- testsub | regress_subscription_user | t | {testpub2,testpub3}
+ List of subscriptions
+ Name | Owner | Enabled | Publication
+-----------------+---------------------------+---------+---------------------
+ regress_testsub | regress_subscription_user | t | {testpub2,testpub3}
(1 row)
-ALTER SUBSCRIPTION testsub DISABLE;
+ALTER SUBSCRIPTION regress_testsub DISABLE;
\dRs
- List of subscriptions
- Name | Owner | Enabled | Publication
----------+---------------------------+---------+---------------------
- testsub | regress_subscription_user | f | {testpub2,testpub3}
+ List of subscriptions
+ Name | Owner | Enabled | Publication
+-----------------+---------------------------+---------+---------------------
+ regress_testsub | regress_subscription_user | f | {testpub2,testpub3}
(1 row)
COMMIT;
-- fail - must be owner of subscription
SET ROLE regress_subscription_user_dummy;
-ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy;
-ERROR: must be owner of subscription testsub
+ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_dummy;
+ERROR: must be owner of subscription regress_testsub
RESET ROLE;
-ALTER SUBSCRIPTION testsub RENAME TO testsub_foo;
-ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = local);
-ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = foobar);
+ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_foo;
+ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = local);
+ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = foobar);
ERROR: invalid value for parameter "synchronous_commit": "foobar"
HINT: Available values: local, remote_write, remote_apply, on, off.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
--------------+---------------------------+---------+---------------------+--------------------+----------------------
- testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | local | dbname=doesnotexist2
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Synchronous commit |
Conninfo
+---------------------+---------------------------+---------+---------------------+--------------------+------------------------------
+ regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | local |
dbname=regress_doesnotexist2
(1 row)
-- rename back to keep the rest simple
-ALTER SUBSCRIPTION testsub_foo RENAME TO testsub;
+ALTER SUBSCRIPTION regress_testsub_foo RENAME TO regress_testsub;
-- fail - new owner must be superuser
-ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
-ERROR: permission denied to change owner of subscription "testsub"
+ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2;
+ERROR: permission denied to change owner of subscription "regress_testsub"
HINT: The owner of a subscription must be a superuser.
ALTER ROLE regress_subscription_user2 SUPERUSER;
-- now it works
-ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
+ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2;
-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name
BEGIN;
-DROP SUBSCRIPTION testsub;
+DROP SUBSCRIPTION regress_testsub;
ERROR: DROP SUBSCRIPTION cannot run inside a transaction block
COMMIT;
-ALTER SUBSCRIPTION testsub SET (slot_name = NONE);
+ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
-- now it works
BEGIN;
-DROP SUBSCRIPTION testsub;
+DROP SUBSCRIPTION regress_testsub;
COMMIT;
-DROP SUBSCRIPTION IF EXISTS testsub;
-NOTICE: subscription "testsub" does not exist, skipping
-DROP SUBSCRIPTION testsub; -- fail
-ERROR: subscription "testsub" does not exist
+DROP SUBSCRIPTION IF EXISTS regress_testsub;
+NOTICE: subscription "regress_testsub" does not exist, skipping
+DROP SUBSCRIPTION regress_testsub; -- fail
+ERROR: subscription "regress_testsub" does not exist
RESET SESSION AUTHORIZATION;
DROP ROLE regress_subscription_user;
DROP ROLE regress_subscription_user2;
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 1bfaf54..bd94cd6 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -46,7 +46,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
FROM SQL WITH FUNCTION prsd_lextype(internal),
TO SQL WITH FUNCTION int4recv(internal));
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
-CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
+CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
@@ -195,7 +195,7 @@ WITH objects (type, name, args) AS (VALUES
('access method', '{btree}', '{}'),
('publication', '{addr_pub}', '{}'),
('publication relation', '{addr_nsp, gentable}', '{addr_pub}'),
- ('subscription', '{addr_sub}', '{}'),
+ ('subscription', '{regress_addr_sub}', '{}'),
('statistics object', '{addr_nsp, gentable_stat}', '{}')
)
SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
@@ -212,7 +212,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
---
DROP FOREIGN DATA WRAPPER addr_fdw CASCADE;
DROP PUBLICATION addr_pub;
-DROP SUBSCRIPTION addr_sub;
+DROP SUBSCRIPTION regress_addr_sub;
DROP SCHEMA addr_nsp CASCADE;
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
index b285456..5a3cf44 100644
--- a/src/test/regress/sql/rolenames.sql
+++ b/src/test/regress/sql/rolenames.sql
@@ -40,12 +40,22 @@ SELECT r.rolname, s.srvname, m.umoptions
ORDER BY 2;
$$ LANGUAGE SQL;
+--
+-- We test creation and use of these role names to ensure that the server
+-- correctly distinguishes role keywords from quoted names that look like
+-- those keywords. In a test environment, creation of these roles may
+-- provoke warnings, so hide the warnings by raising client_min_messages.
+--
+SET client_min_messages = ERROR;
+
CREATE ROLE "Public";
CREATE ROLE "None";
CREATE ROLE "current_user";
CREATE ROLE "session_user";
CREATE ROLE "user";
+RESET client_min_messages;
+
CREATE ROLE current_user; -- error
CREATE ROLE current_role; -- error
CREATE ROLE session_user; -- error
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 36fa1bb..9e234ab 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -8,75 +8,75 @@ CREATE ROLE regress_subscription_user_dummy LOGIN NOSUPERUSER;
SET SESSION AUTHORIZATION 'regress_subscription_user';
-- fail - no publications
-CREATE SUBSCRIPTION testsub CONNECTION 'foo';
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'foo';
-- fail - no connection
-CREATE SUBSCRIPTION testsub PUBLICATION foo;
+CREATE SUBSCRIPTION regress_testsub PUBLICATION foo;
-- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block
BEGIN;
-CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot);
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot);
COMMIT;
-- fail - invalid connection string
-CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub;
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'testconn' PUBLICATION testpub;
-- fail - duplicate publications
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false);
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo, testpub, foo WITH
(connect= false);
-- ok
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false);
-COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
+COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
-- fail - name already exists
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false);
-- fail - must be superuser
SET SESSION AUTHORIZATION 'regress_subscription_user2';
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (connect = false);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo WITH (connect = false);
SET SESSION AUTHORIZATION 'regress_subscription_user';
-- fail - invalid option combinations
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, copy_data =
true);
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, enabled =
true);
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot =
true);
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled =
true);
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot
=true);
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE);
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled =
false);
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot
=false);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false,copy_data = true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false,enabled = true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
false,create_slot = true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,enabled = true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,create_slot = true);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,enabled = false);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,create_slot = false);
-- ok - with slot_name = NONE
-CREATE SUBSCRIPTION testsub3 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect =
false);
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name =
NONE,connect = false);
-- fail
-ALTER SUBSCRIPTION testsub3 ENABLE;
-ALTER SUBSCRIPTION testsub3 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION regress_testsub3 ENABLE;
+ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION;
-DROP SUBSCRIPTION testsub3;
+DROP SUBSCRIPTION regress_testsub3;
-- fail - invalid connection string
-ALTER SUBSCRIPTION testsub CONNECTION 'foobar';
+ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar';
\dRs+
-ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
-ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2';
-ALTER SUBSCRIPTION testsub SET (slot_name = 'newname');
+ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
+ALTER SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist2';
+ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname');
-- fail
-ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2';
-ALTER SUBSCRIPTION testsub SET (create_slot = false);
+ALTER SUBSCRIPTION regress_doesnotexist CONNECTION 'dbname=regress_doesnotexist2';
+ALTER SUBSCRIPTION regress_testsub SET (create_slot = false);
\dRs+
BEGIN;
-ALTER SUBSCRIPTION testsub ENABLE;
+ALTER SUBSCRIPTION regress_testsub ENABLE;
\dRs
-ALTER SUBSCRIPTION testsub DISABLE;
+ALTER SUBSCRIPTION regress_testsub DISABLE;
\dRs
@@ -84,38 +84,38 @@ COMMIT;
-- fail - must be owner of subscription
SET ROLE regress_subscription_user_dummy;
-ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy;
+ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_dummy;
RESET ROLE;
-ALTER SUBSCRIPTION testsub RENAME TO testsub_foo;
-ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = local);
-ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = foobar);
+ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_foo;
+ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = local);
+ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = foobar);
\dRs+
-- rename back to keep the rest simple
-ALTER SUBSCRIPTION testsub_foo RENAME TO testsub;
+ALTER SUBSCRIPTION regress_testsub_foo RENAME TO regress_testsub;
-- fail - new owner must be superuser
-ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
+ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2;
ALTER ROLE regress_subscription_user2 SUPERUSER;
-- now it works
-ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
+ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2;
-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name
BEGIN;
-DROP SUBSCRIPTION testsub;
+DROP SUBSCRIPTION regress_testsub;
COMMIT;
-ALTER SUBSCRIPTION testsub SET (slot_name = NONE);
+ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
-- now it works
BEGIN;
-DROP SUBSCRIPTION testsub;
+DROP SUBSCRIPTION regress_testsub;
COMMIT;
-DROP SUBSCRIPTION IF EXISTS testsub;
-DROP SUBSCRIPTION testsub; -- fail
+DROP SUBSCRIPTION IF EXISTS regress_testsub;
+DROP SUBSCRIPTION regress_testsub; -- fail
RESET SESSION AUTHORIZATION;
DROP ROLE regress_subscription_user;
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> We could make the new subdirectory be something specific like
>> "src/test/modules/test_rolenames", but I think very likely we'll be
>> wanting some additional test scripts that we likewise deem unsafe to
>> run during "installcheck". So I'd rather choose a more generic module
>> name, but I'm not sure what ... "unsafe_tests"?
> Agreed but haven't got any particularly good suggestions on names..
Hearing no better suggestions, I went with "unsafe_tests" in the
attached.
This patch just moves rolenames.sql lock-stock-and-barrel into
src/test/modules/unsafe_tests. Another approach would be to split
the test script into a portion that doesn't violate any installcheck
rule and could be kept in the core tests, versus the unsafe tests.
I lack the interest to do that, but if somebody else is excited enough
about it, have at it.
I'm wondering whether we ought to back-patch this. The odds that
somebody would be affected by "make installcheck" resetting the
application_name property of existing roles seem pretty small,
but it could be nasty if it did matter. Perhaps squeezing this into
v12 is good enough.
Another idea would be to just take out the ALTER USER ALL tests
in the back branches.
Thoughts?
regards, tom lane
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index dfd0956..60d6d7b 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -19,6 +19,7 @@ SUBDIRS = \
test_rbtree \
test_rls_hooks \
test_shm_mq \
+ unsafe_tests \
worker_spi
$(recurse)
diff --git a/src/test/modules/unsafe_tests/.gitignore b/src/test/modules/unsafe_tests/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/src/test/modules/unsafe_tests/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/unsafe_tests/Makefile b/src/test/modules/unsafe_tests/Makefile
new file mode 100644
index 0000000..321252f
--- /dev/null
+++ b/src/test/modules/unsafe_tests/Makefile
@@ -0,0 +1,14 @@
+# src/test/modules/unsafe_tests/Makefile
+
+REGRESS = rolenames
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/unsafe_tests
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/unsafe_tests/README b/src/test/modules/unsafe_tests/README
new file mode 100644
index 0000000..a7e5b2a
--- /dev/null
+++ b/src/test/modules/unsafe_tests/README
@@ -0,0 +1,8 @@
+This directory doesn't actually contain any extension module.
+
+What it is is a home for regression tests that we don't want to run
+during "make installcheck" because they could have side-effects that
+seem undesirable for a production installation.
+
+An example is that rolenames.sql tests ALTER USER ALL and so could
+have effects on pre-existing roles.
diff --git a/src/test/modules/unsafe_tests/expected/rolenames.out
b/src/test/modules/unsafe_tests/expected/rolenames.out
new file mode 100644
index 0000000..03c1a25
--- /dev/null
+++ b/src/test/modules/unsafe_tests/expected/rolenames.out
@@ -0,0 +1,1010 @@
+CREATE OR REPLACE FUNCTION chkrolattr()
+ RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
+ AS $$
+SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
+ FROM pg_roles r
+ JOIN (VALUES(CURRENT_USER, 'current_user'),
+ (SESSION_USER, 'session_user'),
+ ('current_user', '-'),
+ ('session_user', '-'),
+ ('Public', '-'),
+ ('None', '-'))
+ AS v(uname, keyword)
+ ON (r.rolname = v.uname)
+ ORDER BY 1;
+$$ LANGUAGE SQL;
+CREATE OR REPLACE FUNCTION chksetconfig()
+ RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[])
+ AS $$
+SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'),
+ COALESCE(v.keyword, '-'), s.setconfig
+ FROM pg_db_role_setting s
+ LEFT JOIN pg_roles r ON (r.oid = s.setrole)
+ LEFT JOIN pg_database d ON (d.oid = s.setdatabase)
+ LEFT JOIN (VALUES(CURRENT_USER, 'current_user'),
+ (SESSION_USER, 'session_user'))
+ AS v(uname, keyword)
+ ON (r.rolname = v.uname)
+ WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2')
+ORDER BY 1, 2;
+$$ LANGUAGE SQL;
+CREATE OR REPLACE FUNCTION chkumapping()
+ RETURNS TABLE (umname name, umserver name, umoptions text[])
+ AS $$
+SELECT r.rolname, s.srvname, m.umoptions
+ FROM pg_user_mapping m
+ LEFT JOIN pg_roles r ON (r.oid = m.umuser)
+ JOIN pg_foreign_server s ON (s.oid = m.umserver)
+ ORDER BY 2;
+$$ LANGUAGE SQL;
+--
+-- We test creation and use of these role names to ensure that the server
+-- correctly distinguishes role keywords from quoted names that look like
+-- those keywords. In a test environment, creation of these roles may
+-- provoke warnings, so hide the warnings by raising client_min_messages.
+--
+SET client_min_messages = ERROR;
+CREATE ROLE "Public";
+CREATE ROLE "None";
+CREATE ROLE "current_user";
+CREATE ROLE "session_user";
+CREATE ROLE "user";
+RESET client_min_messages;
+CREATE ROLE current_user; -- error
+ERROR: CURRENT_USER cannot be used as a role name here
+LINE 1: CREATE ROLE current_user;
+ ^
+CREATE ROLE current_role; -- error
+ERROR: syntax error at or near "current_role"
+LINE 1: CREATE ROLE current_role;
+ ^
+CREATE ROLE session_user; -- error
+ERROR: SESSION_USER cannot be used as a role name here
+LINE 1: CREATE ROLE session_user;
+ ^
+CREATE ROLE user; -- error
+ERROR: syntax error at or near "user"
+LINE 1: CREATE ROLE user;
+ ^
+CREATE ROLE all; -- error
+ERROR: syntax error at or near "all"
+LINE 1: CREATE ROLE all;
+ ^
+CREATE ROLE public; -- error
+ERROR: role name "public" is reserved
+LINE 1: CREATE ROLE public;
+ ^
+CREATE ROLE "public"; -- error
+ERROR: role name "public" is reserved
+LINE 1: CREATE ROLE "public";
+ ^
+CREATE ROLE none; -- error
+ERROR: role name "none" is reserved
+LINE 1: CREATE ROLE none;
+ ^
+CREATE ROLE "none"; -- error
+ERROR: role name "none" is reserved
+LINE 1: CREATE ROLE "none";
+ ^
+CREATE ROLE pg_abc; -- error
+ERROR: role name "pg_abc" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE "pg_abc"; -- error
+ERROR: role name "pg_abc" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE pg_abcdef; -- error
+ERROR: role name "pg_abcdef" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE "pg_abcdef"; -- error
+ERROR: role name "pg_abcdef" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE regress_testrol0 SUPERUSER LOGIN;
+CREATE ROLE regress_testrolx SUPERUSER LOGIN;
+CREATE ROLE regress_testrol2 SUPERUSER;
+CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2;
+\c -
+SET SESSION AUTHORIZATION regress_testrol1;
+SET ROLE regress_testrol2;
+-- ALTER ROLE
+BEGIN;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | f
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | f
+ session_user | - | f | f
+(6 rows)
+
+ALTER ROLE CURRENT_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | f
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | f
+(6 rows)
+
+ALTER ROLE "current_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | f
+(6 rows)
+
+ALTER ROLE SESSION_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | t
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | f
+(6 rows)
+
+ALTER ROLE "session_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | t
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | t
+(6 rows)
+
+ALTER USER "Public" WITH REPLICATION;
+ALTER USER "None" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | t
+ Public | - | f | t
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | t
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | t
+(6 rows)
+
+ALTER USER regress_testrol1 WITH NOREPLICATION;
+ALTER USER regress_testrol2 WITH NOREPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | t
+ Public | - | f | t
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | f
+ session_user | - | f | t
+(6 rows)
+
+ROLLBACK;
+ALTER ROLE USER WITH LOGIN; -- error
+ERROR: syntax error at or near "USER"
+LINE 1: ALTER ROLE USER WITH LOGIN;
+ ^
+ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ALTER ROLE CURRENT_ROLE WITH LOGIN;
+ ^
+ALTER ROLE ALL WITH REPLICATION; -- error
+ERROR: syntax error at or near "WITH"
+LINE 1: ALTER ROLE ALL WITH REPLICATION;
+ ^
+ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
+ERROR: role "session_role" does not exist
+ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
+ERROR: role "public" does not exist
+ALTER ROLE "public" WITH NOREPLICATION; -- error
+ERROR: role "public" does not exist
+ALTER ROLE NONE WITH NOREPLICATION; -- error
+ERROR: role name "none" is reserved
+LINE 1: ALTER ROLE NONE WITH NOREPLICATION;
+ ^
+ALTER ROLE "none" WITH NOREPLICATION; -- error
+ERROR: role name "none" is reserved
+LINE 1: ALTER ROLE "none" WITH NOREPLICATION;
+ ^
+ALTER ROLE nonexistent WITH NOREPLICATION; -- error
+ERROR: role "nonexistent" does not exist
+-- ALTER USER
+BEGIN;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | f
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | f
+ session_user | - | f | f
+(6 rows)
+
+ALTER USER CURRENT_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | f
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | f
+(6 rows)
+
+ALTER USER "current_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | f
+(6 rows)
+
+ALTER USER SESSION_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | t
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | f
+(6 rows)
+
+ALTER USER "session_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | f
+ Public | - | f | f
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | t
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | t
+(6 rows)
+
+ALTER USER "Public" WITH REPLICATION;
+ALTER USER "None" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | t
+ Public | - | f | t
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | t
+ regress_testrol2 | current_user | f | t
+ session_user | - | f | t
+(6 rows)
+
+ALTER USER regress_testrol1 WITH NOREPLICATION;
+ALTER USER regress_testrol2 WITH NOREPLICATION;
+SELECT * FROM chkrolattr();
+ role | rolekeyword | canlogin | replication
+------------------+--------------+----------+-------------
+ None | - | f | t
+ Public | - | f | t
+ current_user | - | f | t
+ regress_testrol1 | session_user | t | f
+ regress_testrol2 | current_user | f | f
+ session_user | - | f | t
+(6 rows)
+
+ROLLBACK;
+ALTER USER USER WITH LOGIN; -- error
+ERROR: syntax error at or near "USER"
+LINE 1: ALTER USER USER WITH LOGIN;
+ ^
+ALTER USER CURRENT_ROLE WITH LOGIN; -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ALTER USER CURRENT_ROLE WITH LOGIN;
+ ^
+ALTER USER ALL WITH REPLICATION; -- error
+ERROR: syntax error at or near "WITH"
+LINE 1: ALTER USER ALL WITH REPLICATION;
+ ^
+ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
+ERROR: role "session_role" does not exist
+ALTER USER PUBLIC WITH NOREPLICATION; -- error
+ERROR: role "public" does not exist
+ALTER USER "public" WITH NOREPLICATION; -- error
+ERROR: role "public" does not exist
+ALTER USER NONE WITH NOREPLICATION; -- error
+ERROR: role name "none" is reserved
+LINE 1: ALTER USER NONE WITH NOREPLICATION;
+ ^
+ALTER USER "none" WITH NOREPLICATION; -- error
+ERROR: role name "none" is reserved
+LINE 1: ALTER USER "none" WITH NOREPLICATION;
+ ^
+ALTER USER nonexistent WITH NOREPLICATION; -- error
+ERROR: role "nonexistent" does not exist
+-- ALTER ROLE SET/RESET
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+----+------+------------+-----------
+(0 rows)
+
+ALTER ROLE CURRENT_USER SET application_name to 'FOO';
+ALTER ROLE SESSION_USER SET application_name to 'BAR';
+ALTER ROLE "current_user" SET application_name to 'FOOFOO';
+ALTER ROLE "Public" SET application_name to 'BARBAR';
+ALTER ROLE ALL SET application_name to 'SLAP';
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+-----+------------------+--------------+---------------------------
+ ALL | Public | - | {application_name=BARBAR}
+ ALL | current_user | - | {application_name=FOOFOO}
+ ALL | regress_testrol1 | session_user | {application_name=BAR}
+ ALL | regress_testrol2 | current_user | {application_name=FOO}
+(4 rows)
+
+ALTER ROLE regress_testrol1 SET application_name to 'SLAM';
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+-----+------------------+--------------+---------------------------
+ ALL | Public | - | {application_name=BARBAR}
+ ALL | current_user | - | {application_name=FOOFOO}
+ ALL | regress_testrol1 | session_user | {application_name=SLAM}
+ ALL | regress_testrol2 | current_user | {application_name=FOO}
+(4 rows)
+
+ALTER ROLE CURRENT_USER RESET application_name;
+ALTER ROLE SESSION_USER RESET application_name;
+ALTER ROLE "current_user" RESET application_name;
+ALTER ROLE "Public" RESET application_name;
+ALTER ROLE ALL RESET application_name;
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+----+------+------------+-----------
+(0 rows)
+
+ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ';
+ ^
+ALTER ROLE USER SET application_name to 'BOOM'; -- error
+ERROR: syntax error at or near "USER"
+LINE 1: ALTER ROLE USER SET application_name to 'BOOM';
+ ^
+ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error
+ERROR: role "public" does not exist
+ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error
+ERROR: role "nonexistent" does not exist
+-- ALTER USER SET/RESET
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+----+------+------------+-----------
+(0 rows)
+
+ALTER USER CURRENT_USER SET application_name to 'FOO';
+ALTER USER SESSION_USER SET application_name to 'BAR';
+ALTER USER "current_user" SET application_name to 'FOOFOO';
+ALTER USER "Public" SET application_name to 'BARBAR';
+ALTER USER ALL SET application_name to 'SLAP';
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+-----+------------------+--------------+---------------------------
+ ALL | Public | - | {application_name=BARBAR}
+ ALL | current_user | - | {application_name=FOOFOO}
+ ALL | regress_testrol1 | session_user | {application_name=BAR}
+ ALL | regress_testrol2 | current_user | {application_name=FOO}
+(4 rows)
+
+ALTER USER regress_testrol1 SET application_name to 'SLAM';
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+-----+------------------+--------------+---------------------------
+ ALL | Public | - | {application_name=BARBAR}
+ ALL | current_user | - | {application_name=FOOFOO}
+ ALL | regress_testrol1 | session_user | {application_name=SLAM}
+ ALL | regress_testrol2 | current_user | {application_name=FOO}
+(4 rows)
+
+ALTER USER CURRENT_USER RESET application_name;
+ALTER USER SESSION_USER RESET application_name;
+ALTER USER "current_user" RESET application_name;
+ALTER USER "Public" RESET application_name;
+ALTER USER ALL RESET application_name;
+SELECT * FROM chksetconfig();
+ db | role | rolkeyword | setconfig
+----+------+------------+-----------
+(0 rows)
+
+ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error
+ALTER USER USER SET application_name to 'BOOM'; -- error
+ERROR: syntax error at or near "USER"
+LINE 1: ALTER USER USER SET application_name to 'BOOM';
+ ^
+ALTER USER PUBLIC SET application_name to 'BOMB'; -- error
+ERROR: role "public" does not exist
+ALTER USER NONE SET application_name to 'BOMB'; -- error
+ERROR: role name "none" is reserved
+LINE 1: ALTER USER NONE SET application_name to 'BOMB';
+ ^
+ALTER USER nonexistent SET application_name to 'BOMB'; -- error
+ERROR: role "nonexistent" does not exist
+-- CREATE SCHEMA
+CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER;
+CREATE SCHEMA newschema2 AUTHORIZATION "current_user";
+CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER;
+CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx;
+CREATE SCHEMA newschema5 AUTHORIZATION "Public";
+CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error
+ERROR: syntax error at or near "USER"
+LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER;
+ ^
+CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE;
+ ^
+CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error
+ERROR: role "public" does not exist
+CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error
+ERROR: role "public" does not exist
+CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error
+ERROR: role name "none" is reserved
+LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE;
+ ^
+CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error
+ERROR: role "nonexistent" does not exist
+SELECT n.nspname, r.rolname FROM pg_namespace n
+ JOIN pg_roles r ON (r.oid = n.nspowner)
+ WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
+ nspname | rolname
+------------+------------------
+ newschema1 | regress_testrol2
+ newschema2 | current_user
+ newschema3 | regress_testrol1
+ newschema4 | regress_testrolx
+ newschema5 | Public
+(5 rows)
+
+CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER;
+NOTICE: schema "newschema1" already exists, skipping
+CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user";
+NOTICE: schema "newschema2" already exists, skipping
+CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER;
+NOTICE: schema "newschema3" already exists, skipping
+CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx;
+NOTICE: schema "newschema4" already exists, skipping
+CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public";
+NOTICE: schema "newschema5" already exists, skipping
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error
+ERROR: syntax error at or near "USER"
+LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER;
+ ^
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO...
+ ^
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error
+ERROR: role "public" does not exist
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error
+ERROR: role "public" does not exist
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error
+ERROR: role name "none" is reserved
+LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE;
+ ^
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error
+ERROR: role "nonexistent" does not exist
+SELECT n.nspname, r.rolname FROM pg_namespace n
+ JOIN pg_roles r ON (r.oid = n.nspowner)
+ WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
+ nspname | rolname
+------------+------------------
+ newschema1 | regress_testrol2
+ newschema2 | current_user
+ newschema3 | regress_testrol1
+ newschema4 | regress_testrolx
+ newschema5 | Public
+(5 rows)
+
+-- ALTER TABLE OWNER TO
+\c -
+SET SESSION AUTHORIZATION regress_testrol0;
+CREATE TABLE testtab1 (a int);
+CREATE TABLE testtab2 (a int);
+CREATE TABLE testtab3 (a int);
+CREATE TABLE testtab4 (a int);
+CREATE TABLE testtab5 (a int);
+CREATE TABLE testtab6 (a int);
+\c -
+SET SESSION AUTHORIZATION regress_testrol1;
+SET ROLE regress_testrol2;
+ALTER TABLE testtab1 OWNER TO CURRENT_USER;
+ALTER TABLE testtab2 OWNER TO "current_user";
+ALTER TABLE testtab3 OWNER TO SESSION_USER;
+ALTER TABLE testtab4 OWNER TO regress_testrolx;
+ALTER TABLE testtab5 OWNER TO "Public";
+ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE;
+ ^
+ALTER TABLE testtab6 OWNER TO USER; --error
+ERROR: syntax error at or near "USER"
+LINE 1: ALTER TABLE testtab6 OWNER TO USER;
+ ^
+ALTER TABLE testtab6 OWNER TO PUBLIC; -- error
+ERROR: role "public" does not exist
+ALTER TABLE testtab6 OWNER TO "public"; -- error
+ERROR: role "public" does not exist
+ALTER TABLE testtab6 OWNER TO nonexistent; -- error
+ERROR: role "nonexistent" does not exist
+SELECT c.relname, r.rolname
+ FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner)
+ WHERE relname LIKE 'testtab_'
+ ORDER BY 1;
+ relname | rolname
+----------+------------------
+ testtab1 | regress_testrol2
+ testtab2 | current_user
+ testtab3 | regress_testrol1
+ testtab4 | regress_testrolx
+ testtab5 | Public
+ testtab6 | regress_testrol0
+(6 rows)
+
+-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are
+-- changed their owner in the same way.
+-- ALTER AGGREGATE
+\c -
+SET SESSION AUTHORIZATION regress_testrol0;
+CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
+ERROR: function "testagg5" already exists with same argument types
+CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8);
+\c -
+SET SESSION AUTHORIZATION regress_testrol1;
+SET ROLE regress_testrol2;
+ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER;
+ALTER AGGREGATE testagg2(int2) OWNER TO "current_user";
+ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER;
+ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx;
+ALTER AGGREGATE testagg5(int2) OWNER TO "Public";
+ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE;
+ ^
+ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error
+ERROR: syntax error at or near "USER"
+LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER;
+ ^
+ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error
+ERROR: role "public" does not exist
+ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error
+ERROR: role "public" does not exist
+ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error
+ERROR: role "nonexistent" does not exist
+SELECT p.proname, r.rolname
+ FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner)
+ WHERE proname LIKE 'testagg_'
+ ORDER BY 1;
+ proname | rolname
+----------+------------------
+ testagg1 | regress_testrol2
+ testagg2 | current_user
+ testagg3 | regress_testrol1
+ testagg4 | regress_testrolx
+ testagg5 | Public
+ testagg6 | regress_testrol0
+ testagg7 | regress_testrol0
+ testagg8 | regress_testrol0
+ testagg9 | regress_testrol0
+(9 rows)
+
+-- CREATE USER MAPPING
+CREATE FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper;
+CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
+CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
+CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
+CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
+CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
+CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
+CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
+CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
+CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
+ OPTIONS (user 'CURRENT_ROLE'); -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
+ ^
+CREATE USER MAPPING FOR nonexistent SERVER sv9
+ OPTIONS (user 'nonexistent'); -- error;
+ERROR: role "nonexistent" does not exist
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+---------------------------
+ regress_testrol2 | sv1 | {user=CURRENT_USER}
+ current_user | sv2 | {"user=\"current_user\""}
+ regress_testrol2 | sv3 | {user=USER}
+ user | sv4 | {"user=\"USER\""}
+ regress_testrol1 | sv5 | {user=SESSION_USER}
+ | sv6 | {user=PUBLIC}
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(8 rows)
+
+-- ALTER USER MAPPING
+ALTER USER MAPPING FOR CURRENT_USER SERVER sv1
+ OPTIONS (SET user 'CURRENT_USER_alt');
+ALTER USER MAPPING FOR "current_user" SERVER sv2
+ OPTIONS (SET user '"current_user"_alt');
+ALTER USER MAPPING FOR USER SERVER sv3
+ OPTIONS (SET user 'USER_alt');
+ALTER USER MAPPING FOR "user" SERVER sv4
+ OPTIONS (SET user '"user"_alt');
+ALTER USER MAPPING FOR SESSION_USER SERVER sv5
+ OPTIONS (SET user 'SESSION_USER_alt');
+ALTER USER MAPPING FOR PUBLIC SERVER sv6
+ OPTIONS (SET user 'public_alt');
+ALTER USER MAPPING FOR "Public" SERVER sv7
+ OPTIONS (SET user '"Public"_alt');
+ALTER USER MAPPING FOR regress_testrolx SERVER sv8
+ OPTIONS (SET user 'regress_testrolx_alt');
+ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
+ OPTIONS (SET user 'CURRENT_ROLE_alt');
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
+ ^
+ALTER USER MAPPING FOR nonexistent SERVER sv9
+ OPTIONS (SET user 'nonexistent_alt'); -- error
+ERROR: role "nonexistent" does not exist
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+-------------------------------
+ regress_testrol2 | sv1 | {user=CURRENT_USER_alt}
+ current_user | sv2 | {"user=\"current_user\"_alt"}
+ regress_testrol2 | sv3 | {user=USER_alt}
+ user | sv4 | {"user=\"user\"_alt"}
+ regress_testrol1 | sv5 | {user=SESSION_USER_alt}
+ | sv6 | {user=public_alt}
+ Public | sv7 | {"user=\"Public\"_alt"}
+ regress_testrolx | sv8 | {user=regress_testrolx_alt}
+(8 rows)
+
+-- DROP USER MAPPING
+DROP USER MAPPING FOR CURRENT_USER SERVER sv1;
+DROP USER MAPPING FOR "current_user" SERVER sv2;
+DROP USER MAPPING FOR USER SERVER sv3;
+DROP USER MAPPING FOR "user" SERVER sv4;
+DROP USER MAPPING FOR SESSION_USER SERVER sv5;
+DROP USER MAPPING FOR PUBLIC SERVER sv6;
+DROP USER MAPPING FOR "Public" SERVER sv7;
+DROP USER MAPPING FOR regress_testrolx SERVER sv8;
+DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9;
+ ^
+DROP USER MAPPING FOR nonexistent SERVER sv; -- error
+ERROR: role "nonexistent" does not exist
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+--------+----------+-----------
+(0 rows)
+
+CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
+CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
+CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
+CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
+CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
+CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
+CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
+CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+---------------------------
+ regress_testrol2 | sv1 | {user=CURRENT_USER}
+ current_user | sv2 | {"user=\"current_user\""}
+ regress_testrol2 | sv3 | {user=USER}
+ user | sv4 | {"user=\"USER\""}
+ regress_testrol1 | sv5 | {user=SESSION_USER}
+ | sv6 | {user=PUBLIC}
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(8 rows)
+
+-- DROP USER MAPPING IF EXISTS
+DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+---------------------------
+ current_user | sv2 | {"user=\"current_user\""}
+ regress_testrol2 | sv3 | {user=USER}
+ user | sv4 | {"user=\"USER\""}
+ regress_testrol1 | sv5 | {user=SESSION_USER}
+ | sv6 | {user=PUBLIC}
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(7 rows)
+
+DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+-------------------------
+ regress_testrol2 | sv3 | {user=USER}
+ user | sv4 | {"user=\"USER\""}
+ regress_testrol1 | sv5 | {user=SESSION_USER}
+ | sv6 | {user=PUBLIC}
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(6 rows)
+
+DROP USER MAPPING IF EXISTS FOR USER SERVER sv3;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+-------------------------
+ user | sv4 | {"user=\"USER\""}
+ regress_testrol1 | sv5 | {user=SESSION_USER}
+ | sv6 | {user=PUBLIC}
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(5 rows)
+
+DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+-------------------------
+ regress_testrol1 | sv5 | {user=SESSION_USER}
+ | sv6 | {user=PUBLIC}
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(4 rows)
+
+DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+-------------------------
+ | sv6 | {user=PUBLIC}
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(3 rows)
+
+DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+-------------------------
+ Public | sv7 | {"user=\"Public\""}
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(2 rows)
+
+DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+------------------+----------+-------------------------
+ regress_testrolx | sv8 | {user=regress_testrolx}
+(1 row)
+
+DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8;
+SELECT * FROM chkumapping();
+ umname | umserver | umoptions
+--------+----------+-----------
+(0 rows)
+
+DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9;
+ ^
+DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
+NOTICE: role "nonexistent" does not exist, skipping
+-- GRANT/REVOKE
+GRANT regress_testrol0 TO pg_signal_backend; -- success
+SET ROLE pg_signal_backend; --success
+RESET ROLE;
+CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success
+SET ROLE regress_testrol2;
+UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+ proname | proacl
+----------+--------
+ testagg1 |
+ testagg2 |
+ testagg3 |
+ testagg4 |
+ testagg5 |
+ testagg6 |
+ testagg7 |
+ testagg8 |
+ testagg9 |
+(9 rows)
+
+REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC;
+GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC;
+GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
+GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user";
+GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER;
+GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public";
+GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx;
+GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public";
+GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2)
+ TO current_user, public, regress_testrolx;
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+ proname | proacl
+----------+-----------------------------------------------------------------------------------------------------------------------------------
+ testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
+ testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
+ testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
+ testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
+ testagg5 | {Public=X/Public}
+ testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
+ testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
+ testagg8 |
{regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
+ testagg9 |
+(9 rows)
+
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO...
+ ^
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error
+ERROR: syntax error at or near "USER"
+LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER;
+ ^
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error
+ERROR: role name "none" is reserved
+LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE;
+ ^
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error
+ERROR: role name "none" is reserved
+LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none";
+ ^
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+ proname | proacl
+----------+-----------------------------------------------------------------------------------------------------------------------------------
+ testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
+ testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
+ testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
+ testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
+ testagg5 | {Public=X/Public}
+ testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
+ testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
+ testagg8 |
{regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
+ testagg9 |
+(9 rows)
+
+REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user";
+REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public";
+REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public";
+REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2)
+ FROM current_user, public, regress_testrolx;
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+ proname | proacl
+----------+---------------------------------------
+ testagg1 | {regress_testrol2=X/regress_testrol2}
+ testagg2 | {current_user=X/current_user}
+ testagg3 | {regress_testrol1=X/regress_testrol1}
+ testagg4 | {regress_testrolx=X/regress_testrolx}
+ testagg5 | {}
+ testagg6 | {regress_testrol0=X/regress_testrol0}
+ testagg7 | {regress_testrol0=X/regress_testrol0}
+ testagg8 | {regress_testrol0=X/regress_testrol0}
+ testagg9 |
+(9 rows)
+
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error
+ERROR: syntax error at or near "CURRENT_ROLE"
+LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO...
+ ^
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error
+ERROR: syntax error at or near "USER"
+LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER;
+ ^
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error
+ERROR: role name "none" is reserved
+LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE;
+ ^
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
+ERROR: role name "none" is reserved
+LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none";
+ ^
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+ proname | proacl
+----------+---------------------------------------
+ testagg1 | {regress_testrol2=X/regress_testrol2}
+ testagg2 | {current_user=X/current_user}
+ testagg3 | {regress_testrol1=X/regress_testrol1}
+ testagg4 | {regress_testrolx=X/regress_testrolx}
+ testagg5 | {}
+ testagg6 | {regress_testrol0=X/regress_testrol0}
+ testagg7 | {regress_testrol0=X/regress_testrol0}
+ testagg8 | {regress_testrol0=X/regress_testrol0}
+ testagg9 |
+(9 rows)
+
+-- DEFAULT MONITORING ROLES
+CREATE ROLE regress_role_haspriv;
+CREATE ROLE regress_role_nopriv;
+-- pg_read_all_stats
+GRANT pg_read_all_stats TO regress_role_haspriv;
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- returns true with role member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+ WHERE query = '<insufficient privilege>';
+ haspriv
+---------
+ t
+(1 row)
+
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- returns false with role not member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+ WHERE query = '<insufficient privilege>';
+ haspriv
+---------
+ f
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_read_all_stats FROM regress_role_haspriv;
+-- pg_read_all_settings
+GRANT pg_read_all_settings TO regress_role_haspriv;
+BEGIN;
+-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
+SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- passes with role member of pg_read_all_settings
+SHOW session_preload_libraries;
+ session_preload_libraries
+-----------------------------
+ "path-to-preload-libraries"
+(1 row)
+
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- fails with role not member of pg_read_all_settings
+SHOW session_preload_libraries;
+ERROR: must be superuser or a member of pg_read_all_settings to examine "session_preload_libraries"
+RESET SESSION AUTHORIZATION;
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+REVOKE pg_read_all_settings FROM regress_role_haspriv;
+-- clean up
+\c
+DROP SCHEMA test_roles_schema;
+DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx
CASCADE;
+DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
+DROP ROLE "Public", "None", "current_user", "session_user", "user";
+DROP ROLE regress_role_haspriv, regress_role_nopriv;
diff --git a/src/test/modules/unsafe_tests/sql/rolenames.sql b/src/test/modules/unsafe_tests/sql/rolenames.sql
new file mode 100644
index 0000000..5a3cf44
--- /dev/null
+++ b/src/test/modules/unsafe_tests/sql/rolenames.sql
@@ -0,0 +1,488 @@
+CREATE OR REPLACE FUNCTION chkrolattr()
+ RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
+ AS $$
+SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
+ FROM pg_roles r
+ JOIN (VALUES(CURRENT_USER, 'current_user'),
+ (SESSION_USER, 'session_user'),
+ ('current_user', '-'),
+ ('session_user', '-'),
+ ('Public', '-'),
+ ('None', '-'))
+ AS v(uname, keyword)
+ ON (r.rolname = v.uname)
+ ORDER BY 1;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION chksetconfig()
+ RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[])
+ AS $$
+SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'),
+ COALESCE(v.keyword, '-'), s.setconfig
+ FROM pg_db_role_setting s
+ LEFT JOIN pg_roles r ON (r.oid = s.setrole)
+ LEFT JOIN pg_database d ON (d.oid = s.setdatabase)
+ LEFT JOIN (VALUES(CURRENT_USER, 'current_user'),
+ (SESSION_USER, 'session_user'))
+ AS v(uname, keyword)
+ ON (r.rolname = v.uname)
+ WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2')
+ORDER BY 1, 2;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION chkumapping()
+ RETURNS TABLE (umname name, umserver name, umoptions text[])
+ AS $$
+SELECT r.rolname, s.srvname, m.umoptions
+ FROM pg_user_mapping m
+ LEFT JOIN pg_roles r ON (r.oid = m.umuser)
+ JOIN pg_foreign_server s ON (s.oid = m.umserver)
+ ORDER BY 2;
+$$ LANGUAGE SQL;
+
+--
+-- We test creation and use of these role names to ensure that the server
+-- correctly distinguishes role keywords from quoted names that look like
+-- those keywords. In a test environment, creation of these roles may
+-- provoke warnings, so hide the warnings by raising client_min_messages.
+--
+SET client_min_messages = ERROR;
+
+CREATE ROLE "Public";
+CREATE ROLE "None";
+CREATE ROLE "current_user";
+CREATE ROLE "session_user";
+CREATE ROLE "user";
+
+RESET client_min_messages;
+
+CREATE ROLE current_user; -- error
+CREATE ROLE current_role; -- error
+CREATE ROLE session_user; -- error
+CREATE ROLE user; -- error
+CREATE ROLE all; -- error
+
+CREATE ROLE public; -- error
+CREATE ROLE "public"; -- error
+CREATE ROLE none; -- error
+CREATE ROLE "none"; -- error
+
+CREATE ROLE pg_abc; -- error
+CREATE ROLE "pg_abc"; -- error
+CREATE ROLE pg_abcdef; -- error
+CREATE ROLE "pg_abcdef"; -- error
+
+CREATE ROLE regress_testrol0 SUPERUSER LOGIN;
+CREATE ROLE regress_testrolx SUPERUSER LOGIN;
+CREATE ROLE regress_testrol2 SUPERUSER;
+CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2;
+
+\c -
+SET SESSION AUTHORIZATION regress_testrol1;
+SET ROLE regress_testrol2;
+
+-- ALTER ROLE
+BEGIN;
+SELECT * FROM chkrolattr();
+ALTER ROLE CURRENT_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER ROLE "current_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER ROLE SESSION_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER ROLE "session_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER USER "Public" WITH REPLICATION;
+ALTER USER "None" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER USER regress_testrol1 WITH NOREPLICATION;
+ALTER USER regress_testrol2 WITH NOREPLICATION;
+SELECT * FROM chkrolattr();
+ROLLBACK;
+
+ALTER ROLE USER WITH LOGIN; -- error
+ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
+ALTER ROLE ALL WITH REPLICATION; -- error
+ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
+ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
+ALTER ROLE "public" WITH NOREPLICATION; -- error
+ALTER ROLE NONE WITH NOREPLICATION; -- error
+ALTER ROLE "none" WITH NOREPLICATION; -- error
+ALTER ROLE nonexistent WITH NOREPLICATION; -- error
+
+-- ALTER USER
+BEGIN;
+SELECT * FROM chkrolattr();
+ALTER USER CURRENT_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER USER "current_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER USER SESSION_USER WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER USER "session_user" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER USER "Public" WITH REPLICATION;
+ALTER USER "None" WITH REPLICATION;
+SELECT * FROM chkrolattr();
+ALTER USER regress_testrol1 WITH NOREPLICATION;
+ALTER USER regress_testrol2 WITH NOREPLICATION;
+SELECT * FROM chkrolattr();
+ROLLBACK;
+
+ALTER USER USER WITH LOGIN; -- error
+ALTER USER CURRENT_ROLE WITH LOGIN; -- error
+ALTER USER ALL WITH REPLICATION; -- error
+ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
+ALTER USER PUBLIC WITH NOREPLICATION; -- error
+ALTER USER "public" WITH NOREPLICATION; -- error
+ALTER USER NONE WITH NOREPLICATION; -- error
+ALTER USER "none" WITH NOREPLICATION; -- error
+ALTER USER nonexistent WITH NOREPLICATION; -- error
+
+-- ALTER ROLE SET/RESET
+SELECT * FROM chksetconfig();
+ALTER ROLE CURRENT_USER SET application_name to 'FOO';
+ALTER ROLE SESSION_USER SET application_name to 'BAR';
+ALTER ROLE "current_user" SET application_name to 'FOOFOO';
+ALTER ROLE "Public" SET application_name to 'BARBAR';
+ALTER ROLE ALL SET application_name to 'SLAP';
+SELECT * FROM chksetconfig();
+ALTER ROLE regress_testrol1 SET application_name to 'SLAM';
+SELECT * FROM chksetconfig();
+ALTER ROLE CURRENT_USER RESET application_name;
+ALTER ROLE SESSION_USER RESET application_name;
+ALTER ROLE "current_user" RESET application_name;
+ALTER ROLE "Public" RESET application_name;
+ALTER ROLE ALL RESET application_name;
+SELECT * FROM chksetconfig();
+
+
+ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error
+ALTER ROLE USER SET application_name to 'BOOM'; -- error
+ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error
+ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error
+
+-- ALTER USER SET/RESET
+SELECT * FROM chksetconfig();
+ALTER USER CURRENT_USER SET application_name to 'FOO';
+ALTER USER SESSION_USER SET application_name to 'BAR';
+ALTER USER "current_user" SET application_name to 'FOOFOO';
+ALTER USER "Public" SET application_name to 'BARBAR';
+ALTER USER ALL SET application_name to 'SLAP';
+SELECT * FROM chksetconfig();
+ALTER USER regress_testrol1 SET application_name to 'SLAM';
+SELECT * FROM chksetconfig();
+ALTER USER CURRENT_USER RESET application_name;
+ALTER USER SESSION_USER RESET application_name;
+ALTER USER "current_user" RESET application_name;
+ALTER USER "Public" RESET application_name;
+ALTER USER ALL RESET application_name;
+SELECT * FROM chksetconfig();
+
+
+ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error
+ALTER USER USER SET application_name to 'BOOM'; -- error
+ALTER USER PUBLIC SET application_name to 'BOMB'; -- error
+ALTER USER NONE SET application_name to 'BOMB'; -- error
+ALTER USER nonexistent SET application_name to 'BOMB'; -- error
+
+-- CREATE SCHEMA
+CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER;
+CREATE SCHEMA newschema2 AUTHORIZATION "current_user";
+CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER;
+CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx;
+CREATE SCHEMA newschema5 AUTHORIZATION "Public";
+
+CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error
+CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error
+CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error
+CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error
+CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error
+CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error
+
+SELECT n.nspname, r.rolname FROM pg_namespace n
+ JOIN pg_roles r ON (r.oid = n.nspowner)
+ WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
+
+CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER;
+CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user";
+CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER;
+CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx;
+CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public";
+
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error
+CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error
+
+SELECT n.nspname, r.rolname FROM pg_namespace n
+ JOIN pg_roles r ON (r.oid = n.nspowner)
+ WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
+
+-- ALTER TABLE OWNER TO
+\c -
+SET SESSION AUTHORIZATION regress_testrol0;
+CREATE TABLE testtab1 (a int);
+CREATE TABLE testtab2 (a int);
+CREATE TABLE testtab3 (a int);
+CREATE TABLE testtab4 (a int);
+CREATE TABLE testtab5 (a int);
+CREATE TABLE testtab6 (a int);
+
+\c -
+SET SESSION AUTHORIZATION regress_testrol1;
+SET ROLE regress_testrol2;
+
+ALTER TABLE testtab1 OWNER TO CURRENT_USER;
+ALTER TABLE testtab2 OWNER TO "current_user";
+ALTER TABLE testtab3 OWNER TO SESSION_USER;
+ALTER TABLE testtab4 OWNER TO regress_testrolx;
+ALTER TABLE testtab5 OWNER TO "Public";
+
+ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error
+ALTER TABLE testtab6 OWNER TO USER; --error
+ALTER TABLE testtab6 OWNER TO PUBLIC; -- error
+ALTER TABLE testtab6 OWNER TO "public"; -- error
+ALTER TABLE testtab6 OWNER TO nonexistent; -- error
+
+SELECT c.relname, r.rolname
+ FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner)
+ WHERE relname LIKE 'testtab_'
+ ORDER BY 1;
+
+-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are
+-- changed their owner in the same way.
+
+-- ALTER AGGREGATE
+\c -
+SET SESSION AUTHORIZATION regress_testrol0;
+CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8);
+CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8);
+
+\c -
+SET SESSION AUTHORIZATION regress_testrol1;
+SET ROLE regress_testrol2;
+
+ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER;
+ALTER AGGREGATE testagg2(int2) OWNER TO "current_user";
+ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER;
+ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx;
+ALTER AGGREGATE testagg5(int2) OWNER TO "Public";
+
+ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error
+ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error
+ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error
+ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error
+ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error
+
+SELECT p.proname, r.rolname
+ FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner)
+ WHERE proname LIKE 'testagg_'
+ ORDER BY 1;
+
+-- CREATE USER MAPPING
+CREATE FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper;
+CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper;
+
+CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
+CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
+CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
+CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
+CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
+CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
+CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
+CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
+
+CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
+ OPTIONS (user 'CURRENT_ROLE'); -- error
+CREATE USER MAPPING FOR nonexistent SERVER sv9
+ OPTIONS (user 'nonexistent'); -- error;
+
+SELECT * FROM chkumapping();
+
+-- ALTER USER MAPPING
+ALTER USER MAPPING FOR CURRENT_USER SERVER sv1
+ OPTIONS (SET user 'CURRENT_USER_alt');
+ALTER USER MAPPING FOR "current_user" SERVER sv2
+ OPTIONS (SET user '"current_user"_alt');
+ALTER USER MAPPING FOR USER SERVER sv3
+ OPTIONS (SET user 'USER_alt');
+ALTER USER MAPPING FOR "user" SERVER sv4
+ OPTIONS (SET user '"user"_alt');
+ALTER USER MAPPING FOR SESSION_USER SERVER sv5
+ OPTIONS (SET user 'SESSION_USER_alt');
+ALTER USER MAPPING FOR PUBLIC SERVER sv6
+ OPTIONS (SET user 'public_alt');
+ALTER USER MAPPING FOR "Public" SERVER sv7
+ OPTIONS (SET user '"Public"_alt');
+ALTER USER MAPPING FOR regress_testrolx SERVER sv8
+ OPTIONS (SET user 'regress_testrolx_alt');
+
+ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
+ OPTIONS (SET user 'CURRENT_ROLE_alt');
+ALTER USER MAPPING FOR nonexistent SERVER sv9
+ OPTIONS (SET user 'nonexistent_alt'); -- error
+
+SELECT * FROM chkumapping();
+
+-- DROP USER MAPPING
+DROP USER MAPPING FOR CURRENT_USER SERVER sv1;
+DROP USER MAPPING FOR "current_user" SERVER sv2;
+DROP USER MAPPING FOR USER SERVER sv3;
+DROP USER MAPPING FOR "user" SERVER sv4;
+DROP USER MAPPING FOR SESSION_USER SERVER sv5;
+DROP USER MAPPING FOR PUBLIC SERVER sv6;
+DROP USER MAPPING FOR "Public" SERVER sv7;
+DROP USER MAPPING FOR regress_testrolx SERVER sv8;
+
+DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error
+DROP USER MAPPING FOR nonexistent SERVER sv; -- error
+SELECT * FROM chkumapping();
+
+CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
+CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
+CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
+CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
+CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
+CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
+CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
+CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
+SELECT * FROM chkumapping();
+
+-- DROP USER MAPPING IF EXISTS
+DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1;
+SELECT * FROM chkumapping();
+DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2;
+SELECT * FROM chkumapping();
+DROP USER MAPPING IF EXISTS FOR USER SERVER sv3;
+SELECT * FROM chkumapping();
+DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4;
+SELECT * FROM chkumapping();
+DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5;
+SELECT * FROM chkumapping();
+DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6;
+SELECT * FROM chkumapping();
+DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7;
+SELECT * FROM chkumapping();
+DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8;
+SELECT * FROM chkumapping();
+
+DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
+DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
+
+-- GRANT/REVOKE
+GRANT regress_testrol0 TO pg_signal_backend; -- success
+
+SET ROLE pg_signal_backend; --success
+RESET ROLE;
+CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success
+SET ROLE regress_testrol2;
+
+UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+
+REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC;
+
+GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC;
+GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
+GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user";
+GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER;
+GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public";
+GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx;
+GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public";
+GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2)
+ TO current_user, public, regress_testrolx;
+
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error
+GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error
+
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+
+REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user";
+REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public";
+REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx;
+REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public";
+REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2)
+ FROM current_user, public, regress_testrolx;
+
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error
+REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
+
+SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
+
+-- DEFAULT MONITORING ROLES
+CREATE ROLE regress_role_haspriv;
+CREATE ROLE regress_role_nopriv;
+
+-- pg_read_all_stats
+GRANT pg_read_all_stats TO regress_role_haspriv;
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- returns true with role member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+ WHERE query = '<insufficient privilege>';
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- returns false with role not member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+ WHERE query = '<insufficient privilege>';
+RESET SESSION AUTHORIZATION;
+REVOKE pg_read_all_stats FROM regress_role_haspriv;
+
+-- pg_read_all_settings
+GRANT pg_read_all_settings TO regress_role_haspriv;
+BEGIN;
+-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
+SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- passes with role member of pg_read_all_settings
+SHOW session_preload_libraries;
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- fails with role not member of pg_read_all_settings
+SHOW session_preload_libraries;
+RESET SESSION AUTHORIZATION;
+ROLLBACK;
+REVOKE pg_read_all_settings FROM regress_role_haspriv;
+
+-- clean up
+\c
+
+DROP SCHEMA test_roles_schema;
+DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx
CASCADE;
+DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
+DROP ROLE "Public", "None", "current_user", "session_user", "user";
+DROP ROLE regress_role_haspriv, regress_role_nopriv;
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
deleted file mode 100644
index 03c1a25..0000000
--- a/src/test/regress/expected/rolenames.out
+++ /dev/null
@@ -1,1010 +0,0 @@
-CREATE OR REPLACE FUNCTION chkrolattr()
- RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
- AS $$
-SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
- FROM pg_roles r
- JOIN (VALUES(CURRENT_USER, 'current_user'),
- (SESSION_USER, 'session_user'),
- ('current_user', '-'),
- ('session_user', '-'),
- ('Public', '-'),
- ('None', '-'))
- AS v(uname, keyword)
- ON (r.rolname = v.uname)
- ORDER BY 1;
-$$ LANGUAGE SQL;
-CREATE OR REPLACE FUNCTION chksetconfig()
- RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[])
- AS $$
-SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'),
- COALESCE(v.keyword, '-'), s.setconfig
- FROM pg_db_role_setting s
- LEFT JOIN pg_roles r ON (r.oid = s.setrole)
- LEFT JOIN pg_database d ON (d.oid = s.setdatabase)
- LEFT JOIN (VALUES(CURRENT_USER, 'current_user'),
- (SESSION_USER, 'session_user'))
- AS v(uname, keyword)
- ON (r.rolname = v.uname)
- WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2')
-ORDER BY 1, 2;
-$$ LANGUAGE SQL;
-CREATE OR REPLACE FUNCTION chkumapping()
- RETURNS TABLE (umname name, umserver name, umoptions text[])
- AS $$
-SELECT r.rolname, s.srvname, m.umoptions
- FROM pg_user_mapping m
- LEFT JOIN pg_roles r ON (r.oid = m.umuser)
- JOIN pg_foreign_server s ON (s.oid = m.umserver)
- ORDER BY 2;
-$$ LANGUAGE SQL;
---
--- We test creation and use of these role names to ensure that the server
--- correctly distinguishes role keywords from quoted names that look like
--- those keywords. In a test environment, creation of these roles may
--- provoke warnings, so hide the warnings by raising client_min_messages.
---
-SET client_min_messages = ERROR;
-CREATE ROLE "Public";
-CREATE ROLE "None";
-CREATE ROLE "current_user";
-CREATE ROLE "session_user";
-CREATE ROLE "user";
-RESET client_min_messages;
-CREATE ROLE current_user; -- error
-ERROR: CURRENT_USER cannot be used as a role name here
-LINE 1: CREATE ROLE current_user;
- ^
-CREATE ROLE current_role; -- error
-ERROR: syntax error at or near "current_role"
-LINE 1: CREATE ROLE current_role;
- ^
-CREATE ROLE session_user; -- error
-ERROR: SESSION_USER cannot be used as a role name here
-LINE 1: CREATE ROLE session_user;
- ^
-CREATE ROLE user; -- error
-ERROR: syntax error at or near "user"
-LINE 1: CREATE ROLE user;
- ^
-CREATE ROLE all; -- error
-ERROR: syntax error at or near "all"
-LINE 1: CREATE ROLE all;
- ^
-CREATE ROLE public; -- error
-ERROR: role name "public" is reserved
-LINE 1: CREATE ROLE public;
- ^
-CREATE ROLE "public"; -- error
-ERROR: role name "public" is reserved
-LINE 1: CREATE ROLE "public";
- ^
-CREATE ROLE none; -- error
-ERROR: role name "none" is reserved
-LINE 1: CREATE ROLE none;
- ^
-CREATE ROLE "none"; -- error
-ERROR: role name "none" is reserved
-LINE 1: CREATE ROLE "none";
- ^
-CREATE ROLE pg_abc; -- error
-ERROR: role name "pg_abc" is reserved
-DETAIL: Role names starting with "pg_" are reserved.
-CREATE ROLE "pg_abc"; -- error
-ERROR: role name "pg_abc" is reserved
-DETAIL: Role names starting with "pg_" are reserved.
-CREATE ROLE pg_abcdef; -- error
-ERROR: role name "pg_abcdef" is reserved
-DETAIL: Role names starting with "pg_" are reserved.
-CREATE ROLE "pg_abcdef"; -- error
-ERROR: role name "pg_abcdef" is reserved
-DETAIL: Role names starting with "pg_" are reserved.
-CREATE ROLE regress_testrol0 SUPERUSER LOGIN;
-CREATE ROLE regress_testrolx SUPERUSER LOGIN;
-CREATE ROLE regress_testrol2 SUPERUSER;
-CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2;
-\c -
-SET SESSION AUTHORIZATION regress_testrol1;
-SET ROLE regress_testrol2;
--- ALTER ROLE
-BEGIN;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | f
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | f
- session_user | - | f | f
-(6 rows)
-
-ALTER ROLE CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | f
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | t
- session_user | - | f | f
-(6 rows)
-
-ALTER ROLE "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | t
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | t
- session_user | - | f | f
-(6 rows)
-
-ALTER ROLE SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | t
- regress_testrol1 | session_user | t | t
- regress_testrol2 | current_user | f | t
- session_user | - | f | f
-(6 rows)
-
-ALTER ROLE "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | t
- regress_testrol1 | session_user | t | t
- regress_testrol2 | current_user | f | t
- session_user | - | f | t
-(6 rows)
-
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | t
- Public | - | f | t
- current_user | - | f | t
- regress_testrol1 | session_user | t | t
- regress_testrol2 | current_user | f | t
- session_user | - | f | t
-(6 rows)
-
-ALTER USER regress_testrol1 WITH NOREPLICATION;
-ALTER USER regress_testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | t
- Public | - | f | t
- current_user | - | f | t
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | f
- session_user | - | f | t
-(6 rows)
-
-ROLLBACK;
-ALTER ROLE USER WITH LOGIN; -- error
-ERROR: syntax error at or near "USER"
-LINE 1: ALTER ROLE USER WITH LOGIN;
- ^
-ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ALTER ROLE CURRENT_ROLE WITH LOGIN;
- ^
-ALTER ROLE ALL WITH REPLICATION; -- error
-ERROR: syntax error at or near "WITH"
-LINE 1: ALTER ROLE ALL WITH REPLICATION;
- ^
-ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
-ERROR: role "session_role" does not exist
-ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
-ERROR: role "public" does not exist
-ALTER ROLE "public" WITH NOREPLICATION; -- error
-ERROR: role "public" does not exist
-ALTER ROLE NONE WITH NOREPLICATION; -- error
-ERROR: role name "none" is reserved
-LINE 1: ALTER ROLE NONE WITH NOREPLICATION;
- ^
-ALTER ROLE "none" WITH NOREPLICATION; -- error
-ERROR: role name "none" is reserved
-LINE 1: ALTER ROLE "none" WITH NOREPLICATION;
- ^
-ALTER ROLE nonexistent WITH NOREPLICATION; -- error
-ERROR: role "nonexistent" does not exist
--- ALTER USER
-BEGIN;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | f
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | f
- session_user | - | f | f
-(6 rows)
-
-ALTER USER CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | f
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | t
- session_user | - | f | f
-(6 rows)
-
-ALTER USER "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | t
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | t
- session_user | - | f | f
-(6 rows)
-
-ALTER USER SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | t
- regress_testrol1 | session_user | t | t
- regress_testrol2 | current_user | f | t
- session_user | - | f | f
-(6 rows)
-
-ALTER USER "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | f
- Public | - | f | f
- current_user | - | f | t
- regress_testrol1 | session_user | t | t
- regress_testrol2 | current_user | f | t
- session_user | - | f | t
-(6 rows)
-
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | t
- Public | - | f | t
- current_user | - | f | t
- regress_testrol1 | session_user | t | t
- regress_testrol2 | current_user | f | t
- session_user | - | f | t
-(6 rows)
-
-ALTER USER regress_testrol1 WITH NOREPLICATION;
-ALTER USER regress_testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
- role | rolekeyword | canlogin | replication
-------------------+--------------+----------+-------------
- None | - | f | t
- Public | - | f | t
- current_user | - | f | t
- regress_testrol1 | session_user | t | f
- regress_testrol2 | current_user | f | f
- session_user | - | f | t
-(6 rows)
-
-ROLLBACK;
-ALTER USER USER WITH LOGIN; -- error
-ERROR: syntax error at or near "USER"
-LINE 1: ALTER USER USER WITH LOGIN;
- ^
-ALTER USER CURRENT_ROLE WITH LOGIN; -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ALTER USER CURRENT_ROLE WITH LOGIN;
- ^
-ALTER USER ALL WITH REPLICATION; -- error
-ERROR: syntax error at or near "WITH"
-LINE 1: ALTER USER ALL WITH REPLICATION;
- ^
-ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
-ERROR: role "session_role" does not exist
-ALTER USER PUBLIC WITH NOREPLICATION; -- error
-ERROR: role "public" does not exist
-ALTER USER "public" WITH NOREPLICATION; -- error
-ERROR: role "public" does not exist
-ALTER USER NONE WITH NOREPLICATION; -- error
-ERROR: role name "none" is reserved
-LINE 1: ALTER USER NONE WITH NOREPLICATION;
- ^
-ALTER USER "none" WITH NOREPLICATION; -- error
-ERROR: role name "none" is reserved
-LINE 1: ALTER USER "none" WITH NOREPLICATION;
- ^
-ALTER USER nonexistent WITH NOREPLICATION; -- error
-ERROR: role "nonexistent" does not exist
--- ALTER ROLE SET/RESET
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
-----+------+------------+-----------
-(0 rows)
-
-ALTER ROLE CURRENT_USER SET application_name to 'FOO';
-ALTER ROLE SESSION_USER SET application_name to 'BAR';
-ALTER ROLE "current_user" SET application_name to 'FOOFOO';
-ALTER ROLE "Public" SET application_name to 'BARBAR';
-ALTER ROLE ALL SET application_name to 'SLAP';
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
------+------------------+--------------+---------------------------
- ALL | Public | - | {application_name=BARBAR}
- ALL | current_user | - | {application_name=FOOFOO}
- ALL | regress_testrol1 | session_user | {application_name=BAR}
- ALL | regress_testrol2 | current_user | {application_name=FOO}
-(4 rows)
-
-ALTER ROLE regress_testrol1 SET application_name to 'SLAM';
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
------+------------------+--------------+---------------------------
- ALL | Public | - | {application_name=BARBAR}
- ALL | current_user | - | {application_name=FOOFOO}
- ALL | regress_testrol1 | session_user | {application_name=SLAM}
- ALL | regress_testrol2 | current_user | {application_name=FOO}
-(4 rows)
-
-ALTER ROLE CURRENT_USER RESET application_name;
-ALTER ROLE SESSION_USER RESET application_name;
-ALTER ROLE "current_user" RESET application_name;
-ALTER ROLE "Public" RESET application_name;
-ALTER ROLE ALL RESET application_name;
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
-----+------+------------+-----------
-(0 rows)
-
-ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ';
- ^
-ALTER ROLE USER SET application_name to 'BOOM'; -- error
-ERROR: syntax error at or near "USER"
-LINE 1: ALTER ROLE USER SET application_name to 'BOOM';
- ^
-ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error
-ERROR: role "public" does not exist
-ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error
-ERROR: role "nonexistent" does not exist
--- ALTER USER SET/RESET
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
-----+------+------------+-----------
-(0 rows)
-
-ALTER USER CURRENT_USER SET application_name to 'FOO';
-ALTER USER SESSION_USER SET application_name to 'BAR';
-ALTER USER "current_user" SET application_name to 'FOOFOO';
-ALTER USER "Public" SET application_name to 'BARBAR';
-ALTER USER ALL SET application_name to 'SLAP';
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
------+------------------+--------------+---------------------------
- ALL | Public | - | {application_name=BARBAR}
- ALL | current_user | - | {application_name=FOOFOO}
- ALL | regress_testrol1 | session_user | {application_name=BAR}
- ALL | regress_testrol2 | current_user | {application_name=FOO}
-(4 rows)
-
-ALTER USER regress_testrol1 SET application_name to 'SLAM';
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
------+------------------+--------------+---------------------------
- ALL | Public | - | {application_name=BARBAR}
- ALL | current_user | - | {application_name=FOOFOO}
- ALL | regress_testrol1 | session_user | {application_name=SLAM}
- ALL | regress_testrol2 | current_user | {application_name=FOO}
-(4 rows)
-
-ALTER USER CURRENT_USER RESET application_name;
-ALTER USER SESSION_USER RESET application_name;
-ALTER USER "current_user" RESET application_name;
-ALTER USER "Public" RESET application_name;
-ALTER USER ALL RESET application_name;
-SELECT * FROM chksetconfig();
- db | role | rolkeyword | setconfig
-----+------+------------+-----------
-(0 rows)
-
-ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error
-ALTER USER USER SET application_name to 'BOOM'; -- error
-ERROR: syntax error at or near "USER"
-LINE 1: ALTER USER USER SET application_name to 'BOOM';
- ^
-ALTER USER PUBLIC SET application_name to 'BOMB'; -- error
-ERROR: role "public" does not exist
-ALTER USER NONE SET application_name to 'BOMB'; -- error
-ERROR: role name "none" is reserved
-LINE 1: ALTER USER NONE SET application_name to 'BOMB';
- ^
-ALTER USER nonexistent SET application_name to 'BOMB'; -- error
-ERROR: role "nonexistent" does not exist
--- CREATE SCHEMA
-CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER;
-CREATE SCHEMA newschema2 AUTHORIZATION "current_user";
-CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER;
-CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx;
-CREATE SCHEMA newschema5 AUTHORIZATION "Public";
-CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error
-ERROR: syntax error at or near "USER"
-LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER;
- ^
-CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE;
- ^
-CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error
-ERROR: role "public" does not exist
-CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error
-ERROR: role "public" does not exist
-CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error
-ERROR: role name "none" is reserved
-LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE;
- ^
-CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error
-ERROR: role "nonexistent" does not exist
-SELECT n.nspname, r.rolname FROM pg_namespace n
- JOIN pg_roles r ON (r.oid = n.nspowner)
- WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
- nspname | rolname
-------------+------------------
- newschema1 | regress_testrol2
- newschema2 | current_user
- newschema3 | regress_testrol1
- newschema4 | regress_testrolx
- newschema5 | Public
-(5 rows)
-
-CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER;
-NOTICE: schema "newschema1" already exists, skipping
-CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user";
-NOTICE: schema "newschema2" already exists, skipping
-CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER;
-NOTICE: schema "newschema3" already exists, skipping
-CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx;
-NOTICE: schema "newschema4" already exists, skipping
-CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public";
-NOTICE: schema "newschema5" already exists, skipping
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error
-ERROR: syntax error at or near "USER"
-LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER;
- ^
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO...
- ^
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error
-ERROR: role "public" does not exist
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error
-ERROR: role "public" does not exist
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error
-ERROR: role name "none" is reserved
-LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE;
- ^
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error
-ERROR: role "nonexistent" does not exist
-SELECT n.nspname, r.rolname FROM pg_namespace n
- JOIN pg_roles r ON (r.oid = n.nspowner)
- WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
- nspname | rolname
-------------+------------------
- newschema1 | regress_testrol2
- newschema2 | current_user
- newschema3 | regress_testrol1
- newschema4 | regress_testrolx
- newschema5 | Public
-(5 rows)
-
--- ALTER TABLE OWNER TO
-\c -
-SET SESSION AUTHORIZATION regress_testrol0;
-CREATE TABLE testtab1 (a int);
-CREATE TABLE testtab2 (a int);
-CREATE TABLE testtab3 (a int);
-CREATE TABLE testtab4 (a int);
-CREATE TABLE testtab5 (a int);
-CREATE TABLE testtab6 (a int);
-\c -
-SET SESSION AUTHORIZATION regress_testrol1;
-SET ROLE regress_testrol2;
-ALTER TABLE testtab1 OWNER TO CURRENT_USER;
-ALTER TABLE testtab2 OWNER TO "current_user";
-ALTER TABLE testtab3 OWNER TO SESSION_USER;
-ALTER TABLE testtab4 OWNER TO regress_testrolx;
-ALTER TABLE testtab5 OWNER TO "Public";
-ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE;
- ^
-ALTER TABLE testtab6 OWNER TO USER; --error
-ERROR: syntax error at or near "USER"
-LINE 1: ALTER TABLE testtab6 OWNER TO USER;
- ^
-ALTER TABLE testtab6 OWNER TO PUBLIC; -- error
-ERROR: role "public" does not exist
-ALTER TABLE testtab6 OWNER TO "public"; -- error
-ERROR: role "public" does not exist
-ALTER TABLE testtab6 OWNER TO nonexistent; -- error
-ERROR: role "nonexistent" does not exist
-SELECT c.relname, r.rolname
- FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner)
- WHERE relname LIKE 'testtab_'
- ORDER BY 1;
- relname | rolname
-----------+------------------
- testtab1 | regress_testrol2
- testtab2 | current_user
- testtab3 | regress_testrol1
- testtab4 | regress_testrolx
- testtab5 | Public
- testtab6 | regress_testrol0
-(6 rows)
-
--- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are
--- changed their owner in the same way.
--- ALTER AGGREGATE
-\c -
-SET SESSION AUTHORIZATION regress_testrol0;
-CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
-ERROR: function "testagg5" already exists with same argument types
-CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8);
-\c -
-SET SESSION AUTHORIZATION regress_testrol1;
-SET ROLE regress_testrol2;
-ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER;
-ALTER AGGREGATE testagg2(int2) OWNER TO "current_user";
-ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER;
-ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx;
-ALTER AGGREGATE testagg5(int2) OWNER TO "Public";
-ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE;
- ^
-ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error
-ERROR: syntax error at or near "USER"
-LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER;
- ^
-ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error
-ERROR: role "public" does not exist
-ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error
-ERROR: role "public" does not exist
-ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error
-ERROR: role "nonexistent" does not exist
-SELECT p.proname, r.rolname
- FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner)
- WHERE proname LIKE 'testagg_'
- ORDER BY 1;
- proname | rolname
-----------+------------------
- testagg1 | regress_testrol2
- testagg2 | current_user
- testagg3 | regress_testrol1
- testagg4 | regress_testrolx
- testagg5 | Public
- testagg6 | regress_testrol0
- testagg7 | regress_testrol0
- testagg8 | regress_testrol0
- testagg9 | regress_testrol0
-(9 rows)
-
--- CREATE USER MAPPING
-CREATE FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper;
-CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
-CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
-CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
-CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
-CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
-CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
-CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
-CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
-CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
- OPTIONS (user 'CURRENT_ROLE'); -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
- ^
-CREATE USER MAPPING FOR nonexistent SERVER sv9
- OPTIONS (user 'nonexistent'); -- error;
-ERROR: role "nonexistent" does not exist
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+---------------------------
- regress_testrol2 | sv1 | {user=CURRENT_USER}
- current_user | sv2 | {"user=\"current_user\""}
- regress_testrol2 | sv3 | {user=USER}
- user | sv4 | {"user=\"USER\""}
- regress_testrol1 | sv5 | {user=SESSION_USER}
- | sv6 | {user=PUBLIC}
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(8 rows)
-
--- ALTER USER MAPPING
-ALTER USER MAPPING FOR CURRENT_USER SERVER sv1
- OPTIONS (SET user 'CURRENT_USER_alt');
-ALTER USER MAPPING FOR "current_user" SERVER sv2
- OPTIONS (SET user '"current_user"_alt');
-ALTER USER MAPPING FOR USER SERVER sv3
- OPTIONS (SET user 'USER_alt');
-ALTER USER MAPPING FOR "user" SERVER sv4
- OPTIONS (SET user '"user"_alt');
-ALTER USER MAPPING FOR SESSION_USER SERVER sv5
- OPTIONS (SET user 'SESSION_USER_alt');
-ALTER USER MAPPING FOR PUBLIC SERVER sv6
- OPTIONS (SET user 'public_alt');
-ALTER USER MAPPING FOR "Public" SERVER sv7
- OPTIONS (SET user '"Public"_alt');
-ALTER USER MAPPING FOR regress_testrolx SERVER sv8
- OPTIONS (SET user 'regress_testrolx_alt');
-ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
- OPTIONS (SET user 'CURRENT_ROLE_alt');
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
- ^
-ALTER USER MAPPING FOR nonexistent SERVER sv9
- OPTIONS (SET user 'nonexistent_alt'); -- error
-ERROR: role "nonexistent" does not exist
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+-------------------------------
- regress_testrol2 | sv1 | {user=CURRENT_USER_alt}
- current_user | sv2 | {"user=\"current_user\"_alt"}
- regress_testrol2 | sv3 | {user=USER_alt}
- user | sv4 | {"user=\"user\"_alt"}
- regress_testrol1 | sv5 | {user=SESSION_USER_alt}
- | sv6 | {user=public_alt}
- Public | sv7 | {"user=\"Public\"_alt"}
- regress_testrolx | sv8 | {user=regress_testrolx_alt}
-(8 rows)
-
--- DROP USER MAPPING
-DROP USER MAPPING FOR CURRENT_USER SERVER sv1;
-DROP USER MAPPING FOR "current_user" SERVER sv2;
-DROP USER MAPPING FOR USER SERVER sv3;
-DROP USER MAPPING FOR "user" SERVER sv4;
-DROP USER MAPPING FOR SESSION_USER SERVER sv5;
-DROP USER MAPPING FOR PUBLIC SERVER sv6;
-DROP USER MAPPING FOR "Public" SERVER sv7;
-DROP USER MAPPING FOR regress_testrolx SERVER sv8;
-DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9;
- ^
-DROP USER MAPPING FOR nonexistent SERVER sv; -- error
-ERROR: role "nonexistent" does not exist
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
---------+----------+-----------
-(0 rows)
-
-CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
-CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
-CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
-CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
-CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
-CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
-CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
-CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+---------------------------
- regress_testrol2 | sv1 | {user=CURRENT_USER}
- current_user | sv2 | {"user=\"current_user\""}
- regress_testrol2 | sv3 | {user=USER}
- user | sv4 | {"user=\"USER\""}
- regress_testrol1 | sv5 | {user=SESSION_USER}
- | sv6 | {user=PUBLIC}
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(8 rows)
-
--- DROP USER MAPPING IF EXISTS
-DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+---------------------------
- current_user | sv2 | {"user=\"current_user\""}
- regress_testrol2 | sv3 | {user=USER}
- user | sv4 | {"user=\"USER\""}
- regress_testrol1 | sv5 | {user=SESSION_USER}
- | sv6 | {user=PUBLIC}
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(7 rows)
-
-DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+-------------------------
- regress_testrol2 | sv3 | {user=USER}
- user | sv4 | {"user=\"USER\""}
- regress_testrol1 | sv5 | {user=SESSION_USER}
- | sv6 | {user=PUBLIC}
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(6 rows)
-
-DROP USER MAPPING IF EXISTS FOR USER SERVER sv3;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+-------------------------
- user | sv4 | {"user=\"USER\""}
- regress_testrol1 | sv5 | {user=SESSION_USER}
- | sv6 | {user=PUBLIC}
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(5 rows)
-
-DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+-------------------------
- regress_testrol1 | sv5 | {user=SESSION_USER}
- | sv6 | {user=PUBLIC}
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(4 rows)
-
-DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+-------------------------
- | sv6 | {user=PUBLIC}
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(3 rows)
-
-DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+-------------------------
- Public | sv7 | {"user=\"Public\""}
- regress_testrolx | sv8 | {user=regress_testrolx}
-(2 rows)
-
-DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
-------------------+----------+-------------------------
- regress_testrolx | sv8 | {user=regress_testrolx}
-(1 row)
-
-DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8;
-SELECT * FROM chkumapping();
- umname | umserver | umoptions
---------+----------+-----------
-(0 rows)
-
-DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9;
- ^
-DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
-NOTICE: role "nonexistent" does not exist, skipping
--- GRANT/REVOKE
-GRANT regress_testrol0 TO pg_signal_backend; -- success
-SET ROLE pg_signal_backend; --success
-RESET ROLE;
-CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success
-SET ROLE regress_testrol2;
-UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
- proname | proacl
-----------+--------
- testagg1 |
- testagg2 |
- testagg3 |
- testagg4 |
- testagg5 |
- testagg6 |
- testagg7 |
- testagg8 |
- testagg9 |
-(9 rows)
-
-REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC;
-GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC;
-GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
-GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user";
-GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER;
-GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public";
-GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx;
-GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public";
-GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2)
- TO current_user, public, regress_testrolx;
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
- proname | proacl
-----------+-----------------------------------------------------------------------------------------------------------------------------------
- testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
- testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
- testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
- testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
- testagg5 | {Public=X/Public}
- testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
- testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
- testagg8 |
{regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
- testagg9 |
-(9 rows)
-
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO...
- ^
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error
-ERROR: syntax error at or near "USER"
-LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER;
- ^
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error
-ERROR: role name "none" is reserved
-LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE;
- ^
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error
-ERROR: role name "none" is reserved
-LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none";
- ^
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
- proname | proacl
-----------+-----------------------------------------------------------------------------------------------------------------------------------
- testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
- testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
- testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
- testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
- testagg5 | {Public=X/Public}
- testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
- testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
- testagg8 |
{regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
- testagg9 |
-(9 rows)
-
-REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user";
-REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public";
-REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public";
-REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2)
- FROM current_user, public, regress_testrolx;
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
- proname | proacl
-----------+---------------------------------------
- testagg1 | {regress_testrol2=X/regress_testrol2}
- testagg2 | {current_user=X/current_user}
- testagg3 | {regress_testrol1=X/regress_testrol1}
- testagg4 | {regress_testrolx=X/regress_testrolx}
- testagg5 | {}
- testagg6 | {regress_testrol0=X/regress_testrol0}
- testagg7 | {regress_testrol0=X/regress_testrol0}
- testagg8 | {regress_testrol0=X/regress_testrol0}
- testagg9 |
-(9 rows)
-
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error
-ERROR: syntax error at or near "CURRENT_ROLE"
-LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO...
- ^
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error
-ERROR: syntax error at or near "USER"
-LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER;
- ^
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error
-ERROR: role name "none" is reserved
-LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE;
- ^
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
-ERROR: role name "none" is reserved
-LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none";
- ^
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
- proname | proacl
-----------+---------------------------------------
- testagg1 | {regress_testrol2=X/regress_testrol2}
- testagg2 | {current_user=X/current_user}
- testagg3 | {regress_testrol1=X/regress_testrol1}
- testagg4 | {regress_testrolx=X/regress_testrolx}
- testagg5 | {}
- testagg6 | {regress_testrol0=X/regress_testrol0}
- testagg7 | {regress_testrol0=X/regress_testrol0}
- testagg8 | {regress_testrol0=X/regress_testrol0}
- testagg9 |
-(9 rows)
-
--- DEFAULT MONITORING ROLES
-CREATE ROLE regress_role_haspriv;
-CREATE ROLE regress_role_nopriv;
--- pg_read_all_stats
-GRANT pg_read_all_stats TO regress_role_haspriv;
-SET SESSION AUTHORIZATION regress_role_haspriv;
--- returns true with role member of pg_read_all_stats
-SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
- WHERE query = '<insufficient privilege>';
- haspriv
----------
- t
-(1 row)
-
-SET SESSION AUTHORIZATION regress_role_nopriv;
--- returns false with role not member of pg_read_all_stats
-SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
- WHERE query = '<insufficient privilege>';
- haspriv
----------
- f
-(1 row)
-
-RESET SESSION AUTHORIZATION;
-REVOKE pg_read_all_stats FROM regress_role_haspriv;
--- pg_read_all_settings
-GRANT pg_read_all_settings TO regress_role_haspriv;
-BEGIN;
--- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
-SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
-SET SESSION AUTHORIZATION regress_role_haspriv;
--- passes with role member of pg_read_all_settings
-SHOW session_preload_libraries;
- session_preload_libraries
------------------------------
- "path-to-preload-libraries"
-(1 row)
-
-SET SESSION AUTHORIZATION regress_role_nopriv;
--- fails with role not member of pg_read_all_settings
-SHOW session_preload_libraries;
-ERROR: must be superuser or a member of pg_read_all_settings to examine "session_preload_libraries"
-RESET SESSION AUTHORIZATION;
-ERROR: current transaction is aborted, commands ignored until end of transaction block
-ROLLBACK;
-REVOKE pg_read_all_settings FROM regress_role_haspriv;
--- clean up
-\c
-DROP SCHEMA test_roles_schema;
-DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx
CASCADE;
-DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
-DROP ROLE "Public", "None", "current_user", "session_user", "user";
-DROP ROLE regress_role_haspriv, regress_role_nopriv;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f23fe8d..8fb55f0 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -55,7 +55,7 @@ test: create_index create_index_spgist create_view index_including index_includi
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum
drop_if_existsupdatable_views rolenames roleattributes create_am hash_func errors
+test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum
drop_if_existsupdatable_views roleattributes create_am hash_func errors
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index ca200eb..a39ca10 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -77,7 +77,6 @@ test: typed_table
test: vacuum
test: drop_if_exists
test: updatable_views
-test: rolenames
test: roleattributes
test: create_am
test: hash_func
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
deleted file mode 100644
index 5a3cf44..0000000
--- a/src/test/regress/sql/rolenames.sql
+++ /dev/null
@@ -1,488 +0,0 @@
-CREATE OR REPLACE FUNCTION chkrolattr()
- RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
- AS $$
-SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
- FROM pg_roles r
- JOIN (VALUES(CURRENT_USER, 'current_user'),
- (SESSION_USER, 'session_user'),
- ('current_user', '-'),
- ('session_user', '-'),
- ('Public', '-'),
- ('None', '-'))
- AS v(uname, keyword)
- ON (r.rolname = v.uname)
- ORDER BY 1;
-$$ LANGUAGE SQL;
-
-CREATE OR REPLACE FUNCTION chksetconfig()
- RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[])
- AS $$
-SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'),
- COALESCE(v.keyword, '-'), s.setconfig
- FROM pg_db_role_setting s
- LEFT JOIN pg_roles r ON (r.oid = s.setrole)
- LEFT JOIN pg_database d ON (d.oid = s.setdatabase)
- LEFT JOIN (VALUES(CURRENT_USER, 'current_user'),
- (SESSION_USER, 'session_user'))
- AS v(uname, keyword)
- ON (r.rolname = v.uname)
- WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2')
-ORDER BY 1, 2;
-$$ LANGUAGE SQL;
-
-CREATE OR REPLACE FUNCTION chkumapping()
- RETURNS TABLE (umname name, umserver name, umoptions text[])
- AS $$
-SELECT r.rolname, s.srvname, m.umoptions
- FROM pg_user_mapping m
- LEFT JOIN pg_roles r ON (r.oid = m.umuser)
- JOIN pg_foreign_server s ON (s.oid = m.umserver)
- ORDER BY 2;
-$$ LANGUAGE SQL;
-
---
--- We test creation and use of these role names to ensure that the server
--- correctly distinguishes role keywords from quoted names that look like
--- those keywords. In a test environment, creation of these roles may
--- provoke warnings, so hide the warnings by raising client_min_messages.
---
-SET client_min_messages = ERROR;
-
-CREATE ROLE "Public";
-CREATE ROLE "None";
-CREATE ROLE "current_user";
-CREATE ROLE "session_user";
-CREATE ROLE "user";
-
-RESET client_min_messages;
-
-CREATE ROLE current_user; -- error
-CREATE ROLE current_role; -- error
-CREATE ROLE session_user; -- error
-CREATE ROLE user; -- error
-CREATE ROLE all; -- error
-
-CREATE ROLE public; -- error
-CREATE ROLE "public"; -- error
-CREATE ROLE none; -- error
-CREATE ROLE "none"; -- error
-
-CREATE ROLE pg_abc; -- error
-CREATE ROLE "pg_abc"; -- error
-CREATE ROLE pg_abcdef; -- error
-CREATE ROLE "pg_abcdef"; -- error
-
-CREATE ROLE regress_testrol0 SUPERUSER LOGIN;
-CREATE ROLE regress_testrolx SUPERUSER LOGIN;
-CREATE ROLE regress_testrol2 SUPERUSER;
-CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2;
-
-\c -
-SET SESSION AUTHORIZATION regress_testrol1;
-SET ROLE regress_testrol2;
-
--- ALTER ROLE
-BEGIN;
-SELECT * FROM chkrolattr();
-ALTER ROLE CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER ROLE "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER ROLE SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER ROLE "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER regress_testrol1 WITH NOREPLICATION;
-ALTER USER regress_testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
-ROLLBACK;
-
-ALTER ROLE USER WITH LOGIN; -- error
-ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
-ALTER ROLE ALL WITH REPLICATION; -- error
-ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
-ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
-ALTER ROLE "public" WITH NOREPLICATION; -- error
-ALTER ROLE NONE WITH NOREPLICATION; -- error
-ALTER ROLE "none" WITH NOREPLICATION; -- error
-ALTER ROLE nonexistent WITH NOREPLICATION; -- error
-
--- ALTER USER
-BEGIN;
-SELECT * FROM chkrolattr();
-ALTER USER CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER regress_testrol1 WITH NOREPLICATION;
-ALTER USER regress_testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
-ROLLBACK;
-
-ALTER USER USER WITH LOGIN; -- error
-ALTER USER CURRENT_ROLE WITH LOGIN; -- error
-ALTER USER ALL WITH REPLICATION; -- error
-ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
-ALTER USER PUBLIC WITH NOREPLICATION; -- error
-ALTER USER "public" WITH NOREPLICATION; -- error
-ALTER USER NONE WITH NOREPLICATION; -- error
-ALTER USER "none" WITH NOREPLICATION; -- error
-ALTER USER nonexistent WITH NOREPLICATION; -- error
-
--- ALTER ROLE SET/RESET
-SELECT * FROM chksetconfig();
-ALTER ROLE CURRENT_USER SET application_name to 'FOO';
-ALTER ROLE SESSION_USER SET application_name to 'BAR';
-ALTER ROLE "current_user" SET application_name to 'FOOFOO';
-ALTER ROLE "Public" SET application_name to 'BARBAR';
-ALTER ROLE ALL SET application_name to 'SLAP';
-SELECT * FROM chksetconfig();
-ALTER ROLE regress_testrol1 SET application_name to 'SLAM';
-SELECT * FROM chksetconfig();
-ALTER ROLE CURRENT_USER RESET application_name;
-ALTER ROLE SESSION_USER RESET application_name;
-ALTER ROLE "current_user" RESET application_name;
-ALTER ROLE "Public" RESET application_name;
-ALTER ROLE ALL RESET application_name;
-SELECT * FROM chksetconfig();
-
-
-ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error
-ALTER ROLE USER SET application_name to 'BOOM'; -- error
-ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error
-ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error
-
--- ALTER USER SET/RESET
-SELECT * FROM chksetconfig();
-ALTER USER CURRENT_USER SET application_name to 'FOO';
-ALTER USER SESSION_USER SET application_name to 'BAR';
-ALTER USER "current_user" SET application_name to 'FOOFOO';
-ALTER USER "Public" SET application_name to 'BARBAR';
-ALTER USER ALL SET application_name to 'SLAP';
-SELECT * FROM chksetconfig();
-ALTER USER regress_testrol1 SET application_name to 'SLAM';
-SELECT * FROM chksetconfig();
-ALTER USER CURRENT_USER RESET application_name;
-ALTER USER SESSION_USER RESET application_name;
-ALTER USER "current_user" RESET application_name;
-ALTER USER "Public" RESET application_name;
-ALTER USER ALL RESET application_name;
-SELECT * FROM chksetconfig();
-
-
-ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error
-ALTER USER USER SET application_name to 'BOOM'; -- error
-ALTER USER PUBLIC SET application_name to 'BOMB'; -- error
-ALTER USER NONE SET application_name to 'BOMB'; -- error
-ALTER USER nonexistent SET application_name to 'BOMB'; -- error
-
--- CREATE SCHEMA
-CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER;
-CREATE SCHEMA newschema2 AUTHORIZATION "current_user";
-CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER;
-CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx;
-CREATE SCHEMA newschema5 AUTHORIZATION "Public";
-
-CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error
-CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error
-CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error
-CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error
-CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error
-CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error
-
-SELECT n.nspname, r.rolname FROM pg_namespace n
- JOIN pg_roles r ON (r.oid = n.nspowner)
- WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
-
-CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER;
-CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user";
-CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER;
-CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx;
-CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public";
-
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error
-CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error
-
-SELECT n.nspname, r.rolname FROM pg_namespace n
- JOIN pg_roles r ON (r.oid = n.nspowner)
- WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
-
--- ALTER TABLE OWNER TO
-\c -
-SET SESSION AUTHORIZATION regress_testrol0;
-CREATE TABLE testtab1 (a int);
-CREATE TABLE testtab2 (a int);
-CREATE TABLE testtab3 (a int);
-CREATE TABLE testtab4 (a int);
-CREATE TABLE testtab5 (a int);
-CREATE TABLE testtab6 (a int);
-
-\c -
-SET SESSION AUTHORIZATION regress_testrol1;
-SET ROLE regress_testrol2;
-
-ALTER TABLE testtab1 OWNER TO CURRENT_USER;
-ALTER TABLE testtab2 OWNER TO "current_user";
-ALTER TABLE testtab3 OWNER TO SESSION_USER;
-ALTER TABLE testtab4 OWNER TO regress_testrolx;
-ALTER TABLE testtab5 OWNER TO "Public";
-
-ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error
-ALTER TABLE testtab6 OWNER TO USER; --error
-ALTER TABLE testtab6 OWNER TO PUBLIC; -- error
-ALTER TABLE testtab6 OWNER TO "public"; -- error
-ALTER TABLE testtab6 OWNER TO nonexistent; -- error
-
-SELECT c.relname, r.rolname
- FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner)
- WHERE relname LIKE 'testtab_'
- ORDER BY 1;
-
--- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are
--- changed their owner in the same way.
-
--- ALTER AGGREGATE
-\c -
-SET SESSION AUTHORIZATION regress_testrol0;
-CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8);
-CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8);
-
-\c -
-SET SESSION AUTHORIZATION regress_testrol1;
-SET ROLE regress_testrol2;
-
-ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER;
-ALTER AGGREGATE testagg2(int2) OWNER TO "current_user";
-ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER;
-ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx;
-ALTER AGGREGATE testagg5(int2) OWNER TO "Public";
-
-ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error
-ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error
-ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error
-ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error
-ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error
-
-SELECT p.proname, r.rolname
- FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner)
- WHERE proname LIKE 'testagg_'
- ORDER BY 1;
-
--- CREATE USER MAPPING
-CREATE FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper;
-CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper;
-
-CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
-CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
-CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
-CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
-CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
-CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
-CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
-CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
-
-CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
- OPTIONS (user 'CURRENT_ROLE'); -- error
-CREATE USER MAPPING FOR nonexistent SERVER sv9
- OPTIONS (user 'nonexistent'); -- error;
-
-SELECT * FROM chkumapping();
-
--- ALTER USER MAPPING
-ALTER USER MAPPING FOR CURRENT_USER SERVER sv1
- OPTIONS (SET user 'CURRENT_USER_alt');
-ALTER USER MAPPING FOR "current_user" SERVER sv2
- OPTIONS (SET user '"current_user"_alt');
-ALTER USER MAPPING FOR USER SERVER sv3
- OPTIONS (SET user 'USER_alt');
-ALTER USER MAPPING FOR "user" SERVER sv4
- OPTIONS (SET user '"user"_alt');
-ALTER USER MAPPING FOR SESSION_USER SERVER sv5
- OPTIONS (SET user 'SESSION_USER_alt');
-ALTER USER MAPPING FOR PUBLIC SERVER sv6
- OPTIONS (SET user 'public_alt');
-ALTER USER MAPPING FOR "Public" SERVER sv7
- OPTIONS (SET user '"Public"_alt');
-ALTER USER MAPPING FOR regress_testrolx SERVER sv8
- OPTIONS (SET user 'regress_testrolx_alt');
-
-ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
- OPTIONS (SET user 'CURRENT_ROLE_alt');
-ALTER USER MAPPING FOR nonexistent SERVER sv9
- OPTIONS (SET user 'nonexistent_alt'); -- error
-
-SELECT * FROM chkumapping();
-
--- DROP USER MAPPING
-DROP USER MAPPING FOR CURRENT_USER SERVER sv1;
-DROP USER MAPPING FOR "current_user" SERVER sv2;
-DROP USER MAPPING FOR USER SERVER sv3;
-DROP USER MAPPING FOR "user" SERVER sv4;
-DROP USER MAPPING FOR SESSION_USER SERVER sv5;
-DROP USER MAPPING FOR PUBLIC SERVER sv6;
-DROP USER MAPPING FOR "Public" SERVER sv7;
-DROP USER MAPPING FOR regress_testrolx SERVER sv8;
-
-DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error
-DROP USER MAPPING FOR nonexistent SERVER sv; -- error
-SELECT * FROM chkumapping();
-
-CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
-CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
-CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
-CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
-CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
-CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
-CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
-CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
-SELECT * FROM chkumapping();
-
--- DROP USER MAPPING IF EXISTS
-DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1;
-SELECT * FROM chkumapping();
-DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2;
-SELECT * FROM chkumapping();
-DROP USER MAPPING IF EXISTS FOR USER SERVER sv3;
-SELECT * FROM chkumapping();
-DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4;
-SELECT * FROM chkumapping();
-DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5;
-SELECT * FROM chkumapping();
-DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6;
-SELECT * FROM chkumapping();
-DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7;
-SELECT * FROM chkumapping();
-DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8;
-SELECT * FROM chkumapping();
-
-DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
-DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
-
--- GRANT/REVOKE
-GRANT regress_testrol0 TO pg_signal_backend; -- success
-
-SET ROLE pg_signal_backend; --success
-RESET ROLE;
-CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success
-SET ROLE regress_testrol2;
-
-UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
-
-REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC;
-
-GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC;
-GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
-GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user";
-GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER;
-GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public";
-GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx;
-GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public";
-GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2)
- TO current_user, public, regress_testrolx;
-
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
-
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error
-GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error
-
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
-
-REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user";
-REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public";
-REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx;
-REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public";
-REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2)
- FROM current_user, public, regress_testrolx;
-
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
-
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error
-REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
-
-SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
-
--- DEFAULT MONITORING ROLES
-CREATE ROLE regress_role_haspriv;
-CREATE ROLE regress_role_nopriv;
-
--- pg_read_all_stats
-GRANT pg_read_all_stats TO regress_role_haspriv;
-SET SESSION AUTHORIZATION regress_role_haspriv;
--- returns true with role member of pg_read_all_stats
-SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
- WHERE query = '<insufficient privilege>';
-SET SESSION AUTHORIZATION regress_role_nopriv;
--- returns false with role not member of pg_read_all_stats
-SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
- WHERE query = '<insufficient privilege>';
-RESET SESSION AUTHORIZATION;
-REVOKE pg_read_all_stats FROM regress_role_haspriv;
-
--- pg_read_all_settings
-GRANT pg_read_all_settings TO regress_role_haspriv;
-BEGIN;
--- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
-SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
-SET SESSION AUTHORIZATION regress_role_haspriv;
--- passes with role member of pg_read_all_settings
-SHOW session_preload_libraries;
-SET SESSION AUTHORIZATION regress_role_nopriv;
--- fails with role not member of pg_read_all_settings
-SHOW session_preload_libraries;
-RESET SESSION AUTHORIZATION;
-ROLLBACK;
-REVOKE pg_read_all_settings FROM regress_role_haspriv;
-
--- clean up
-\c
-
-DROP SCHEMA test_roles_schema;
-DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx
CASCADE;
-DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
-DROP ROLE "Public", "None", "current_user", "session_user", "user";
-DROP ROLE regress_role_haspriv, regress_role_nopriv;
On 2019-06-29 19:21, Tom Lane wrote: > Stephen Frost<sfrost@snowman.net> writes: >> * Tom Lane (tgl@sss.pgh.pa.us) wrote: >>> We could make the new subdirectory be something specific like >>> "src/test/modules/test_rolenames", but I think very likely we'll be >>> wanting some additional test scripts that we likewise deem unsafe to >>> run during "installcheck". So I'd rather choose a more generic module >>> name, but I'm not sure what ... "unsafe_tests"? >> Agreed but haven't got any particularly good suggestions on names.. > Hearing no better suggestions, I went with "unsafe_tests" in the > attached. I found this directory, and it seems like a good place to add some more tests (other thread), but I'm puzzled why it's hidden under modules/, since it's not, well, a module. Why is it not in src/test/? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> I found this directory, and it seems like a good place to add some more
> tests (other thread), but I'm puzzled why it's hidden under modules/,
> since it's not, well, a module. Why is it not in src/test/?
Doing it like this allowed it to be picked up automatically by the
buildfarm script. If we'd put it in a new src/test directory, the
buildfarm script would have had to be taught about that, and we'd
have no coverage until owners updated their machines. So it
seemed expedient to do it like this.
regards, tom lane