Create a user on PostgreSQL

To create an user on a PostgreSQL database you can use:

CREATE USER new_user WITH PASSWORD 'password';

To guarantee full access on a database you can:

GRANT ALL PRIVILEGES ON DATABASE database_name TO new_user;

Also to allow the user to connect on the database you need to:

GRANT CONNECT ON DATABASE database_name TO new_user;

If you want the user to have full access to all tables, sequences, and other objects in the database, you can use:

\c database_name  -- To connect to the database
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;

To ensure the user gets automatic privileges on any new tables or sequences created in the future, run the following:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO new_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO new_user;

Full

-- Connect to PostgreSQL
psql -U postgres

-- Create a new user
CREATE USER new_user WITH PASSWORD 'securepassword';

-- Grant full access to the database
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO new_user; 

-- Connect to the target database
\c mydatabase 

-- Grant privileges on existing tables and sequences
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;

-- Ensure future tables and sequences are also accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO new_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO new_user;