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
. Another, not so flexible but easier, method is the CONNECT
privilege.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With