This page contains commands and logs for creating users and groups and copying tables and schemas between databases.
Back to PostgreSQL
View Components
To view db system users.
(:source lang=sql:)
SELECT * FROM pg_user;
To view db system groups.
(:source lang=sql:)
SELECT * FROM pg_group;
To view databases.
(:source lang=sql:)
SELECT * FROM pg_database;
To view members of a group.
(:source lang=sql:)
Copying Sub Databases
To copy a sub database (schema) to another database, first dump the definition and data to text files. From the Linux command-line, do
(:source lang=text:)
pg_dump -h bg6 -n dbname -s > dbname.sch
pg_dump -h bg6 -n dbname -a > dbname.data
Then, build the new tables and add the data. From the psql command-line, do
(:source lang=text:)
=> \f dbname.sch
=> \f dbname.data
Create Group
Create a group role for the course to which students can be added and
privileges set for the entire group.
(:source lang=sql:)
CREATE ROLE cs321 WITH ROLE necaise ADMIN necaise;
Managing Users
To create user accounts for the students in the course. Each student will be added to the cs321 group. (where the username is test321 and the password is pass.)
(:source lang=sql:)
CREATE ROLE test321 WITH LOGIN PASSWORD 'pass' INHERIT IN ROLE cs321;
To remove a student account from the system and the cs321 group.
(:source lang=sql:)
DROP USER uname;
To add a user to a group or remove a user from a group.
(:source lang=sql:)
ALTER GROUP name ADD USER username [, ... ]
ALTER GROUP name DROP USER username [, ... ]
Set Privileges
Set privileges for the course database including the schemas and tables.
Course Database Privileges
Revoke all privileges on the course database for the public
(:source lang=sql:)
REVOKE ALL ON database cs321 FROM PUBLIC;
Grant basic privileges for the cs321 group
(:source lang=sql:)
GRANT connect, temp ON DATABASE cs321 TO cs321;
Schema Privileges
Allow members of the cs321 group to use the gradebook schema.
(:source lang=sql:)
GRANT USAGE ON SCHEMA gradebook to cs321;
Table Privileges
Grant read privileges to the cs321 group on the various gradebook tables.
(:source lang=sql:)
GRANT SELECT ON gradebook.student TO cs321;
Remove Privileges
To remove privileges from a schema.
Managing Databases
Create a new database for a student
(:source lang=sql:)
CREATE DATABASE test321 WITH OWNER necaise;
and set the privileges
(:source lang=sql:)
GRANT ALL ON DATABASE test321 TO test321;
REVOKE ALL ON DATABASE test321 FROM PUBLIC;
To remove an existing database
(:source lang=sql:)
DROP DATABASE dbname;
Log
