Usability fail with psql's \dp command
I noticed today that \dp does not distinguish empty acl fields
(meaning nobody has any privileges) from null acl fields
(which mean default privileges, typically not empty).
For instance
regression=# \c joe joe
You are now connected to database "joe" as user "joe".
joe=> create table jt (f1 int);
CREATE TABLE
joe=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | jt | table | | |
(1 row)
joe=> insert into jt values(1);
INSERT 0 1
joe=> revoke all on table jt from joe;
REVOKE
joe=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | jt | table | | |
(1 row)
joe=> insert into jt values(1);
ERROR: permission denied for table jt
So those are definitely different privilege states, but they look
the same.
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.
What do people think of printing "Default" if the ACL is null?
Alternatively, since the state with an empty ACL is certainly
the unusual case, maybe we should mark that specially, perhaps
by printing "None" or "No privileges".
(I've not looked at the code to see how hard such changes would be.)
regards, tom lane
On Jul 28, 2018, at 11:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alternatively, since the state with an empty ACL is certainly
the unusual case, maybe we should mark that specially, perhaps
by printing "None" or "No privileges".
+1 for "No privileges". I was just bitted by that this week.
--
-- Christophe Pettus
xof@thebuild.com
What do people think of printing "Default" if the ACL is null?
Alternatively, since the state with an empty ACL is certainly
the unusual case, maybe we should mark that specially, perhaps
by printing "None" or "No privileges".
Old problem. +1.
On Sat, Jul 28, 2018 at 02:41:24PM -0400, Tom Lane wrote:
I noticed today that \dp does not distinguish empty acl fields
(meaning nobody has any privileges) from null acl fields
(which mean default privileges, typically not empty).
For instanceregression=# \c joe joe
You are now connected to database "joe" as user "joe".
joe=> create table jt (f1 int);
CREATE TABLE
joe=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | jt | table | | |
(1 row)joe=> insert into jt values(1);
INSERT 0 1
joe=> revoke all on table jt from joe;
REVOKE
joe=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | jt | table | | |
(1 row)joe=> insert into jt values(1);
ERROR: permission denied for table jtSo those are definitely different privilege states, but they look
the same.
Please find attached a patch to fix this. Would this be a
back-patchable bug?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
0001-Show-that-all-privileges-revoked-is-a-distinct-state.patchtext/x-diff; charset=us-asciiDownload
From 8b2b8c2b7ab7a731bab479afff778afbda3f0b9c Mon Sep 17 00:00:00 2001
From: David Fetter <david@fetter.org>
Date: Sat, 28 Jul 2018 13:33:46 -0700
Subject: [PATCH] Show that "all privileges revoked" is a distinct state from
default
To: pgsql-hackers@postgresql.org
---
src/bin/psql/describe.c | 8 ++++----
src/test/regress/expected/psql.out | 18 ++++++++++++++++++
src/test/regress/sql/psql.sql | 7 +++++++
3 files changed, 29 insertions(+), 4 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 80d8338b96..711e94c023 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5560,10 +5560,10 @@ printACLColumn(PQExpBuffer buf, const char *colname)
{
if (pset.sversion >= 80100)
appendPQExpBuffer(buf,
- "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
- colname, gettext_noop("Access privileges"));
+ "CASE pg_catalog.array_upper(%s,1) WHEN 0 THEN 'No privileges' ELSE pg_catalog.array_to_string(%s, E'\\n') END AS \"%s\"",
+ colname, colname, gettext_noop("Access privileges"));
else
appendPQExpBuffer(buf,
- "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
- colname, gettext_noop("Access privileges"));
+ "CASE pg_catalog.array_upper(%s,1) WHEN 0 THEN 'No privileges' ELSE pg_catalog.array_to_string(%s, '\\n') END AS \"%s\"",
+ colname, colname, gettext_noop("Access privileges"));
}
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3818cfea7e..809ca27191 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -3243,3 +3243,21 @@ last error message: division by zero
\echo 'last error code:' :LAST_ERROR_SQLSTATE
last error code: 22012
\unset FETCH_COUNT
+-- show when a user has no permissions on a table
+CREATE TABLE no_permissions_test(f1 int);
+\dp no_permissions_test;
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------------+-------+-------------------+-------------------+----------
+ public | no_permissions_test | table | | |
+(1 row)
+
+REVOKE ALL ON TABLE no_permissions_test FROM CURRENT_USER;
+\dp no_permissions_test;
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------------+-------+-------------------+-------------------+----------
+ public | no_permissions_test | table | No privileges | |
+(1 row)
+
+DROP TABLE no_permissions_test;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b45da9bb8d..68a2fb06ce 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -688,3 +688,10 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
\echo 'last error code:' :LAST_ERROR_SQLSTATE
\unset FETCH_COUNT
+
+-- show when a user has no permissions on a table
+CREATE TABLE no_permissions_test(f1 int);
+\dp no_permissions_test;
+REVOKE ALL ON TABLE no_permissions_test FROM CURRENT_USER;
+\dp no_permissions_test;
+DROP TABLE no_permissions_test;
--
2.17.1
So those are definitely different privilege states, but they look
the same.Please find attached a patch to fix this. Would this be a
back-patchable bug?
My 0.02ￜ: this creates an exception for anyone trying to parse the output.
I would have preferred empty logically meaning no rights, and the default
being spelled out explicitely.
--
Fabien.
On Sat, Jul 28, 2018 at 08:11:17PM -0400, Fabien COELHO wrote:
So those are definitely different privilege states, but they look
the same.Please find attached a patch to fix this. Would this be a
back-patchable bug?My 0.02€: this creates an exception for anyone trying to parse the output.
I would have preferred empty logically meaning no rights, and the default
being spelled out explicitely.
If we were designing this on a clean sheet of paper, I'd agree that
what you propose is a better UI, but it's a significant change from
what we have now. People parsing much more common output--this is
pretty corner-case--will have to make changes. Do you think it's
"better enough" to break compatibility in the way you propose, forcing
it into 12?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Fabien COELHO <coelho@cri.ensmp.fr> writes:
My 0.02¤: this creates an exception for anyone trying to parse the output.
I would have preferred empty logically meaning no rights, and the default
being spelled out explicitely.
Uh, who'd be trying to parse the output of \dp?
The reason we provide psql's -E option is so that people can look at the
underlying queries and adapt them to their own purposes. In many cases,
that would include stripping out provisions that make the output more
human-friendly and less machine-friendly. (Localization of output words
is one obvious example of things that psql does that are quite
machine-parsing-unfriendly.) This seems to me to be another such case.
We could certainly consider the explicit-default approach (and it's one
of the options I suggested), but to my mind we should evaluate the
options entirely on what humans find readable, with exactly zero weight
to machine readability.
regards, tom lane
My 0.02�: this creates an exception for anyone trying to parse the output.
I would have preferred empty logically meaning no rights, and the default
being spelled out explicitely.Uh, who'd be trying to parse the output of \dp?
Ok. Maybe humans?
Note that 'No privileges' could be somehow interpreted as "default
privileges" (no "special/given" privileges) or as "no permissions at all",
so there is still some ambiguity, at least for me.
We could certainly consider the explicit-default approach (and it's one
of the options I suggested), but to my mind we should evaluate the
options entirely on what humans find readable, with exactly zero weight
to machine readability.
Ok. So I agree with your suggestion, on the ground of avoiding a special
output syntax in one particular case if possible.
Attached is a quick and dirty attempt at regenerating default privileges
from dp query, with an added join on roles & and test on kind.
I'm not 100% sure of the list of privileges for all types, and I do not
like much having them in a query like that because in the unlikely event
that a new one is added, the query output suddenly becomes false.
From a programming point of view there is another pain with that approach
as "describe.c" uses "printACLColumn", but this version would need the
kind and the owner role as well, and it seems that all 11 instances of
printACLColumn should be adapted as well.
As for David point of breaking anything from a user perspective, as the
current output is currently ambiguous thus unreliable/unusable, I think it
is more a bug fix than anything else.
--
Fabien.
Attachments:
Hello.
At Sun, 29 Jul 2018 21:34:29 -0400 (EDT), Fabien COELHO <coelho@cri.ensmp.fr> wrote in <alpine.DEB.2.21.1807291818460.14827@lancre>
My 0.02¤: this creates an exception for anyone trying to parse the
output.
I would have preferred empty logically meaning no rights, and the
default
being spelled out explicitely.Uh, who'd be trying to parse the output of \dp?
Ok. Maybe humans?
Note that 'No privileges' could be somehow interpreted as "default
privileges" (no "special/given" privileges) or as "no permissions at
all", so there is still some ambiguity, at least for me.
FWIW "No privileges" seems to me as "The user cannot access it at
all" with no ambiguity.
Currently the behavior is documented here. (This needs to be
edited.)
https://www.postgresql.org/docs/10/static/sql-grant.html
| If the “Access privileges” 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
| PUBLIC depending on the object type, as explained above. The
| first GRANT or REVOKE on an object will instantiate the default
| privileges (producing, for example, {miriam=arwdDxt/miriam}) and
| then modify them per the specified request.
So it changes the existing documented behavior.
What is most significant to me here is it's confusing that the
empty representation means rather opposite things for
pg_class.relacl and the correspondent in \dp's output.
relacl | Access privileges
--------+------------------
(null) | joe=arwdDxt/joe
{} | (null)
We could certainly consider the explicit-default approach (and it's
one
of the options I suggested), but to my mind we should evaluate the
options entirely on what humans find readable, with exactly zero
weight
to machine readability.Ok. So I agree with your suggestion, on the ground of avoiding a
special output syntax in one particular case if possible.
\dp is a convenient shortcut for users so the output should be
intuitive or easy-to-grasp. If we wanted to use the output as a
input of other programs, we are to use bare tables.. maybe, or
should handle the special indications. But, if we were to change
the documented behavior, I'd propose the following.
relacl | Access privileges
--------+------------------
(null) | '(default)'
{} | '(no privilege)'
The parentheses ('()') can be '<>' as pg_stat_activity uses for a
content with a special meaning. (<insufficient privilege>). Also
I found that \dC shows '(binary coercible)' when the cast is a
relabel. So I'm not confident on whether to use but I'd like to
choose '()' for them. Both are not "parsable" for... a human?
Attached is a quick and dirty attempt at regenerating default
privileges from dp query, with an added join on roles & and test on
kind.I'm not 100% sure of the list of privileges for all types, and I do
not like much having them in a query like that because in the unlikely
event that a new one is added, the query output suddenly becomes
false.From a programming point of view there is another pain with that
approach as "describe.c" uses "printACLColumn", but this version would
need the kind and the owner role as well, and it seems that all 11
instances of printACLColumn should be adapted as well.As for David point of breaking anything from a user perspective, as
the current output is currently ambiguous thus unreliable/unusable, I
think it is more a bug fix than anything else.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
Hello Kyotaro-san,
Note that 'No privileges' could be somehow interpreted as "default
privileges" (no "special/given" privileges) or as "no permissions at
all", so there is still some ambiguity, at least for me.FWIW "No privileges" seems to me as "The user cannot access it at
all" with no ambiguity.
Ok. For me '' and 'No privileges' still looks like they mean the same,
whereas the point of the patch is to solve the ambiguity.
Currently the behavior is documented here. (This needs to be
edited.)
Sure, but user friendlyness would suggest that the output should not be
misleading from the start.
So it changes the existing documented behavior.
Sure. The behavior is misleading, and documentation is of little help in
such a case.
\dp is a convenient shortcut for users so the output should be
intuitive or easy-to-grasp.
Yes!
[...]
relacl | Access privileges
--------+------------------
(null) | '(default)'
{} | '(no privilege)'
This suggestion is better as it avoids the "empty/no" ambiguity. It breaks
the documented behavior, but I'm fine with that anyway.
The human I am now has to know what "default" permissions are depending on
the kind of object, where it could have said it to me directly. Moreover,
the line is not self-contained because the default permission depends on
the owner, but "\dp" does not tell who the owner is, which is another
annoyance.
A benefit of your approach is that its coding is easy because it does not
have to fetch the owner tuple and reconstruct the default perms depending
on the kind of object.
A cleaner approach would be to have a NOT NULL column and have the default
always explicit, instead of having a lazy instantiation of the field
managed on GRANT/REVOKE but not on initialization. However this is
probably too big a change for the problem at hand, and it would not solve
the ambiguity issue for previous versions.
--
Fabien.
On Sat, Jul 28, 2018 at 4:36 PM, David Fetter <david@fetter.org> wrote:
Please find attached a patch to fix this. Would this be a
back-patchable bug?
In my view, this is not a bug fix, but an improvement, and therefore
should not be back-patched.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Tue, Jul 31, 2018 at 7:24 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Sat, Jul 28, 2018 at 4:36 PM, David Fetter <david@fetter.org> wrote:
Please find attached a patch to fix this. Would this be a
back-patchable bug?In my view, this is not a bug fix, but an improvement, and therefore
should not be back-patched.
I was leaning toward "bug fix" but what we are actually doing here is
compensating for our default presentation of null being the empty string;
so I'm inclined to side with "usability" and not back-patching.
David J.
On 28.07.2018 21:41, Tom Lane wrote:
I noticed today that \dp does not distinguish empty acl fields
(meaning nobody has any privileges) from null acl fields
(which mean default privileges, typically not empty).
This confusing behavior exists not only for \dp command.
Consider schemas and \dn+ command:
postgres=# create schema s authorization u;
CREATE SCHEMA
postgres=# \dn+ s
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
s | u | |
(1 row)
postgres=# \c - u
You are now connected to database "postgres" as user "u".
postgres=> create table s.t(id int);
CREATE TABLE
postgres=> revoke all on schema s from u;
REVOKE
postgres=> \dn+ s
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
s | u | |
(1 row)
postgres=> create table s.t2(id int);
ERROR: permission denied for schema s
LINE 1: create table s.t2(id int);
One idea is to replace a null ACL value with the actual effective
permissions, which we could get from the acldefault() function.
As for me, this is a right option.
Very hard to describe (I am engaged in the development of training
courses) why after GRANT command
we see two records in acl column, but after CREATE TABLE - no records.
Phrases like "for historical reasons" are not very convincing:
postgres=# create table t (id int);
CREATE TABLE
postgres=# \dp t
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | t | table | | |
(1 row)
postgres=# grant select on t to u;
GRANT
postgres=# \dp t
Access privileges
Schema | Name | Type | Access privileges | Column privileges
| Policies
--------+------+-------+---------------------------+-------------------+----------
public | t | table | postgres=arwdDxt/postgres+| |
| | | u=r/postgres | |
-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
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.
Attachments:
psql-describe-default-perms-1.patchtext/plain; name=psql-describe-default-perms-1.patchDownload
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;
Hello Pavel,
I noticed today that \dp does not distinguish empty acl fields
(meaning nobody has any privileges) from null acl fields
(which mean default privileges, typically not empty).This confusing behavior exists not only for \dp command.
Consider schemas and \dn+ command:
Indeed, all \d* which display perms have the empty/default confusion:
\dp \ddp \des \dew \l \dn \db \df \dT \dD \dL
I fixed them all to display the default acl in the patch I just sent.
I also noticed that although large objects have permissions, they are not
printed by any backslash commands.
--
Fabien.
On 31 July 2018 at 15:02, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
[....]
Indeed, all \d* which display perms have the empty/default confusion:
\dp \ddp \des \dew \l \dn \db \df \dT \dD \dL
I fixed them all to display the default acl in the patch I just sent.
I also noticed that although large objects have permissions, they are not
printed by any backslash commands.Also using \df to display permissions is inconvenient because \df+ is
required, which also shows function source code which usually overwhelms
the rest of the display. Any chance we can remove the source code column
from \df now that we have \sf? I usually avoid looking at function
permissions and select directly from pg_proc if I absolutely must know.
Fabien,
On 31.07.2018 22:02, Fabien COELHO wrote:
Indeed, all \d* which display perms have the empty/default confusion:
\dp \ddp \des \dew \l \dn \db \df \dT \dD \dL
I fixed them all to display the default acl in the patch I just sent.
I also noticed that although large objects have permissions, they are
not printed by any backslash commands.
I tried your patch. From my point of view this is desirable behavior.
Hopes this patch will be included in v12.
-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company