Apache Cloudberry 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 Apache Cloudberry 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 Apache Cloudberry by following Install a Single-Node Apache Cloudberry.
Create a user using the CREATE USER command
-
Log into Apache Cloudberry in Docker. Connect to the database as the
gpadmin
user.[gpadmin@mdw ~]$ psql
psql (14.4, server 14.4)
Type "help" for help.gpadmin=#
-
Create a user named
lily
using theCREATE USER
command, 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
lily
has 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
lucy
using thecreateuser
utility command.gpadmin=# \q -- exit psql
[gpadmin@mdw ~]$ createuser --interactive lucy
You will be asked to choose whether the new role should be a superuser. Enter
y
to create a superuser.Shall the new role be a superuser? (y/n)
-
Connect to the database as the
gpadmin
user.[gpadmin@mdw ~]$ psql
psql (14.4, server 14.4)
Type "help" for help. -
Verify that the user
lucy
has 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
gpadmin
user.[gpadmin@mdw ~]$ psql
psql (14.4, server 14.4)
Type "help" for help.Output:
gpadmin=#
-
Create a group named
users
using theCREATE ROLE
command.gpadmin=# CREATE ROLE users;
Output:
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE -
Add the
lily
andlucy
users to theusers
group.gpadmin=# GRANT users TO lily, lucy;
Output:
GRANT ROLE
-
Verify that the two users have been added to the
users
group.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 Apache Cloudberry 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 md5
andlocal gpadmin lucy trust
to thepg_hba.conf
file 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.conf
is a configuration file in Apache Cloudberry to control access permissions.md5
andtrust
are the authentication methods.md5
means that the user needs to enter the password to log in.trust
means that the user can log in without entering the password.
-
Use
gpstop
to populate the change.[gpadmin@mdw ~]$ gpstop -u
20230818: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 (Apache Cloudberry) 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
lily
and userlucy
have 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: