Cloudberry Database manages database access using roles. Initially, there is one superuser role, the role associated with the OS user who initialized the database instance, usually gpadmin. This user owns all of the Cloudberry Database files and OS processes, so it is important to reserve the gpadmin role for system tasks only.
A role can be a user or a group. A user role can log into a database; that is, it has the LOGIN attribute. A user or group role can become a member of a group.
Permissions can be granted to users or groups. Initially, only the gpadmin role is able to create roles. You can add roles using the createuser utility command, CREATE ROLE SQL command, or the CREATE USER SQL command. The CREATE USER command is the same as the CREATE ROLE command except that it automatically assigns the role the LOGIN attribute.
Quick-start operations
You can follow the examples below to create users and roles.
Before moving on to the operations, make sure that you have installed Cloudberry Database by following Install a Single-Node Cloudberry Database.
Create a user using the CREATE USER command
-
Log into Cloudberry Database in Docker. Connect to the database as the
gpadminuser.[gpadmin@mdw ~]$ psql
psql (14.4, server 14.4)
Type "help" for help.gpadmin=# -
Create a user named
lilyusing theCREATE USERcommand, with a passwordchangeme. After the creation, you need to enter the password to log in as the userlily.gpadmin=# CREATE USER lily WITH PASSWORD 'changeme';Output:
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE -
Verify that the user
lilyhas been created.gpadmin=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
gpadmin | Superuser, Create role, Create DB, Replication | {}
lily | | {}
Create a user using the createuser utility command
-
Create a user named
lucyusing thecreateuserutility command.gpadmin=# \q -- exit psql[gpadmin@mdw ~]$ createuser --interactive lucyYou will be asked to choose whether the new role should be a superuser. Enter
yto create a superuser.Shall the new role be a superuser? (y/n) -
Connect to the database as the
gpadminuser.[gpadmin@mdw ~]$ psql
psql (14.4, server 14.4)
Type "help" for help. -
Verify that the user
lucyhas been created.gpadmin=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
gpadmin | Superuser, Create role, Create DB, Replication | {}
lily | | {}
lucy | Superuser, Create role, Create DB | {}gpadmin=# \q -- exit psql
Create a users group and add the users to it
-
Connect to the database as the
gpadminuser.[gpadmin@mdw ~]$ psql
psql (14.4, server 14.4)
Type "help" for help.Output:
gpadmin=# -
Create a group named
usersusing theCREATE ROLEcommand.gpadmin=# CREATE ROLE users;Output:
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE -
Add the
lilyandlucyusers to theusersgroup.gpadmin=# GRANT users TO lily, lucy;Output:
GRANT ROLE -
Verify that the two users have been added to the
usersgroup.gpadmin=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
gpadmin | Superuser, Create role, Create DB, Replication | {}
lily | | {users}
lucy | Superuser, Create role, Create DB | {users}
users | Cannot login | {}
However, after creating the users group, lily and lucy cannot log into Cloudberry Database yet. See the following error messages.
[gpadmin@mdw ~]$ psql -U lily -d gpadmin
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "lily", database "gpadmin", no encryption
[gpadmin@mdw ~]$ psql -U lucy -d gpadmin
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "lucy", database "gpadmin", no encryption
To make users (lily and lucy) able to log into the database, you need to adjust the pg_hba.conf configuration file on the master node and use gpstop to populate the change.
-
Append
local gpadmin lily md5andlocal gpadmin lucy trustto thepg_hba.conffile on the master node.gpadmin=# \q -- exit psql[gpadmin@mdw ~]$ echo "local gpadmin lily md5" >> /data0/database/master/gpseg-1/pg_hba.conf
[gpadmin@mdw ~]$ echo "local gpadmin lucy trust" >> /data0/database/master/gpseg-1/pg_hba.confInfo:
pg_hba.confis a configuration file in Cloudberry Database to control access permissions.md5andtrustare the authentication methods.md5means that the user needs to enter the password to log in.trustmeans that the user can log in without entering the password.
-
Use
gpstopto populate the change.[gpadmin@mdw ~]$ gpstop -u20230818:14:16:05:003653 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -u
20230818:14:16:05:003653 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating the environment...
20230818:14:16:05:003653 gpstop:mdw:gpadmin-[INFO]:-Obtaining Cloudberry Coordinator catalog information
20230818:14:16:05:003653 gpstop:mdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
20230818:14:16:05:003653 gpstop:mdw:gpadmin-[INFO]:-Cloudberry Version: 'postgres (Cloudberry Database) 1.0.0 build dev'
20230818:14:16:05:003653 gpstop:mdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload -
Verify that the two users can log into the database.
[gpadmin@mdw ~]$ psql -U lily -d gpadmin
Password for user lily: # changeme
psql (14.4, server 14.4)
Type "help" for help.[gpadmin@mdw ~]$ psql -U lucy -d gpadmin
psql (14.4, server 14.4)
Type "help" for help.User
lilyand userlucyhave had different privileges. You need to provide the password "changeme" for lily when login.
What's next
After creating users and groups, you can follow Lesson 2: Create and Prepare Database to create and prepare a database for the users
Other tutorials:
