BUG #14724: Unable to revoke CREATE privileges on public schema

On Fri, Jun 30, 2017 at 12:18 PM, <zam6ak@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14724
Logged by: zam6ak
Email address: zam6ak@gmail.com
PostgreSQL version: 10beta1
Operating system: Windows Server 2012
Description:

-- create a test db
CREATE DATABASE test
WITH OWNER = postgres
ENCODING = 'UTF8';

-- log in as 'postres' and do:
REVOKE CREATE ON SCHEMA public FROM public;

WARNING: no privileges could be revoked for "public"

Query returned successfully with no result in 16 msec.

https://www.postgresql.org/docs/9.6/static/sql-grant.html.

"No privileges are granted to PUBLIC by default on tables, columns, schemas
or tablespaces."

You've been correctly warned that you've attempted to revoke a privilege
that doesn't exist.

David J.

On 6/30/17 17:14, David G. Johnston wrote:

-- create a test db
CREATE DATABASE test
WITH OWNER = postgres
ENCODING = 'UTF8';

-- log in as 'postres' and do:
REVOKE CREATE ON SCHEMA public FROM public;

WARNING: no privileges could be revoked for "public"

Query returned successfully with no result in 16 msec.

https://www.postgresql.org/docs/9.6/static/sql-grant.html.

"No privileges are granted to PUBLIC by default on tables, columns,
schemas or tablespaces."

You've been correctly warned that you've attempted to revoke a privilege
that doesn't exist.

The "public" schema does by default have privileges, and the above
commands do work for me. Maybe there is some silly stuff going on in
the template databases.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

zam6ak@gmail.com writes:

-- create a test db
CREATE DATABASE test
WITH OWNER = postgres
ENCODING = 'UTF8';

-- log in as 'postres' and do:
REVOKE CREATE ON SCHEMA public FROM public;
WARNING: no privileges could be revoked for "public"

Works for me. Maybe your "postgres" isn't a superuser?
I can duplicate the above if I try it with a non-superuser
database owner. (And no, that is not a bug, it's just
how it works. Being owner of a DB doesn't in itself give
you ownership of contained objects.)

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs