No PUBLIC access by default?

On Thu, Aug 11, 2005 at 12:28:44PM -0500, Peter Fein wrote:

Is there any way to disable PUBLIC access by default? When I create a
new object (table, function, etc.), it has no ACL, as expected.
However, the first time I run:

GRANT ALL ON FUNCTION foo() to GROUP developers;

Postgress seems to do:

GRANT ALL ON FUNCTION foo() to PUBLIC;

Actually, that last grant is implicit. When an ACL is found to be null,
it's considered to have a grant to public. So what you should actually
do is revoke those implicit permissions at object creation time.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Investigaci�n es lo que hago cuando no s� lo que estoy haciendo"
(Wernher von Braun)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

Actually, that last grant is implicit. When an ACL is found to be null,
it's considered to have a grant to public.

No, it's considered to be whatever the default for the object type is.
Read the GRANT manual page.

regards, tom lane

Peter Fein wrote:

Hi all-

Is there any way to disable PUBLIC access by default? When I create

You can revoke permissions from the public schema, and you can even
delete the public schema entirely. I did the first:

revoke create on schema public from public
revoke create on tablespace pg_default from public
revoke create on tablespace pg_global from public

If you do this while connect to template1, then all future databases
will have this set up at the time the database is created.

a new object (table, function, etc.), it has no ACL, as expected.
However, the first time I run:

GRANT ALL ON FUNCTION foo() to GROUP developers;

Postgress seems to do:

GRANT ALL ON FUNCTION foo() to PUBLIC;

I assume this is b/c no ACL is equivalent to PUBLIC access & this
gets included when adding specific privileges.

I want *no* PUBLIC access to anything by default for security
reasons. Is there a way to prevent this behavior?

---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
Guy Rouillier

Tom Lane wrote:

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

Actually, that last grant is implicit. When an ACL is found to be null,
it's considered to have a grant to public.

No, it's considered to be whatever the default for the object type is.
Read the GRANT manual page.

I'm obviously missing something. From the GRANT documentation:

Depending on the type of object, the initial default privileges may
include granting some privileges to PUBLIC. The default is no public
access for tables, schemas, and tablespaces; TEMP table creation
privilege for databases; EXECUTE privilege for functions; and USAGE
privilege for languages.

So as I read it, PUBLIC has no access to a schema if not explicitly
granted. However:

pfein@scoober ~ $ createdb -U postgres test
CREATE DATABASE
pfein@scoober ~ $ psql -U testuser test
test=> \dn+
List of schemas
Name | Owner | Access privileges |
Description
--------------------+----------+-------------------------------------+----------------------------------
information_schema | postgres | {postgres=UC/postgres,=U/postgres} |
pg_catalog | postgres | {postgres=UC/postgres,=U/postgres} |
System catalog schema
pg_toast | postgres | |
Reserved schema for TOAST tables
public | postgres | {postgres=UC/postgres,=UC/postgres} |
Standard public schema
(4 rows)

If I read my ACL's correctly, =UC/postgres means full access for PUBLIC.
Why is that happening? Do I need to REVOKE ALL ON SCHEMA public FROM
public in template1? If that's correct, what else should I be revoking on?

Peter Fein <pfein@pobox.com> writes:

If I read my ACL's correctly, =UC/postgres means full access for PUBLIC.
Why is that happening?

Because that's the way it's set up in template1. CREATE DATABASE just
copies the source database, it doesn't editorialize on the contents
thereof.

regards, tom lane

Tom Lane wrote:

Peter Fein <pfein@pobox.com> writes:

If I read my ACL's correctly, =UC/postgres means full access for PUBLIC.
Why is that happening?

Because that's the way it's set up in template1. CREATE DATABASE just
copies the source database, it doesn't editorialize on the contents
thereof.

Ok. ;) A little further investigation revealed that template0 gives the
same result. It's potentially confusing that template0 is initialized
this way - I couldn't find any indication of such in the manual. In
fact, from CREATE DATABASE:

In particular, by writing TEMPLATE template0, you can create a virgin
database containing only the standard objects predefined by your version
of PostgreSQL.

I guess I'm just surprised that template0 would have *any* ACLs set
(aside from those needed by system catalogs, etc.). It seems to be
favoring convenience by default instead of security by default.

--
Peter Fein pfein@pobox.com 773-575-0694

Basically, if you're not a utopianist, you're a schmuck. -J. Feldman

Peter Fein <pfein@pobox.com> writes:

In particular, by writing TEMPLATE template0, you can create a virgin
database containing only the standard objects predefined by your version
of PostgreSQL.

I guess I'm just surprised that template0 would have *any* ACLs set

PUBLIC is one of the standard predefined objects.

(aside from those needed by system catalogs, etc.). It seems to be
favoring convenience by default instead of security by default.

I don't see the ability to create a table as a security violation.
If you do, you can lock down your database however you want ... but
that doesn't mean that everyone else should have to follow your ideas.

regards, tom lane

On Fri, Aug 12, 2005 at 08:34:23AM -0500, Peter Fein wrote:

Ok. ;) A little further investigation revealed that template0 gives the
same result. It's potentially confusing that template0 is initialized
this way - I couldn't find any indication of such in the manual. In
fact, from CREATE DATABASE:

In particular, by writing TEMPLATE template0, you can create a virgin
database containing only the standard objects predefined by your version
of PostgreSQL.

It's mentioned in:

http://www.postgresql.org/docs/8.0/static/ddl-schemas.html#DDL-SCHEMAS-PRIV

The public schema is setup so anyone can access it, that's why it's
called public.

I guess I'm just surprised that template0 would have *any* ACLs set
(aside from those needed by system catalogs, etc.). It seems to be
favoring convenience by default instead of security by default.

The purpose of blocking access to public by default would be... If you
don't want people to access the database, don't let them login.

I imagine it's also partly because in prior versions before schemas
existed, if your database administrator gave you access to a database,
you got access to anything the ACLs on the tables said you could. Now
schemas are added, but this remains true. It means you can ignore
schemas if you want, no need to remind the admin to give you
permissions to create things in the database he created for you.

Seems akin to removing all permissions from the home directory of a new
user so not even they can access it. Sure it's secure, but not terribly
useful.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout wrote:

On Fri, Aug 12, 2005 at 08:34:23AM -0500, Peter Fein wrote:

Ok. ;) A little further investigation revealed that template0 gives the
same result. It's potentially confusing that template0 is initialized
this way - I couldn't find any indication of such in the manual. In
fact, from CREATE DATABASE:

In particular, by writing TEMPLATE template0, you can create a virgin
database containing only the standard objects predefined by your version
of PostgreSQL.

It's mentioned in:

http://www.postgresql.org/docs/8.0/static/ddl-schemas.html#DDL-SCHEMAS-PRIV

The public schema is setup so anyone can access it, that's why it's
called public.

Ack, ok. Thanks for the link.

I guess I'm just surprised that template0 would have *any* ACLs set
(aside from those needed by system catalogs, etc.). It seems to be
favoring convenience by default instead of security by default.

The purpose of blocking access to public by default would be... If you
don't want people to access the database, don't let them login.

Forcing admins to specify who has access? Anyway, I'm persuaded. I've
been thinking of the public schema as the place where all my
application-level data & functionality will live, with separate schemas
for more generic functionality - think packages/modules in the software
world. My DB backends a webapp & I'm worried about SQL injection & the
like. Revoking all access from PUBLIC obviously doesn't solve that
problem, but it limits the scope of potential damage.

Seems akin to removing all permissions from the home directory of a new
user so not even they can access it. Sure it's secure, but not terribly
useful.

I think it's more like chmod 640...

Thanks all for the help.

--
Peter Fein pfein@pobox.com 773-575-0694

Basically, if you're not a utopianist, you're a schmuck. -J. Feldman