pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16124: Altering default privileges problem
2+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2019-11-19 787b3fd33f d30e0ed55c 93b2bbede9 ff6de57750 611a4aba15 de8c2d38fd e5df9bb017 Doc: clarify behavior of ALTER DEFAULT PRIVILEGES ... IN SCHEMA.
* BUG #16124: Altering default privileges problem
@ 2019-11-18 14:44 PG Bug reporting form <noreply@postgresql.org>
2019-11-18 15:31 ` Re: BUG #16124: Altering default privileges problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2019-11-18 14:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: john.doe@example.com
The following bug has been logged on the website:
Bug reference: 16124
Logged by: John Doe
Email address: john.doe@example.com
PostgreSQL version: 11.5
Operating system: NixOS
Description:
Altering default privileges in specific schema to revoke all privileges on
types from PUBLIC does nothing.
Steps to reproduce:
- Connect as superuser and execute:
CREATE ROLE ownerrole WITH INHERIT LOGIN PASSWORD 'ownerrole';
CREATE ROLE approle WITH INHERIT LOGIN PASSWORD 'approle';
CREATE DATABASE test1 ENCODING = 'UTF8' OWNER = ownerrole;
CREATE DATABASE test2 ENCODING = 'UTF8' OWNER = ownerrole;
Test 1
- Setup: Connect as ownerrole to test1 and execute the following commands:
CREATE SCHEMA myschema;
GRANT ALL PRIVILEGES ON SCHEMA myschema TO approle;
ALTER DEFAULT PRIVILEGES
FOR ROLE ownerrole
IN SCHEMA myschema
REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;
CREATE DOMAIN myschema.comment AS text;
- Test: connect as approle to test1 and execute the following command:
CREATE TABLE myschema.table(
comment myschema.comment
);
- Actual result
The myschema.table table is created.
- Expected result:
The table creation should fail due to the following error:
ERROR: permission denied for type myschema.comment
- Notes:
$ psql -U ownerrole -d test1
psql (11.5)
Type "help" for help.
test1=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)
Test 2
- Setup: Connect as ownerrole to test2 and execute the following commands
(ALTER DEFAULT PRIVILEGES is not specific to any schema):
CREATE SCHEMA myschema;
GRANT ALL PRIVILEGES ON SCHEMA myschema TO approle;
ALTER DEFAULT PRIVILEGES
FOR ROLE ownerrole
REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;
CREATE DOMAIN myschema.comment AS text;
- Test: connect as approle to test2 and execute the following command:
CREATE TABLE myschema.table(
comment myschema.comment
);
- Actual result
ERROR: permission denied for type myschema.comment
- Expected result:
ERROR: permission denied for type myschema.comment
- Notes:
$ psql -U ownerrole -d test2
psql (11.5)
Type "help" for help.
test2=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-----------+--------+------+-----------------------
ownerrole | | type | ownerrole=U/ownerrole
(1 row)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16124: Altering default privileges problem
2019-11-18 14:44 BUG #16124: Altering default privileges problem PG Bug reporting form <noreply@postgresql.org>
@ 2019-11-18 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2019-11-18 15:31 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Bug reference: 16124
> Logged by: John Doe
> Email address: john.doe@example.com
... so, effectively an anonymous report. Hope the submitter reads
pgsql-bugs, else he'll get no answer.
> Altering default privileges in specific schema to revoke all privileges on
> types from PUBLIC does nothing.
AFAICT, this is operating as designed. SetDefaultACL() says:
* The default for a global entry is the hard-wired default ACL for the
* particular object type. The default for non-global entries is an empty
* ACL. This must be so because global entries replace the hard-wired
* defaults, while others are added on.
Hence, "ALTER ... IN SCHEMA whatever REVOKE ALL PRIVILEGES ON TYPES FROM
PUBLIC" is a no-op because the privilege was never granted at the schema
level in the first place.
This seems kind of unfortunate, and it's certainly not adequately
documented. The man page does say "Default privileges that are specified
per-schema are added to whatever the global default privileges are for the
particular object type" but the implication that you can't revoke
privileges at that level isn't obvious.
I don't see any way to change the actual behavior, at least not within the
existing representation of pg_default_acl, but we ought to improve the
documentation. I'm inclined to split out the just-quoted sentence to
a new para along the lines of
Default privileges that are specified per-schema are added to whatever
the global default privileges are for the particular object type.
This means you cannot revoke privileges per-schema if they are granted
globally (either by default, or according to a previous ALTER DEFAULT
PRIVILEGES command that did not specify a schema). Per-schema REVOKE
is only useful to reverse the effects of a previous per-schema GRANT.
Perhaps an explicit example too?
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-11-18 15:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-18 14:44 BUG #16124: Altering default privileges problem PG Bug reporting form <noreply@postgresql.org>
2019-11-18 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox