One idea is to replace a null ACL value with the actual effective
permissions, which we could get from the acldefault() function.
However, acldefault() only exists since 9.2, and in any case
I'm afraid that might be perceived as mostly clutter.
Here is an poc implementation of this which does not rely on
"acldefault()". Cannot say I'm thrilled, but it is not too bad either. I
worked around \ddp with a recursion.
Writing this piece code makes me realize once again the abysmal coverage
of psql non-regression tests, where basically no \d* functions are tested.
I think at least this part could be salvage from the patch.
Another benefit of expliciting the defaults is that the documentation can
be made more straightforward: shown permissions means what you can see,
which simplifies the description.
Another implementation approach could be to use acldefault() from 9.2, and
just display (no privileges) and (default privileges) before, as suggested
on the thread. This would probably help simplify the code.
I find that having "default privileges" written as not very helpful,
because you have to remember them. Ok, it is all perms for the owner, but
then there are some objects which also have some permissions to public,
and do not memorize these exception. Also, some \d* (eg \dT \dD) do not
display the owner.
I do not perceive as "clutter" the fact that a column advertising "Access
privileges" provides the access privileges... Mostly it is shown only
under "+"; For \dp, you ask for it, you get it.
--
Fabien.
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index ff64c7a3ba..90850f185d 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -573,20 +573,22 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO
miriam_rw;
<para>
If the <quote>Access privileges</quote> column is empty for a given object,
- it means the object has default privileges (that is, its privileges column
- is null). Default privileges always include all privileges for the owner,
- and can include some privileges for <literal>PUBLIC</literal> depending on
the
- object type, as explained above. The first <command>GRANT</command> or
- <command>REVOKE</command> on an object
- will instantiate the default privileges (producing, for example,
- <literal>{miriam=arwdDxt/miriam}</literal>) and then modify them per the
- specified request. Similarly, entries are shown in <quote>Column access
- privileges</quote> only for columns with nondefault privileges.
- (Note: for this purpose, <quote>default privileges</quote> always means the
- built-in default privileges for the object's type. An object whose
- privileges have been affected by an <command>ALTER DEFAULT
PRIVILEGES</command>
- command will always be shown with an explicit privilege entry that
- includes the effects of the <command>ALTER</command>.)
+ it means the object has no privileges, i.e. all privileges have been
revoked.
+ </para>
+
+ <note>
+ <para>
+ In previous versions of PostgreSQL, an empty <quote>Access
privileges</quote>
+ column could mean either that the object had no provileges, or that it has
+ the default privileges for the object's type.
+ This ambiguity is fixed starting from PostgreSQL 12.
+ </para>
+ </note>
+
+ <para>
+ Entries in <quote>Column access privileges</quote> show per-column
+ access privileges added to the table's access privileges already
+ outlined in <quote>Access privileges</quote>.
</para>
<para>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 80d8338b96..097d137b09 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -41,7 +41,7 @@ static bool listTSConfigsVerbose(const char *pattern);
static bool describeOneTSConfig(const char *oid, const char *nspname,
const char *cfgname,
const char *pnspname, const char
*prsname);
-static void printACLColumn(PQExpBuffer buf, const char *colname);
+static void printACLColumn(PQExpBuffer buf, const char *colname, const char
*kind, const char *rolname, const char *ddp_type);
static bool listOneExtensionContents(const char *extname, const char *oid);
@@ -233,16 +233,16 @@ describeTablespaces(const char *pattern, bool verbose)
if (pset.sversion >= 90200)
printfPQExpBuffer(&buf,
- "SELECT spcname AS \"%s\",\n"
- "
pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
- "
pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
+ "SELECT ts.spcname AS
\"%s\",\n"
+ "
pg_catalog.pg_get_userbyid(ts.spcowner) AS \"%s\",\n"
+ "
pg_catalog.pg_tablespace_location(ts.oid) AS \"%s\"",
gettext_noop("Name"),
gettext_noop("Owner"),
gettext_noop("Location"));
else
printfPQExpBuffer(&buf,
- "SELECT spcname AS \"%s\",\n"
- "
pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
+ "SELECT ts.spcname AS
\"%s\",\n"
+ "
pg_catalog.pg_get_userbyid(ts.spcowner) AS \"%s\",\n"
" spclocation AS \"%s\"",
gettext_noop("Name"),
gettext_noop("Owner"),
@@ -251,26 +251,30 @@ describeTablespaces(const char *pattern, bool verbose)
if (verbose)
{
appendPQExpBufferStr(&buf, ",\n ");
- printACLColumn(&buf, "spcacl");
+ printACLColumn(&buf, "ts.spcacl", "\\db", "u.usename", NULL);
}
if (verbose && pset.sversion >= 90000)
appendPQExpBuffer(&buf,
- ",\n spcoptions AS \"%s\"",
+ ",\n ts.spcoptions AS
\"%s\"",
gettext_noop("Options"));
if (verbose && pset.sversion >= 90200)
appendPQExpBuffer(&buf,
- ",\n
pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+ ",\n
pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(ts.oid)) AS \"%s\"",
gettext_noop("Size"));
if (verbose && pset.sversion >= 80200)
appendPQExpBuffer(&buf,
- ",\n
pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
+ ",\n
pg_catalog.shobj_description(ts.oid, 'pg_tablespace') AS \"%s\"",
gettext_noop("Description"));
appendPQExpBufferStr(&buf,
- "\nFROM
pg_catalog.pg_tablespace\n");
+ "\nFROM
pg_catalog.pg_tablespace ts\n");
+
+ if (verbose)
+ appendPQExpBufferStr(&buf,
+ "LEFT JOIN
pg_catalog.pg_user u ON u.usesysid = ts.spcowner\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "spcname", NULL,
@@ -302,6 +306,7 @@ describeTablespaces(const char *pattern, bool verbose)
*
* a for aggregates
* n for normal
+ * p for procedure
* t for trigger
* w for window
*
@@ -499,7 +504,7 @@ describeFunctions(const char *functypes, const char
*pattern, bool verbose, bool
gettext_noop("invoker"),
gettext_noop("Security"));
appendPQExpBufferStr(&buf, ",\n ");
- printACLColumn(&buf, "p.proacl");
+ printACLColumn(&buf, "p.proacl", "\\df", "u.usename", NULL);
appendPQExpBuffer(&buf,
",\n l.lanname as \"%s\""
",\n p.prosrc as \"%s\""
@@ -515,7 +520,8 @@ describeFunctions(const char *functypes, const char
*pattern, bool verbose, bool
if (verbose)
appendPQExpBufferStr(&buf,
- " LEFT JOIN
pg_catalog.pg_language l ON l.oid = p.prolang\n");
+ " LEFT JOIN
pg_catalog.pg_language l ON l.oid = p.prolang\n"
+ " LEFT JOIN
pg_catalog.pg_user u ON u.usesysid = p.proowner\n");
have_where = false;
@@ -714,7 +720,7 @@ describeTypes(const char *pattern, bool verbose, bool
showSystem)
}
if (verbose && pset.sversion >= 90200)
{
- printACLColumn(&buf, "t.typacl");
+ printACLColumn(&buf, "t.typacl", "\\dT", "u.usename", NULL);
appendPQExpBufferStr(&buf, ",\n ");
}
@@ -725,6 +731,9 @@ describeTypes(const char *pattern, bool verbose, bool
showSystem)
appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_type t\n"
" LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
+ if (verbose && pset.sversion >= 90200)
+ appendPQExpBufferStr(&buf, " LEFT JOIN pg_catalog.pg_user
u ON u.usesysid = t.typowner\n");
+
/*
* do not include complex types (typrelid!=0) unless they are standalone
* composite types
@@ -875,7 +884,7 @@ listAllDbs(const char *pattern, bool verbose)
gettext_noop("Collate"),
gettext_noop("Ctype"));
appendPQExpBufferStr(&buf, " ");
- printACLColumn(&buf, "d.datacl");
+ printACLColumn(&buf, "d.datacl", "\\l", "u.usename", NULL);
if (verbose && pset.sversion >= 80200)
appendPQExpBuffer(&buf,
",\n CASE WHEN
pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
@@ -892,7 +901,8 @@ listAllDbs(const char *pattern, bool verbose)
",\n
pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
gettext_noop("Description"));
appendPQExpBufferStr(&buf,
- "\nFROM pg_catalog.pg_database
d\n");
+ "\nFROM pg_catalog.pg_database
d\n"
+ "LEFT JOIN pg_catalog.pg_user
u ON u.usesysid = d.datdba\n");
if (verbose && pset.sversion >= 80000)
appendPQExpBufferStr(&buf,
" JOIN
pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
@@ -957,7 +967,7 @@ permissionsList(const char *pattern)
gettext_noop("table"), /*
partitioned table */
gettext_noop("Type"));
- printACLColumn(&buf, "c.relacl");
+ printACLColumn(&buf, "c.relacl", "c.relkind", "u.usename", NULL);
if (pset.sversion >= 80400)
appendPQExpBuffer(&buf,
@@ -1035,6 +1045,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " LEFT JOIN
pg_catalog.pg_user u ON u.usesysid = c.relowner\n"
"WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION)
","
CppAsString2(RELKIND_VIEW) ","
@@ -1121,10 +1132,11 @@ listDefaultACLs(const char *pattern)
gettext_noop("schema"),
gettext_noop("Type"));
- printACLColumn(&buf, "d.defaclacl");
+ printACLColumn(&buf, "d.defaclacl", "\\ddp", "u.usename",
"d.defaclobjtype");
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
- " LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
+ " LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n"
+ " LEFT JOIN
pg_catalog.pg_user u ON u.usesysid = d.defaclrole\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL,
@@ -3673,7 +3685,7 @@ listLanguages(const char *pattern, bool verbose, bool
showSystem)
if (pset.sversion >= 90000)
appendPQExpBuffer(&buf,
"l.laninline::pg_catalog.regprocedure AS \"%s\",\n ",
gettext_noop("Inline
handler"));
- printACLColumn(&buf, "l.lanacl");
+ printACLColumn(&buf, "l.lanacl", "\\dL", "u.usename", NULL);
}
appendPQExpBuffer(&buf,
@@ -3684,6 +3696,9 @@ listLanguages(const char *pattern, bool verbose, bool
showSystem)
" AND d.objsubid = 0\n",
gettext_noop("Description"));
+ if (verbose)
+ appendPQExpBufferStr(&buf, "LEFT JOIN pg_catalog.pg_user u ON
u.usesysid = l.lanowner\n");
+
if (pattern)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "l.lanname",
NULL, NULL);
@@ -3752,7 +3767,7 @@ listDomains(const char *pattern, bool verbose, bool
showSystem)
if (pset.sversion >= 90200)
{
appendPQExpBufferStr(&buf, ",\n ");
- printACLColumn(&buf, "t.typacl");
+ printACLColumn(&buf, "t.typacl", "\\dD",
"pg_catalog.pg_get_userbyid(t.typowner)", NULL);
}
appendPQExpBuffer(&buf,
",\n d.description as
\"%s\"",
@@ -4143,7 +4158,7 @@ listSchemas(const char *pattern, bool verbose, bool
showSystem)
if (verbose)
{
appendPQExpBufferStr(&buf, ",\n ");
- printACLColumn(&buf, "n.nspacl");
+ printACLColumn(&buf, "n.nspacl", "\\dn",
"pg_catalog.pg_get_userbyid(n.nspowner)", NULL);
appendPQExpBuffer(&buf,
",\n
pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
gettext_noop("Description"));
@@ -4812,7 +4827,7 @@ listForeignDataWrappers(const char *pattern, bool verbose)
if (verbose)
{
appendPQExpBufferStr(&buf, ",\n ");
- printACLColumn(&buf, "fdwacl");
+ printACLColumn(&buf, "fdwacl", "\\dew",
"pg_catalog.pg_get_userbyid(fdw.fdwowner)", NULL);
appendPQExpBuffer(&buf,
",\n CASE WHEN fdwoptions IS
NULL THEN '' ELSE "
" '(' ||
pg_catalog.array_to_string(ARRAY(SELECT "
@@ -4890,7 +4905,7 @@ listForeignServers(const char *pattern, bool verbose)
if (verbose)
{
appendPQExpBufferStr(&buf, ",\n ");
- printACLColumn(&buf, "s.srvacl");
+ printACLColumn(&buf, "s.srvacl", "\\des",
"pg_catalog.pg_get_userbyid(s.srvowner)", NULL);
appendPQExpBuffer(&buf,
",\n"
" s.srvtype AS \"%s\",\n"
@@ -5548,6 +5563,60 @@ describeSubscriptions(const char *pattern, bool verbose)
return true;
}
+static void
+getACLDefault(PQExpBuffer buf, const char *kind, const char *rolname, const
char *ddp_type)
+{
+ char *ext = pset.sversion >= 80100 ? "E" : "";
+
+ if (strcmp(kind, "\\df") == 0) /* function */
+ appendPQExpBuffer(buf, "%s'=X/' || %s || %s'\\n' || %s ||
%s'=X/' || %s\n",
+ ext, rolname, ext, rolname,
ext, rolname);
+ else if (strcmp(kind, "\\l") == 0) /* list of catalogs (database) */
+ appendPQExpBuffer(buf, "%s'=Tc/' || %s || %s'\\n' || %s ||
%s'=CTc/' || %s\n",
+ ext, rolname, ext, rolname,
ext, rolname);
+ else if (strcmp(kind, "\\dT") == 0 ||
+ strcmp(kind, "\\dL") == 0) /* type & language */
+ appendPQExpBuffer(buf, "%s'=U/' || %s || %s'\\n' || %s ||
%s'=U/' || %s\n",
+ ext, rolname, ext, rolname,
ext, rolname);
+ else if (strcmp(kind, "\\db") == 0) /* tablespace */
+ appendPQExpBuffer(buf, "%s || %s'=C/' || %s\n", rolname, ext,
rolname);
+ else if (strcmp(kind, "\\dn") == 0) /* schema (namespace) */
+ appendPQExpBuffer(buf, "%s || %s'=UC/' || %s\n", rolname, ext,
rolname);
+ else if (strcmp(kind, "\\des") == 0 || strcmp(kind, "\\dew") == 0 ||
+ strcmp(kind, "\\dD") == 0) /* foreign server/data
wrapper, domain */
+ appendPQExpBuffer(buf, "%s || %s'=U/' || %s\n", rolname, ext,
rolname);
+ else if (strcmp(kind, "\\ddp") == 0) /* default acl */
+ {
+ /* ddp defaclacl IS NULL means hardcoded, so recurse */
+ appendPQExpBuffer(buf, "CASE %s WHEN 'r' THEN ", ddp_type);
+ getACLDefault(buf, "'r'", rolname, NULL);
+ appendPQExpBufferStr(buf, " WHEN 'S' THEN ");
+ getACLDefault(buf, "'S'", rolname, NULL);
+ appendPQExpBufferStr(buf, " WHEN 'f' THEN ");
+ getACLDefault(buf, "\\df", rolname, NULL);
+ appendPQExpBufferStr(buf, " WHEN 'T' THEN ");
+ getACLDefault(buf, "\\dT", rolname, NULL);
+ appendPQExpBufferStr(buf, " WHEN 'n' THEN ");
+ getACLDefault(buf, "\\dn", rolname, NULL);
+ appendPQExpBufferStr(buf, " ELSE NULL END");
+ }
+ else /* relation like */
+ appendPQExpBuffer(buf,
+ "%s || '=' || CASE"
+ /* relation-like objects */
+ " WHEN %s = "
CppAsString2(RELKIND_SEQUENCE) " THEN 'rwU'"
+ " WHEN %s IN ("
+
CppAsString2(RELKIND_RELATION) ","
+ CppAsString2(RELKIND_VIEW) ","
+ CppAsString2(RELKIND_MATVIEW)
","
+
CppAsString2(RELKIND_FOREIGN_TABLE) ","
+
CppAsString2(RELKIND_PARTITIONED_TABLE)
+ ") THEN 'arwdDxt'"
+ " ELSE NULL"
+ " END || '/' || %s\n",
+ rolname, kind, kind, rolname);
+}
+
/*
* printACLColumn
*
@@ -5556,14 +5625,15 @@ describeSubscriptions(const char *pattern, bool verbose)
* whitespace or comma decoration.
*/
static void
-printACLColumn(PQExpBuffer buf, const char *colname)
+printACLColumn(PQExpBuffer buf, const char *aclcol, const char *kind, const
char *rolname, const char * ddp_type)
{
- if (pset.sversion >= 80100)
- appendPQExpBuffer(buf,
-
"pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
- colname, gettext_noop("Access
privileges"));
- else
- appendPQExpBuffer(buf,
-
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
- colname, gettext_noop("Access
privileges"));
+ Assert(kind != NULL && rolname != NULL);
+ appendPQExpBuffer(buf,
+ "CASE\n"
+ " WHEN %s IS NULL THEN\n", aclcol);
+ getACLDefault(buf, kind, rolname, ddp_type);
+ appendPQExpBuffer(buf,
+ " ELSE
pg_catalog.array_to_string(%s, %s'\\n')",
+ aclcol, pset.sversion >= 80100 ? "E"
: "");
+ appendPQExpBuffer(buf, " END AS \"%s\"", gettext_noop("Access
privileges"));
}
diff --git a/src/test/regress/expected/foreign_data.out
b/src/test/regress/expected/foreign_data.out
index 75365501d4..effc9f05f0 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -52,12 +52,12 @@ ERROR: foreign-data wrapper "foo" already exists
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+---------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| (testing '1') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+---------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | (testing '1') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
@@ -65,12 +65,12 @@ CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1',
testing '2'); -- ERROR
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
- List of foreign-data
wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+----------------------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| (testing '1', another '2') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List
of foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options
| Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+----------------------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user |
| useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | (testing '1', another
'2') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user |
|
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
@@ -81,12 +81,12 @@ HINT: Must be superuser to create a foreign-data wrapper.
RESET ROLE;
CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+-------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ foo | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(3 rows)
-- HANDLER related checks
@@ -106,12 +106,12 @@ ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar;
-- ERROR
ERROR: function bar(text[], oid) does not exist
ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+-------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
@@ -121,34 +121,34 @@ ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c);
-- ERROR
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| (a '1', b '2') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+----------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | (a '1', b '2') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| (b '3', c '4') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+----------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | (b '3', c '4') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
- List of foreign-data
wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-----------------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| (b '3', c '4', a '2') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+-----------------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | |
useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | (b '3', c '4', a '2') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(3 rows)
SET ROLE regress_test_role;
@@ -158,12 +158,12 @@ HINT: Must be superuser to alter a foreign-data wrapper.
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
- List of foreign-data
wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List
of foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options
| Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+------------------------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user |
| useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | (b '3', c '4', a '2', d
'5') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user |
|
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
@@ -177,22 +177,22 @@ ERROR: permission denied to alter foreign-data wrapper
"foo"
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
- List of foreign-data
wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_test_role_super | - | - |
| (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List
of foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options
| Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+------------------------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user |
| useless
+ foo | regress_test_role_super | - | - |
regress_test_role_super=U/regress_test_role_super | (b '3', c '4', a '2', d
'5') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user |
|
(3 rows)
ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1;
\dew+
- List of foreign-data
wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo1 | regress_test_role_super | - | - |
| (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List
of foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options
| Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+------------------------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user |
| useless
+ foo1 | regress_test_role_super | - | - |
regress_test_role_super=U/regress_test_role_super | (b '3', c '4', a '2', d
'5') |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user |
|
(3 rows)
ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo;
@@ -210,12 +210,12 @@ ERROR: foreign-data wrapper "nonexistent" does not exist
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
- List of
foreign-data wrappers
- Name | Owner | Handler | Validator
| Access privileges | FDW options | Description
-------------+---------------------------+------------------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | -
| | | useless
- foo | regress_test_role_super | test_fdw_handler | -
| | (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - |
postgresql_fdw_validator | | |
+
List of foreign-data wrappers
+ Name | Owner | Handler | Validator
| Access privileges | FDW
options | Description
+------------+---------------------------+------------------+--------------------------+-------------------------------------------------------+------------------------------+-------------
+ dummy | regress_foreign_data_user | - | -
| regress_foreign_data_user=U/regress_foreign_data_user |
| useless
+ foo | regress_test_role_super | test_fdw_handler | -
| regress_test_role_super=U/regress_test_role_super | (b '3', c '4',
a '2', d '5') |
+ postgresql | regress_foreign_data_user | - |
postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user |
|
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
@@ -226,11 +226,11 @@ DROP FOREIGN DATA WRAPPER foo;
RESET ROLE;
DROP ROLE regress_test_role_super;
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+-------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(2 rows)
CREATE FOREIGN DATA WRAPPER foo;
@@ -242,19 +242,19 @@ ERROR: user mapping for "regress_foreign_data_user"
already exists for server s
CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
NOTICE: user mapping for "regress_foreign_data_user" already exists for
server s1, skipping
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- foo | regress_foreign_data_user | - | - |
| |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+-------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ foo | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | |
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(3 rows)
\des+
- List of foreign servers
- Name | Owner | Foreign-data wrapper | Access privileges |
Type | Version | FDW options | Description
-------+---------------------------+----------------------+-------------------+------+---------+-------------+----------------
- s1 | regress_foreign_data_user | foo | |
| | | foreign server
+ List of
foreign servers
+ Name | Owner | Foreign-data wrapper |
Access privileges | Type | Version | FDW options |
Description
+------+---------------------------+----------------------+-------------------------------------------------------+------+---------+-------------+----------------
+ s1 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | |
| foreign server
(1 row)
\deu+
@@ -278,11 +278,11 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to server s1
drop cascades to user mapping for regress_foreign_data_user on server s1
\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator |
Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - |
| | useless
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
| |
+ List of
foreign-data wrappers
+ Name | Owner | Handler | Validator |
Access privileges | FDW options |
Description
+------------+---------------------------+---------+--------------------------+-------------------------------------------------------+-------------+-------------
+ dummy | regress_foreign_data_user | - | - |
regress_foreign_data_user=U/regress_foreign_data_user | | useless
+ postgresql | regress_foreign_data_user | - | postgresql_fdw_validator |
regress_foreign_data_user=U/regress_foreign_data_user | |
(2 rows)
\des+
@@ -317,17 +317,17 @@ ERROR: invalid option "foo"
HINT: Valid options in this context are: authtype, service, connect_timeout,
dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost',
dbname 's8db');
\des+
- List of foreign
servers
- Name | Owner | Foreign-data wrapper | Access privileges |
Type | Version | FDW options | Description
-------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
- s1 | regress_foreign_data_user | foo | |
| | |
- s2 | regress_foreign_data_user | foo | |
| | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | |
oracle | | |
- s4 | regress_foreign_data_user | foo | |
oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | |
| 15.0 | |
- s6 | regress_foreign_data_user | foo | |
| 16.0 | (host 'a', dbname 'b') |
- s7 | regress_foreign_data_user | foo | |
oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | |
| | (host 'localhost', dbname 's8db') |
+
List of foreign servers
+ Name | Owner | Foreign-data wrapper |
Access privileges | Type | Version | FDW options
| Description
+------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+-----------------------------------+-------------
+ s1 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | |
|
+ s2 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
|
+ s4 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
(host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 15.0 |
|
+ s6 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 16.0 |
(host 'a', dbname 'b') |
+ s7 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | 17.0 |
(host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | postgresql |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(host 'localhost', dbname 's8db') |
(8 rows)
SET ROLE regress_test_role;
@@ -339,18 +339,18 @@ SET ROLE regress_test_role;
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
- List of foreign
servers
- Name | Owner | Foreign-data wrapper | Access privileges |
Type | Version | FDW options | Description
-------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
- s1 | regress_foreign_data_user | foo | |
| | |
- s2 | regress_foreign_data_user | foo | |
| | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | |
oracle | | |
- s4 | regress_foreign_data_user | foo | |
oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | |
| 15.0 | |
- s6 | regress_foreign_data_user | foo | |
| 16.0 | (host 'a', dbname 'b') |
- s7 | regress_foreign_data_user | foo | |
oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | |
| | (host 'localhost', dbname 's8db') |
- t1 | regress_test_role | foo | |
| | |
+
List of foreign servers
+ Name | Owner | Foreign-data wrapper |
Access privileges | Type | Version | FDW options
| Description
+------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+-----------------------------------+-------------
+ s1 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | |
|
+ s2 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
|
+ s4 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
(host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 15.0 |
|
+ s6 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 16.0 |
(host 'a', dbname 'b') |
+ s7 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | 17.0 |
(host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | postgresql |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(host 'localhost', dbname 's8db') |
+ t1 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
@@ -363,19 +363,19 @@ GRANT regress_test_indirect TO regress_test_role;
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
- List of foreign
servers
- Name | Owner | Foreign-data wrapper | Access privileges |
Type | Version | FDW options | Description
-------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
- s1 | regress_foreign_data_user | foo | |
| | |
- s2 | regress_foreign_data_user | foo | |
| | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | |
oracle | | |
- s4 | regress_foreign_data_user | foo | |
oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | |
| 15.0 | |
- s6 | regress_foreign_data_user | foo | |
| 16.0 | (host 'a', dbname 'b') |
- s7 | regress_foreign_data_user | foo | |
oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | |
| | (host 'localhost', dbname 's8db') |
- t1 | regress_test_role | foo | |
| | |
- t2 | regress_test_role | foo | |
| | |
+
List of foreign servers
+ Name | Owner | Foreign-data wrapper |
Access privileges | Type | Version | FDW options
| Description
+------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+-----------------------------------+-------------
+ s1 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | |
|
+ s2 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
|
+ s4 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
(host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 15.0 |
|
+ s6 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 16.0 |
(host 'a', dbname 'b') |
+ s7 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | 17.0 |
(host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | postgresql |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(host 'localhost', dbname 's8db') |
+ t1 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
+ t2 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
(10 rows)
RESET ROLE;
@@ -398,16 +398,16 @@ GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2
WITH GRANT OPTION;
------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+-----------------------------------+-------------
s1 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user+| | 1.0 |
(servername 's1') |
| | |
regress_test_role=U/regress_foreign_data_user | | |
|
- s2 | regress_foreign_data_user | foo |
| | 1.1 | (host 'a', dbname 'b')
|
- s3 | regress_foreign_data_user | foo |
| oracle | | ("tns name" 'orcl', port
'1521') |
- s4 | regress_foreign_data_user | foo |
| oracle | | (host 'a', dbname 'b')
|
- s5 | regress_foreign_data_user | foo |
| | 15.0 |
|
+ s2 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 1.1 |
(host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
("tns name" 'orcl', port '1521') |
+ s4 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
(host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 15.0 |
|
s6 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 |
(host 'a', dbname 'b') |
| | |
regress_test_role2=U*/regress_foreign_data_user | | |
|
- s7 | regress_foreign_data_user | foo |
| oracle | 17.0 | (host 'a', dbname 'b')
|
- s8 | regress_foreign_data_user | postgresql |
| | | (host 'localhost',
dbname 's8db') |
- t1 | regress_test_role | foo |
| | |
|
- t2 | regress_test_role | foo |
| | |
|
+ s7 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | 17.0 |
(host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | postgresql |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(host 'localhost', dbname 's8db') |
+ t1 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
+ t2 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
(10 rows)
SET ROLE regress_test_role;
@@ -448,16 +448,16 @@ privileges for foreign-data wrapper foo
Name | Owner | Foreign-data wrapper |
Access privileges | Type | Version | FDW
options | Description
------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+-------------
s1 | regress_test_indirect | foo |
regress_test_indirect=U/regress_test_indirect | | 1.1 |
(servername 's1') |
- s2 | regress_foreign_data_user | foo |
| | 1.1 | (host 'a', dbname 'b')
|
- s3 | regress_foreign_data_user | foo |
| oracle | | ("tns name" 'orcl', port
'1521') |
- s4 | regress_foreign_data_user | foo |
| oracle | | (host 'a', dbname 'b')
|
- s5 | regress_foreign_data_user | foo |
| | 15.0 |
|
+ s2 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 1.1 |
(host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
("tns name" 'orcl', port '1521') |
+ s4 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
(host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 15.0 |
|
s6 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 |
(host 'a', dbname 'b') |
| | |
regress_test_role2=U*/regress_foreign_data_user | | |
|
- s7 | regress_foreign_data_user | foo |
| oracle | 17.0 | (host 'a', dbname 'b')
|
- s8 | regress_foreign_data_user | postgresql |
| | | (dbname 'db1',
connect_timeout '30') |
- t1 | regress_test_role | foo |
| | |
|
- t2 | regress_test_role | foo |
| | |
|
+ s7 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | 17.0 |
(host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | postgresql |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(dbname 'db1', connect_timeout '30') |
+ t1 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
+ t2 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
(10 rows)
ALTER SERVER s8 RENAME to s8new;
@@ -466,16 +466,16 @@ ALTER SERVER s8 RENAME to s8new;
Name | Owner | Foreign-data wrapper |
Access privileges | Type | Version | FDW
options | Description
-------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+-------------
s1 | regress_test_indirect | foo |
regress_test_indirect=U/regress_test_indirect | | 1.1 |
(servername 's1') |
- s2 | regress_foreign_data_user | foo |
| | 1.1 | (host 'a', dbname 'b')
|
- s3 | regress_foreign_data_user | foo |
| oracle | | ("tns name" 'orcl',
port '1521') |
- s4 | regress_foreign_data_user | foo |
| oracle | | (host 'a', dbname 'b')
|
- s5 | regress_foreign_data_user | foo |
| | 15.0 |
|
+ s2 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 1.1 |
(host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
("tns name" 'orcl', port '1521') |
+ s4 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | |
(host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | | 15.0 |
|
s6 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 |
(host 'a', dbname 'b') |
| | |
regress_test_role2=U*/regress_foreign_data_user | | |
|
- s7 | regress_foreign_data_user | foo |
| oracle | 17.0 | (host 'a', dbname 'b')
|
- s8new | regress_foreign_data_user | postgresql |
| | | (dbname 'db1',
connect_timeout '30') |
- t1 | regress_test_role | foo |
| | |
|
- t2 | regress_test_role | foo |
| | |
|
+ s7 | regress_foreign_data_user | foo |
regress_foreign_data_user=U/regress_foreign_data_user | oracle | 17.0 |
(host 'a', dbname 'b') |
+ s8new | regress_foreign_data_user | postgresql |
regress_foreign_data_user=U/regress_foreign_data_user | | |
(dbname 'db1', connect_timeout '30') |
+ t1 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
+ t2 | regress_test_role | foo |
regress_test_role=U/regress_test_role | | |
|
(10 rows)
ALTER SERVER s8new RENAME to s8;
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index 3818cfea7e..79a790b0a8 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -3243,3 +3243,124 @@ last error message: division by zero
\echo 'last error code:' :LAST_ERROR_SQLSTATE
last error code: 22012
\unset FETCH_COUNT
+-- various \d* default permission tests
+CREATE USER d_test_user;
+CREATE GROUP d_test_group;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ GRANT ALL ON SCHEMAS TO GROUP d_test_group WITH GRANT OPTION;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ GRANT USAGE ON SCHEMAS TO PUBLIC;
+\ddp
+ Default access privileges
+ Owner | Schema | Type | Access privileges
+-------------+--------+--------+-------------------------------
+ d_test_user | | schema | =U/d_test_user +
+ | | | d_test_user=UC/d_test_user +
+ | | | d_test_group=U*C*/d_test_user
+(1 row)
+
+CREATE TABLE dp_test_table(id SERIAL PRIMARY KEY, stuff TEXT NOT NULL);
+ALTER TABLE dp_test_table OWNER TO d_test_user;
+GRANT SELECT (stuff) ON dp_test_table TO PUBLIC;
+\dp dp_test_table
+ Access privileges
+ Schema | Name | Type | Access privileges | Column
privileges | Policies
+--------+---------------+-------+---------------------------------+-------------------+----------
+ public | dp_test_table | table | d_test_user=arwdDxt/d_test_user | stuff:
+|
+ | | | |
=r/d_test_user |
+(1 row)
+
+CREATE FUNCTION df_test_function() RETURNS INT AS 'SELECT 1;' LANGUAGE SQL;
+ALTER FUNCTION df_test_function() OWNER TO d_test_user;
+\df df_test_function
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+------------------+------------------+---------------------+------
+ public | df_test_function | integer | | func
+(1 row)
+
+\df+ df_test_function
+
List of functions
+ Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges |
Language | Source code | Description
+--------+------------------+------------------+---------------------+------+------------+----------+-------------+----------+---------------------------+----------+-------------+-------------
+ public | df_test_function | integer | | func |
volatile | unsafe | d_test_user | invoker | =X/d_test_user +|
sql | SELECT 1; |
+ | | | | |
| | | | d_test_user=X/d_test_user |
| |
+(1 row)
+
+CREATE DOMAIN dD_test_domain AS INTEGER CHECK(VALUE > 18);
+ALTER DOMAIN dD_test_domain OWNER TO d_test_user;
+\dD dD_test_domain
+ List of domains
+ Schema | Name | Type | Collation | Nullable | Default |
Check
+--------+----------------+---------+-----------+----------+---------+--------------------
+ public | dd_test_domain | integer | | | | CHECK
(VALUE > 18)
+(1 row)
+
+\dD+ dD_test_domain
+ List of domains
+ Schema | Name | Type | Collation | Nullable | Default |
Check | Access privileges | Description
+--------+----------------+---------+-----------+----------+---------+--------------------+---------------------------+-------------
+ public | dd_test_domain | integer | | | | CHECK
(VALUE > 18) | d_test_user=U/d_test_user |
+(1 row)
+
+CREATE TYPE dT_test_type AS ENUM ('PostgreSQL');
+ALTER TYPE dT_test_type OWNER TO d_test_user;
+\dT dT_test_type
+ List of data types
+ Schema | Name | Description
+--------+--------------+-------------
+ public | dt_test_type |
+(1 row)
+
+\dT+ dT_test_type
+ List of data types
+ Schema | Name | Internal name | Size | Elements | Owner |
Access privileges | Description
+--------+--------------+---------------+------+------------+-------------+---------------------------+-------------
+ public | dt_test_type | dt_test_type | 4 | PostgreSQL | d_test_user |
=U/d_test_user +|
+ | | | | | |
d_test_user=U/d_test_user |
+(1 row)
+
+CREATE SCHEMA dn_test_schema;
+ALTER SCHEMA dn_test_schema OWNER TO d_test_user;
+GRANT ALL ON SCHEMA dn_test_schema TO PUBLIC;
+\dn dn_test_schema
+ List of schemas
+ Name | Owner
+----------------+-------------
+ dn_test_schema | d_test_user
+(1 row)
+
+\dn+ dn_test_schema
+ List of schemas
+ Name | Owner | Access privileges | Description
+----------------+-------------+----------------------------+-------------
+ dn_test_schema | d_test_user | d_test_user=UC/d_test_user+|
+ | | =UC/d_test_user |
+(1 row)
+
+CREATE DATABASE l_test_database;
+ALTER DATABASE l_test_database OWNER TO d_test_user;
+GRANT ALL ON DATABASE l_test_database TO GROUP d_test_group;
+\l l_test_database
+ List of databases
+ Name | Owner | Encoding | Collate | Ctype |
Access privileges
+-----------------+-------------+----------+-------------+-------------+------------------------------
+ l_test_database | d_test_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=Tc/d_test_user +
+ | | | | |
d_test_user=CTc/d_test_user +
+ | | | | |
d_test_group=CTc/d_test_user
+(1 row)
+
+-- not tested (here): \dL \db \des \dew
+-- cleanup \d* tests
+DROP DATABASE l_test_database;
+DROP SCHEMA dn_test_schema;
+DROP TYPE dT_test_type;
+DROP DOMAIN dD_test_domain;
+DROP FUNCTION df_test_function();
+DROP TABLE dp_test_table;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ REVOKE ALL ON SCHEMAS FROM GROUP d_test_group;
+DROP GROUP d_test_group;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ REVOKE ALL ON SCHEMAS FROM PUBLIC;
+DROP USER d_test_user;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b45da9bb8d..ab6f0146fa 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -688,3 +688,52 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
\echo 'last error code:' :LAST_ERROR_SQLSTATE
\unset FETCH_COUNT
+
+-- various \d* default permission tests
+CREATE USER d_test_user;
+CREATE GROUP d_test_group;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ GRANT ALL ON SCHEMAS TO GROUP d_test_group WITH GRANT OPTION;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ GRANT USAGE ON SCHEMAS TO PUBLIC;
+\ddp
+CREATE TABLE dp_test_table(id SERIAL PRIMARY KEY, stuff TEXT NOT NULL);
+ALTER TABLE dp_test_table OWNER TO d_test_user;
+GRANT SELECT (stuff) ON dp_test_table TO PUBLIC;
+\dp dp_test_table
+CREATE FUNCTION df_test_function() RETURNS INT AS 'SELECT 1;' LANGUAGE SQL;
+ALTER FUNCTION df_test_function() OWNER TO d_test_user;
+\df df_test_function
+\df+ df_test_function
+CREATE DOMAIN dD_test_domain AS INTEGER CHECK(VALUE > 18);
+ALTER DOMAIN dD_test_domain OWNER TO d_test_user;
+\dD dD_test_domain
+\dD+ dD_test_domain
+CREATE TYPE dT_test_type AS ENUM ('PostgreSQL');
+ALTER TYPE dT_test_type OWNER TO d_test_user;
+\dT dT_test_type
+\dT+ dT_test_type
+CREATE SCHEMA dn_test_schema;
+ALTER SCHEMA dn_test_schema OWNER TO d_test_user;
+GRANT ALL ON SCHEMA dn_test_schema TO PUBLIC;
+\dn dn_test_schema
+\dn+ dn_test_schema
+CREATE DATABASE l_test_database;
+ALTER DATABASE l_test_database OWNER TO d_test_user;
+GRANT ALL ON DATABASE l_test_database TO GROUP d_test_group;
+\l l_test_database
+-- not tested (here): \dL \db \des \dew
+
+-- cleanup \d* tests
+DROP DATABASE l_test_database;
+DROP SCHEMA dn_test_schema;
+DROP TYPE dT_test_type;
+DROP DOMAIN dD_test_domain;
+DROP FUNCTION df_test_function();
+DROP TABLE dp_test_table;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ REVOKE ALL ON SCHEMAS FROM GROUP d_test_group;
+DROP GROUP d_test_group;
+ALTER DEFAULT PRIVILEGES FOR USER d_test_user
+ REVOKE ALL ON SCHEMAS FROM PUBLIC;
+DROP USER d_test_user;
