Connecting to PostgreSQL with PHP | by Vahit Saglam | A Young Developer | Medium

PostgreSQL database parameters

Suppose you have the following PostgreSQL database parameters:

The following creates a new database configuration file named config.php that stores the PostgreSQL database parameters:

Enable PostgreSQL driver

The PDO_PGSQL is a driver that implements the PDO interface. It allows you to access PostgreSQL databases from PHP.

To check if the PDO_PGSQL driver is enabled, you open the php.ini file. Typically, the php.ini file is located under the php directory. For example, if you use XAMPP on Windows, you can find the php.ini file under the C:xamppphp directory.

in the php.ini file, you can find the following line:

;extension=php_pdo_pgsql.dll

If you see the comma(;) placed at the beginning of the line, it means that the line is commented and the database driver is not enabled.

To enable the driver, you need to uncomment the line by removing the comma (;) like this:

extension=php_pdo_pgsql.dll
%MINIFYHTMLfa6f0bba54650cb25d8b395e6689b63213%

After that, you need to restart the web server to apply the change.

Connecting to database

The following PHP code shows how to connect to an existing database on a local machine and finally a database connection object will be returned.

Connecting to postgresql with php

Create a table

The following PHP program will be used to create a table in a previously created database −

Delete operation

The following PHP code shows how we can use the DELETE statement to delete any record and then fetch and display the remaining records from our COMPANY table −

Insert operation

The following PHP program shows how we can create records in our COMPANY table created in above example −

Php interface apis

The following are important PHP routines, which can suffice your requirement to work with PostgreSQL database from your PHP program. If you are looking for a more sophisticated application, then you can look into the PHP official documentation.

Php и postgresql | php

<?
$host='localhost';
$db = 'database';
$username = 'user';
$password = 'password';

# Создаем соединение с базой PostgreSQL с указанными выше параметрами
$dbconn = pg_connect("host=$host port=5432 dbname=$db user=$username password=$password");

if (!$dbconn) {
die('Could not connect');
}
else {
echo ("Connected to local DB");
# Выполняем запрос на создание таблицы testtable

$sql = "CREATE TABLE IF NOT EXISTS testtable (
id serial PRIMARY KEY,
number character varying(20) NOT NULL UNIQUE,
name character varying(20) NOT NULL,
kol character varying(20) NOT NULL
)";

$res = pg_query($sql);
# Сделаем запрос на получение списка созданных таблиц
$res = pg_query($dbconn, "select table_name, column_name from information_schema.columns where table_schema='public'");
if (!$res) {
echo "Произошла ошибка.n";
}
# Выведем список таблиц и столбцов в каждой таблице

while ($row = pg_fetch_row($res)) {
echo "tableName: $row[0] ColumnName: $row[1]";
echo "<br />n";
}

# Добавим в созданную таблицу две строчки

$res = pg_query($dbconn, "INSERT INTO testtable (id,number,name,kol) VALUES(1,'2','Name1','4')");
$res = pg_query($dbconn, "INSERT INTO testtable (id,number,name,kol) VALUES(2,'3','Name2','4')");

# Сделаем запрос на получение строк с id=2
$res = pg_query($dbconn, "select name, kol from testtable where id=2");

# Выведем полученные строки
while ($row = pg_fetch_row($res)) {
echo "Name: $row[0] Kol: $row[1]";
echo "<br />n";
}
}
?>

<?

$host='localhost';
$db = 'database';
$username = 'user';
$password = 'password';


# Создаем соединение с базой PostgreSQL с указанными выше параметрами
$dsn = "pgsql:host=$host;port=5432;dbname=$db;user=$username;password=$password";
try{
$conn = new PDO($dsn);
if($conn){
echo "Connected to the <strong>$db</strong> database successfully!";

# Выполняем запрос на создание таблицы testtable

$sql = "CREATE TABLE IF NOT EXISTS testtable (
id serial PRIMARY KEY,
number character varying(20) NOT NULL UNIQUE,
name character varying(20) NOT NULL,
kol character varying(20) NOT NULL
)";
$conn->exec($sql);
print("Created $table Table.n");

# Сделаем запрос на получение списка созданных таблиц

$res = $conn->query("select table_name, column_name from information_schema.columns where table_schema='public'");

# Выведем список таблиц и столбцов в каждой таблице

while ($row = $res->fetch(PDO::FETCH_ASSOC)){
echo($row["table_name"].'-'.$row["column_name"]);
}

# Добавим в созданную таблицу две строчки
$conn->query("INSERT INTO testtable (id,number,name,kol) VALUES(1,'1', 'Name1','4')");
$conn->query("INSERT INTO testtable (id,number,name,kol) VALUES(2,'2', 'Name2','4')");

# Сделаем запрос на получение строк с id=2
$res = $conn->query("select name, kol from testtable where id=2");

# Выведем полученные строки
while ($row = $res->fetch(PDO::FETCH_ASSOC)){
echo($row["name"].'-'.$row["kol"]);
}

}
}catch (PDOException $e){
echo $e->getMessage();
}

?>

Php: pg_connect – manual

Postgresql data source name

The data source name or DSN holds database parameters that enable access to a database system. The data source name of the PostgreSQL consists of the following parameters:

• pgsql: is the DNS prefix.
• host: the host of the server where the PostgreSQL runs.
• port: the port to which PostgreSQL listens. The default PostgreSQL’s port is 5432.
• dbname: the database name that you want to connect to.
• And other parameters.

The following shows a DSN that connects to dvdrental database in the local PostgreSQL Server, port 5432:

pgsql:host=localhost;port=5432;dbname=dvdrental;

Select operation

The following PHP program shows how we can fetch and display records from our COMPANY table created in above example −

Summary

• Enable PostgreSQL extension in php.ini file by removing the semicolon (;) from the line extension=php_pdo_pgsql.dll
• Create a new instance of PDO by passing the data source name (DSN) to its constructor to make a connection to the PostgreSQL database server.

Update operation

The following PHP code shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −

Зачем нужна авторизация в postgresql

Прежде чем углубиться в вопрос

как

, сделаем шаг назад и зададимся вопросом,

зачем

использовать авторизацию в PostgreSQL.

Для любого приложения или веб-сайта, где пользователи проходят аутентификацию и имеют доступ к различному контенту или могут выполнять отличные друг от друга действия, вам необходима авторизация. При использовании фреймворков, таких, как Rails и Django, документация и комьюнити, как правило, советуют следующее: использовать одного пользователя БД с правами суперпользователя (или правами произвольного чтения/записи).

Затем авторизация реализуется в виде логики и правил в кодовой базе Rails/Django. Если вы добавляете смежные службы, которые работают с одними и теми же данными (например, очереди, background workers, cronjobs, хранилища данных), вам может потребоваться дублировать некоторую логику авторизации в этих службах, или эти службы могут использовать логику авторизации через разделяемые библиотеки или прямое включение в их код (что усложняет разработку, развертывание, продумывание архитектуры и безопасности).

Реализация авторизации в PostgreSQL позволяет определять правила доступа с одном месте (в базе данных), и эти правила буду последовательно применяться ко всем службам и приложениям, которые обращаются к данным в БД. Изучение и использование инструментов авторизации в базе данных естественным образом побуждает использовать отдельные роли с минимальными привилегиями, что ограничивает масштабы и серьезность атак и ошибок.

Еще одно преимущество использования PostgreSQL для авторизации: это мощный, хорошо протестированный инструмент, которые вы уже используете. Вам не нужно самостоятельно внедрять механизмы авторизации (подверженные ошибкам и отнимающие много времени) или заморачиваться с аудитом, интеграцией и обновлениями сторонних библиотек.

Несмотря на приведенные выше аргументы, использование авторизации PostgreSQL подходит не для всех и каждого проекта! Если у вас простое приложение с ограниченной областью действия или у вас аллергия на SQL или его (относительно примитивный) инструментарий, реализовать авторизацию в одном месте на удобном для вас языке, скорее всего, будет быстрее и проще.

Если вам нужна авторизация, которая охватывает множество источников данных и сервисов, вам может понадобиться что-то вроде Zanzibar. Создание авторизации с помощью PostgreSQL — это не панацея, но подход, который стоит рассмотреть в контексте вашего конкретного проекта и команды.

Привилегии

Права доступа на работу с объектами базы данных контролируются привилегиями, управляемыми с помощью команд

GRANTREVOKE

. Мы можем проверить привилегии в psql с помощью команды

dp

=> dp
                                      Access privileges
 Schema |         Name          |   Type   | Access privileges | Column privileges | Policies 
-------- ----------------------- ---------- ------------------- ------------------- ----------
 public | albums                | table    |                   |                   | 
 public | albums_album_id_seq   | sequence |                   |                   | 
 public | artists               | table    |                   |                   | 
 public | artists_artist_id_seq | sequence |                   |                   | 
 public | fan_follows           | table    |                   |                   | 
 public | fans                  | table    |                   |                   | 
 public | fans_fan_id_seq       | sequence |                   |                   |

dt

, мы видим, что «postgres» является владельцем всех таблиц:

=> dt
            List of relations
 Schema |    Name     | Type  |  Owner   
-------- ------------- ------- ----------
 public | albums      | table | postgres
 public | artists     | table | postgres
 public | fan_follows | table | postgres
 public | fans        | table | postgres

Давайте разрешим роли «artist» делать выборку из таблицы с исполнителями, а затем снова проверим привилегии:

Создание регистрации и управления пользователями на сайте с помощью php postgresql

Тестовая схема

Теперь, когда мы понимаем, почему мы можем захотеть использовать PostgreSQL для авторизации, рассмотрим, как нам это реализовать. В качестве примера возьмем приложение, похожие на

, где музыкальные исполнители могут публиковать альбомы, а фанаты — находить исполнителей и следить за ними. Возьмем схему «музыкальные исполнители и альбомы» из предыдущей

и немного скорректируем ее, добавив другой тип пользователя (фанаты) и удалив таблицы с «жанрами», чтобы сохранить схему простой и доступной.

Пример схемы с музыкальным исполнителям, альбомам и подписчикам (фанатам).