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 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.
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
>From 8b2b8c2b7ab7a731bab479afff778afbda3f0b9c Mon Sep 17 00:00:00 2001
From: David Fetter <da...@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-hack...@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
