Learning Oracle: User Management in PostgreSQL

User Management in PostgreSQL

These commands allow you to create, alter, and manage users and roles in PostgreSQL.

1. Create a User:

To create a new user, you can use the CREATE USER command. Users are also referred to as roles in PostgreSQL.

CREATE USER username WITH PASSWORD 'password';

Replace username with the desired username and password with the user's password.

2. Create a Role with Specific Privileges:

You can create a user as a role with specific privileges like login, create databases, or superuser access.

CREATE ROLE role_name WITH LOGIN PASSWORD 'password' CREATEDB CREATEROLE;

CREATEDB: Allows the role to create databases.

CREATEROLE: Allows the role to create other roles.

SUPERUSER: Gives the role superuser privileges (use with caution).

3. Grant Permissions to a User:

To grant specific permissions to a user, you use the GRANT command.

Grant Database Access:

GRANT CONNECT ON DATABASE dbname TO username;

Grant Table Permissions (e.g., SELECT, INSERT, UPDATE):

GRANT SELECT, INSERT, UPDATE ON TABLE tablename TO username;

Grant All Permissions on a Schema:

GRANT ALL PRIVILEGES ON SCHEMA schemaname TO username;

4. Revoke Permissions:

To revoke a user’s access or privileges, you use the REVOKE command.

Revoke Specific Permissions on a Table:

REVOKE SELECT, INSERT ON TABLE tablename FROM username;

Revoke All Privileges on a Schema:

REVOKE ALL PRIVILEGES ON SCHEMA schemaname FROM username;

5. Alter User Role:

You can modify a user’s attributes like changing their password or adding/removing permissions.

Change Password:

ALTER USER username WITH PASSWORD 'newpassword';

Grant Superuser Role (making a user a superuser):

ALTER USER username WITH SUPERUSER;

Revoke Superuser Role (making a user non-superuser):

ALTER USER username WITH NOSUPERUSER;

6. Delete User:

To delete a user from the PostgreSQL database, use the DROP USER command.

DROP USER username;

Schema Management in PostgreSQL:

Schemas in PostgreSQL are used to organize database objects like tables, views, and functions.

1. Create a Schema:

To create a new schema, use the CREATE SCHEMA command.

CREATE SCHEMA schema_name;

2. Set a Schema Search Path:

You can specify the default schemas that PostgreSQL should look into when querying objects.

SET search_path TO schema_name;

3. Grant Permissions on Schema:

You can give a user access to a specific schema by using the GRANT command.

GRANT USAGE ON SCHEMA schema_name TO username;

USAGE allows the user to access the schema and its objects.

4. List All Schemas:

To view all schemas in the database, run:

\dn

This will list all the schemas in the current database.

5. List All Tables in a Schema:

To list tables in a specific schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'schema_name';

6. Drop a Schema:

To drop a schema from the database, use the DROP SCHEMA command. Be careful, as this will delete all objects within the schema.

DROP SCHEMA schema_name CASCADE;

The CASCADE option automatically deletes all objects within the schema.

7. Rename a Schema:

To rename a schema:

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

Schema and Object Management:

Managing database objects within a schema, such as tables, views, and sequences.

1. Create a Table:

To create a new table inside a schema:

CREATE TABLE schema_name.table_name (

column1 datatype PRIMARY KEY,

column2 datatype,

column3 datatype

);

2. Modify Table Structure:

To add, modify, or drop columns in a table, use the following commands:

Add a Column:

ALTER TABLE schema_name.table_name ADD COLUMN column_name datatype;

Rename a Column:

ALTER TABLE schema_name.table_name RENAME COLUMN old_column_name TO new_column_name;

Change Data Type of a Column:

ALTER TABLE schema_name.table_name ALTER COLUMN column_name TYPE new_datatype;

Drop a Column:

ALTER TABLE schema_name.table_name DROP COLUMN column_name;

3. Drop a Table:

To delete a table from a schema:

DROP TABLE schema_name.table_name;

4. Create a View:

To create a view, which is a virtual table based on a query:

CREATE VIEW schema_name.view_name AS

SELECT column1, column2

FROM schema_name.table_name

WHERE condition;

5. Drop a View:

To delete a view:

DROP VIEW schema_name.view_name;

6. Create an Index:

To improve query performance, you can create an index on a table’s column(s):

CREATE INDEX index_name ON schema_name.table_name (column_name);

7. Drop an Index:

To delete an index:

DROP INDEX schema_name.index_name;

Miscellaneous Commands:

1. List All Users (Roles):

To list all users (roles) in the database:

\du

2. List All Tables:

To list all tables in the current schema:

\dt

3. Change Database:

To switch to another database:

\c database_name;

Backup and Restore Commands:

1. Backup a Database:

To take a backup of a database, use the pg_dump utility:

pg_dump -U username -W -F t dbname > backupfile.tar

-U username: The username to connect to the database.

-W: Prompts for the password.

-F t: Specifies the format (tar).

dbname: The database you want to back up.

2. Restore a Database:

To restore a database, use the pg_restore utility:

pg_restore -U username -W -d dbname backupfile.tar

-d dbname: The target database where the backup will be restored.

Here’s a comprehensive set of PostgreSQL commands for user management and schema management that will help you manage your database as an administrator.

User Management in PostgreSQL: