the shell command can be used to create a PostgreSQL user.
To create a user
that is allowed to create databases, with username alice:
postgres@idoru:/$ createuser -d -P alice
-d – allow user to create databases
-P – prompt for password
deleting a user
the shell command can be used to delete a PostgreSQL user, i.e.
Invoked without any argument, asks for the user that is to be deleted.
creating and editing roles in psql
creating a role
postgres=# create role <role> with login encrypted password 'guessme';
setting password of a user
postgres=# alter user bob with password 'n3w-s3cr3t-p455w0rd';
deleting a user
postgres=# DROP ROLE whatever;</code>
listing users
postgres=# \du
sample output
postgres=# \du
List of roles
Role name | Attributes | Member of
----------+------------------------------------------------------------+-----------
gitea | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
rudolf | Create DB | {}
grant permissions on a databases and database objects
grant permission to connect to a database
postgres=# grant connect on database sampledb to bob;
grant all privileges on a database to a specified user (role):
postgres=# grant all privileges on database sampledb to bob;
The keyword is optional in PostgreSQL but mandatory in strict SQL.
may be a bit omnicidal. You might want to fine tune
the privileges.
general form of grant
The general form to grant privileges to database objects is
grant <priviledge> on <database object> <database object name> to <user|role>
The database objects used most often and the possible privileges for their use are:
table
grant
select
insert
update
delete
truncate
drop
on table
on all tables in schema
sequence
grant
usage
select
update
drop
on sequence
on all sequences in schema
note that in order to get the next value of a sequence, i.e. in JPA when persisting entities,
you need the privilege select for the sequence.