Postgresql: Create a new user with no privileges?
postgresql, privileges, roles
Solution
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.
Problem
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.