It is always advised to restrict permissions for connections to the minimal amount that they need to perform their function. When it comes to a database you have a few permissions to consider. In the blog post here, I go over how to restrict what hosts or ip’s can log into a system, but it is important to also restrict what users can do once they connect to the system. If a user only needs select permission, then there is no need to give them insert, update, and delete privileges as well. If they need all of the Data Manipulation Langauge (DML) permissions they still may not need Data Definition Language (DDL) permissions too. I’d like to go over what setting up these user level permissions looks like in MySQL and what I have discovered about setting these permissions in Postgres.
Creating a user and granting permissions to the database
For both MySQL and Postgres, I will assume you are in their native cli for this. To enter the mysql cli, type ‘mysql’. To enter postgres, type ‘psql’.
First we create the user
create user [username]@'[ip]' identified by '[password'];
#alternatively we can use “identified by password ‘[passwordhash]’;” to specify the encrypted password
Next we grant permissions to the database
grant select, insert, update, delete on [databasename].* to [username]@'[ip]';
If we wanted individual tables to be specified for certain permissions, they could be granted using databasename.tablename where databasename.* is in use above.
First we create the user and give them the ability to login to the server
create user [username] with login unencrypted password '[password]';
#alternatively you could get a password hash and use that with the keyword encrypted
You then must use the context of the database that you want to add them to
Then you can grant them usage to the schema
grant usage on schema public to [username];
Then you can grant them the individual permissions you want them to have
grant select, insert, update, delete on all tables in schema public to [username];
That statement only gives the ability to use those permissions on existing tables though. You also have to adjust the default privileges for the database so that any tables created in the future will have the same access for your user. Applying the default permissions now will prevent maintenance requiring you to come back and add permissions again in the future.
alter default privileges in schema public grant select, insert, update, delete on tables to [username];
This still doesn’t apply to sequences which will need to have access to be updated
grant usage on all sequences in schema public to [username];
And then the default permissions for sequences…
alter default privileges in schema public grant usage on sequences to [username];
This still doesn’t apply to function though, which must be done separately
grant execute on all functions in schema public to [username];
But you guessed it, that didn’t apply to things created in the future, so we have to alter the default privileges for that too
alter default privileges in schema public grant execute on functions to [username];
Now you are possibly done. Depending on how your table was created, you may have inherited the ability to create tables even though we never explicitly gave it to our new user. There is a template0 database and a template1 database that are part of postgres. When you create a new database, you are actually copying a skeleton of one of these two databases and then adding on the things you specified in your create statement. By default, the template1 database is used. The thought is that you can tune the template1 database to have various default things in it, and the template0 database is the minimal database to allow things to work. If your database was created with the “TEMPLATE template0” option at the end of it, you are probably good to go. If not, you will have to remove the create table permissions.
To verify if this permission exists, run the below query. If you get a row back, then the permission exists for the username you checked.
select 1 where (select has_schema_privilege('[username]','public','CREATE')) = 't';
To remove the permission, run the below command
revoke create on database [databasename] from [username];
In short, dealing with permissions in Postgres has been a much more involved process than what I am used to in MySQL. If there are any Postgres users willing to comment with an easier way or explanation as to why it is so hard to restrict a user to minimal permissions, I would like to get a better understanding of the thought process, or if there is something I have missed in my research on how to properly add an application user.