1

In postgresql documentation on GRANT option it is written:

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. (The owner could, however, choose to revoke some of their own privileges for safety.)

Now I have a database database table with an owner user, yet I receive ERROR: permission denied for table alert whenever I try to select from this table:

database=# SET ROLE user;
SET
database=> select * from pg_tables where tablename = 'alert';
 schemaname | tablename |     tableowner     | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+--------------------+------------+------------+----------+-------------+-------------
 public     | alert     | user |            | t          | f        | f           | f
(1 строка)
database=> select * from alert limit 1;
ERROR:  permission denied for table alert

Why is this?

There are no additional table privileges:

database=> \z                                             Access privileges
 Schema |                Name                |   Type   | Access privileges | Column privileges | Policies 
--------+------------------------------------+----------+-------------------+-------------------+----------
 public | alert                              | table    |                   |                   |

Also, schema and database owner was changed to user.

To workaround this issue I have to GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user; and then everything works fine.

So, reiterating question: how is it possible that owner is unable to select from table? Yes, I did run REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM user; but I expect this to reset privileges to default values and, probably, this my assumption is wrong. But then, how can I find out, that my permissions are not default any more? If I look at \z output everything looks as it was before GRANT/REVOKE. Also, looking at other databases, where I have no problems with select, I don't see any difference in output of \z. After lot's of googling I found that \l output (and I suppose \z) are not shows for default values. But then how can I find this defaults? \ddp output is empty and I'm lost here :(

Thanks in advance for any help.

Improve this question

1 Answer 1

Reset to default
2

Your expectation is wrong: if you revoke all privileges on the tables from the table owner, the table owner doesn't have any privileges on the tables and cannot select from them.

By granting all privileges to the table owner, you are restoring the default.

The confusing part is that \z or \dp show empty privileges for both cases: the initial state (default privileges) and no privileges at all (which you inadvertently created). PostgreSQL v17 fixes that by showing (none) in the latter case.

Improve this answer
2
  • Thank you for your answer @Laurenz! One last question that puzzles me: how can I get back default permissions, so that \z will show empty output again? I've table permissions for two databases, where permissions are default and where they are not, and I don't see a difference. To compare permissions I've used: SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE table_schema = 'public'; Commented Nov 24, 2023 at 10:15
  • 1
    You cannot do that. Once the NULL value on pg_class has been replaced with an ACL entry, you will never get the NULL value back. But don't worry: the permissions are the same. Commented Nov 24, 2023 at 10:26

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.