Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql: Create a new user with no privileges?

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.

like image 610
Robin Winslow Avatar asked Dec 08 '13 21:12

Robin Winslow


1 Answers

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.

like image 194
Milen A. Radev Avatar answered Oct 30 '22 19:10

Milen A. Radev