Mammoth
  • |
  • Contact
  • |
CMD | Command Prompt, Inc. - PostgreSQL Solutions, Support & Hosting
  • |
  • |
  • |
  • |
  • |
A new user discovers the PostgreSQL public schema
Posted Tuesday Jun 2nd, 2015 12:16pm
by Joshua Drake
| Permalink

Follow cmdpromptinc on Twitter


A new user of PostgreSQL recently discovered that PostgreSQL allows any PostgreSQL user to create objects and data by default.[1] I know you are saying, "What... PostgreSQL has some of the most advanced and flexible security in the industry!" and you are absolutely correct, we do. However, once you can connect to PostgreSQL, you have some interesting default capabilities. Consider the following example:

postgres@sqitch:/# psql -U postgres
psql (9.2.11)
Type "help" for help.

postgres=# create user foo;
CREATE ROLE
postgres=# \q

No biggy, we created a user foo as the super user postgres. All is good. However, what can that user foo do?

postgres@sqitch:/# psql -U foo postgres
psql (9.2.11)
Type "help" for help.
postgres=> create table bar (id text);
CREATE TABLE
postgres=> 

What? Yes. I connected to the postgres database as the unprivileged user foo. I then proceeded to create a table and as I own that table, I can insert data into that table. If we continue the example:

postgres=# \z bar
                          Access privileges
 Schema | Name | Type  | Access privileges | Column access privileges 
--------+------+-------+-------------------+--------------------------
 public | bar  | table |                   | 
(1 row)

As noted above there are zero access privileges on the table bar. Now let's create a new unprivileged user, baz.

postgres=# create user baz;
CREATE ROLE
postgres=# \q

Now we reconnect as the user baz and try to insert data into table bar.

postgres@sqitch:/# psql -U baz postgres
psql (9.2.11)
Type "help" for help.

postgres=> insert into bar values ('1');
ERROR:  permission denied for relation bar
postgres=> 

The error represents exactly what should happen but I could just create a new table as user baz and start adding data. As a very simple example of why this could have undesirable results, try this as the user foo:

postgres@sqitch:/#psql -U foo postgres;
psql (9.2.11)
Type "help" for help.

postgres=> insert into bar values (generate_series(1, 1000000000));

1. postgres db permissions


Categories: Business, OpenSource, PostgreSQL, Python, SQL

blog comments powered by Disqus

Copyright © 2000-2015 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.