ChatDB
Docs
PostgreSQL Database User Setup

PostgreSQL Database User Setup

How to create a PostgreSQL database user for executing SQL queries and retrieving schema information

This guide explains how to create a dedicated PostgreSQL database user with the appropriate permissions for executing SQL queries and retrieving schema information in your Text-to-SQL application.

Prerequisites

  • Access to a PostgreSQL server with administrative privileges
  • PostgreSQL command-line client (psql) or a PostgreSQL administration tool like pgAdmin

Creating a Database User

Using PostgreSQL Command Line (psql)

  1. Connect to your PostgreSQL server as an administrator:
psql -U postgres
  1. Create a new user with a secure password:
CREATE USER texttosql_user WITH PASSWORD 'your_secure_password';
  1. Grant the necessary permissions for schema retrieval and query execution:
-- Connect to your specific database
\c your_database
 
-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO texttosql_user;
 
-- Grant select permission on all tables in the public schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO texttosql_user;
 
-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO texttosql_user;
 
-- If you need the user to execute other types of queries (INSERT, UPDATE, DELETE)
-- add the appropriate permissions:
-- GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO texttosql_user;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO texttosql_user;
  1. Exit psql:
\q

Using pgAdmin

  1. Log in to pgAdmin with administrative privileges
  2. Right-click on "Login/Group Roles" and select "Create > Login/Group Role"
  3. On the "General" tab, enter the name (e.g., texttosql_user)
  4. On the "Definition" tab, enter a secure password
  5. On the "Privileges" tab, set "Can login?" to "Yes"
  6. Click "Save" to create the user
  7. Navigate to your database, right-click and select "Properties"
  8. Go to the "Security" tab
  9. Click "+" to add a new privilege
  10. Select the new user and grant the appropriate privileges (SELECT on tables)

Verifying the Setup

To verify that the user has the correct permissions:

  1. Connect to PostgreSQL using the new user:
psql -U texttosql_user -d your_database
  1. Try to retrieve schema information:
SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema = 'public';
  1. Try executing a simple query:
SELECT * FROM your_table LIMIT 5;

Connection Configuration

When configuring your Text-to-SQL application, use these credentials:

  • Host: Your PostgreSQL server hostname or IP
  • Port: 5432 (default PostgreSQL port)
  • Database: Your database name
  • Username: texttosql_user
  • Password: your_secure_password

Security Considerations

  • Use a strong, unique password for the database user
  • Consider restricting the user's access to specific IP addresses by modifying the pg_hba.conf file
  • Regularly audit the user's permissions to ensure they remain appropriate
  • Store connection credentials securely and never expose them in client-side code
  • Consider using SSL connections for added security
MySQLMSSQL