Re: [GENERAL] No PUBLIC access by default?
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 [EMAIL PROTECTED] 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] No PUBLIC access by default?
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 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. pgpxRwblEW8rn.pgp Description: PGP signature
Re: [GENERAL] No PUBLIC access by default?
Peter Fein <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] No PUBLIC access by default?
Tom Lane wrote: > Peter Fein <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] No PUBLIC access by default?
Peter Fein <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] No PUBLIC access by default?
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] ~ $ createdb -U postgres test CREATE DATABASE [EMAIL PROTECTED] ~ $ 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? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] No PUBLIC access by default?
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] No PUBLIC access by default?
Alvaro Herrera <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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 () "Investigación es lo que hago cuando no sé lo que estoy haciendo" (Wernher von Braun) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] 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? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org