When attempting to remove a user role from PostgreSQL it is possible to get an error. This error will be received if the role attempting to be removed still has access privileges for instance SELECT privileges on specific tables. Below I describe the error as well as how to remove access privileges for each table as well as a link to a previous article we wrote that will loop through every table within a specified database and revoke privileges.
How To Remove Role Privileges Created By The PostgreSQL GRANT Command, Shell Script To Remove PostgreSQL Access Privileges
PostgreSQL isn’t the most friendly when it comes to creating access to various databases or tables within those databases however with a couple easy shell scripts it becomes much easier. I previously wrote this article regarding a shell script created to provide SELECT access, which is essentially read only access, to every table located within a specified database. The problem now might be if you want to remove a user from having access to that database you will be required to manually remove each access privilege for the role you created. Below is the reverse of the initial shell script which will remove SELECT access from each table within a specified database.
Modifying user level permissions in Redmine is fairly easy though it is done in two locations. The first location to modify user permissions is “Roles and Permissions” under the Administration section. The second location is Workflow which is also located under the Administration section. Below I describe a bit more about each section and what can be modified, added, and removed on a per “Role” level.
Modify Roles and Permissions:
To modify a user’s password in PostgreSQL via the psql CLI use the below syntax.
- [postgres@server pgsql]# psql -U postgres
- postgres=# ALTER USER user WITH PASSWORD 'password';
Change user to the proper username and password to the new password you want to set. You will see a “ALTER ROLE” confirmation after issuing the command.