Thread: No PUBLIC access by default?
Hi all- 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; 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?
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/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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