Postgresql: Create a new user with no privileges?

Question

I'm just learning PostgreSQL. Every time I create users, they seem to have the full privileges to do anything in any database:

$ sudo useradd fool # create the user "fool" in the system
# And in postgresql, as restrictively as possible
$ sudo -u postgres createuser fool --no-superuser --no-createdb --no-createrole --no-inherit
$ sudo -u fool psql postgres # but it can still connect to the "postgres" db
postgres=> drop table ids; # and delete tables
DROP TABLE

I tried creating the user through the create user PostgreSQL command rather than the command-line tool but it had exactly the same effect.

How can I create a user that only has privileges to access its own database? Or do I have to specifically revoke all permissions after creating the user? 'Cos that kinda sucks from a security perspective - it's easy to accidentally forget (or just not know it's necessary) to revoke permissions for new users.

All roles are "members" of the pseudo-role `PUBLIC`. And by default `PUBLIC` has many privileges. My practice is to revoke all privileges from `PUBLIC` where I need and grant various combinations to specific roles. A recent addition helps with that - [`ALTER DEFAULT PRIVILEGES`](http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html). Another, not so flexible but easier, method is the [`CONNECT`](http://www.postgresql.org/docs/current/static/sql-grant.html) privilege. — Milen A. Radev, Dec 08 '13 at 21:58
Actually I just found this duplicate question (oops): http://stackoverflow.com/questions/6884020/why-new-user-in-postgresql-can-connect-to-all-databases — Robin Winslow, Dec 08 '13 at 22:04
@MilenA.Radev do you know of an article that could walk me through setting up my default roles and privileges so that when I create a new user the will have full access to their own database but cannot even connect to anyone else's? — Robin Winslow, Dec 08 '13 at 22:27
@RobinWinslow You just need to `REVOKE CONNECT ON eachdatabase FROM public;` and/or drop the `public` schema in the DBs. `ALTER DEFAULT PRIVILEGES` might help with this. It's kind of annoying that these default grants exist, but people would complain even more if they didn't - as it is there's lots of complaint that Pg is "hard to set up". — Craig Ringer, Dec 09 '13 at 01:32

score 7 · Accepted Answer · answered Dec 09 '13 at 08:15

All roles are "members" of the pseudo-role PUBLIC. And by default PUBLIC has many privileges. My practice is to revoke all privileges from PUBLIC where I need and grant various combinations to specific roles. A recent addition helps with that - ALTER DEFAULT PRIVILEGES. Another, not so flexible but easier, method is the CONNECT privilege.

Postgresql: Create a new user with no privileges?

1 Answers1