ubuntu11.04下POSTGRES SQL 创建角色，赋予角色访问数据库权限及sqlalchmey的database_url配置

最新推荐文章于 2025-03-29 09:34:20 发布

原创最新推荐文章于 2025-03-29 09:34:20 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #数据库 #url #ubuntu #sql #postgresql

Python 同时被 3 个专栏收录

10 篇文章

订阅专栏

Ubuntu

8 篇文章

订阅专栏

Postgresql

3 篇文章

订阅专栏

在Ubuntu 11.04上，通过`sudo -u postgres psql`创建PostgreSQL角色和数据库，并修改`pg_hba.conf`文件授权。数据库URL配置示例包括使用不同的驱动和参数。接着，重启数据库服务，以`postgresql://scott:tiger@localhost/mydatabase`等示例展示SQLAlchemy的database_url配置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sudo -u postgres psql
postgres=# create role username login;
postgres=# create database databasename owner username;

然后修改/etc/postgresql/8.4/main/pg_hba.conf，在all/all前加一行

local databasename username trust

对应数据库url配置： dialect+driver://username@/database

host databasename username 127.0.0.1/32 trust

对应数据库url配置：dialect+driver://username@host:port/database

然后重启sql服务

sudo /etc/init.d/postgresql restart

或 sudo service postgresql restart

sqlalchemy的database的url配置 dialect+driver://username:password@host:port/database

# postgresql - psycopg2 is the default driver.
pg_db = create_engine('postgresql://scott:tiger@localhost/mydatabase')
pg_db = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
pg_db = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
pg_db = create_engine('postgresql+pypostgresql://scott:tiger@localhost/mydatabase')

# postgresql on Jython
pg_db = create_engine('postgresql+zxjdbc://scott:tiger@localhost/mydatabase')

# mysql - MySQLdb (mysql-python) is the default driver
mysql_db = create_engine('mysql://scott:tiger@localhost/foo')
mysql_db = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

# mysql on Jython
mysql_db = create_engine('mysql+zxjdbc://localhost/foo')

# mysql with pyodbc (buggy)
mysql_db = create_engine('mysql+pyodbc://scott:tiger@some_dsn')

# oracle - cx_oracle is the default driver
oracle_db = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')

# oracle via TNS name
oracle_db = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

# mssql using ODBC datasource names.  PyODBC is the default driver.
mssql_db = create_engine('mssql://mydsn')
mssql_db = create_engine('mssql+pyodbc://mydsn')
mssql_db = create_engine('mssql+adodbapi://mydsn')
mssql_db = create_engine('mssql+pyodbc://username:password@mydsn')
# sqlite://<nohostname>/<path>
# where <path> is relative:
sqlite_db = create_engine('sqlite:///foo.db')

# or absolute, starting with a slash:
sqlite_db = create_engine('sqlite:////absolute/path/to/foo.db')